刚好有需求,封装了下较为通用的导出工具类
代码实现
maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
工具类实现:
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @Author:Ding Wenhao
* @Date:2020-07-31 16:39
* @Description:Excel导出工具类
*/
public class ExcelExportUtil {
/**
* 将传入的数据导出excel表并下载
* @param response 返回的HttpServletResponse
* @param importList 导出的数据Map
* @param titleMap 标题Map, key :importList 中 Map 的key ,即列对应的key value : 标题文字
* @param fileName 文件名
*/
public static void export(HttpServletResponse response, List<Map> importList,Map<String,String> titleMap,String fileName) {
// 声明工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成表格
HSSFSheet sheet = workbook.createSheet();
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 18);
// 循环字段名数组,创建标题行
Row row = sheet.createRow(0);
// 列key数组
String[] columnKeys = new String[titleMap.size()];
// 处理Map key 并 创建标题行
int position = 0;
for(Map.Entry<String, String> vo : titleMap.entrySet()){
String key = vo.getKey();
String value = vo.getValue();
// 创建列
Cell cell = row.createCell(position);
// 设置单元类型为String
cell.setCellType(CellType.STRING);
cell.setCellValue(value);
columnKeys[position] = key;
position++;
}
// 创建普通行
for (int i = 0;i < importList.size();i++){
// 从第二行开始
row = sheet.createRow(i+1);
for (int j = 0;j < columnKeys.length;j++){
//创建列
Cell cell = row.createCell(j);
Map lineMap = importList.get(i);
Object columnValue = lineMap.get(columnKeys[j]);
if(CommonUtil.isEmpty(columnValue)){
// 数据为空 默认空字符
cell.setCellValue("");
cell.setCellType(CellType.STRING);
}else if(columnValue instanceof Integer){
// 数字
cell.setCellValue((Integer)columnValue);
cell.setCellType(CellType.NUMERIC);
}else if(columnValue instanceof Date){
// 日期
SimpleDateFormat sdfDay = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(sdfDay.format(columnValue));
cell.setCellType(CellType.STRING);
}else{
// 默认字符串
cell.setCellValue(columnValue.toString());
cell.setCellType(CellType.STRING);
}
}
}
response.setContentType("application/octet-stream;charset=utf-8");
//默认Excel名称
response.setHeader("Content-Disposition", "attachment;fileName="+fileName+".xls");
try {
response.flushBuffer();
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
}
测试代码:
@GetMapping("/import")
public void importExcel(HttpServletResponse response){
// 模拟查询数据
List<Map> importList = new ArrayList<>();
for(int i = 1; i <= 5; i++){
Map map = new HashMap();
map.put("fromUser","关注人"+i);
map.put("createTime","关注时间"+i);
map.put("eventKey","二维码来源"+i);
importList.add(map);
}
// 处理标题
Map<String,String> titleMap = new LinkedHashMap<>(3);
titleMap.put("fromUser","关注人");
titleMap.put("createTime","关注时间");
titleMap.put("eventKey","二维码来源");
ExcelExportUtil.export(response,importList,titleMap,"testData");
}
导出结果: