欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-03-08 14:01:09
...

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

实现效果

  • SXSSFWorkbook超大数据导出
  • 标题、表头、内容有样式
  • 可以多个sheet(满65535行数据换新的sheet)
  • 一度为快
    • java利用poi实现Excel考勤报表的输出

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