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("错误信息"); } }