Java 使用 Apache POI 创建 Excel 文件 - 劳务费明细表

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.stream.IntStream;

public class CreateWorkbook {

    private static final int COLUMN_WIDTH_0 = 25 * 256;
    private static final int COLUMN_WIDTH_1 = 12 * 256;
    private static final int COLUMN_WIDTH_2 = 20 * 256;
    private static final int COLUMN_WIDTH_3 = 25 * 256;
    private static final int COLUMN_WIDTH_4 = 15 * 256;
    private static final int COLUMN_WIDTH_5 = 15 * 256;
    private static final int COLUMN_WIDTH_6 = 20 * 256;
    private static final int COLUMN_WIDTH_7 = 20 * 256;
    private static final int COLUMN_WIDTH_8 = 12 * 256;
    private static final int COLUMN_WIDTH_9 = 12 * 256;
    private static final int COLUMN_WIDTH_10 = 12 * 256;

    private static final String SHEET_NAME = '劳务费明细';
    private static final String TITLE = 'XX基金会专家劳务报酬明细表';
    private static final String PROJECT_NAME = '基金会合作项目';
    private static final String PROJECT_DATE = '2023-3-13';

    private static final String[] HEADER_TITLES = {
            '序号', '姓名', '身份证号码', '单位名称', '电话号码', '银行所在省市',
            '开户银行及支行', '银行卡号', '应发含税金额（税前）', '个税', '实发税后金额（税后）'
    };

    public static Workbook createWorkbook() {
        try (Workbook workbook = new XSSFWorkbook()) {
            // 创建 Sheet 对象
            Sheet sheet = workbook.createSheet(SHEET_NAME);

            // 设置列宽度
            sheet.setColumnWidth(0, COLUMN_WIDTH_0);
            sheet.setColumnWidth(1, COLUMN_WIDTH_1);
            sheet.setColumnWidth(2, COLUMN_WIDTH_2);
            sheet.setColumnWidth(3, COLUMN_WIDTH_3);
            sheet.setColumnWidth(4, COLUMN_WIDTH_4);
            sheet.setColumnWidth(5, COLUMN_WIDTH_5);
            sheet.setColumnWidth(6, COLUMN_WIDTH_6);
            sheet.setColumnWidth(7, COLUMN_WIDTH_7);
            sheet.setColumnWidth(8, COLUMN_WIDTH_8);
            sheet.setColumnWidth(9, COLUMN_WIDTH_9);
            sheet.setColumnWidth(10, COLUMN_WIDTH_10);

            // 创建标题行
            createTitleRow(workbook, sheet);

            // 创建表头行
            createHeaderRow(workbook, sheet);

            // 创建数据行
            createDataRows(workbook, sheet);

            // 创建合计行
            createFooterRow(workbook, sheet);

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

    // 创建标题行
    private static void createTitleRow(Workbook workbook, Sheet sheet) {
        Row row = sheet.createRow(0);
        row.setHeightInPoints(50);

        CellStyle cellStyle = createTitleCellStyle(workbook);

        Cell cell = row.createCell(0);
        cell.setCellValue(TITLE);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));

        // 设置剩余的单元格内容
        for (int i = 1; i <= 10; i++) {
            cell = row.createCell(i);
            cell.setCellValue('');
        }
    }

    // 创建表头行
    private static void createHeaderRow(Workbook workbook, Sheet sheet) {
        Row headerRow = sheet.createRow(3);
        headerRow.setHeightInPoints(40);

        CellStyle headerCellStyle = createHeaderCellStyle(workbook);

        for (int i = 0; i < HEADER_TITLES.length; i++) {
            Cell headCell = headerRow.createCell(i);
            headCell.setCellStyle(headerCellStyle);
            headCell.setCellValue(HEADER_TITLES[i]);
        }
    }

    // 创建数据行
    private static void createDataRows(Workbook workbook, Sheet sheet) {
        CellStyle dataCellStyle = createDataCellStyle(workbook);

        IntStream.range(0, 15).forEach(i -> {
            Row dataRow = sheet.createRow(4 + i);
            dataRow.setHeightInPoints(30);
            IntStream.range(0, 11).forEach(j -> {
                Cell dataCell = dataRow.createCell(j);
                dataCell.setCellValue('数据' + i + '-' + j);
                dataCell.setCellStyle(dataCellStyle);
            });
        });
    }

    // 创建合计行
    private static void createFooterRow(Workbook workbook, Sheet sheet) {
        Row footerRow = sheet.createRow(19);
        footerRow.setHeightInPoints(40);

        CellStyle footerCellStyle = createFooterCellStyle(workbook);

        Cell footerCell = footerRow.createCell(0);
        footerCell.setCellStyle(footerCellStyle);
        footerCell.setCellValue('合计');

        double sumAmount = 16;
        double sumTax = 32;
        double sumNetAmount = 48;

        footerCell = footerRow.createCell(8);
        footerCell.setCellStyle(footerCellStyle);
        footerCell.setCellValue(sumAmount);

        footerCell = footerRow.createCell(9);
        footerCell.setCellStyle(footerCellStyle);
        footerCell.setCellValue(sumTax);

        footerCell = footerRow.createCell(10);
        footerCell.setCellStyle(footerCellStyle);
        footerCell.setCellValue(sumNetAmount);
    }

    // 创建标题单元格样式
    private static CellStyle createTitleCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_NORMAL);
        font.setBold(true);
        font.setFontHeightInPoints((short) 18);
        cellStyle.setFont(font);
        return cellStyle;
    }

    // 创建表头单元格样式
    private static CellStyle createHeaderCellStyle(Workbook workbook) {
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setWrapText(true);
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
        headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return headerCellStyle;
    }

    // 创建数据单元格样式
    private static CellStyle createDataCellStyle(Workbook workbook) {
        CellStyle dataCellStyle = workbook.createCellStyle();
        dataCellStyle.setWrapText(true);
        dataCellStyle.setAlignment(HorizontalAlignment.CENTER);
        dataCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return dataCellStyle;
    }

    // 创建合计单元格样式
    private static CellStyle createFooterCellStyle(Workbook workbook) {
        CellStyle footerCellStyle = workbook.createCellStyle();
        Font footerFont = workbook.createFont();
        footerFont.setBold(true);
        footerCellStyle.setFont(footerFont);
        footerCellStyle.setAlignment(HorizontalAlignment.CENTER);
        footerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return footerCellStyle;
    }
}