Apache POI 常用Api整理

admin 发布于：2022-06-25 23:58:03

阅读：loading

本篇文章是对于Apache POI的常用Api整理，涉及到一些常用的文件读写和样式设置以及其它辅助型的设置类操作，很早之前就想来整理汇总一下曾经应用到的各种功能点，今天终于动手了。实际工作应用中多数情况会使用基于模板的组件使用，如EasyPoi、Jxls、Jxls2等。以下是个人常用的Api整理，具体如下。

1.创建工作簿

在Excel中提供了xls和xlsx两种格式，POI对于这两种格式也都有提供对应的实现，完全以Api的实现层面做的适配，想要实现不同版本的文档读写只需使用对应版本的Api即可。一个Excel文件代表一个工作簿对象Workbook，一个工作表对应一个Sheet页，行为Row，单元格为Cell，所以对于Excel文档的操作就是对于Workbook、Sheet、Row、Cell几个主要对象的读取和写入，参考创建工作簿代码如下：

package cn.chendd.poi;

import ...;

/**
 * Poi常用Api测试
 *
 * @author chendd
 * @date 2022/6/26 17:10
 */
@RunWith(JUnit4.class)
public class PoiApiTest {

    @Test
    public void createExcel2003() throws IOException {
        try (Workbook workbook2003 = new HSSFWorkbook();
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream();) {
            workbook2003.createSheet("Excel2003");
            workbook2003.write(outputStream);
            System.out.println(Arrays.toString(outputStream.toByteArray()));
        }
    }

    @Test
    public void createExcel2007() throws IOException {
        File file = File.createTempFile("Excel", ".xlsx");
        try (Workbook workbook2007 = new XSSFWorkbook();
             FileOutputStream outputStream = new FileOutputStream(file)) {
            Sheet sheet = workbook2007.createSheet("Excel2007");
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("hello");
            workbook2007.write(outputStream);
            System.out.println(file.getAbsolutePath());
        } catch (Exception e) {
            System.err.println(e.getMessage());
            file.delete();
        }
    }

    @Test
    public void createExcel() throws IOException {
        File file = File.createTempFile("Excel", ".xlsx");
        try (Workbook workbook = new SXSSFWorkbook();
             FileOutputStream outputStream = new FileOutputStream(file)) {

            Sheet sheet = workbook.createSheet("Excel");
            //设置3列2行的冻结
            sheet.createFreezePane(3 , 2);
            //是否显示网格
            sheet.setDisplayGridlines(false);
            //是否打印网格
            sheet.setPrintGridlines(false);
            //设置纸张方向和纸张大小
            PrintSetup setup = sheet.getPrintSetup();
            setup.setLandscape(true);
            setup.setPaperSize(PrintSetup.A4_PAPERSIZE);
            //设置默认的行高
            sheet.setDefaultRowHeightInPoints(26);
            //设置列宽
            this.setColumnWidth(sheet);
            //填充标题行
            this.fillTitleRow(workbook , sheet , 0);
            //填充表头行
            this.fillHeaderRow(workbook , sheet , 1);
            //填充数据行
            this.fillDataRow(workbook , sheet , 2 , 1_000 * 50);
            //设置下拉框
            this.addSexDropdown(sheet , 2 ,1000 + 1 , 2 ,  2);
            workbook.write(outputStream);
            System.out.println(file.getAbsolutePath());
        } catch (Exception e) {
            System.err.println(e.getMessage());
            file.delete();
        }
    }

    /**
     * 设置下拉框
     * @param sheet sheet
     * @param firstRow 开始行
     * @param lastRow 结束行
     * @param firstCol 开始列
     * @param lastCol 结束列
     */
    private void addSexDropdown(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint dataValidationConstraint = validationHelper.createExplicitListConstraint(new String[]{"男", "女"});
        CellRangeAddressList rangeList = new CellRangeAddressList(firstRow , lastRow , firstCol , lastCol);
        DataValidation dataValidation = validationHelper.createValidation(dataValidationConstraint, rangeList);
        sheet.addValidationData(dataValidation);
    }

