Excel读写组件封装,支持图片、下拉框、单元格合并等,支持大数据量操作

分类: 工作记录 0人评论 selfly 1年前发布

前言

git@osc地址:https://git.oschina.net/selfly/imesne-assistant/blob/master/md/excel-doc.md

Excel操作组件,使用poi实现,对Excel的读写进行了简单的封装.

行号、列号等在使用上尽量跟Excel软件界面看到的保持一致,行号从1开始,列号从A开始。

但程序代码中索引总是从0开始,在编写扩展时难免会搞混,因此需要记住下面这条原则:

xxxIndex总是从0开始,例如sheetIndex、rowIndex、cellIndex,xxxNum总是从1开始,例如sheetNum、rowNum、cellNum。

列头如果使用字母则 A = 1,B = 2 以此类推。

读取

对于excel的读取,不必关心是03版(.xls)或07版(.xlsx),会自动进行识别。

读取简单示例

读取一个excel文件,默认第一行为title,返回一个ExcelBook对象,可以直接输出查看数据:

ExcelBook excelBook = ExcelKit.createReader()
            .read(new File("/Users/liyd/testspace/writer.xlsx"));

System.out.println(excelBook);

指定范围的读取

以下代码将读取excel文件中第1个sheet中第2-5行的第B-E列,这里的行号和列号与在Excel界面中看到的保持一致,行号从1开始,列号从A开始。

其中sheetIndex必须指定,行号和列号可以选择指定:

ExcelReaderConfig excelReaderConfig = new ExcelReaderConfig();

ExcelRange range = ExcelRange.builder()
        .sheetNum(1)
        .beginRowNum(2)
        .endRowNum(5)
        .beginCellNum("B")
        .endCellNum("E")
        .build();
excelReaderConfig.addRange(range);

ExcelBook excelBook = ExcelKit.createReader(excelReaderConfig)
        .read(new File("/Users/liyd/testspace/writer.xlsx"))

以下代码将读取第1个sheet中,第2行的C列,第3行的B列(列头可以是字母或数字,字母从A开始,数字从1开始),第4行的A列:

ExcelReaderConfig excelReaderConfig = new ExcelReaderConfig();

excelReaderConfig.addRangeCell(1, 2, "C");
excelReaderConfig.addRangeCell(1, 3, 2);
excelReaderConfig.addRangeCell(1, 4, "A");

ExcelBook excelBook = ExcelKit.createReader(excelReaderConfig)
        .read(new File("/Users/liyd/testspace/writer.xlsx"));

也可以混合指定:

ExcelReaderConfig excelReaderConfig = new ExcelReaderConfig();
ExcelRange range = ExcelRange.builder()
        .sheetNum(1)
        .beginRowNum(2)
        .endRowNum(5)
        .beginCellNum("B")
        .endCellNum("E")
        .build();
excelReaderConfig.addRange(range);

excelReaderConfig.addRangeCell(1, 7, "C");
excelReaderConfig.addRangeCell(1, 8, 2);
excelReaderConfig.addRangeCell(1, 9, "A");

ExcelBook excelBook = ExcelKit.createReader(excelReaderConfig)
        .read(new File("/Users/liyd/testspace/writer.xlsx"));

读取时添加校验器

校验器必须实现CellValueValidator接口:

public interface CellValueValidator {

    boolean isSupportValidate(Workbook workbook, Sheet sheet, int sheetNum, Row row, int rowNum, int cellNum);

    void validate(Object value, int sheetNum, int rowNum, int cellNum) throws CellValueInvalidException;
}

isSupportValidate方法返回 true 时,validate方法将被调用。

校验不通过时,可以直接抛出CellValueInvalidException异常。

下面是一个简单的示例,校验第2行的第二列性别不能为男:

public class TestCellValueValidator implements CellValueValidator {
    public boolean isSupportValidate(Workbook workbook, Sheet sheet, int sheetNum, Row row, int rowNum, int cellNum) {
        if (rowNum == 2 && cellNum == 2) {
            return true;
        }
        return false;
    }
    public void validate(Object value, int sheetNum, int rowNum, int cellNum) throws CellValueInvalidException {

        if ("男".equals(value)) {
            throw new CellValueInvalidException("性别错误");
        }
    }
}

写入

写入简单示例

这里要注意,createTitleRow标题会占用掉1行,所以下面的数据行从2开始。

createRowcreateCell方法,里面的参数是rowNumcellNum,所以从1开始。

ExcelBook excelBook = new ExcelBook();
ExcelSheet excelSheet = excelBook.createSheet("工作表A");
ExcelRow titleRow = excelSheet.createTitleRow();
for (int i = 1; i < 16; i++) {
    titleRow.createCell(i, "标题" + i);
}

