Java 使用 Apache POI 创建 Excel 工作簿 - 劳务费明细表

public Workbook createWorkbook() {
    // 常量和变量
    private static final int COLUMN_COUNT = 11;
    private static final String[] HEADER_NAMES = {"序号", "姓名", "身份证号码", "单位名称", "电话号码",
        "银行所在省市", "开户银行及支行", "银行卡号", "应发含税金额（税前）", "个税", "实发税后金额（税后）"};
    private static final int[] COLUMN_WIDTHS = {25, 12, 20, 25, 15, 15, 20, 20, 12, 12, 12};

    try (Workbook workbook = new XSSFWorkbook()) {
        // 创建一个Sheet对象
        Sheet sheet = workbook.createSheet("劳务费明细");

        // 设置列宽度
        for (int i = 0; i < COLUMN_COUNT; i++) {
            sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
        }

        // 创建一个行对象，并设置第一行的高度
        Row row = sheet.createRow(0);
        row.setHeightInPoints(50);

        // 设置标题行样式
        CellStyle titleCellStyle = createCellStyle(workbook, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Font.COLOR_NORMAL, true, (short) 18);

        // 合并单元格并设置单元格内容和样式
        Cell cell = createCell(row, 0, "XX基金会专家劳务报酬明细表", titleCellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10)); // 合并单元格

        // 设置剩余的单元格内容
        for (int i = 1; i <= 10; i++) {
            createCell(row, i, "", titleCellStyle);
        }

        // 设置项目名称和项目日期
        Row row2 = sheet.createRow(1);
        Row row3 = sheet.createRow(2);
        createCell(row2, 0, "项目名称：基金会合作项目", createCellStyle(workbook));
        createCell(row3, 0, "项目日期： 2023-3-13", createCellStyle(workbook));

        // 创建表头行
        Row headerRow = sheet.createRow(3);
        headerRow.setHeightInPoints(40);
        CellStyle headerCellStyle = createCellStyle(workbook, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, true);

        // 创建表头单元格并设置样式和值
        for (int i = 0; i < COLUMN_COUNT; i++) {
            createCell(headerRow, i, HEADER_NAMES[i], headerCellStyle);
        }

        // 创建数据行
        CellStyle dataCellStyle = createCellStyle(workbook, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
        for (int i = 0; i < 15; i++) {
            Row dataRow = sheet.createRow(4 + i);
            dataRow.setHeightInPoints(30);
            for (int j = 0; j < COLUMN_COUNT; j++) {
                createCell(dataRow, j, "数据" + i + "-" + j, dataCellStyle);
            }
        }

        // 计算合计值
        double sumAmount = IntStream.rangeClosed(1, 16).sum();
        double sumTax = IntStream.rangeClosed(1, 16).mapToDouble(i -> i * 2).sum();
        double sumNetAmount = IntStream.rangeClosed(1, 16).mapToDouble(i -> i * 3).sum();

        // 创建表格尾部行
        Row footerRow = sheet.createRow(19);
        footerRow.setHeightInPoints(40);
        CellStyle footerCellStyle = createCellStyle(workbook, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, true);

        // 创建表格尾部单元格并设置样式和值
        createCell(footerRow, 0, "合计", footerCellStyle);
        createCell(footerRow, 8, sumAmount, footerCellStyle);
        createCell(footerRow, 9, sumTax, footerCellStyle);
        createCell(footerRow, 10, sumNetAmount, footerCellStyle);

        return workbook;
    } catch (IOException e) {
        e.printStackTrace();
        return null;
    }
}

private Cell createCell(Row row, int index, Object value, CellStyle style) {
    Cell cell = row.createCell(index);
    if (value instanceof Number) {
        cell.setCellValue((Number) value);
    } else {
        cell.setCellValue(String.valueOf(value));
    }
    cell.setCellStyle(style);
    return cell;
}

private CellStyle createCellStyle(Workbook workbook, HorizontalAlignment alignment, VerticalAlignment verticalAlignment, boolean bold, short fontSize) {
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(alignment);
    cellStyle.setVerticalAlignment(verticalAlignment);
    Font font = workbook.createFont();
    font.setBold(bold);
    font.setFontHeightInPoints(fontSize);
    cellStyle.setFont(font);
    return cellStyle;
}

private CellStyle createCellStyle(Workbook workbook, HorizontalAlignment alignment, VerticalAlignment verticalAlignment, boolean bold) {
    return createCellStyle(workbook, alignment, verticalAlignment, bold, (short) 12);
}

private CellStyle createCellStyle(Workbook workbook) {
    return createCellStyle(workbook, HorizontalAlignment.LEFT, VerticalAlignment.CENTER, false, (short) 12);
}