java利用poi实现Excel考勤报表的输出

迷南。 2023-06-21 15:29 23阅读 0赞

# java利用poi实现Excel考勤报表的输出 #

### 实现效果 ###

*  SXSSFWorkbook超大数据导出
 *  标题、表头、内容有样式
 *  可以多个sheet（满65535行数据换新的sheet）
 *  一度为快
    
     *  ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VpZWllaTQzOA_size_16_color_FFFFFF_t_70][]

### maven依赖 ###

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.10</version>
        </dependency>

### 函数实现 ###

import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    
    import java.io.*;
    
    public class Testt {
        public static void test(String toFilePath, int total_records) {
            File file = new File(toFilePath);
            if (!file.exists()) {
                try {
                    file.createNewFile();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            String[] assetHeadTemp = {"姓名", "工号", "人员类型", "出勤天数", "休息天数", "2019-12-13"};
            SXSSFWorkbook wb = new SXSSFWorkbook(1024);
            Row row;
            OutputStream os = null;
            //样式
            CellStyle titleStyle = createTitleCellStyle(wb);
            CellStyle titleReportTimeCellStyle = createTitleReportTimeCellStyle(wb);
            CellStyle headerStyle = createHeadCellStyle(wb);
            CellStyle contentStyle = createContentCellStyle(wb);
            try {
                os = new FileOutputStream(file.getAbsolutePath());
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
            Sheet sheet;
    
            //确认sheet的个数--65534+表头=65535；一般的表格每个sheet支持65535row数据
            int temp_sheet_count = total_records / 65534;
            int sheet_count = (total_records % 65534) == 0 ? temp_sheet_count : temp_sheet_count + 1;
    
            for (int j = 0; j < sheet_count; j++) {
                sheet = wb.createSheet("汇总分页" + (j + 1));
                sheet.setDefaultRowHeight((short) 500);
                //创建标题
                row = sheet.createRow(0);
                row.setHeight((short) 800);
                Cell title_cell = row.createCell(0);
                title_cell.setCellValue("汇总报表  日期 2019-12-12 00:00:00 - 2019-12-13 00:00:00");
                title_cell.setCellStyle(titleStyle);
                sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, assetHeadTemp.length - 1));
    
                //创建生成时间
                row = sheet.createRow(1);
                row.setHeight((short) 600);
                Cell create_report_cell = row.createCell(0);
                create_report_cell.setCellValue("报表生成时间 2019-12-13 00:00:00");
                create_report_cell.setCellStyle(titleReportTimeCellStyle);
                sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, assetHeadTemp.length - 1));
    
                // 输出表头
                row = sheet.createRow(2);
                for (int i = 0; i < assetHeadTemp.length; i++) {
                    if (i > 4) {
                        sheet.setColumnWidth(i, 9532);
                    } else {
                        sheet.setColumnWidth(i, 4766);
                    }
                    Cell cell = row.createCell(i);
                    cell.setCellStyle(headerStyle);
                    cell.setCellValue(assetHeadTemp[i]);
                }
                // 输出内容
                int rowIndex = 3;
                //创建每个sheet的row数据
                int row_nums = 65534;
                if (j == sheet_count - 1) {
                    row_nums = total_records % 65534;
                }
    
                for (int n = 0; n < row_nums; n++) {
                    row = sheet.createRow(rowIndex++);
                    row.setHeight((short) 600);
    
                    Cell cell_0 = row.createCell(0);
                    cell_0.setCellStyle(contentStyle);
                    cell_0.setCellValue("0");
    
                    Cell cell_1 = row.createCell(1);
                    cell_1.setCellStyle(contentStyle);
                    cell_1.setCellValue("1");
    
                    Cell cell_2 = row.createCell(2);
                    cell_2.setCellStyle(contentStyle);
                    cell_2.setCellValue("2");
    
                    Cell cell_3 = row.createCell(3);
                    cell_3.setCellStyle(contentStyle);
                    cell_3.setCellValue("3");
    
                    Cell cell_4 = row.createCell(4);
                    cell_4.setCellStyle(contentStyle);
                    cell_4.setCellValue("4");
    
                    Cell cell_5 = row.createCell(5);
                    cell_5.setCellStyle(contentStyle);
                    cell_5.setCellValue("5");
                }
            }
            try {
                wb.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (os != null) {
                    try {
                        os.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (wb != null) {
                    try {
                        wb.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    
        private static CellStyle createTitleCellStyle(SXSSFWorkbook wb) {
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());//背景颜色
    
            Font headerFont1 = wb.createFont(); // 创建字体样式
            headerFont1.setBold(true); //字体加粗
            headerFont1.setColor(IndexedColors.GREEN.getIndex());
            headerFont1.setFontName("黑体"); // 设置字体类型
            headerFont1.setFontHeightInPoints((short) 12); // 设置字体大小
            cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
    
            return cellStyle;
        }
    
        private static CellStyle createTitleReportTimeCellStyle(SXSSFWorkbook wb) {
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());//背景颜色
    
            Font headerFont1 = wb.createFont(); // 创建字体样式
            headerFont1.setBold(true); //字体加粗
            headerFont1.setColor(IndexedColors.GREEN.getIndex());
            headerFont1.setFontName("黑体"); // 设置字体类型
            headerFont1.setFontHeightInPoints((short) 10); // 设置字体大小
            cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
    
            return cellStyle;
        }
    
        private static CellStyle createHeadCellStyle(SXSSFWorkbook wb) {
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setWrapText(true);// 设置自动换行
            cellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());//背景颜色
            cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
            cellStyle.setBorderRight(BorderStyle.THIN); //右边框
            cellStyle.setBorderTop(BorderStyle.THIN); //上边框
    
            Font headerFont = wb.createFont(); // 创建字体样式
            headerFont.setBold(true); //字体加粗
            headerFont.setFontName("黑体"); // 设置字体类型
            headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
            cellStyle.setFont(headerFont); // 为标题样式设置字体样式
    
            return cellStyle;
        }
    
        private static CellStyle createContentCellStyle(SXSSFWorkbook wb) {
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
            cellStyle.setWrapText(true);// 设置自动换行
    
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 12);
            cellStyle.setFont(font);
    
            return cellStyle;
        }
    
        public static void main(String[] args) {
            test("D:\\test.xlsx", 10);
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VpZWllaTQzOA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191213150447561.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VpZWllaTQzOA==,size_16,color_FFFFFF,t_70