for (int i = 2; i < 20; i++) {

    ExcelRow excelRow = excelSheet.createRow(i);

    for (int j = 1; j < 16; j++) {
        excelRow.createCell(j, "值" + i + "-" + j);
    }
}

ExcelKit.createWriter()
        .write(excelBook, new File("/Users/liyd/cmb2/writer.xlsx"));

指定行列

在生成excel文件时,数据是根据行列号来进入写入的,这就允许你显示的指定需要写入的单元格,例如下面代码:

ExcelBook excelBook = new ExcelBook();
ExcelSheet excelSheet = excelBook.createSheet();
for (int i = 1; i < 20; i++) {
    if (i % 2 == 0) {
        continue;
    }
    ExcelRow excelRow = excelSheet.createRow(i);
    for (int j = 1; j < 10; j++) {
        if (j % 3 != 0) {
            continue;
        }
        excelRow.createCell(j, RandomUtils.nextInt(0, 1000000));
    }
}
ExcelKit.createWriter()
        .write(excelBook, new File("/Users/liyd/cmb2/writer.xlsx"));

结果:

写入数字

在Excel中,当数字超过12位长度时,会变成科学计数法显示,不方便查看。大部分时候并不需要对它们做运算,只需要正常显示就可以,因此在数字长度超过12位时会写入成字符串:

ExcelBook excelBook = new ExcelBook();
ExcelSheet excelSheet = excelBook.createSheet();
for (int i = 1; i < 5; i++) {
    ExcelRow excelRow = excelSheet.createRow(i);
    for (int j = 1; j < 5; j++) {
        excelRow.createCell(j, RandomUtils.nextInt(0, 1000000));
    }
    //是否显示成科学计数法测试
    excelRow.createCell(5, 1234560605112345L);
}
ExcelKit.createWriter()
        .write(excelBook, new File("/Users/liyd/cmb2/writer.xlsx"));

结果:

写入下拉框

对于把值设置成String[]的cell,将会处理成下拉框。在实际测试中当下拉框的值多于53个时,打开excel文件时会有错误提示。

ExcelBook excelBook = new ExcelBook();
ExcelSheet excelSheet = excelBook.createSheet();
for (int i = 1; i < 5; i++) {
    ExcelRow excelRow = excelSheet.createRow(i);
    for (int j = 1; j < 5; j++) {
        excelRow.createCell(j, RandomUtils.nextInt(0, 1000000));
    }
    //下拉框测试
    List list = new ArrayList<>();
    for (int k = 0; k < 5; k++) {
        list.add("value" + k);
    }
    excelRow.createCell(5, list.toArray(new String[]{}));
}
ExcelKit.createWriter()
        .write(excelBook, new File("/Users/liyd/cmb2/writer.xlsx"));

结果:

写入图片

对于把值设置成byte[]的cell,会处理成图片:

ExcelBook excelBook = new ExcelBook();
ExcelSheet excelSheet = excelBook.createSheet();
for (int i = 1; i < 5; i++) {
    ExcelRow excelRow = excelSheet.createRow(i);
    for (int j = 1; j < 5; j++) {
        excelRow.createCell(j, RandomUtils.nextInt(0, 1000000));
    }
    //图片测试,设置行高和列宽方便显示图片
    excelRow.setHeight(60);
    byte[] bytes = FileUtils.readFileToByteArray(new File("/Users/liyd/cmb2/images.jpeg"));
    ExcelCell cell = excelRow.createCell(5, bytes);
    cell.setWidth(80);
}
ExcelKit.createWriter()
        .write(excelBook, new File("/Users/liyd/cmb2/writer.xlsx"));

结果:

指定excel版本

默认生成的07以后的版本xlsx,可以指定生成03版本xls

ExcelBook excelBook = new ExcelBook();
ExcelSheet excelSheet = excelBook.createSheet();
for (int i = 1; i < 5; i++) {
    ExcelRow excelRow = excelSheet.createRow(i);
    excelRow.createCell(1, RandomUtils.nextInt(0, 1000000));
}
ExcelWriterConfig excelWriterConfig = new ExcelWriterConfig();
excelWriterConfig.setVersion(ExcelVersion.XLS);
ExcelKit.createWriter(excelWriterConfig)
        .write(excelBook, new File("/Users/liyd/cmb2/writer.xls"));

合并单元格

可以指定哪些单元格进行合并,合并后对单元格的设值,只有起始行和起始列的值才会起效,其它的都无效。

下面的代码,合并后只有第3行第5列的值起效,在实际操作时需要注意:

