java 操作 Excel

Excel进行过大的版本变更：

1. Excel 97-2003（.xls），.xls为二进制格式
2. Excel 2007及更高版本（.xlsx）.xlsx采用了基于XML的Open XML格式

因此这两种文件格式的处理方式并不一样。

1.JXL（只操作.xls 目前已不维护）

JXL（JExcelApi，目前已不维护）通过解析Excel文件的二进制格式，可以处理Excel（.xls）文件。如操作单元格值、格式、合并单元格、添加公式等。不完全支持.xlsx格式的文件。

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

2. POI （Poor Obfuscation Implementation）

POI （Poor Obfuscation Implementation）主要是指 Apache提供的操作Office的基础工具包（Apache POI），Apache POI 是一个底层的工具类，可实现的功能最全。

EasyExcel、Hutool的ExcelUtil 是在 Apache POI上作了不同程度的封装，便于快速开发。

• 如果操作Excel复杂度高，建议使用POI，编程灵活。
• 如果操作Excel数据量大，对性能有一定要求的情况，建议使用EasyExcel。
• 如果操作Excel数据量小，而且追求编程效率，建议使用Hutool的ExcelUtil。

2.1 Apache POI 

Apache提供了基础的操作Office的工具包，解析文件时一次性全部加载到内存中，系统并发量不大时可行，一旦并发上来后会产生OOM或者JVM频繁的full gc。Apache POI 的官网地址：

Apache POI—通过Java 操作 Excelhttps://poi.apache.org/components/spreadsheet/quick-guide.html

Apache POI中，使用HSSF操作.xls格式文件，使用XSSF操作.xlsx文件 

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.3.0</version>
</dependency>

Apache POI 操作Excel有两种类型，DOM 和 SAX

2.1.1 工作簿对象

工作簿对象支持设置一些全局性质的对象，例如：

1.  单元格样式的对象。一个工作簿最多支持6w个单元格的样式；
2.  创建字体样式的对象。一个工作簿最多支持32767个 字体样式；
3. 修改默认的调色板（自定义调色板，更改调色板的颜色次序）；
4. 设置单元格的数据格式。
5. 设置打印区域。

代码摘选自Apache POI 的官网（Apache POI—通过Java 操作 Excel） 

//新建工作簿
Workbbok wb =  new HSSFWorkbook();
try(OutputStream fileOutStream = new FileOutStream("workbook.xls")){
    wb.write(fileOutStream);
}

//作用
//获取 CreationHelper 对象
CreationHelper createHelper = wb.getCreationHelper();
//创建单元格格式,一个工作簿最多支持6w个单元格格式。单元格格式通过工作簿创建，否则可能会修改内置格式。
wb.createCellStyle();
//创建字体 请注意，工作簿唯一字体的最大数量限制为 32767。应该在程序中重复使用字体，而不是 为每个单元格创建字体。
wb.createFont();
//修改调色板(创建自定义调色板)
wb.getCustomPalette();
//创建数据格式
wb.setDataFormat();
//设置打印区域
wb.setPrintArea(0,0,1,0,0);

2.1.2 工作表对象 

工作表对象支持只在单个工作表生效的设置，例如：

1. 设置筛选按钮； 
2. 设置某列的自适应列宽；
3. 冻结窗格；
4. 拆分窗口；
5. 设置缩放；
6. 设置打印区域；
7. 移动行;
8. 设置单元表选定状态。

//新建工作簿
Workbbok wb =  new HSSFWorkbook();
try(OutputStream fileOutStream = new FileOutStream("workbook.xls")){
    wb.write(fileOutStream);
}

//创建工作表
Sheet sheet1 = wb.createSheet("新工作表");
Sheet sheet = wb.createSheet("第二个工作表");

//1. 设置筛选
sheet.setAutoFilter(CellRangeAddress.valueOf("C5:F200"));

//2. 设置自适应列宽
sheet.autoSizeColumn(0); //设置A列自适应列宽

//3. 冻结窗格。 冻结前三列及前两行，活动区域从第4列，第3行 进行展示。
sheet1.createFreezePane( 3, 2, 3, 2 );

//4. 拆分窗口并指定当前活动区域
sheet.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT );

//5. 设置缩放
sheet。setZoom(75);

//6. 进行工作表打印设置(拟合到一页)
PrintSetup ps = sheet1.getPrintSetup();
//设置页脚的页码
Footer footer = sheet1.getFooter();
footer.setRight("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

//7. 移动行,将6-11行移动到第1-6行
sheet.shiftRows(5,10,-5);

//8. 设置工作表所选状态
sheet.setSelected(true);

2.1.3 行对象

//创建新的一行
Row row = sheet1.createRow(0);

Row 支持的操作有：

1. 创建单元格
2. 获取/遍历单元格
3. 设置行高
4. 获取行号
5. 设置/隐藏行

//1. 创建单元格
Cell cell = row.createCell(0);
row.createCell(1).setCellValue(1.2);

//2. 获取/遍历 单元格    
Cell c = r.getCell(columnIndex, Row.RETURN_BLANK_AS_NULL);
row.cellIterator()

//3. 设置行高                     
row.setHeightInPoints(30);
row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints())); 

