目录
背景描述
最近博主在做的Web项目中,有一个导出数据到Excel表格的需求,之前用纯JS实现过,这次打算用Java在后端实现,将数据通过response以IO流的方式传输给前端,使浏览器能直接下载。这里做一下记录、笔记。
技术准备
我的项目是基于Spring Boot的,这里只贴出POI框架需要依赖的两个包,其他的都无所谓,只要能提供Controller让浏览器访问即可
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
导出Excel——尝鲜版
这里为了让大家理解POI这个框架一系列的API,先以最low的方式去实现,稍后我们再进行封装,达到“编写一次、处处可用”。
我们只需要提供一个Controller接口:
/**
* 导出数据到Excel
* @param response 响应体
* 注意,我这里是基于Spring Boot的,全局有一个@RestController注解,所以没加@ResponseBody,
* 如果你的不是,请加上@ResponseBody注解
* */
@GetMapping(value = "/out-excel-demo")
public Object outExcelDemo(HttpServletResponse response) throws IOException {
//创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
//创建sheet对象(excel的表单)
HSSFSheet sheet=wb.createSheet("sheet1");
//创建第一行,这里即是表头。行的最小值是0,代表每一行,上限没研究过,可参考官方的文档
HSSFRow row1=sheet.createRow(0);
//在这一行创建单元格,并且将这个单元格的内容设为“账号”,下面同理。
//列的最小值标识也是0
row1.createCell(0).setCellValue("账号");
row1.createCell(1).setCellValue("用户名");
row1.createCell(2).setCellValue("日期");
row1.createCell(3).setCellValue("是否完成");
//第二行
HSSFRow row2=sheet.createRow(1);
row2.createCell(0).setCellValue("123456");
row2.createCell(1).setCellValue("张三");
row2.createCell(2).setCellValue("2019-08-05");
row2.createCell(3).setCellValue("是");
//第三行
HSSFRow row3=sheet.createRow(2);
row3.createCell(0).setCellValue("5681464");
row3.createCell(1).setCellValue("李四");
row3.createCell(2).setCellValue("2019-08-01");
row3.createCell(3).setCellValue("否");
//输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
response
.setHeader("Content-disposition", "attachment; filename=demo.xls");
response.setContentType("application/x-xls");
wb.write(output);
output.close();
return null;
}
然后你可以在页面上写一个按钮,点击的时候通过location.href指向上面的接口路径,我这里就省略了,看一下效果:
打开表格:
到这里,相信大家对POI有认识了吧?
其实它就是以每个HSSFRow为一个主体,每一个HSSFRow代表一行记录,我们只需要通过这个对象的createCell方法去创建单元格、赋值就行,这样就很清晰了吧?
导出Excel——封装版(通过反射)
以上我们实现了简单的数据导出,但是实际的场景根本不是这样,我们都是从数据库里查出来数据,而且不可能这样一行一行的去设置。
你肯定想到了循环,没错,循环是肯定的,但是仅仅循环还不够灵活,为什么呢?
根据面向对象的思维,我们可以将所有的表头(即第一行)做成一个List集合参数,将所有的数据做成一个List集合参数,这个数据集合的泛型是我们的POJO实体类,然后我们两个循环就能省略一大段代码。
但是问题来了,我们例子中的导出表格,是“账号、用户名、日期、是否完成”这四个表头,实体类也是对应的四个属性。假如又来了一个导出需求呢?表头不一样了,所对应的实体类也不一样了,难道我们再封装成一个其他的方法?难道每个不同的Excel表结构都要封装一个新的方法吗?
做的时候博主立马就想到了反射机制,我们可以传入List集合,对泛型不做限制,遍历数据集合的时候,通过反射得到这个对象的字段,动态赋值。
但是这就有一个强制要求:在实体类声明字段的时候,顺序必须和表头的前后顺序一致,否则循环遍历的时候会出现数据不对应的现象。
首先我们声明一个实体类,这也符合我们真正的开发环境:
package com.dosion.smart.future.api.entity.activity.json;
import lombok.Data;
/**
* 导出报名情况的数据传输对象
* @author 秋枫艳梦
* @date 2019-08-05
* */
@Data
public class SignOutExcelJSON {
//用户账号
private String account;
//用户名
private String username;
//报名时间
private String signDate;
//是否完成
private String finish;
}
然后封装一个工具类出来:
package com.dosion.smart.future.utils;
import com.dosion.smart.future.api.entity.activity.json.SignOutExcelJSON;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
/**
* 数据导出excel,工具类
* @author 秋枫艳梦
* @date 209-08-05
* */
public class ExcelUtil {
/**
* 生成Excel表格
* @param sheetName sheet名称
* @param titleList 表头列表
* @param dataList 数据列表
* @return HSSFWorkbook对象
* */
public static HSSFWorkbook createExcel(String sheetName,
List<String> titleList,List dataList) throws IllegalAccessException {
//创建HSSFWorkbook对象
HSSFWorkbook wb = new HSSFWorkbook();
//创建sheet对象
HSSFSheet sheet=wb.createSheet(sheetName);
//在sheet里创建第一行,这里即是表头
HSSFRow rowTitle=sheet.createRow(0);
//写入表头的每一个列
for (int i = 0; i < titleList.size(); i++) {
//创建单元格
rowTitle.createCell(i).setCellValue(titleList.get(i));
}
//写入每一行的记录
for (int i = 0; i < dataList.size(); i++) {
//创建新的一行,递增
HSSFRow rowData = sheet.createRow(i+1);
//通过反射,获取POJO对象
Class cl = dataList.get(i).getClass();
//获取类的所有字段
Field[] fields = cl.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
//设置字段可见,否则会报错,禁止访问
fields[j].setAccessible(true);
//创建单元格
rowData.createCell(j).setCellValue((String) fields[j].get(dataList.get(i)));
}
}
return wb;
}
}
然后我们模仿一下调用(这里手动制造数据,真实情况下通过数据库查询):
/**
* 导出Excel
*
*
* */
@GetMapping(value = "/out-excel-demo")
public String outExcelDemo(HttpServletResponse response) throws IOException, IllegalAccessException {
//文件名
String fileName = "活动报名情况一览表";
//sheet名
String sheetName = "报名情况sheet";
//表头集合,作为表头参数
List<String> titleList = new ArrayList<>();
titleList.add("用户账户");
titleList.add("用户名");
titleList.add("报名时间");
titleList.add("是否完成");
//数据对象,这里模拟手动添加,真实的环境往往是从数据库中得到
SignOutExcelJSON excelJSON = new SignOutExcelJSON();
excelJSON.setAccount("18210825916");
excelJSON.setUsername("张三");
excelJSON.setSignDate("2019-08-05");
excelJSON.setFinish("是");
SignOutExcelJSON excelJSON2 = new SignOutExcelJSON();
excelJSON2.setAccount("15939305781");
excelJSON2.setUsername("李四");
excelJSON2.setSignDate("2019-08-01");
excelJSON2.setFinish("否");
//将两个对象加入到集合中,作为数据参数
List<SignOutExcelJSON> excelJSONList = new ArrayList<>();
excelJSONList.add(excelJSON);
excelJSONList.add(excelJSON2);
//调取封装的方法,传入相应的参数
HSSFWorkbook workbook = ExcelUtil.createExcel(sheetName,titleList, excelJSONList);
//输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
//中文名称要进行编码处理
response
.setHeader("Content-disposition", "attachment; filename="+new String(fileName.getBytes("GB2312"),"ISO8859-1")+".xls");
response.setContentType("application/x-xls");
workbook.write(output);
output.close();
return null;
}
运行结果:
效果是一样的,而且很灵活。各位可以试一下,假如你导出其他模块的数据,你只需要传入不同的表头集合、实体类集合,就能实现你的需求,这也是封装的魅力所在。
导出Excel——深度封装(设置下拉选项)
假如我有一个需求:是否完成这一列只能输入是否,以下拉框的形式出现。
POI框架肯定有对应的API,大家看文档也能学会,这里我带大家封装一下,毕竟以可重用性为荣。
先封装一个下拉条件对象:
package com.dosion.smart.future.api.entity.activity;
import lombok.Data;
/**
* 导出Excel时的条件,有下列选项时使用
* @author 秋枫艳梦
* @date 2019-08-05
* */
@Data
public class OutExcelQuery {
//起始行
private int rowStart;
//结束行
private int rowEnd;
//起始列
private int colStart;
//结束列
private int colEnd;
//下拉参数
private String[] params;
//构造函数
public OutExcelQuery(int rowStart,int rowEnd,int colStart,int colEnd,String[] params){
this.rowStart = rowStart;
this.rowEnd = rowEnd;
this.colStart = colStart;
this.colEnd = colEnd;
this.params = params;
}
}
再贴出来工具类:
package com.dosion.smart.future.utils;
import com.dosion.smart.future.api.entity.activity.OutExcelQuery;
import com.dosion.smart.future.api.entity.activity.json.SignOutExcelJSON;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
/**
* 数据导出excel,工具类
* @author 秋枫艳梦
* @date 209-08-05
* */
public class ExcelUtil {
/**
* 生成Excel表格
* @param sheetName sheet名称
* @param titleList 表头列表
* @param dataList 数据列表
* @param outExcelQuery 下拉选项设置
* @return HSSFWorkbook对象
* */
public static HSSFWorkbook createExcel(String sheetName, List<String> titleList,
List dataList, OutExcelQuery outExcelQuery) throws IllegalAccessException {
//创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
//创建sheet对象(excel的表单)
HSSFSheet sheet=wb.createSheet(sheetName);
//在sheet里创建第一行,这里即是表头
HSSFRow rowTitle=sheet.createRow(0);
//写入表头的每一个列
for (int i = 0; i < titleList.size(); i++) {
//创建单元格
rowTitle.createCell(i).setCellValue(titleList.get(i));
}
//写入每一行的记录
int count = 0;
for (int i = 0; i < dataList.size(); i++) {
count++;
//创建新的一行,递增
HSSFRow rowData = sheet.createRow(i+1);
//通过反射,获取POJO对象
Class cl = dataList.get(i).getClass();
//获取类的所有字段
Field[] fields = cl.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
//设置字段可见,否则会报错,禁止访问
fields[j].setAccessible(true);
//创建单元格
rowData.createCell(j).setCellValue((String) fields[j].get(dataList.get(i)));
}
}
//如果开启了下拉选项
if (outExcelQuery!=null){
//如果表格中的记录数不是0
if (count!=0){
// 获取下拉列表数据
String[] strs = outExcelQuery.getParams();
//设置哪些行的哪些列为下拉选项
CellRangeAddressList rangeList =
new CellRangeAddressList(outExcelQuery.getRowStart(),
//结束行为-1时,说明设置所有行
outExcelQuery.getRowEnd()==-1?count:outExcelQuery.getRowEnd(),
outExcelQuery.getColStart(),outExcelQuery.getColEnd());
//绑定下拉数据
DVConstraint constraint = DVConstraint.createExplicitListConstraint(strs);
//绑定两者的关系
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList,constraint);
//添加到sheet中
sheet.addValidationData(dataValidation);
}
}
return wb;
}
}
如果我不想设置任何列为下拉选项,那我调用的时候将最后一个参数传入null即可。如果想设置某一列或某几列为下拉选项,那我调用的时候只需要这样(省略其他代码):
String[] params = new String[]{"是","否"};
//从第一行开始,到最后一行结束,设置第4列为下拉选项
OutExcelQuery outExcelQuery = new OutExcelQuery(1,-1,3,3,params);
HSSFWorkbook workbook = ExcelUtil.createExcel(sheetName,titleList,activityService.outExcel(id),outExcelQuery);
效果:
扩展——多个列分别是不同的下拉选项怎么封装
以上下拉选项的封装,只是针对某一列或某几列使用相同的下拉选项的情况,假如几个数据列的下拉选项不同呢?
比如,再加一个性别列,下拉选项的值是男和女,此时一张Excel表中就出现了两个下拉选项设置,该怎么封装?
博主就不再写了,留给大家思考,有疑问的可以留言。
提示一个思路:可以应用Java可选参数的特性,传入多个OutExcelQuery对象,进行循环添加条件。
挖坑填坑,其乐融融。
2019-10-28 更新,必看!!!
最近有一位博友用到了我的这个工具类,首先表示很荣幸。
但是帮他解决问题的过程中,也发现了这个工具类的一点瑕疵,那就是:
之前的版本,必须要求表头、实体类的字段一一对应,且顺序要一致,比如你导出的表格中有姓名和年龄两个列,那么你的实体类中只能有name和age两个字段,且顺序要一致,否则会出现年龄的值出现在姓名列的情况。
这样确实有点不灵活,如果一个实体类有多个字段呢?如果依然采用这种方式,那我岂不是还要为导出表格专门写一个实体类?
所以,我做了以下改进,在循环写入每一行的列的时候,遍历的是titleList集合的长度,而不是实体类的字段数量,这样一来,我们有一个表头列,就会遍历出对象的几个属性,对象其他的属性将不会体现到表格里。举个例子:
假设导出的表头有姓名、年龄两个列,但是实体类有name、age、sex三个字段,那么我们遍历的是表头的长度,即2,那么sex这个字段将不会被写入表格里。这样一来,实体类就可以有任意个字段了,只需要保证前n个字段与表头保持一致即可。
需要注意的是,你需要导出的字段,在实体类里仍然需要按照表头的顺序进行排列,没办法,只能这么取舍了,否则就做不到万能了,当然大家也可以根据自己的业务去做定制化。
另外,此次增加了Excel的导入功能。
最后贴出来完整的工具类:
import com.dosion.model.activity.query.OutExcelQuery;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
/**
* 数据导出、导入excel,工具类
* @author 秋枫艳梦
* @date 2019-10-28
* */
public class ExcelUtil {
/**
* 生成Excel表格
* @param sheetName sheet名称
* @param titleList 表头列表
* @param dataList 数据列表
* @param outExcelQuery 下拉选项设置
* @return HSSFWorkbook对象
* */
public static HSSFWorkbook createExcel(String sheetName, List<String> titleList,
List dataList, OutExcelQuery outExcelQuery) throws IllegalAccessException {
//创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
//创建sheet对象(excel的表单)
HSSFSheet sheet=wb.createSheet(sheetName);
//在sheet里创建第一行,这里即是表头
HSSFRow rowTitle=sheet.createRow(0);
//写入表头的每一个列
for (int i = 0; i < titleList.size(); i++) {
//创建单元格
rowTitle.createCell(i).setCellValue(titleList.get(i));
}
int count = 0;
//写入每一行的记录
if (dataList!=null){
for (int i = 0; i < dataList.size(); i++) {
count++;
//创建新的一行,递增
HSSFRow rowData = sheet.createRow(i+1);
//通过反射,获取POJO对象
Class cl = dataList.get(i).getClass();
//获取类的所有字段
Field[] fields = cl.getDeclaredFields();
for (int j = 0; j < titleList.size(); j++) {
//设置字段可见,否则会报错,禁止访问
fields[j].setAccessible(true);
//创建单元格
rowData.createCell(j).setCellValue((String) fields[j].get(dataList.get(i)));
}
}
}
//如果开启了下拉选项
if (outExcelQuery!=null){
//如果表格中的记录数不是0
if (count!=0){
// 获取下拉列表数据
String[] strs = outExcelQuery.getParams();
//设置哪些行的哪些列为下拉选项
CellRangeAddressList rangeList =
new CellRangeAddressList(outExcelQuery.getRowStart(),
//结束行为-1时,说明设置所有行
outExcelQuery.getRowEnd()==-1?count:outExcelQuery.getRowEnd(),
outExcelQuery.getColStart(),outExcelQuery.getColEnd());
//绑定下拉数据
DVConstraint constraint = DVConstraint.createExplicitListConstraint(strs);
//绑定两者的关系
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList,constraint);
//添加到sheet中
sheet.addValidationData(dataValidation);
}
}
return wb;
}
/**
* 读入excel文件,解析后返回
* @param file
* @throws IOException
*/
public static List<String[]> readExcel(MultipartFile file) throws IOException{
//检查文件
checkFile(file);
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
//创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回
List<String[]> list = new ArrayList<String[]>();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//循环除了第一行的所有行
for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
//获得当前行
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
//循环当前行
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
workbook.close();
}
return list;
}
/**
* 检查用户上传的文件
* @param file 文件对象
* */
private static void checkFile(MultipartFile file) throws IOException{
//判断文件是否存在
if(null == file){
throw new FileNotFoundException("文件不存在!");
}
//获得文件名
String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
throw new IOException(fileName + "不是excel文件");
}
}
/**
* 获取Workbook对象
* @param file 文件对象
* @return Workbook对象
* */
private static Workbook getWorkBook(MultipartFile file) {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象,表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith("xls")){
//2003
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith("xlsx")){
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
}
return workbook;
}
/**
* 获取单元格的值
* @param cell 单元格对象
* @return 值
* */
private static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//把数字当成String来读,避免出现1读成1.0的情况
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判断数据的类型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "未填写";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}
2019-12-18更新,修复小概率的文件名乱码问题
最近有朋友反映说,他导出时有时候会出现文件名乱码的问题,博主复现了很多次,都没有复现出来。后来同事也反映这个问题,博主才重视起来。
经排查发现,首先之前的ISO8859-1,博主写的不规范,应该是ISO-8859-1(但是我感觉跟它没关系,哈哈);其次,博主把响应流的类型设置为了application/x-xls,这是标准的excel响应流格式。亲测有效,无论是.xls后缀还是.xlsx后缀,都没有问题。
在这里要感谢这篇文章,总结了各种的response响应流格式:
https://blog.csdn.net/luman1991/article/details/53423305
贴出关键代码部分:
//输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
//中文名称要进行编码处理
response
.setHeader("Content-disposition", "attachment; filename="+new String("游戏列表".getBytes("GB2312"),"ISO-8859-1")+".xlsx");
response.setContentType("application/x-xls");
workbook.write(output);
output.close();
另外,我发现有一个奇怪的现象,大部分中文名都能兼容,但是我把“游戏列表”换成“学校”,那么就会乱码,换成“学校列表”就又好了……
有人说跟浏览器的编码方式有关,需要在后端代码做处理,但是博主试了一个遍,还是失败。。。博主就退而求其次吧,我觉得这是可以接受的,换一个同义的文件名而已。