CUCU2/src/custom/restcontroller/webmanage/sale/itempriceadjust/ExportExcel.java

package restcontroller.webmanage.sale.itempriceadjust;


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.*;

import java.awt.*;

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) 8500);
        sheet.setColumnWidth((short) 1, (short) 8500);
        sheet.setColumnWidth((short) 2, (short) 8500);
        sheet.setColumnWidth((short) 3, (short) 8500);
        sheet.setColumnWidth((short) 4, (short) 8500);
        sheet.setColumnWidth((short) 5, (short) 8500);
        sheet.setColumnWidth((short) 6, (short) 8500);
        sheet.setColumnWidth((short) 7, (short) 8500);

    }


    /**
     * 2022-07-14 17:41:39
     * 设置表格宽度(返回错误Excel的样式)
     **/
    public static void setBatchDetailSheetColumn2(XSSFSheet sheet) {
        sheet.setDefaultRowHeight((short) 600);
        sheet.setColumnWidth((short) 0, (short) 8500);
        sheet.setColumnWidth((short) 1, (short) 8500);
        sheet.setColumnWidth((short) 2, (short) 8500);
        sheet.setColumnWidth((short) 3, (short) 8500);
        sheet.setColumnWidth((short) 4, (short) 8500);
        sheet.setColumnWidth((short) 5, (short) 8500);
        sheet.setColumnWidth((short) 6, (short) 8500);
        sheet.setColumnWidth((short) 7, (short) 8500);
        sheet.setColumnWidth((short) 8, (short) 8500);

    }

    /**
     * 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, 6));

        /*第一行塞值*/
        row = sheet.createRow(0);
        cell = row.createCell(0);// ID
        cell.setCellStyle(cellStyle4);
        cell.setCellValue("商品调价单导入");
        /*第二行*/
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
        /*第二行塞值*/
        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("一级调整价(必填,价格不变时填原价)");

        cell = row.createCell(3);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("二级调整价(必填,价格不变时填原价)");

        cell = row.createCell(4);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("三级调整价(必填,价格不变时填原价)");

        cell = row.createCell(5);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("四级调整价(必填,价格不变时填原价)");

        cell = row.createCell(6);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("五级调整价(必填,价格不变时填原价)");

        cell = row.createCell(7);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("亲民价(非必填)");


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

        cell = row.createCell(0);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("NBNTO-28571530003");

        cell = row.createCell(1);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("备注");

        cell = row.createCell(2);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("100");

        cell = row.createCell(3);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("100");

        cell = row.createCell(4);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("100");

        cell = row.createCell(5);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("100");

        cell = row.createCell(6);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("100");

        cell = row.createCell(7);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("100");


    }


    /**
     * 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, 7));

        /*第一行塞值*/
        row = sheet.createRow(0);
        cell = row.createCell(0);// ID
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("商品调价单导入");
        /*第二行*/
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
        /*第二行塞值*/
        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("一级调整价(必填,价格不变时填原价)");

        cell = row.createCell(3);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("二级调整价(必填,价格不变时填原价)");

        cell = row.createCell(4);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("三级调整价(必填,价格不变时填原价)");

        cell = row.createCell(5);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("四级调整价(必填,价格不变时填原价)");

        cell = row.createCell(6);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("五级调整价(必填,价格不变时填原价)");

        cell = row.createCell(7);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("亲民价(非必填)");

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

    }
}