1. package com.sgcc.lms.easypoi.util;
    3. import org.apache.poi.ss.usermodel.*;
    4. import org.apache.poi.ss.util.CellRangeAddress;
    5. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    7. import java.io.FileOutputStream;
    8. import java.util.*;
    10. /**
    11.  * @Author: 李孟帅
    12.  * @CreateTime: 2021/5/11$ 10:35$
    13.  * @Description: TODO
    14.  */
    15. public class Poi {
    17.     private static CellStyle createTitleCellStyle(Workbook workbook) {
    18.         CellStyle cellStyle = workbook.createCellStyle();
    19.         cellStyle.setAlignment(HorizontalAlignment.CENTER);
    20.         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    22.         cellStyle.setBorderBottom(BorderStyle.THIN);
    23.         cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
    24.         cellStyle.setBorderLeft(BorderStyle.THIN);
    25.         cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
    26.         cellStyle.setBorderRight(BorderStyle.THIN);
    27.         cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
    28.         cellStyle.setBorderTop(BorderStyle.THIN);
    29.         cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
    31.         cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);
    32.         cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    34.         return cellStyle;
    35.     }
    37.     /**
    38.      * 使用 SXSSFWorkbook 对象实现excel导出
    39.      * (一般是导出百万级数据的excel)
    40.      */
    41.     public void exportBigDataExcel() {
    42.         long startTime = System.currentTimeMillis();    //开始时间
    43.         System.out.println("start execute time: " + new Date(startTime));
    45.         // 1.创建工作簿
    46.         // 阈值，内存中的对象数量最大值，超过这个值会生成一个临时文件存放到硬盘中
    47.         SXSSFWorkbook wb = new SXSSFWorkbook(1000);
    49.         //2.在Workbook中添加一个sheet,对应Excel文件中的sheet
    50.         Sheet sheet = wb.createSheet();
    52.         //3.设置样式以及字体样式
    53.         CellStyle titleCellStyle = createTitleCellStyle(wb);
    54.         CellStyle headCellStyle = createHeadCellStyle(wb);
    55.         CellStyle cellStyle = createCellStyle(wb);
    57.         //4.创建标题、表头，内容和合并单元格等操作
    58.         int rowNum = 0;// 行号
    59.         // 创建第一行，索引从0开始
    60.         Row row0 = sheet.createRow(rowNum++);
    61.         row0.setHeight((short) 800);// 设置行高
    63.         String title = "这里是标题标题标题";
    64.         Cell c00 = row0.createCell(0);
    65.         c00.setCellValue(title);
    66.         c00.setCellStyle(titleCellStyle);
    67.         // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
    68.         sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));//标题合并单元格操作，6为总列数
    70.         // 第二行
    71.         Row row1 = sheet.createRow(rowNum++);
    72.         row1.setHeight((short) 500);
    73.         String[] row_first = {"填表单位：", "", "", "", "", " xxxx年第x季度 ", ""};
    74.         for (int i = 0; i < row_first.length; i++) {
    75.             Cell tempCell = row1.createCell(i);
    76.             tempCell.setCellStyle(headCellStyle);
    77.             if (i == 0) {
    78.                 tempCell.setCellValue(row_first[i] + "测试单位");
    79.             } else if (i == 5) {
    80.                 tempCell.setCellStyle(headCellStyle);
    81.                 tempCell.setCellValue(row_first[i]);
    82.             } else {
    83.                 tempCell.setCellValue(row_first[i]);
    84.             }
    85.         }
    87.         // 合并
    88.         sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
    89.         sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 6));
    91.         //第三行
    92.         Row row2 = sheet.createRow(rowNum++);
    93.         row2.setHeight((short) 700);
    94.         String[] row_second = {"名称", "采集情况", "", "", "登记情况", "", "备注"};
    95.         for (int i = 0; i < row_second.length; i++) {
    96.             Cell tempCell = row2.createCell(i);
    97.             tempCell.setCellValue(row_second[i]);
    98.             tempCell.setCellStyle(headCellStyle);
    99.         }
    101.         // 合并
    102.         sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));//名称
    103.         sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 3));//人数情况
    104.         sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 5));//登记情况
    105.         sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));//备注
    107.         //第三行
    108.         Row row3 = sheet.createRow(rowNum++);
    109.         row3.setHeight((short) 700);
    110.         String[] row_third = {"", "登记数(人)", "办证总数(人)", "办证率(%)", "登记户数(户)", "登记时间", ""};
    111.         for (int i = 0; i < row_third.length; i++) {
    112.             Cell tempCell = row3.createCell(i);
    113.             tempCell.setCellValue(row_third[i]);
    114.             tempCell.setCellStyle(headCellStyle);
    115.         }
    117.         // 数据处理(创建100万条测试数据)
    118.         List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
    119.         for (int i = 0; i < 999999; i++) {
    120.             Map<String, Object> map = new HashMap<String, Object>();
    121.             map.put("name", "测试名称" + i);
    122.             map.put("r1", "111");
    123.             map.put("r2", "222");
    124.             map.put("r3", "333");
    125.             map.put("r4", "444");
    126.             map.put("addTime", new Date());
    127.             map.put("r6", "这里是备注" + i);
    128.             dataList.add(map);
    129.         }
    131.         for (Map<String, Object> excelData : dataList) {
    132.             Row tempRow = sheet.createRow(rowNum++);
    133.             tempRow.setHeight((short) 500);
    134.             // 循环单元格填入数据
    135.             for (int j = 0; j < 7; j++) {
    136.                 Cell tempCell = tempRow.createCell(j);
    137.                 tempCell.setCellStyle(cellStyle);
    138.                 String tempValue;
    139.                 if (j == 0) {
    140.                     // 乡镇、街道名称
    141.                     tempValue = excelData.get("name").toString();
    142.                 } else if (j == 1) {
    143.                     // 登记数（人）
    144.                     tempValue = excelData.get("r1").toString();
    145.                 } else if (j == 2) {
    146.                     // 办证总数（人）
    147.                     tempValue = excelData.get("r2").toString();
    148.                 } else if (j == 3) {
    149.                     // 办证率（%）
    150.                     tempValue = excelData.get("r3").toString();
    151.                 } else if (j == 4) {
    152.                     // 登记户数（户）
    153.                     tempValue = excelData.get("r4").toString();
    154.                 } else if (j == 5) {
    155.                     // 登记日期
    156.                     tempValue = excelData.get("addTime").toString();
    157.                 } else {
    158.                     // 备注
    159.                     tempValue = excelData.get("r6").toString();
    160.                 }
    161.                 tempCell.setCellValue(tempValue);
    163. //                sheet.autoSizeColumn(j);// 根据内容自动调整列宽，
    164.             }
    165.         }
    166.         //设置列宽，必须在单元格设值以后进行
    167.         sheet.setColumnWidth(0, 4000);//名称
    168.         sheet.setColumnWidth(1, 3000);//登记数(人)
    169.         sheet.setColumnWidth(2, 3000);//办证总数(人)
    170.         sheet.setColumnWidth(3, 3000);//办证率(%)
    171.         sheet.setColumnWidth(4, 3000);//登记户数(户)
    172.         sheet.setColumnWidth(5, 6000);//登记时间
    173.         sheet.setColumnWidth(6, 4000);//备注
    175.         // 注释行
    176.         Row remark = sheet.createRow(rowNum++);
    177.         remark.setHeight((short) 500);
    178.         String[] row_remark = {"注：表中的“办证率=办证总数÷登记数×100%”", "", "", "", "", "", ""};
    179.         for (int i = 0; i < row_remark.length; i++) {
    180.             Cell tempCell = remark.createCell(i);
    181.             if (i == 0) {
    182.                 tempCell.setCellStyle(headCellStyle);
    183.             } else {
    184.                 tempCell.setCellStyle(cellStyle);
    185.             }
    186.             tempCell.setCellValue(row_remark[i]);
    187.         }
    188.         int remarkRowNum = dataList.size() + 4;
    189.         sheet.addMergedRegion(new CellRangeAddress(remarkRowNum, remarkRowNum, 0, 6));//注释行合并单元格
    191.         // 尾行
    192.         Row foot = sheet.createRow(rowNum++);
    193.         foot.setHeight((short) 500);
    194.         String[] row_foot = {"审核人：", "", "填表人：", "", "填表时间：", "", ""};
    195.         for (int i = 0; i < row_foot.length; i++) {
    196.             Cell tempCell = foot.createCell(i);
    197.             tempCell.setCellStyle(cellStyle);
    198.             if (i == 0) {
    199.                 tempCell.setCellValue(row_foot[i] + "张三");
    200.             } else if (i == 2) {
    201.                 tempCell.setCellValue(row_foot[i] + "李四");
    202.             } else if (i == 4) {
    203.                 tempCell.setCellValue(row_foot[i] + "xxxx");
    204.             } else {
    205.                 tempCell.setCellValue(row_foot[i]);
    206.             }
    207.         }
    208.         int footRowNum = dataList.size() + 5;
    209.         // 注
    210.         sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 0, 1));
    211.         sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 2, 3));
    212.         sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 4, 6));
    215.         long finishedTime = System.currentTimeMillis();    //处理完成时间
    216.         System.out.println("finished execute  time: " + (finishedTime - startTime) / 1000 + "s");
    218.         //导出
    219.         String fileName = "C:\\Users\\lms\\Desktop\\报表文件名称.xlsx";
    220.         try {
    221.             FileOutputStream stream = new FileOutputStream(fileName);
    222.             wb.write(stream);// 将数据写出去
    223.             wb.close();
    224.             stream.close();
    225.             long stopTime = System.currentTimeMillis();        //写文件时间
    226.             System.out.println("write xlsx file time: " + (stopTime - startTime) / 1000 + "s");
    227.         } catch (Exception e) {
    228.             e.printStackTrace();
    229.         }
    231.     }
    233.     private static CellStyle createHeadCellStyle(Workbook workbook) {
    234.         CellStyle cellStyle = workbook.createCellStyle();
    235.         cellStyle.setAlignment(HorizontalAlignment.CENTER);
    236.         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    238.         cellStyle.setBorderBottom(BorderStyle.THIN);
    239.         cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
    240.         cellStyle.setBorderLeft(BorderStyle.THIN);
    241.         cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
    242.         cellStyle.setBorderRight(BorderStyle.THIN);
    243.         cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
    244.         cellStyle.setBorderTop(BorderStyle.THIN);
    245.         cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
    247.         cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
    248.         cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    250.         return cellStyle;
    251.     }
    253.     private static CellStyle createCellStyle(Workbook workbook) {
    254.         CellStyle cellStyle = workbook.createCellStyle();
    255.         cellStyle.setAlignment(HorizontalAlignment.CENTER);
    256.         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    258.         cellStyle.setBorderBottom(BorderStyle.THIN);
    259.         cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
    260.         cellStyle.setBorderLeft(BorderStyle.THIN);
    261.         cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
    262.         cellStyle.setBorderRight(BorderStyle.THIN);
    263.         cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
    264.         cellStyle.setBorderTop(BorderStyle.THIN);
    265.         cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
    267.         return cellStyle;
    268.     }
    270.     public static void main(String[] args) {
    271.         Poi poi = new Poi();
    272.         poi.exportBigDataExcel();
    273.     }
    274. }