ExcelBook excelBook = new ExcelBook();
ExcelSheet excelSheet = excelBook.createSheet();

ExcelRow titleRow = excelSheet.createTitleRow();
for (int i = 1; i < 16; i++) {
    titleRow.createCell(i, "标题" + i);
}

for (int i = 2; i < 20; i++) {
    ExcelRow excelRow = excelSheet.createRow(i);
    for (int j = 1; j < 16; j++) {
        excelRow.createCell(j, "值" + i + "-" + j);
    }
}

ExcelWriterConfig excelWriterConfig = new ExcelWriterConfig();

ExcelRange excelRange = ExcelRange.builder()
        .sheetNum(1)
        .beginRowNum(3)
        .endRowNum(6)
        .beginCellNum(5)
        .endCellNum(8)
        .build();
excelWriterConfig.addMergedRegion(excelRange);

ExcelKit.createWriter(excelWriterConfig)
        .write(excelBook, new File("/Users/liyd/cmb2/writer.xlsx"));

结果:

追加内容

有时候可能会需要向本就存在的excel文件中追加内容,而不是新生成一个文件,典型的场景就是使用excel模板导出报表。

下面的代码将在已经存在的sheet 1中追加写入单元格内容,而sheet 2本来并不存在,所以会新创建一个:

ExcelBook excelBook = new ExcelBook();
ExcelSheet excelSheet = excelBook.createSheet();
excelSheet.setSheetNum(1);

ExcelRow excelRow = excelSheet.createRow(22);
excelRow.createCell("A", "111AAA");
excelRow.createCell("b", "bbbb");
excelRow.createCell("C", 33333);

ExcelSheet excelSheet2 = excelBook.createSheet("追加的sheet");
ExcelRow excelRow2 = excelSheet2.createRow(1);
excelRow2.createCell("A", "2111AAA");
excelRow2.createCell("b", "2bbbb");
excelRow2.createCell("C", 233333);

ExcelWriterConfig excelWriterConfig = new ExcelWriterConfig();
excelWriterConfig.setAppend(true);
ExcelKit.createWriter(excelWriterConfig)
        .write(excelBook, new File("/Users/liyd/cmb2/writer.xlsx"));

追加前:

追加后,sheet 1:

追加后,sheet 2:

大数据量读写

在项目中难免会碰到大数据量的导入和导出,一不小心很容易导致内存溢出。所幸poi也提供了对大数据量excel数据文件的操作接口,特别是07版(.xlsx)以后的支持。这里也只对07版进行了封装。

大数据量读取

因为07版以后的excel文件本质上就是xml文件,因此在解析上也类似,采用SAX方式实现。

这里定义了一个读取处理接口:

public interface ExcelSaxReadHandler {

    /**
    * 处理行
    *
    * @param excelRow
    */
    void row(ExcelRow excelRow);

    /**
    * 处理列
    *
    * @param excelCell the excel cell
    */
    void cell(ExcelCell excelCell);
}

当解析出行和列时会回调相应的方法,可以在该方法中完成对数据的处理,示例代码:

ExcelKit.createSaxReader()
        .read("/Users/liyd/cmb2/big.xlsx", new ExcelSaxReadHandler() {
            @Override
            public void row(ExcelRow excelRow) {
                System.out.println(excelRow);
            }

            @Override
            public void cell(ExcelCell excelCell) {
                System.out.println(excelCell);
            }
        });

大数据量写入

同样的,也采用SAX方式实现。一个不足之处是该方式无法对excel中的行列进行样式设置,甚至连简单的字体加粗都不行,只能是默认。

同样,定义了一个写入处理接口:

public interface ExcelSaxWriteHandler {

    /**
    * 是否创建新的sheet,第1个sheet默认创建
    * 返回true时将创建新的sheet,后面的数据将写入到该sheet直到再次创建新sheet
    *
    * @param totalRowNum
    * @param sheetRowNum
    * @return
    */
    boolean isNewSheet(int totalRowNum, int sheetRowNum);

    /**
    * 标题行,返回null时不创建标题
    * 默认第1行为标题,当创建新的sheet时每个sheet都会创建标题
    *
    * @return
    */
    ExcelRow getTitleRows(int sheetNum);

    /**
    * 数据行
    * 每次返回一个数据list,list大小由自己决定
    * 将一直调用该方法直到返回null或list.isEmpty() == true
    *
    * @param count
    * @param totalRowNum
    * @param sheetRowNum
    * @return
    */
    List getRows(int count, int totalRowNum, int sheetRowNum);
}

