前言：在日常的开发中，避免不了操作Excel，比如从系统当中导出一个报表，或者通过解析客户上传的Excel文件进行批量解析数据入库等等，本篇博客主要汇总日常开发中如何使用开源的Apache提供的POI流操作Excel进行导入导出功能详解。
目录
一、导入pom.xml依赖
二、准备数据
三、导出功能
3.1、新建文件
3.2、新建工作簿
3.3、自定义样式
3.4、创建行和列并填充数据
3.5、合并单元格
3.6、创建批注
3.7、遍历数据
3.8、设置响应下载
3.9、运行截图
四、导入功能
4.1、获取单元格值方法
4.2、获取文件流
4.3、获取Excel文件对象
4.4、获取工作簿
4.5、获取首尾行下标
4.6、读取数据
4.7、测试运行
五、完整代码
六、Gitee源码
一、导入pom.xml依赖
这边我使用的是Apache提供的POI流来操作Excel。
org.springframework.boot
spring-boot-starter-web
org.projectlombok
lombok
true
org.apache.poi
poi-ooxml
4.1.2
org.springframework.boot
spring-boot-starter-test
test
二、准备数据
1、学生实体类
@Data
public class Student {
private int id;
private String name;
private String sex;
private String address;
private String phone;
}
2、初始化数据
public List initData(){
List list = new ArrayList();
Student student = new Student();
student.setId(1);
student.setName("张三");
student.setSex("男");
student.setAddress("江苏");
student.setPhone("19991198111");
list.add(student);
student = new Student();
student.setId(2);
student.setName("李四");
student.setSex("女");
student.setAddress("南京");
student.setPhone("1887119121");
list.add(student);
return list;
}
public void export(HttpServletResponse response) throws IOException {
//获取学生数据
List students = initData();
//获取标题
String[] titles = {"编号","姓名","性别","地址","电话"};
//导出Excel的代码
...
}三、导出功能
这边主要讲解关于遍历数据、设置单元格样式、合并单元格和插入批注一些常用的功能。
3.1、新建文件
这边提供了2种Excel格式的文件，选一个即可，为了更好的兼容性，这边我采用的是.xls格式。
//.xls格式
HSSFWorkbook workbook = new HSSFWorkbook();
//.xlsx格式
//XSSFWorkbook workbook = new XSSFWorkbook();
3.2、新建工作簿
这行代码就是新建一个名为学生表的工作簿
HSSFSheet sheet = workbook.createSheet("学生表");3.3、自定义样式
实例化出来的cellStyle提供给了我们很多关于设置样式的方法，这边我就提供一些常用的设置单元格样式的代码，把它封装成了一个方法，每行代码上面都有注释。
HSSFCellStyle cellStyle = workbook.createCellStyle();常用样式代码：
/**
* 单元格样式
* @param workbook
* @param fontBold 是否加粗
* @param isBorder 是否带边框
* @param horizontalAlignment 水平对齐格式
* @param verticalAlignment 垂直对齐格式
* @return
*/
private HSSFCellStyle getColumnStyle(HSSFWorkbook workbook,
boolean fontBold,
boolean isBorder,
HorizontalAlignment horizontalAlignment,
VerticalAlignment verticalAlignment) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 12);
//设置字体名字
font.setFontName("宋体");
font.setBold(fontBold);
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 是设置前景色不是背景色
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
if (isBorder) {
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.index);
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.index);
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.index);
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.index);
}
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(horizontalAlignment);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(verticalAlignment);
return style;
}3.4、创建行和列并填充数据
首先我们需要先通过HSSFRow创建单元行，其次再通过HSSFRow实例化的对象去创建单元列，最后通过HSSFCell实例化的对象去填充数据，这边我创建了一个名为学生信息表的标题。
//创建单元行
HSSFRow header = sheet.createRow(0);
//创建单元列
HSSFCell headerCell = header.createCell(0);
//填充信息
headerCell.setCellValue("学生信息表");再通过同样的代码遍历我们的字段标题
//创建第一行
HSSFRow titleRow = sheet.createRow(1);
//样式
HSSFCellStyle cellStyle = this.getColumnStyle(workbook, true, true, HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
//写入标题
for(int i = 0 ; i 3.5、合并单元格
首先创建 CellRangeAddress对象，表示要合并的单元格范围，然后使用 addMergedRegion()方法合并单元格，以下这是CellRangeAddress的参数详解。
| startRow | 开始行0，表示第1行 |
| endRow | 结束行1，表示第2行 |
| startColumn | 开始列2，表示第3列 |
| endColumn | 结束列3，表示第4列 |
代码如下：
CellRangeAddress region = new CellRangeAddress(0, 0 , 0, titles.length-1);
sheet.addMergedRegion(region);3.6、创建批注
首先如果要在工作表上添加批注/图片/图形等元素，需要先调用createDrawingPatriarch()来获取工作表的绘制对象Drawing。
其次在工作表中创建单元格批注的对象，HSSFClientAnchor用于创建一个新的端锚,并设置锚的左下和右下坐标，用于图片插入，画线等操作。
HSSFClientAnchor的参数顺序如下：
左边距、上边距、右边距、下边距 、 开始列、开始行 、结束列、 结束行。
Drawing drawing = sheet.createDrawingPatriarch();
Comment comment = drawing.createCellComment(new HSSFClientAnchor(0, 0, 0, 0,
(short) headerCell.getColumnIndex(),
headerCell.getRowIndex(),
(short) (headerCell.getColumnIndex() + 5),
headerCell.getRowIndex() + 6));
comment.setString(new HSSFRichTextString("这是标题信息哦！"));
headerCell.setCellComment(comment);所以这段代码的意思是：在工作表上，创建了一个单元格批注comment，这个批注的锚点设置在从单元格headerCell开始，延伸5列6行。
3.7、遍历数据
最后我们通过循环来遍历我们集合当中的数据，上面已经讲过，这边不再多做讲解。
for(int i = 0 ; i 3.8、设置响应下载
1、定义Excel文件名
2、把文件名编码为 ISO-8859-1格式,从而支持中文下载文件名
3、设置 Response为附件应答
4、设置附件名称为codeFileName，告诉浏览器这是一个附件应答
5、从workbook中获取输出流，写入内容到response输出流
6、关闭workbook，释放资源
String fileName = "学生表统计";
String codeFileName = new String((fileName + ".xls").getBytes("UTF-8"), "ISO-8859-1");
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + codeFileName);
OutputStream out = response.getOutputStream();
workbook.write(response.getOutputStream());
out.flush();
out.close();
workbook.close();3.9、运行截图
浏览器访问：http://localhost:8080/student/export
四、导入功能
4.1、获取单元格值方法
如果当前单元格的格式是时间格式，则需要特殊处理为时间格式，否则会报错。
String dataFormatString = cell.getCellStyle().getDataFormatString();
if(dataFormatString.equals("m/d/yy")){
cellValue = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
return cellValue;
}如果当前单元格是数字，比如1，那么读出来会默认是1.0，所以要把数字当成String来读。
if(cell.getCellType() == CellType.NUMERIC){
cell.setCellType(CellType.STRING);
}先判断读取的类型，来通过switch结构来获取单元格值。
//判断数据的类型
switch (cell.getCellType()){
//数字
case NUMERIC:
cellValue = String.val
break;
//字符串
case STRING:
cellValue = String.val
break;
//Boolean
case BOOLEAN:
cellValue = String.val
break;
//公式
case FORMULA:
cellValue = String.val
break;
//空值
case BLANK:
cellValue = "";
break;
//故障
case ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}4.2、获取文件流
FileInputStream in = new FileInputStream("F:\学生表统计.xls");4.3、获取Excel文件对象
我这边我获取的是.xls后缀格式的文件
Workbook workbook = new HSSFWorkbook(in);这个是获取.xlsx格式的对象
Workbook workbook = new XSSFWorkbook(in);4.4、获取工作簿
默认通过工作簿名称来获取工作簿对象
Sheet sheet = workbook.getSheet("学生表");也可以通过下标来进行获取
Sheet sheet = workbook.getSheetAt(0);4.5、获取首尾行下标
获取首行下标
int firstRowNum = sheet.getFirstRowNum();
获取尾行下标
int lastRowNum = sheet.getLastRowNum();
4.6、读取数据
使用for循环读取每行的第一列单元格的数据，获取数据的方法就是我上面封装好的方法。
for(int i = firstRowNum ; i 4.7、测试运行
我准备了7种不同类型的数据进行读取
单元测试结果如下，读取一切正常。
五、完整代码
导入和导出的完整代码：
package com.example.excel.service;
import com.example.excel.domain.Student;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
* @author HTT
*/
@Service
public class StudentService {
public List initData(){
List list = new ArrayList();
Student student = new Student();
student.setId(1);
student.setName("张三");
student.setSex("男");
student.setAddress("江苏");
student.setPhone("19991198111");
list.add(student);
student = new Student();
student.setId(2);
student.setName("李四");
student.setSex("女");
student.setAddress("南京");
student.setPhone("1887119121");
list.add(student);
return list;
}
public void export(HttpServletResponse response) throws IOException {
//获取学生数据
List students = initData();
//获取标题
String[] titles = {"编号","姓名","性别","地址","电话"};
//.xls格式
HSSFWorkbook workbook = new HSSFWorkbook();
//.xlsx格式
//XSSFWorkbook workbook = new XSSFWorkbook();
//新增工作表
HSSFSheet sheet = workbook.createSheet("学生表");
//单元格样式对象
HSSFCellStyle cellStyle = this.getColumnStyle(workbook, true, true, HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
//创建第一行
HSSFRow header = sheet.createRow(0);
HSSFCell headerCell = header.createCell(0);
headerCell.setCellValue("学生信息表");
CellRangeAddress region = new CellRangeAddress(0, 0 , 0, titles.length-1);
sheet.addMergedRegion(region);
/**
* - 左边距
* - 上边距
* - 右边距
* - 下边距
* - 开始列
* - 开始行
* - 结束列
* - 结束行
* 在工作表`drawing`上,
* 创建了一个单元格批注 comment。
* 这个批注的锚点设置在从单元格 headerCell 开始,延伸 5 列 6 行。
*/
Drawing drawing = sheet.createDrawingPatriarch();
Comment comment = drawing.createCellComment(new HSSFClientAnchor(0, 0, 0, 0,
(short) headerCell.getColumnIndex(),
headerCell.getRowIndex(),
(short) (headerCell.getColumnIndex() + 5),
headerCell.getRowIndex() + 6));
comment.setString(new HSSFRichTextString("这是标题信息哦！"));
headerCell.setCellComment(comment);
//创建第一行
HSSFRow titleRow = sheet.createRow(1);
//写入标题
for(int i = 0 ; i 请求层
package com.example.excel.controller;
import com.example.excel.service.StudentService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@RestController
@RequestMapping("/student")
public class StudentController {
@Resource
private StudentService service;
@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
service.export(response);
}
}
六、Gitee源码
SpringBoot使用Apache操作Excel导入与导出: 主要汇总日常开发中常用的关于一些关于Excel导入导出功能详解。