    /**
     * 填充数据
     * @param workbook 工作簿
     * @param sheet sheet
     * @param index 索引行
     * @param number 行数
     */
    private void fillDataRow(Workbook workbook, Sheet sheet, int index, int number) {
        CellStyle leftStyle = ExcelStyle.createDataLeftStyle(workbook);
        CellStyle centerStyle = ExcelStyle.createDataCenterStyle(workbook);
        CellStyle amountStyle = ExcelStyle.createDataAmountStyle(workbook);
        CellStyle amountRedStyle = ExcelStyle.createDataAmountRedStyle(workbook);
        ThreadLocalRandom random = ThreadLocalRandom.current();
        for (int i = 0 ; i < number ; i++) {
            Row row = sheet.createRow(i + index);
            //编号列
            Cell numCell = row.createCell(0);
            numCell.setCellStyle(centerStyle);
            numCell.setCellValue(i + 1);
            //姓名列
            Cell nameCell = row.createCell(1);
            nameCell.setCellStyle(leftStyle);
            nameCell.setCellValue(i % 2 == 0 ? "奕廷" : "梓瑜");
            //性别列
            Cell sexCell = row.createCell(2);
            sexCell.setCellStyle(centerStyle);
            sexCell.setCellValue(i % 2 == 0 ? "男" : "女");
            //出生日期
            Cell birthdayCell = row.createCell(3);
            birthdayCell.setCellStyle(centerStyle);
            birthdayCell.setCellValue(i % 2 == 0 ? "2021-07-09" : "2016-11-10");
            //零花钱
            Cell amountCell = row.createCell(4);
            Double amount = random.nextDouble(5000);
            if (amount.compareTo(1000.0D) < 0) {
                amountCell.setCellStyle(amountRedStyle);
            } else {
                amountCell.setCellStyle(amountStyle);
            }
            amountCell.setCellValue(amount);
            //联系地址
            Cell addressCell = row.createCell(5);
            addressCell.setCellStyle(leftStyle);
            addressCell.setCellValue(i % 2 == 0 ? "湖北省武汉市江夏区" : "湖北省襄阳市老河口市");
            if (i % 500 == 0) {
                try {
                    ((SXSSFSheet) sheet).flushRows();
                } catch (IOException ignore) {}
            }
        }
        
    }

    /**
     * 填充表头行
     * @param sheet sheet
     * @param index 索引行
     */
    private void fillHeaderRow(Workbook workbook , Sheet sheet, int index) {
        String[] headers = {
            "序号" , "姓名" , "性别" , "出生日期" , "零花钱（元）" , "联系地址"
        };
        Row row = sheet.createRow(index);
        CellStyle cellStyle = ExcelStyle.createHeaderStyle(workbook);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(cellStyle);
        }
    }

    /**
     * 填充标题行
     * @param workbook workbook
     * @param sheet sheet
     * @param index 索引行
     */
    private void fillTitleRow(Workbook workbook , Sheet sheet, int index) {
        Row row = sheet.createRow(index);
        Cell cell = row.createCell(0);
        cell.setCellStyle(ExcelStyle.createTitleStyle(workbook));
        cell.setCellValue("用户信息表");
        //设置单元格合并
        sheet.addMergedRegion(new CellRangeAddress(index , index , 0 , 5));
        //设置超链接，点击连接至Sheet页名称为Excel的第A列第100行
        Hyperlink hyperlink = workbook.getCreationHelper().createHyperlink(HyperlinkType.DOCUMENT);
        hyperlink.setAddress("'Excel'!A100");
        cell.setHyperlink(hyperlink);
    }

    /**
     * 设置sheet页的列宽
     * @param sheet sheet
     */
    private void setColumnWidth(Sheet sheet) {
        int[][] columns = new int[][] {
                {0 , 256 * 8},
                {1 , 256 * 15},
                {2 , 256 * 15},
                {3 , 256 * 20},
                {4 , 256 * 25},
                {5 , 256 * 50}
        };
        for (int[] column : columns) {
            sheet.setColumnWidth(column[0] , column[1]);
        }
    }

}

2.本例知识点

（1）使用读取Excel文件时，使用WorkbookFactory.create方法可兼容xls和xlsx两种格式；

（2）创建Workbook、Sheet、Row、Cell；

（3）设置Excel文档属性：页面A4大小、页面横向/纵向展示、显示隐藏网格（打印预览效果展示）；

（4）设置单元格样式：边框、背景色、文本颜色、文本字体、居左、居右、居中、垂直居中、千分位金额；

（5）冻结行和冻结列（本例冻结顶部两行和前面三列）；

（6）设置某个区域下拉框（本例C列的所有性别数据区域内展示下拉框，数据范围为：男、女）；

（7）单元格合并（本例合并第一行的A-F列）；

（8）超链接（本例第一行的A列数据存在单元格超链接，点击后定位到当前文件的Sheet页为Excel页的A列100行）；

（9）大数据量写入（本地较少的写入5万条数据，使用flushRows函数刷新至硬盘）；

另有其它的逻辑本例未提供：

（1）读取Excel文档中的所有图片的公共实现；