接口看上面的注释应该就能理解,下面代码写入100万行数据(不算标题行),当sheet内达到50万行数据时新建一个sheet,指定Xms=64m,大概花费了63秒时间:

ExcelKit.createSaxWriter()
        .writer(new File("/Users/liyd/cmb2/big2.xlsx"), new ExcelSaxWriteHandler() {

            int rowCount = 1;

            public boolean isNewSheet(int totalRowNum, int sheetRowNum) {
                if (sheetRowNum > 500001) {
                    return true;
                }
                return false;
            }

            public ExcelRow getTitleRows(int sheetNum) {
                ExcelRow excelRow = new ExcelRow();
                for (int j = 1; j < 30; j++) {
                    excelRow.createCell(j, "标题" + j);
                }
                return excelRow;
            }

            public List getRows(int count, int totalRowNum, int sheetRowNum) {

                if (count > 1000) {
                    return null;
                }

                List rows = new ArrayList<>();
                for (int i = 0; i < 1000; i++) {
                    ExcelRow excelRow = new ExcelRow();
                    for (int j = 1; j < 30; j++) {
                        excelRow.createCell(j, "val:" + rowCount + ":" + j);
                    }
                    rows.add(excelRow);
                    rowCount++;
                }
                return rows;
            }
        });

扩展

有时候在读取或写入时你可能会想要额外增加一些操作的属性,对此组件提供了ExcelReadProcessorExcelWriteProcessor接口来进行简单的扩展。

读取

ExcelReadProcessor接口定义如下,默认提供了空方法的实现抽象类AbstractExcelReadProcessor,可以继承此类根据自身需要覆盖相应的方法。

public interface ExcelReadProcessor {

    /**
    * 处理ExcelBook
    *
    * @param excelBook  the excel book
    * @param workbook   the workbook
    * @param excelBytes the excel bytes
    */
    void processExcelBook(ExcelBook excelBook, Workbook workbook, byte[] excelBytes);

    /**
    * 处理ExcelSheet
    *
    * @param excelSheet the excel sheet
    * @param sheet      the sheet
    * @param workbook   the workbook
    * @param sheetIndex the sheet index
    */
    void processExcelSheet(ExcelSheet excelSheet, Sheet sheet, Workbook workbook, int sheetIndex);


    /**
    * 处理ExcelRow
    *
    * @param excelRow   the excel row
    * @param row        the row
    * @param workbook   the workbook
    * @param sheet      the sheet
    * @param sheetIndex the sheet index
    * @param rowIndex   the row index
    */
    void processExcelRow(ExcelRow excelRow, Row row, Workbook workbook, Sheet sheet, int sheetIndex, int rowIndex);

    /**
    * 处理ExcelCell
    *
    * @param excelCell  the excel cell
    * @param cell       the cell
    * @param workbook   the workbook
    * @param sheet      the sheet
    * @param sheetIndex the sheet index
    * @param row        the row
    * @param rowIndex   the row index
    * @param cellIndex  the cell index
    */
    void processExcelCell(ExcelCell excelCell, Cell cell, Workbook workbook, Sheet sheet, int sheetIndex, Row row, int rowIndex, int cellIndex);
}

示例,这里在读取单元格Cell时,增加一个前景色获取。

读取的excel内容如下:

代码:

ExcelReaderConfig excelReaderConfig = new ExcelReaderConfig();
excelReaderConfig.setExcelReadProcessor(new AbstractExcelReadProcessor() {
    @Override
    public void processExcelCell(ExcelCell excelCell, Cell cell, Workbook workbook, Sheet sheet, int sheetIndex, Row row, int rowIndex, int cellIndex) {
        CellStyle cellStyle = cell.getCellStyle();
        Color color = cellStyle.getFillForegroundColorColor();
        //这里只演示07版本
        if (color instanceof XSSFColor) {
            XSSFColor xssfColor = (XSSFColor) color;
            byte[] rgb = xssfColor.getRgb();
            String hex = String.format("#%02x%02x%02x", rgb[0], rgb[1], rgb[2]);
            excelCell.addProperty("color", hex);
        }
    }
});
ExcelBook excelBook = ExcelKit.createReader(excelReaderConfig)
        .read(new File("/Users/liyd/cmb2/test.xlsx"));

ExcelSheet firstExcelSheet = excelBook.getFirstExcelSheet();
ExcelRow titleRow = firstExcelSheet.getTitleRow();
Collection titleCells = titleRow.getExcelCells();
for (ExcelCell excelCell : titleCells) {
    System.out.println(excelCell.getValue() + " - color:" + excelCell.getProperty("color"));
}

