package restcontroller.sale.cashbill; 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 PayExportExcel { /** * 设置标题单元样式 * * @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);// 设置 账户名称 宽度 sheet.setColumnWidth((short) 2, (short) 5500);// 设置 金额 宽度 sheet.setColumnWidth((short) 3, (short) 5500);// 设置 分类 宽度 sheet.setColumnWidth((short) 4, (short) 5500);// 设置 分类明细 宽度 sheet.setColumnWidth((short) 5, (short) 5500);// 设置 单据日期 宽度 sheet.setColumnWidth((short) 6, (short) 5500);// 设置 单据日期 宽度 sheet.setColumnWidth((short) 7, (short) 11000);// 设置 备注 宽度 } /** * 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);// 设置 金额 宽度 sheet.setColumnWidth((short) 3, (short) 5500);// 设置 分类 宽度 sheet.setColumnWidth((short) 4, (short) 5500);// 设置 分类明细 宽度 sheet.setColumnWidth((short) 5, (short) 5500);// 设置 单据日期 宽度 sheet.setColumnWidth((short) 6, (short) 11000);// 设置 备注 宽度 sheet.setColumnWidth((short) 7, (short) 8000);// 设置 错误信息 宽度 sheet.setColumnWidth((short) 8, (short) 8000);// 设置 错误信息 宽度 } /** * 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); DataFormat format = workbook.createDataFormat(); cellStyle1.setDataFormat(format.getFormat("@")); cellStyle2.setDataFormat(format.getFormat("@")); cellStyle3.setDataFormat(format.getFormat("@")); cellStyle4.setDataFormat(format.getFormat("@")); /*第一行*/ sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); /*第一行塞值*/ row = sheet.createRow(0); cell = row.createCell(0);// ID cell.setCellStyle(cellStyle4); 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("备注"); /*第四行塞值*/ row = sheet.createRow(3); cell = row.createCell(0); cell.setCellStyle(cellStyle3); cell.setCellValue("0000"); 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("0"); cell = row.createCell(4); cell.setCellStyle(cellStyle3); cell.setCellValue("货款"); cell = row.createCell(5); cell.setCellStyle(cellStyle3); cell.setCellValue("分类明细"); cell = row.createCell(6); cell.setCellStyle(cellStyle3); cell.setCellValue("2024-07-01"); cell = row.createCell(7); cell.setCellStyle(cellStyle3); 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, 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("错误信息"); } }