前言
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开始。
createRow和createCell方法，里面的参数是rowNum和cellNum，所以从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<String> 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<ExcelRow> 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<ExcelRow> getRows(int count, int totalRowNum, int sheetRowNum) {
if (count > 1000) {
return null;
}
List<ExcelRow> 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;
}
});
扩展
有时候在读取或写入时你可能会想要额外增加一些操作的属性，对此组件提供了ExcelReadProcessor和ExcelWriteProcessor接口来进行简单的扩展。
读取
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<ExcelCell> titleCells = titleRow.getExcelCells();
for (ExcelCell excelCell : titleCells) {
System.out.println(excelCell.getValue() + " - color:" + excelCell.getProperty("color"));
}
Collection<ExcelRow> excelRows = firstExcelSheet.getExcelRows();
for (ExcelRow excelRow : excelRows) {
Collection<ExcelCell> 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);
具体的实现可以参考默认的实现类。