//4. 获取行号                       
row.getRowNum()

//5. 设置/取消隐藏行
row.getZeroHeight();
row.setZeroHeight();
row.setZeroHeight(false);

2.1.4 单元格对象

单元格对象支持对单个单元格进行设置，例如：

1. 获取/设置单元格的值
2. 获取/设置单元格的类型（字符串、数值[数字/日期]、布尔值、公式、错误值、空）
3. 获取/设置单元格的样式
4. 获取/设置超链接
5. 获取相对位置（行号、列号、地址、所在行、所在工作表）
6. 获取/设置单元格的注释

//1. 获取/设置单元格的值
cell.getRichStringCellValue().getString();
cell.getDateCellValue();
cell.getNumericCellValue();
cell.getBooleanCellValue()
cell.getCellFormula()

cell.setCellValue(1.1);
cell.setCellValue(new Date());
cell.setCellValue(Calendar.getInstance());
cell.setCellValue("a string");
cell.setCellValue(true);
cell.setBlank();

//2. 获取/设置单元格的类型（字符串、数值[数字/日期]、布尔值、公式、错误值、空）
cell.getCellType();
cell.setCellType(CellType.STRING);
cell.setCellType(CellType.ERROR);

//3. 获取/设置单元格的样式
cell.setCellStyle(cellStyle);

//4. 获取/设置超链接
cell.getHyperlink();
cell.setHyperlink(link);

//5. 获取相对位置（行号、列号、地址、所在行、所在工作表）
cell.getRowIndex();
cell.getColumnIndex();
cell.getRow();
cell.getSheet();
cell.getAddress().formatAsString();

//6. 获取/设置注释
Comment comment = cell.getCellComment();
cell.setCellComment(comment);

2.1.5 单元格的值

单元格的值有7种类型[CellType]（数值、字符串、公式、空值、布尔值、错误值），

1. 数值格式：
   日期时间实际上是以数值的形式存储的，因此，根据类型获得单元格值为数值时，应根据单元格格式判定是否为日期时间格式。
2. 字符串格式
   支持在单元格中设置富文本

cell.setCellValue(1.1);
cell.setCellValue(new Date());
cell.setCellValue(Calendar.getInstance());
cell.setCellValue("a string");
cell.setCellValue(true);
cell.setBlank();

//富文本设置
XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");

XSSFFont font1 = wb.createFont();
font1.setBold(true);
font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
rt.applyFont(0, 10, font1);

cell.setCellValue(rt);

2.1.6 单元格样式

单元格样式 支持对单个单元格样式 进行设置，例如：

1. 获取/设置单元格的数据显示格式（Excel中的单元格格式）
2. 设置填充颜色（背景色 + 前景色 + 填充模式）
3. 设置字体
4. 获取/设置 水平/垂直方向 的 对齐方式
5. 设置单元格边框（显隐、粗细、颜色）
6. 支持换行

cellStyle.setDataFormat(

createHelper.createDataFormat().getFormat("m/d/yy h:mm"))

//设置单元格格式
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));

//设置预置的背景色（背景色感觉不太管用，可以把前景色和背景色一起设置，并且要同时设置填充方式）
style.setFillBackgroundColor(IndexedColors.RED.getIndex());
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(FillPatternType.BIG_SPOTS);

//设置自定义颜色
XSSFCellStyle style1 = wb.createCellStyle();
style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128), new DefaultIndexedColorMap()));
style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);

// 设置字体
Font font = wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);

style.setFont(font);

//设置水平方向、垂直方向的对齐方式
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);

//设置边框
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderTop(BorderStyle.MEDIUM_DASHED);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());

//支持换行
style.setWrapText(true);

2.1.7 其他

1. 单元格格式（DataFormat)
   设置单元格格式，获取单元格格式下的显示值
2. 字体
   一个工作簿支持设置 32767个字体样式。请尽量复用样式而不是新建样式。

//设置单元格格式
DataFormat format = wb.createDataFormat();
CellStyle style = wb.createCellStyle();

style.setDataFormat(format.getFormat("0.0"));
style.setDataFormat(format.getFormat("#,##0.0000"));

cell.setCellStyle(style);

//获取单元格内容 在 单元格格式渲染后 的显示值
String text = format.formatCellValue(cell);

//字体
Font font = wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);

2.2 EasyExcel

  EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节省内存著称。在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析，并将一行的解析结果以观察者的模式通知处理。

2.3 Hutool的ExcelUtil

操作Excel数据量小，而且追求编程效率，可以使用Hutool的ExcelUtil。