md_YOS/src/custom/restcontroller/webmanage/sale/aftersalesbom/ExportExcel_pj.java

package restcontroller.webmanage.sale.aftersalesbom;


import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.YosException;
import common.data.Rows;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.util.Objects;

public class ExportExcel_pj {

    /**
     * 2022-07-14 17:41:39
     * 设置表格宽度（导入模板）
     **/
    public static void setBatchDetailSheetColumn1(XSSFSheet sheet) {
        sheet.setDefaultRowHeight((short) 600);
        short width = 3500;
        sheet.setColumnWidth((short) 0, 4000);
        sheet.setColumnWidth((short) 1, 4000);
        for (int i = 1; i < 20; i++) {
            sheet.setColumnWidth((short) i, width);
        }

    }


    /**
     * 2022-07-14 17:41:39
     * 设置表格宽度(返回错误Excel的样式)
     **/
    public static void setBatchDetailSheetColumn2(XSSFSheet sheet) {
        sheet.setDefaultRowHeight((short) 600);
        short width = 3000;
        sheet.setColumnWidth((short) 0, 4000);
        sheet.setColumnWidth((short) 1, 4000);
        for (int i = 1; i < 18; i++) {
            sheet.setColumnWidth((short) i, width);
        }
        sheet.setColumnWidth((short) 20, width * 2);

    }

    /**
     * 2022-07-14 17:42:03
     * 设置表头
     * cellStyle1 中文提示信息样式
     * cellStyle2 标题提示信息样式
     * cellStyle3 正文提示信息样式
     **/
    public static void batchDetail(XSSFSheet sheet, XSSFCellStyle cellStyle1, XSSFCellStyle cellStyle2, XSSFCellStyle cellStyle3, XSSFWorkbook workbook, Rows rows) {
//        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);

        cellStyle1.setDataFormat(workbook.createDataFormat().getFormat("TEXT"));
        cellStyle2.setDataFormat(workbook.createDataFormat().getFormat("TEXT"));
        cellStyle3.setDataFormat(workbook.createDataFormat().getFormat("TEXT"));
        /*第一行*/
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 18));

        /*第一行塞值*/
        row = sheet.createRow(0);
        cell = row.createCell(0);// ID
        cell.setCellStyle(cellStyle1);
        cell.setCellValue("注意：①；最多不超过5000行；②导入前，请记得删除示例行！");

        /*第二行塞值*/
        row = sheet.createRow(1);

        cell = row.createCell(0);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("bom名称");

        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("层级");
        int j=0;
        for (int i = 1; i < 6; i++) {
            cell = row.createCell( 4+j);
            cell.setCellStyle(cellStyle2);
            cell.setCellValue("物料号"+i);

            cell = row.createCell( 5+j);
            cell.setCellStyle(cellStyle2);
            cell.setCellValue("启用时间"+i);

            cell = row.createCell( 6+j);
            cell.setCellStyle(cellStyle2);
            cell.setCellValue("停用时间"+i);
            j=j+3;
        }
        int a=2;
        for(common.data.Row bom : rows){
            String bomname = bom.getString("bomname");
            String module = bom.getString("module");
            String component = bom.getString("component");
            String accessorie = bom.getString("accessorie");
            /*第三行塞值*/
            row = sheet.createRow(a);

            cell = row.createCell(0);
            //cell.setCellStyle(cellStyle3);
            cell.setCellValue(bomname);

            cell = row.createCell(1);
            //cell.setCellStyle(cellStyle3);
            cell.setCellValue(module);

            cell = row.createCell(2);
           //cell.setCellStyle(cellStyle3);
            cell.setCellValue(component);

            cell = row.createCell(3);
            //cell.setCellStyle(cellStyle3);
            cell.setCellValue(accessorie);
            a++;
        }
        //按范围合并单元格
        for(int i = 0; i < 4; i++){
            mergeSameCellContentColumn(sheet, 2, i);
        }



//        for (int i = 1; i < 18; i++) {
//            cell = row.createCell(i);
//            cell.setCellStyle(cellStyle3);
//            cell.setCellValue("0");
//
//        }


    }


    /**
     * 2022-07-14 17:42:03
     * 设置表头
     **/
    public static void batchDetailErr(XSSFSheet sheet, XSSFCellStyle cellStyle1, XSSFCellStyle cellStyle2, 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, 19));//2.提示信息

        /*第一行塞值*/
        row = sheet.createRow(0);
        cell = row.createCell(0);// ID
        cell.setCellStyle(cellStyle1);
        cell.setCellValue("注意：①最多不超过5000行；②导入前，请记得删除示例行！");


        /*第二行塞值*/
        row = sheet.createRow(1);

        cell = row.createCell(0);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue("bom名称");

        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("层级");
        int j=0;
        for (int i = 1; i < 6; i++) {
            cell = row.createCell( 4+j);
            cell.setCellStyle(cellStyle2);
            cell.setCellValue("物料号"+i);

            cell = row.createCell( 5+j);
            cell.setCellStyle(cellStyle2);
            cell.setCellValue("启用时间"+i);

            cell = row.createCell( 6+j);
            cell.setCellStyle(cellStyle2);
            cell.setCellValue("停用时间"+i);
            j=j+3;
        }

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

        //按范围合并单元格
        for(int i = 0; i < 4; i++){
            mergeSameCellContentColumn(sheet, 2, i);
        }


    }

    public static void setBatchDetailSheetColumn3(XSSFSheet sheet) {
        sheet.setDefaultRowHeight((short) 700);
        short width = 3500;
        sheet.setColumnWidth((short) 0, 4000);
        sheet.setColumnWidth((short) 1, 4000);
        for (int i = 1; i < 20; i++) {
            sheet.setColumnWidth((short) i, width);
        }

    }

    /**
     * 合并指定Excel sheet页、指定列中连续相同内容的单元格
     *
     * @param sheet    Excel sheet
     * @param startRow 从第几行开始， startRow的值从1开始
     * @param column   指定列
     */
    public static void mergeSameCellContentColumn(Sheet sheet, int startRow, int column) {
        int totalRows = sheet.getLastRowNum();
        int firstRow = 0;
        int lastRow = 0;
        // 上一次比较是否相同
        boolean isPrevCompareSame = false;
        String prevMergeAddress = null;
        String currentMergeAddress;
        // 从第几开始判断是否相同
        if (totalRows >= startRow) {
            for (int i = startRow; i <= totalRows; i++) {
                String lastRowCellContent = sheet.getRow(i - 1).getCell(column).getStringCellValue();
                String curRowCellContent = sheet.getRow(i).getCell(column).getStringCellValue();
                if (curRowCellContent.equals(lastRowCellContent)) {
                    if (!isPrevCompareSame) {
                        firstRow = i - 1;
                    }
                    lastRow = i;
                    isPrevCompareSame = true;
                } else {
                    isPrevCompareSame = false;
                    currentMergeAddress = firstRow + lastRow + column + column + "";
                    if (lastRow > firstRow && !Objects.equals(currentMergeAddress, prevMergeAddress)) {
                        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, column, column));
                        prevMergeAddress = currentMergeAddress;
                    }
                }
                // 最后一行时判断是否有需要合并的行
                if ((i == totalRows) && (lastRow > firstRow)) {
                    currentMergeAddress = firstRow + lastRow + column + column + "";
                    if (!Objects.equals(currentMergeAddress, prevMergeAddress)) {
                        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, column, column));
                    }
                }
            }
        }
    }


}