作者简介：大家好，我是smart哥，前中兴通讯、美团架构师，现某互联网公司CTO
联系qq：184480602，加我进群，大家一起学习，一起进步，一起对抗互联网寒冬
在日常开发中，常常会遇到Excel导入导出的需求。记得刚入行不久，我接到一个需求，客户希望把数据库的数据导出到Excel表，而且末尾要把对应的衣服图片塞进表格中。当时自学入行才一个月，根本没遇到过处理Excel的情况，慌得不行。心想，Java和Excel八竿子打不着，我怎么用Java代码去操作一个个表格呢？
后来经过百度，才发现Apache早就写好了工具类，调用API就完事了。这个工具类就是POI，也就是今天的主角。
这个系列会分为好多篇，目的不是为了提供一个完美的Excel工具类，而是在这个过程中复习并应用反射、泛型和注解处理实际问题。老实说，无论效率还是稳定性，我们自己封装的Utils都不及阿里开源的EasyExcel，但我希望通过PoiExcelUtils的迭代演示，让大家掌握Excel导入导出的大致流程以及封装工具类的思路，以后遇到其他类似的需求也能驾轻就熟。
POI简介
常见的Excel操作工具有JXL、POI等。JXL只能对Excel进行操作，属于比较老的框架，而且只支持到Excel95-2000版本，现在已经停止更新和维护，这里不做过多介绍。POI是Apache的项目，可以对微软/WPS的Word、Excel、PPT甚至OutLook进行操作。说这句话目的只有一个，就是告诉大家POI不仅仅只能处理Excel，Word、PPT这些办公组件也同样适用。
但实际开发中处理Excel的场景几乎占了95%以上，所以我们只介绍POI处理Excel的部分。
如果大家稍微回忆一下，会发现好像生活中只听过Excel2003和Excel2007，没有什么Excel2004这么一说。正如Java1.5版本是重要的分水岭一样，Excel2007也是一个分水岭。
Excel2003是一个特有的二进制格式，其核心结构是复合文档类型的结构，存储数据量较小，文件后缀是xls。
Excel2007的核心结构则是XML，采用的是基于XML的压缩格式，占用空间更小，操作效率更高，文件后缀是xlsx。
如何验证呢？
首先在桌面任意新建一个student_info.xlsx文件，然后把后缀改为.zip，随即解压得到：
看，遍地的xml文件。
Java是一门面向对象的语言，可以说万物皆对象，POI也是如此。在后面使用POI的过程中，我们将会遇到下面三个的实现类：
- Workbook
- Sheet
- CellStyle
刚好是对上面几个XML文件的抽象，操作它们等同于操作Excel文件本身。
POI提供了HSSF、XSSF以及SXSSF三种方式操作Excel：
- HSSF：Excel97-2003版本，扩展名为.xls。一个sheet最大行数65536，最大列数256
- XSSF：Excel2007版本开始，扩展名为.xlsx。一个sheet最大行数1048576，最大列数16384（百万数据）
- SXSSF：在XSSF基础上，POI3.8版本开始提供的支持低内存占用的操作方式，扩展名为.xlsx
我们会基于XSSF做介绍，反正实际开发推荐使用EasyExcel，我们没必要对POI了解太深。
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
</dependencies>下载Excel文件，后面要用： 百度网盘 请输入提取码 密码: sav4
简单的导入
/**
* 最简单的Excel测试
* 目前为止，我们只是引入了POI依赖，然后准备了一个Excel文件
* 下面写了两段代码，大家花时间阅读一下
*/
public class SimpleTest {
@Test
public void testSimpleRead() throws IOException {
// 获取工作薄（把路径换成你本地的）
XSSFWorkbook workbook = new XSSFWorkbook("/Users/bravo1988/Desktop/student_info.xlsx");
// 获取工作表。一个工作薄中可能有多个工作表，比如sheet1 sheet2，可以根据下标，也可以根据sheet名称
XSSFSheet sheet = workbook.getSheetAt(0);
/**
* 方式1 增强for，Row代表行，Cell代表单元格
*/
// 遍历每一行
for (Row row : sheet) {
// 遍历当前行的每个单元格
for (Cell cell : row) {
// 获取单元格类型
CellType cellType = cell.getCellType();
// 根据类型选择匹配的getXxxValue()方法，比如你判断当前单元格的值是BOOLEAN类型的，你就要用getBooleanCellValue()
if (cellType == CellType.NUMERIC) {
// 你可以试着把这里的getNumericCellValue()改成getStringCellValue()，观察会发生什么
System.out.print(cell.getNumericCellValue() + "\t");
} else if (cellType == CellType.BOOLEAN) {
System.out.print(cell.getBooleanCellValue() + "\t");
} else if (cellType == CellType.STRING) {
System.out.print(cell.getStringCellValue() + "\t");
}
}
System.out.println("\n------------------------------------------");
}
System.out.println("\n==========================================\n");
/**
* 方式2 普通for
*/
// 获取最后一行
int lastRowNum = sheet.getLastRowNum();
// 从第一行开始遍历，直到最后一行
for (int j = 0; j <= lastRowNum; j++) {
// 获取当前行
XSSFRow row = sheet.getRow(j);
if (row != null) {
// 获取当前行最后一个单元格
short cellNum = row.getLastCellNum();
// 从第一个单元格开始遍历，直到最后一个单元格
for (int k = 0; k < cellNum; k++) {
XSSFCell cell = row.getCell(k);
CellType cellType = cell.getCellType();
// 注意，我们的EXCEL有NUMERIC、STRING和BOOLEAN三种类型，但这里省略了BOOLEAN，会发生什么呢？
if (cellType == CellType.NUMERIC) {
System.out.print(cell.getNumericCellValue() + "\t");
} else if (cellType == CellType.STRING) {
System.out.print(cell.getStringCellValue() + "\t");
}
}
}
System.out.println("\n------------------------------------------");
}
}
}输出结果
学生表1
------------------------------------------
姓名 年龄 籍贯 生日 身高 是否内地
------------------------------------------
周星驰 58.0 香港 22819.0 174.0 false
------------------------------------------
李健 46.0 哈尔滨 27295.0 174.5 1.0
------------------------------------------
周深 28.0 贵州 33876.0 161.0 true
------------------------------------------
==========================================
学生表1
------------------------------------------
姓名 年龄 籍贯 生日 身高 是否内地
------------------------------------------
周星驰 58.0 香港 22819.0 174.0
------------------------------------------
李健 46.0 哈尔滨 27295.0 174.5 1.0
------------------------------------------
周深 28.0 贵州 33876.0 161.0
------------------------------------------
关于POI的数值类型
观察上面的代码和输出结果，作出以下总结：
- POI抽象出了CellType的概念，必须判断单元格数据类型后再用对应的方法取值。如果类型枚举不完整，就无法读取到对应的数值（比如第二个for，没有判断Boolean），或者明明是A类型，但是用了getBTypeValue()也会报错
- POI对数值类型的描述比较笼统，NUMERIC对应了传统意义上的整数、小数、日期等一切和数字有关的类型（比如生日，变成了22819.0），在某些场景下会造成困扰
在POI的世界里，它把Excel单元格的数据类型抽象为CellType枚举，里面定义了7种类型：
/**
* @since POI 3.15 beta 3
*/
public enum CellType {
/**
* 未知类型，用于表示初始化之前或缺少具体类型的状态。仅供内部使用
*/
@Internal(since="POI 3.15 beta 3")
_NONE(-1),
/**
* 数字类型(整数、小数/分数、日期)
*/
NUMERIC(0),
/** 文本类型 */
STRING(1),
/**
* 计算公式类型（Excel经常会有sum/average等计算公式）
* @see FormulaType
*/
FORMULA(2),
/**
* 空类型
*/
BLANK(3),
/**
* Boolean类型
*/
BOOLEAN(4),
/**
* 错误类型
* @see FormulaError
*/
ERROR(5);
/**
* @since POI 3.15 beta 3
* @deprecated POI 3.15 beta 3
*/
private final int code;
/**
* @since POI 3.15 beta 3
* @deprecated POI 3.15 beta 3
*/
private CellType(int code) {
this.code = code;
}
/**
* @since POI 3.15 beta 3.
* @deprecated POI 3.15 beta 3. Used to transition code from <code>int</code>s to <code>CellType</code>s.
*/
public static CellType forInt(int code) {
for (CellType type : values()) {
if (type.code == code) {
return type;
}
}
throw new IllegalArgumentException("Invalid CellType code: " + code);
}
/**
* @since POI 3.15 beta 3
* @deprecated POI 3.15 beta 3
*/
public int getCode() {
return code;
}
}- NUMERIC：对应Excel的所有数字类型(整数、小数/分数、日期)，底层用double保存数据
- STRING：对应Excel的所有文本类型
- BOOLEAN：Excel中只有true/false是Boolean类型，1/0在POI中对应NUMERIC，是普通的1/0
- _NONE：POI内部使用，不用管
- FORMULA/ERROR：这两个是一伙的，和计算公式有关，我们很少接触
- BLANK：空
POI实在太懒了，竟然期望用NUMERIC表示整数、小数、分数、日期等所有和数字有关的类型。我们通过上面的案例可以观察到，NUMERIC底层其实使用的是double，统一保存整数、小数、分数及日期，所以上面案例中的年龄、身高、生日导出到Excel后都变成了小数。
优化导入
上面我们说过，POI中只有NUMERIC，所以Excel中的整数、小数、分数及日期被读取到后都用double存储：
如果我们希望内存中解析得到的类型是数据原本的类型，就必须根据CellType判断并进行类型转换。
好在POI有点良心，提供了DateUtil，可以从NUMERIC中拆出Date类型。
public class SimpleTestTwo {
@Test
public void testSimpleRead() throws IOException {
// 获取工作薄（把路径换成你本地的）
XSSFWorkbook workbook = new XSSFWorkbook("/Users/bravo1988/Desktop/student_info.xlsx");
// 获取工作表。一个工作薄中可能有多个工作表，比如sheet1 sheet2，可以根据下标，也可以根据sheet名称
XSSFSheet sheet = workbook.getSheetAt(0);
// 这里采用普通for
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i <= lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);
if (row != null) {
short cellNum = row.getLastCellNum();
for (int k = 0; k < cellNum; k++) {
XSSFCell cell = row.getCell(k);
// 要判断类型并采用对应的get方法，由于比较繁琐，我们抽取成方法
System.out.print(getValue(cell) + "\t");
}
}
System.out.println("\n------------------------------------------");
}
}
/**
* 提供POI数据类型 到 Java数据类型的转换，最终都返回String
*
* @param cell
* @return
*/
private String getValue(Cell cell) {
if (cell == null) {
return "";
}
// 常用的一般就这三大类：STRING、NUMERIC、BOOLEAN，几乎没有别的类型了。但NUMERIC要细分，特别注意
switch (cell.getCellType()) {
case STRING:
return cell.getRichStringCellValue().getString().trim();
// EXCEL的日期和数字都被POI整合为NUMERIC，这里把它们重新拆开
case NUMERIC:
// DateUtil是POI内部提供的日期工具类，可以把原本是日期类型的NUMERIC转为Java的Data类型
if (DateUtil.isCellDateFormatted(cell)) {
Date javaDate = DateUtil.getJavaDate(cell.getNumericCellValue());
return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(javaDate);
} else {
// 无论EXCEL中是58还是58.0，数值类型在POI中最终都被解读为double。这里的解决办法是通过BigDecimal先把double先转成字符串，如果是.0结尾，把.0去掉
String strCell = "";
double num = cell.getNumericCellValue();
BigDecimal bd = new BigDecimal(Double.toString(num));
strCell = bd.toPlainString();
// 去除 浮点型 自动加的 .0
if (strCell.endsWith(".0")) {
strCell = strCell.substring(0, strCell.indexOf("."));
}
return strCell;
}
case BOOLEAN:
boolean booleanCellValue = cell.getBooleanCellValue();
return String.valueOf(booleanCellValue);
default:
return "";
}
}
}输出结果
学生表1
------------------------------------------------------
姓名 年龄 籍贯 生日 身高 是否内地
------------------------------------------------------
周星驰 58 香港 1962-06-22 00:00:00 174 false
------------------------------------------------------
李健 46 哈尔滨 1974-09-23 00:00:00 174.5 true
------------------------------------------------------
周深 28 贵州 1992-09-29 00:00:00 161 true
------------------------------------------------------
简单的导出
public class SimpleTestThree {
@Test
public void testSimpleWrite() throws IOException, ParseException {
// 创建工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表
XSSFSheet sheet = workbook.createSheet("student");
// 构造假数据
List<Student> studentList = new ArrayList<>();
studentList.add(new Student(1L, "周星驰", 58, "香港", new SimpleDateFormat("yyyy-MM-dd").parse("1962-6-22"), 174.0, false));
studentList.add(new Student(2L, "李健", 46, "哈尔滨", new SimpleDateFormat("yyyy-MM-dd").parse("1974-9-23"), 174.5, true));
studentList.add(new Student(3L, "周深", 28, "贵州", new SimpleDateFormat("yyyy-MM-dd").parse("1992-9-29"), 161.0, true));
for (int i = 0; i < studentList.size(); i++) {
Student student = studentList.get(i);
// 创建行
XSSFRow row = sheet.createRow(i);
// 在当前行创建6个单元格，并设置数据（id不导出）
row.createCell(0).setCellValue(student.getName());
row.createCell(1).setCellValue(student.getAge());
row.createCell(2).setCellValue(student.getAddress());
row.createCell(3).setCellValue(student.getBirthday());
row.createCell(4).setCellValue(student.getHeight());
row.createCell(5).setCellValue(student.getIsMainlandChina());
}
FileOutputStream out = new FileOutputStream("/Users/bravo1988/Desktop/student_info_export.xlsx");
workbook.write(out);
out.flush();
out.close();
workbook.close();
System.out.println("导出成功！");
}
@Data
@AllArgsConstructor
static class Student {
private Long id;
private String name;
private Integer age;
private String address;
private Date birthday;
private Double height;
private Boolean isMainlandChina;
}
}
观察导出结果，其实还是NUMERIC的问题。Java的Date、Integer、Double经过POI转换后都变成了double，所以最终在Excel中呈现出来的就是一个数值，而日期也就成了现在看到的22819。
来看看Java中的类型是怎么变成POI的NUMERIC的吧：
setCellValue()有多个重载方法，对于Integer，其实都可以用setCellValue(double)接收：
public static void main(String[] args) {
// age会自动类型提升为double，输出58.0
Integer age = 58;
System.out.println((double)age);
}Double就更不用说了，直接拆箱成double即可。而Date其实最终也会调用setCellValue(double)：
DateUtil.getExcelDate()会把Date变成double，然后调用setCellValue(double)。
总之，NUMERIC类型底层就是double存储，Java的Date/Integer/Double等数值在转换到POI的NUMERIC后都是double，而Excel只不过是原样展示罢了。
如果我们希望导出时Excel按我们期望的格式展示，就要在代码中明确：我希望你按xx格式展示这个double。
那么，如何让Excel把22819.0解读为一个日期格式呢？
告诉Excel如何解读这个double类型的数字即可！比如20200329，无论解读为“两千零二十万叁佰二十九”或者“2020年3月29日”都是对的，当Excel认为这是一个日期时，就会展示成日期。
public class SimpleTestFour {
@Test
public void testSimpleWrite() throws IOException, ParseException {
// 创建工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表
XSSFSheet sheet = workbook.createSheet("student");
// 构造假数据
List<Student> studentList = new ArrayList<>();
studentList.add(new Student(1L, "周星驰", 58, "香港", new SimpleDateFormat("yyyy-MM-dd").parse("1962-6-22"), 174.0, false));
studentList.add(new Student(2L, "李健", 46, "哈尔滨", new SimpleDateFormat("yyyy-MM-dd").parse("1974-9-23"), 174.5, true));
studentList.add(new Student(3L, "周深", 28, "贵州", new SimpleDateFormat("yyyy-MM-dd").parse("1992-9-29"), 161.0, true));
for (int i = 0; i < studentList.size(); i++) {
Student student = studentList.get(i);
// 创建行
XSSFRow row = sheet.createRow(i);
// 在当前行创建6个单元格，并设置数据（id不导出）
row.createCell(0).setCellValue(student.getName());
row.createCell(1).setCellValue(student.getAge());
row.createCell(2).setCellValue(student.getAddress());
// 创建cell
XSSFCell birthdayCell = row.createCell(3);
// 设置样式！！！指定Excel对数值的解读方式
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFDataFormat dataFormat = workbook.createDataFormat();
// 样式随意，可以是yyyy-MM-dd或yyyy/MM-dd都行
cellStyle.setDataFormat(dataFormat.getFormat("yyyy/MM/dd"));
birthdayCell.setCellStyle(cellStyle);
// 上面设置完样式后，才最终设置值
birthdayCell.setCellValue(student.getBirthday());
row.createCell(4).setCellValue(student.getHeight());
row.createCell(5).setCellValue(student.getIsMainlandChina());
}
FileOutputStream out = new FileOutputStream("/Users/bravo1988/Desktop/student_info_export_2.xlsx");
workbook.write(out);
out.flush();
out.close();
workbook.close();
System.out.println("导出成功！");
}
@Data
@AllArgsConstructor
static class Student {
private Long id;
private String name;
private Integer age;
private String address;
private Date birthday;
private Double height;
private Boolean isMainlandChina;
}
}
有同学可能发现了：
row.createCell(3).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(student.getBirthday()));这样也能输出1962-6-22。
没错，但是本质上来说，这是把Date格式化为字符串，也就是POI的STRING，然后按照文本格式输出到Excel。下次再导入时，其实对应POI的STRING，而不再是NUMERIC了，会造成导入导出数据类型产生变化。
至于为什么Integer的age转为NUMERIC时变成了58.0，输出到Excel却变成了58，我认为可能纯粹是因为Excel默认的数据类型是常规，即使你输入58.0，也只会显示58。
这里就不去纠结了，反正我们到时用的是EasyExcel，而不是自己封装POI。
模板导出
把student_info.xlsx改成下面的样子，做成一个模板：
我们希望数据库查询出数据后，读取模板并按照模板第一行的样式填充：
public class SimpleTestFive {
/**
* 按模板样式导出Excel
*
* @throws ParseException
* @throws IOException
*/
@Test
public void testWriteWithStyle() throws IOException, ParseException {
// 查询数据
List<Student> studentList = new ArrayList<>();
studentList.add(new Student(1L, "周深", 28, "贵州", new SimpleDateFormat("yyyy-MM-dd").parse("1992-9-29"), 161.0, true));
studentList.add(new Student(2L, "李健", 46, "哈尔滨", new SimpleDateFormat("yyyy-MM-dd").parse("1974-9-23"), 174.5, true));
studentList.add(new Student(3L, "周星驰", 58, "香港", new SimpleDateFormat("yyyy-MM-dd").parse("1962-6-22"), 174.0, false));
// 读取模板
XSSFWorkbook workbook = new XSSFWorkbook("/Users/bravo1988/Desktop/student_template.xlsx");
// 获取模板sheet
XSSFSheet sheet = workbook.getSheetAt(0);
// 找到数据起始行（前两行是标题和表头，要跳过，所以是getRow(2)）
XSSFRow dataTemplateRow = sheet.getRow(2);
// 构造一个CellStyle数组，用来存放单元格样式。一行有N个单元格，长度就设置为N
CellStyle[] cellStyles = new CellStyle[dataTemplateRow.getLastCellNum()];
for (int i = 0; i < cellStyles.length; i++) {
// 收集每一个格子对应的格式，你可以理解为准备了一把“格式刷”
cellStyles[i] = dataTemplateRow.getCell(i).getCellStyle();
}
// 创建单元格，并设置样式和数据
for (int i = 0; i < studentList.size(); i++) {
// 注意是i+2，模板前两行是大标题和表头。你可能看着难受，想把上面for的i改为i+2，千万别。因为studentList必须从0开始取值
XSSFRow row = sheet.createRow(i + 2);
// 为每一行创建单元格并设置数据
Student student = studentList.get(i);
XSSFCell nameCell = row.createCell(0);// 创建单元格
nameCell.setCellValue(student.getName()); // 设置值
nameCell.setCellStyle(cellStyles[0]); // 设置单元格样式
XSSFCell ageCell = row.createCell(1);
ageCell.setCellValue(student.getAge());
ageCell.setCellStyle(cellStyles[1]);
XSSFCell addressCell = row.createCell(2);
addressCell.setCellValue(student.getAddress());
addressCell.setCellStyle(cellStyles[2]);
/**
* 你可能有疑问，这里是日期类型，是不是要和上一次一样，设置单元格样式为日期类型？
* 这回不用了，因为上面已经拷贝了模板的样式，生日一栏就是按日期类型展示的
*/
XSSFCell birthdayCell = row.createCell(3);
birthdayCell.setCellValue(student.getBirthday());
birthdayCell.setCellStyle(cellStyles[3]);
XSSFCell heightCell = row.createCell(4);
heightCell.setCellValue(student.getHeight());
heightCell.setCellStyle(cellStyles[4]);
XSSFCell mainLandChinaCell = row.createCell(5);
mainLandChinaCell.setCellValue(student.getIsMainlandChina());
mainLandChinaCell.setCellStyle(cellStyles[5]);
}
// 输出
FileOutputStream out = new FileOutputStream("/Users/bravo1988/Desktop/student_info_export_3.xlsx");
workbook.write(out);
out.flush();
out.close();
workbook.close();
System.out.println("导出成功！");
}
@Data
@AllArgsConstructor
static class Student {
private Long id;
private String name;
private Integer age;
private String address;
private Date birthday;
private Double height;
private Boolean isMainlandChina;
}
}
你看，姓名一栏是不是所有人的样式都是 加粗+红色，和模板的姓名样式一样呢？
希望通过这篇文章，大家不再惧怕POI，往后能自信地使用各种第三方Excel工具类。
作者简介：大家好，我是smart哥，前中兴通讯、美团架构师，现某互联网公司CTO
进群，大家一起学习，一起进步，一起对抗互联网寒冬
273
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