Collection excelRows = firstExcelSheet.getExcelRows();
for (ExcelRow excelRow : excelRows) {
    Collection excelCells = excelRow.getExcelCells();
    for (ExcelCell excelCell : excelCells) {
        System.out.println(excelCell.getValue() + " - color:" + excelCell.getProperty("color"));
    }
}

输出结果,可以看到成功获取到了想要的颜色属性:

标题一 - color:null
标题二 - color:#ed7d31
标题三 - color:null
value1 - color:null
value2 - color:#70ad47
value3 - color:null

写入

ExcelWriteProcessor接口定义如下,默认提供了空方法的实现抽象类AbstractExcelWriteProcessor,可以继承此类根据自身需要覆盖相应的方法。

public interface ExcelWriteProcessor {

    /**
    * 处理Workbook
    *
    * @param excelBook
    * @param workbook
    */
    void processWorkbook(ExcelBook excelBook, Workbook workbook);

    /**
    * 处理Sheet
    *
    * @param excelSheet
    * @param sheet
    * @param workbook
    * @param count
    */
    void processSheet(ExcelSheet excelSheet, Sheet sheet, Workbook workbook, int count);

    /**
    * 处理title Row
    *
    * @param excelRow
    * @param row
    * @param workbook
    * @param sheet
    */
    void processTitleRow(ExcelRow excelRow, Row row, Workbook workbook, Sheet sheet);

    /**
    * 处理Row
    *
    * @param excelRow
    * @param row
    * @param workbook
    * @param sheet
    */
    void processRow(ExcelRow excelRow, Row row, Workbook workbook, Sheet sheet);

    /**
    * 处理Cell
    *
    * @param excelCell
    * @param cell
    * @param sheet
    * @param row
    */
    void processCell(ExcelCell excelCell, Cell cell, Sheet sheet, Row row);
}

示例,这里在写入单元格式数据时,增加一个前景色,并替换一列单元格的内容,以前面写入数字的excel内容为例,代码:

ExcelBook excelBook = new ExcelBook();
ExcelSheet excelSheet = excelBook.createSheet();
for (int i = 1; i < 5; i++) {
    ExcelRow excelRow = excelSheet.createRow(i);
    for (int j = 1; j < 5; j++) {
        excelRow.createCell(j, RandomUtils.nextInt(0, 1000000));
    }
    //是否显示成科学计数法
    ExcelCell excelCell = excelRow.createCell(5, 1234560605112345L);
    excelCell.addProperty("color", i % 2 == 0 ? "#2e8b57" : "#a9a9a9");
}
ExcelWriterConfig excelWriterConfig = new ExcelWriterConfig();
excelWriterConfig.setExcelWriteProcessor(new AbstractExcelWriteProcessor() {
    public void processCell(ExcelCell excelCell, Cell cell, Workbook workbook, Sheet sheet, Row row) {

        CellStyle cellStyle = workbook.createCellStyle();
        String color = (String) excelCell.getProperty("color");
        if (color == null) {
            //没有指定颜色,这里使用内置的颜色常量,可以不关心版本
            cellStyle.setFillForegroundColor(IndexedColors.ORANGE.index);
        } else {
            //指定了自定义颜色,这里只演示07版本的处理
            XSSFCellStyle xssfCellStyle = (XSSFCellStyle) cellStyle;
            byte r = (byte) Integer.parseInt(StringUtils.substring(color, 1, 3), 16);
            byte g = (byte) Integer.parseInt(StringUtils.substring(color, 3, 5), 16);
            byte b = (byte) Integer.parseInt(StringUtils.substring(color, 5), 16);
            XSSFColor xssfColor = new XSSFColor(new byte[]{r, g, b});
            xssfCellStyle.setFillForegroundColor(xssfColor);
        }
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cell.setCellStyle(cellStyle);
        if (excelCell.getCellNum() == 2){
            cell.setCellValue("aaabbbccc");
        }
    }
});

ExcelKit.createWriter(excelWriterConfig)
        .write(excelBook, new File("/Users/liyd/cmb2/writer.xlsx"));

结果:

重写实现

如果上面的简单扩展处理还不能满足需求,那么可以重写组件的读写接口。

读取的几个接口:

  • ExcelBookReader
  • ExcelSheetReader
  • ExcelRowReader
  • ExcelCellReader

写入的几个接口:

  • ExcelBookWriter
  • ExcelSheetWriter
  • ExcelRowWriter
  • ExcelCellWriter

分别对应Excel的workbook、sheet、row、cell。

在实现相应的接口后,使用时可以在excelxxxConfig中替换掉默认实现,例如:

excelWriterConfig.setExcelRowWriter(customExcelRowWriter);

具体的实现可以参考默认的实现类。