package restcontroller.webmanage.sale.order; import java.awt.Color; import org.apache.poi.hssf.util.HSSFColor; 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.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; 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) 5500);// 设置 品号 宽度 sheet.setColumnWidth((short) 1, (short) 5500);// 设置 数量 宽度 } /** * 2022-07-14 17:41:39 * 设置表格宽度(返回错误Excel的样式) * **/ public static void setBatchDetailSheetColumn2(XSSFSheet sheet) { sheet.setDefaultRowHeight((short) 600); sheet.setColumnWidth((short) 0, (short) 5500);// 设置 品号 宽度 sheet.setColumnWidth((short) 1, (short) 5500);// 设置 数量 宽度 sheet.setColumnWidth((short) 2, (short) 5500);// 设置 数量 宽度 } /** * 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, 1)); /*第一行塞值*/ row = sheet.createRow(0); cell = row.createCell(0);// ID cell.setCellStyle(cellStyle4); cell.setCellValue("订单导入"); /*第二行*/ sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1)); /*第二行塞值*/ 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("数量（必填）"); /*第四行塞值*/ row = sheet.createRow(3); cell = row.createCell(0); cell.setCellStyle(cellStyle3); cell.setCellValue("test123"); cell = row.createCell(1); cell.setCellStyle(cellStyle3); cell.setCellValue("10"); } /** * 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, 2)); /*第一行塞值*/ row = sheet.createRow(0); cell = row.createCell(0);// ID cell.setCellStyle(cellStyle3); cell.setCellValue("订单导入"); /*第二行*/ sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2)); /*第二行塞值*/ 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("错误信息"); } }