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