cucu_YOS/src/custom/restcontroller/webmanage/sale/order/ExportExcel.java

package restcontroller.webmanage.sale.order;


import java.awt.Color;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExportExcel {
	 /**
     * 设置标题单元样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createTitleCellStyle1(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setColor(IndexedColors.RED.getIndex());
        font.setFontName("微软雅黑");// 设置标题字体
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);  //自动换行
        cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);// 设置列标题样式
        XSSFColor colorGrey = new XSSFColor(new Color(217, 225, 242));
        cellStyle.setFillForegroundColor(colorGrey);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.LEFT); // 居左
        return cellStyle;
    }
    /**
     * 设置中文提示信息样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createTitleCellStyle2(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontName("微软雅黑");// 设置标题字体
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);  //自动换行
        cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);// 设置列标题样式
        XSSFColor colorBlue = new XSSFColor(new Color(48, 84, 150));
        cellStyle.setFillForegroundColor(colorBlue);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.LEFT); // 居左
        return cellStyle;
    }
    /**
     * 设置中文提示信息样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createTitleCellStyle3(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontName("微软雅黑");// 设置标题字体
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);  //自动换行
        cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);// 设置列标题样式
        XSSFColor colorBlue = new XSSFColor(new Color(48, 84, 150));
        cellStyle.setFillForegroundColor(colorBlue);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居左
        return cellStyle;
    }

    /**
     * 设置正文单元样式
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createBodyCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
		font.setFontName("微软雅黑");// 设置标题字体
       // font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体
        cellStyle.setFont(font);
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT); // 居左
        return cellStyle;
    } 
     /**
     * 2022-07-14 17:41:39
               * 设置表格宽度（导入模板）
     * **/
    public static void setBatchDetailSheetColumn1(XSSFSheet sheet) {
        sheet.setDefaultRowHeight((short) 600);
        sheet.setColumnWidth((short) 0, (short) 5500);// 设置 品号 宽度
        sheet.setColumnWidth((short) 1, (short) 5500);// 设置 数量 宽度
    }
    
    
    /**
     * 2022-07-14 17:41:39
              * 设置表格宽度(返回错误Excel的样式)
     * **/
    public static void setBatchDetailSheetColumn2(XSSFSheet sheet) {
        sheet.setDefaultRowHeight((short) 600);
        sheet.setColumnWidth((short) 0, (short) 5500);// 设置 品号 宽度
        sheet.setColumnWidth((short) 1, (short) 5500);// 设置 数量 宽度  
        sheet.setColumnWidth((short) 2, (short) 5500);// 设置 数量 宽度  
    }
    /**
     * 2022-07-14 17:42:03
     * 设置表头
     * cellStyle1 中文提示信息样式
     * cellStyle2 标题提示信息样式
     * cellStyle3 正文提示信息样式
     * **/
    public static void batchDetail(XSSFSheet sheet, XSSFCellStyle cellStyle1, XSSFCellStyle cellStyle2, XSSFCellStyle cellStyle3, XSSFCellStyle cellStyle4,XSSFWorkbook workbook) {
//        HSSFCellStyle bcs = ExportExcel.createTitleCellStyle1(workbook);
//        bcs.setBorderBottom(BorderStyle.THIN); //下边框
//        bcs.setBorderLeft(BorderStyle.THIN);//左边框
//        bcs.setBorderTop(BorderStyle.THIN);//上边框
//        bcs.setBorderRight(BorderStyle.THIN);//右边框
//        bcs.setWrapText(true);


        XSSFRow row = null;
        XSSFCell cell = null;

        cellStyle1.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle1.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle1.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle1.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle1.setWrapText(true);
        
        cellStyle2.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle2.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle2.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle2.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle2.setWrapText(true);

   	 /*第一行*/
       sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));

       /*第一行塞值*/
       row = sheet.createRow(0);
       cell = row.createCell(0);// ID
       cell.setCellStyle(cellStyle4);
       cell.setCellValue("订单导入");
       /*第二行*/
       sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
       /*第二行塞值*/
       row = sheet.createRow(1);
       cell = row.createCell(0);// ID
       cell.setCellStyle(cellStyle1);
       cell.setCellValue("注意：①请按照以下示例填写订单；②订单商品最多不超过5000行；③订单导入前，请记得删除示例行！");
       /*第三行塞值*/
       row = sheet.createRow(2);

       cell = row.createCell(0);
       cell.setCellStyle(cellStyle2);
       cell.setCellValue(" 商品编号（必填）");

       cell = row.createCell(1);
       cell.setCellStyle(cellStyle2);
       cell.setCellValue("数量（必填）");
       

        /*第四行塞值*/
        row = sheet.createRow(3);

        cell = row.createCell(0);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("test123");

        cell = row.createCell(1);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("10");
        
    }
    
    
    /**
     * 2022-07-14 17:42:03
     * 设置表头
     * **/
    public static void batchDetailErr(XSSFSheet sheet, XSSFCellStyle cellStyle1,XSSFCellStyle cellStyle2,XSSFCellStyle cellStyle3,XSSFWorkbook workbook) {
//        HSSFCellStyle bcs = ExportExcel.createTitleCellStyle1(workbook);
//        bcs.setBorderBottom(BorderStyle.THIN); //下边框
//        bcs.setBorderLeft(BorderStyle.THIN);//左边框
//        bcs.setBorderTop(BorderStyle.THIN);//上边框
//        bcs.setBorderRight(BorderStyle.THIN);//右边框
//        bcs.setWrapText(true);


        XSSFRow row = null;
        XSSFCell cell = null;

        cellStyle1.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle1.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle1.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle1.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle1.setWrapText(true);
        
        

      	 /*第一行*/
          sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

          /*第一行塞值*/
          row = sheet.createRow(0);
          cell = row.createCell(0);// ID
          cell.setCellStyle(cellStyle3);
          cell.setCellValue("订单导入");
          /*第二行*/
          sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2));
          /*第二行塞值*/
          row = sheet.createRow(1);
          cell = row.createCell(0);// ID
          cell.setCellStyle(cellStyle1);
          cell.setCellValue("注意：①请按照以下示例填写订单；②订单商品最多不超过5000行；③订单导入前，请记得删除示例行！");
          /*第三行塞值*/
          row = sheet.createRow(2);

          cell = row.createCell(0);
          cell.setCellStyle(cellStyle2);
          cell.setCellValue("商品编码（必填）");

          cell = row.createCell(1);
          cell.setCellStyle(cellStyle2);
          cell.setCellValue("数量");

          cell = row.createCell(2);
          cell.setCellStyle(cellStyle2);
          cell.setCellValue("错误信息");         
      
    }
}