md_YOS/src/custom/restcontroller/webmanage/sale/u8prodprice/ExportExcel.java

package restcontroller.webmanage.sale.u8prodprice;


import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.awt.Color;

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 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 createTitleCellStyle2(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 createBodyCellStyle4(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.CENTER);
        return cellStyle;
    }

    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;
    }

    /**
     * 2022-07-14 17:41:39
     * 设置表格宽度（导入模板）
     **/
    public static void setBatchDetailSheetColumn1(XSSFSheet sheet) {
        sheet.setDefaultRowHeight((short) 600);
        sheet.setColumnWidth((short) 0, (short) 5000);
        sheet.setColumnWidth((short) 1, (short) 5000);
        sheet.setColumnWidth((short) 2, (short) 5000);
    }


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

    /**
     * 2022-07-14 17:42:03
     * 设置表头
     * cellStyle1 中文提示信息样式
     * cellStyle2 标题提示信息样式
     * cellStyle3 正文提示信息样式
     **/
    public static void batchDetail(XSSFSheet sheet, XSSFCellStyle cellStyle1, XSSFCellStyle cellStyle2, XSSFCellStyle cellStyle3, XSSFCellStyle cellStyle4, XSSFWorkbook xssfFWorkbook) {
//        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);
        DataFormat format = xssfFWorkbook.createDataFormat();
        cellStyle1.setDataFormat(format.getFormat("@"));
        cellStyle2.setDataFormat(format.getFormat("@"));
        cellStyle3.setDataFormat(format.getFormat("@"));
        cellStyle4.setDataFormat(format.getFormat("@"));
        cellStyle1.setDataFormat(xssfFWorkbook.createDataFormat().getFormat("TEXT"));
        cellStyle2.setDataFormat(xssfFWorkbook.createDataFormat().getFormat("TEXT"));
        cellStyle3.setDataFormat(xssfFWorkbook.createDataFormat().getFormat("TEXT"));
        cellStyle4.setDataFormat(xssfFWorkbook.createDataFormat().getFormat("TEXT"));

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

        /*第一行塞值*/
        row = sheet.createRow(0);

        cell = row.createCell(0);// ID
        cell.setCellStyle(cellStyle1);
        cell.setCellValue("u8产品入库成本明细导入");

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

        cell = row.createCell(0);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("erp仓库编号");

        cell = row.createCell(1);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("品号(必填)");

        cell = row.createCell(2);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("成本单价");

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

        cell = row.createCell(0);
        cell.setCellStyle(cellStyle4);
        cell.setCellValue("测试erp仓库编号");

        cell = row.createCell(1);
        cell.setCellStyle(cellStyle4);
        cell.setCellValue("测试品号");

        cell = row.createCell(2);
        cell.setCellStyle(cellStyle4);
        cell.setCellValue("测试成本单价");

    }


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

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

        cell = row.createCell(0);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("erp仓库编号");

        cell = row.createCell(1);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("品号(必填)");

        cell = row.createCell(2);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("成本单价");

        cell = row.createCell(3);
        cell.setCellStyle(cellStyle3);
        cell.setCellValue("错误信息");

    }
}