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

实现效果

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

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);
    }
}