Java POI根据模板生成Excel(xlsx)文件

package com; import java.io.FileOutputStream; import java.util.ArrayList; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CreationHelper; 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.usermodel.RichTextString; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class WritePreRegToExcel { private static BAReportStyle style; private static XSSFWorkbook wb; private static String Font_Arial = "Arial"; private static Map<String, List<PreRegReportDTO>> data = new LinkedHashMap<String, List<PreRegReportDTO>>(); static{ String[] regSourceType = new String[]{"Online", "buyer app", "Internal marketing"}; String[] regSources = new String[]{"Test site 001", "Test site 002", "Test site 003"}; for(String type : regSourceType){ for(int i = 0; i < 10; i++){ List<PreRegReportDTO> reportDto = new ArrayList<PreRegReportDTO>(); for(String s : regSources){ PreRegReportDTO dto = new PreRegReportDTO(); dto.setRegSource(s); reportDto.add(dto); } data.put(type, reportDto); } } } public static void main(String args[]) { wb = new XSSFWorkbook(); try { //or new HSSFWorkbook(); CreationHelper creationHelper = wb.getCreationHelper(); style = new BAReportStyle(wb); Sheet sheet = wb.createSheet("my sheet"); sheet.setDisplayGridlines(false); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 6 * 256); sheet.setColumnWidth(2, 12 * 256); sheet.setColumnWidth(3, 25 * 256); for(int i = 4; i <= 7; i++){ sheet.setColumnWidth(i, 12 * 256); } for(int i = 8; i <= 13; i++){ sheet.setColumnWidth(i, 9 * 256); } // title Row row0 = sheet.createRow((short) 0); RichTextString title = creationHelper.createRichTextString("Title here"); XSSFFont titleFont = style.getExcelBoldFont(Font_Arial, 12, IndexedColors.BLACK.getIndex()); title.applyFont(titleFont); row0.createCell(1).setCellValue(title); // title2 Row row1 = sheet.createRow(1); Cell cell_1_1 = row1.createCell(11); RichTextString title2 = creationHelper .createRichTextString("Can write something here."); XSSFFont title2Font = style.getExcelBoldFont(Font_Arial, 10, IndexedColors.RED.getIndex()); title2.applyFont(title2Font); cell_1_1.setCellValue(title2); // Define some common styles. XSSFCellStyle regSourceCellStyle = style.getBoldTitleStyle(Font_Arial, 10, IndexedColors.LIGHT_BLUE.getIndex(), HorizontalAlignment.LEFT); XSSFCellStyle totalCountCellStyle = style.getBoldTitleStyle(Font_Arial, 10, IndexedColors.BLACK.getIndex(), HorizontalAlignment.RIGHT); XSSFCellStyle contentCellStyle = style.getTitleStyle(Font_Arial, 11, IndexedColors.BLACK.getIndex(), HorizontalAlignment.RIGHT); XSSFCellStyle diffCellStyle = style.getTitleStyle(Font_Arial, 10, IndexedColors.GREY_50_PERCENT.getIndex(), HorizontalAlignment.RIGHT); XSSFCellStyle regSourceTypeCellStyle = style.getBoldTitleStyle(Font_Arial, 10, IndexedColors.BLACK.getIndex(), HorizontalAlignment.CENTER); regSourceTypeCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); regSourceTypeCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); int currentLine = 5; int totalMergeStartLine = 5 - 3; String[] showTypes = new String[]{"Type1", "Type2", "Type3", "Type4"}; short[] showColors = new short[]{IndexedColors.ORANGE.getIndex(), IndexedColors.LIGHT_BLUE.getIndex(), IndexedColors.INDIGO.getIndex(), IndexedColors.ROSE.getIndex()}; for(int t = 0; t < showTypes.length; t++){ // writing data to excel. XSSFCellStyle showTypeCellStyle = style.getBoldTitleStyle(Font_Arial, 10, IndexedColors.WHITE.getIndex(), HorizontalAlignment.CENTER); showTypeCellStyle.setFillForegroundColor(showColors[t]); showTypeCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); if(t == 0){ // write title to excel writeTitle(sheet, showTypeCellStyle, showTypes[t]); } for(Map.Entry<String, List<PreRegReportDTO>> entry : data.entrySet()){ int mergeStartLine = currentLine; for(int i = 0, size = entry.getValue().size(); i < size; i++){ PreRegReportDTO dto = entry.getValue().get(i); Row contentRow = sheet.createRow(currentLine); Cell contentCell_1 = contentRow.createCell(1); contentCell_1.setCellStyle(showTypeCellStyle); if(t > 0){ contentCell_1.setCellValue(showTypes[t]); } Cell contentCell_2 = contentRow.createCell(2); contentCell_2.setCellStyle(regSourceTypeCellStyle); if(i == 0){ contentCell_2.setCellValue(entry.getKey()); } Cell contentCell_3 = contentRow.createCell(3); contentCell_3.setCellStyle(regSourceCellStyle); contentCell_3.setCellValue(dto.getRegSource()); Cell contentCell_4 = contentRow.createCell(4); contentCell_4.setCellStyle(contentCellStyle); contentCell_4.setCellValue(dto.getHKCount()); Cell contentCell_5 = contentRow.createCell(5); contentCell_5.setCellStyle(contentCellStyle); contentCell_5.setCellValue(dto.getCNCount()); Cell contentCell_6 = contentRow.createCell(6); contentCell_6.setCellStyle(contentCellStyle); contentCell_6.setCellValue(dto.getNonCNCount()); Cell contentCell_7 = contentRow.createCell(7); contentCell_7.setCellStyle(totalCountCellStyle); contentCell_7.setCellValue(dto.getTotalCount()); Cell contentCell_8 = contentRow.createCell(8); contentCell_8.setCellStyle(diffCellStyle); contentCell_8.setCellValue(dto.getPast1ShowRegCntDiff()); Cell contentCell_9 = contentRow.createCell(9); contentCell_9.setCellStyle(diffCellStyle); contentCell_9.setCellValue(dto.getPast2ShowRegCntDiff()); Cell contentCell_10 = contentRow.createCell(10); contentCell_10.setCellStyle(diffCellStyle); contentCell_10.setCellValue(dto.getPast3ShowRegCntDiff()); Cell contentCell_11 = contentRow.createCell(11); contentCell_11.setCellStyle(diffCellStyle); contentCell_11.setCellValue(dto.getPast1ShowRegCnt()); Cell contentCell_12 = contentRow.createCell(12); contentCell_12.setCellStyle(diffCellStyle); contentCell_12.setCellValue(dto.getPast2ShowRegCnt()); Cell contentCell_13 = contentRow.createCell(13); contentCell_13.setCellStyle(diffCellStyle); contentCell_13.setCellValue(dto.getPast3ShowRegCnt()); currentLine++; } writeTotalRow(new PreRegReportDTO(), sheet, showTypeCellStyle, currentLine); sheet.addMergedRegion(new CellRangeAddress(mergeStartLine,currentLine, 2, 2)); currentLine++; } sheet.addMergedRegion(new CellRangeAddress(totalMergeStartLine,currentLine - 1, 1, 1)); currentLine++; totalMergeStartLine = currentLine; } // Write the output to a file FileOutputStream fileOut = new FileOutputStream("D://ooxml-cell.xlsx"); wb.write(fileOut); fileOut.flush(); fileOut.close(); } catch (Exception e) { e.printStackTrace(); } } private static void writeTitle(Sheet sheet, XSSFCellStyle showTypeCellStyle, String showType){ XSSFCellStyle cellStyle_black_title = st