〇:工具类内容
1.先引用pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.66</version>
<scope>compile</scope>
</dependency>
<!--阿里巴巴导出 easyexcel jar-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
2、定义两个实体类
自行自动生成get,set方法
public class NoModelWriteData implements Serializable {
private String fileName;//文件名
private String[] headMap;//表头数组
private String[] dataStrMap;//对应数据字段数组
private List<Map<String, Object>> dataList;//数据集合
}
public class SimpleWriteData implements Serializable {
private String fileName;//文件名
private List<?> dataList;//数据列表
}
3、添加工具类内容
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import com.google.common.net.HttpHeaders;
import com.roc.common.model.NoModelWriteData;
import com.roc.common.model.SimpleWriteData;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.web.bind.annotation.RequestBody;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
public class EasyExcelUtils {
//不创建对象的导出
public void noModleWrite(@RequestBody NoModelWriteData data, HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
try {
// response.setContentType("application/vnd.ms-excel");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(data.getFileName(), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream()).head(head(data.getHeadMap())).sheet(data.getFileName()).doWrite(dataList(data.getDataList(), data.getDataStrMap()));
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
//创建对象的导出
public <T> void simpleWrite(@RequestBody SimpleWriteData data,Class<T> clazz, HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
// response.setContentType("application/vnd.ms-excel");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(data.getFileName(), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz).sheet(data.getFileName()).doWrite(data.getDataList());
}
//设置表头
private List<List<String>> head(String[] headMap) {
List<List<String>> list = new ArrayList<List<String>>();
for (String head : headMap) {
List<String> headList = new ArrayList<String>();
headList.add(head);
list.add(headList);
}
return list;
}
//设置导出的数据内容
private List<List<Object>> dataList(List<Map<String, Object>> dataList, String[] dataStrMap) {
List<List<Object>> list = new ArrayList<List<Object>>();
for (Map<String, Object> map : dataList) {
List<Object> data = new ArrayList<Object>();
for (int i = 0; i < dataStrMap.length; i++) {
data.add(map.get(dataStrMap[i]));
}
list.add(data);
}
return list;
}
}
一:不使用实体类导出
这里只针对接口返回的List<Map<String,Object>>类型数据做后续操作,跟随文章的步骤操作即可
1、查询数据
如图获取了List<Map<String, Object>>类型的数据
2、定义数组
headMap数组定义的是导出文件表头标题的内容,要按顺序定义
dataStrMap数据定义的是标题对应的字段名(一定要按顺序对应)
String[] headMap = { "项目名称", "楼栋名称", "单元名称", "楼层名称", "房间名称", "业主/租户姓名", "房间状态", "房间功能","认证人数" };
String[] dataStrMap={"hName","bName","uName","fName","pName","cName","pState","pFunction","pNum"};
3、将数据放入工具类方法的实体类中
NoModelWriteData d = new NoModelWriteData();
d.setFileName("认证统计");
d.setHeadMap(headMap);
d.setDataStrMap(dataStrMap);
d.setDataList(listDatas);
EasyExcelUtils easyExcelUtils = new EasyExcelUtils();
easyExcelUtils.noModleWrite(d, response);
二:使用实体类导出
1、定义数据实体类
自行自动生成get,set方法,这里就不放出来了
实体类中的注解可以根据自己的需求使用,更多注解可查看文章开头的官方示例文档
@Data
public class PayInfoBillListExportDto implements Serializable {
@ExcelProperty("项目名称")
private String hName;
@ExcelProperty("楼栋名称")
private String bName;
@ExcelProperty("单元名称")
private String uName;
@ExcelProperty("楼层名称")
private String fName;
@ExcelProperty("房间名称")
private BigDecimal pName;
@ExcelProperty("业主/租户姓名")
private BigDecimal cName;
@ExcelProperty("房间状态")
private BigDecimal pState;
@ExcelProperty("房间功能")
private BigDecimal pFunction;
@ExcelProperty("认证人数")
private String pNum;
// @ExcelIgnore 此注解表示忽略这个字段
// @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒") 时间格式化注解
// @NumberFormat("#.##%") 百分比表示
// @ColumnWidth(50) 设置单元格宽度为50
// @ExcelProperty(value = "标题", index = 0) 第0列为标题列
}
1、查询数据
2、将数据放入工具类方法的实体类中
SimpleWriteData d = new SimpleWriteData();
d.setFileName("认证统计");
d.setDataList(list);
EasyExcelUtils easyExcelUtils = new EasyExcelUtils();
easyExcelUtils.simpleWrite(d,PayInfoBillListExportDto.class,response);
此工具类只针对这两种数据进行封装
补充
但是在任务过程中,生成的文档的格式要求并不符合产品和测试的期望值:如图
经过百度工程师的各种挑战实验和查阅实现了此功能
Excel文档的自动列宽设置
//Excel文档的自动列宽设置
public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
//因为在自动列宽的过程中,有些设置地方让列宽显得紧凑,所以做出了个判断
private static final int COLUMN_WIDTH = 2;
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap(16);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}else {
if(columnWidth<COLUMN_WIDTH){
columnWidth =columnWidth*2;
}
}
Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth* 256);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = (CellData)cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch(type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
//Excel的字体,样式,背景色的设置
public static HorizontalCellStyleStrategy getStyleStrategy(){
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为灰色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
// 字体样式
headWriteFont.setFontName("Frozen");
headWriteFont.setFontHeightInPoints((short)12);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//自动换行
headWriteCellStyle.setWrapped(false);
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
// contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
// 背景白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short)12);
// 字体样式
contentWriteFont.setFontName("Calibri");
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
调用方式
这部分是因为我的Excel都是自定义开头数据,所以用的是List<Map<String, Object>>
EasyExcel.write(response.getOutputStream())
.head(head(data.getHeadMap()))
.registerWriteHandler(new ExcelWidthStyleStrategy())
.registerWriteHandler(ExcelWidthStyleStrategy.getStyleStrategy())
.sheet(data.getFileName())
.doWrite(dataList(data.getDataList()
,data.getDataStrMap()));
下面部分是用上实体类的调用
EasyExcel.write(outputStream).needHead(true)
.head(clazz)
.excelType(ExcelTypeEnum.XLSX)
.registerWriteHandler(new Custemhandler())
.registerWriteHandler(EasyExcelUtil.getStyleStrategy())
.sheet(0, sheetName)
.doWrite(writeList);
修改后
工具类内容
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.dd2007.property.webapp.easyExcel.entity.NoModelWriteData;
import com.dd2007.property.webapp.easyExcel.entity.SimpleWriteData;
import com.sun.deploy.net.URLEncoder;
import org.springframework.web.bind.annotation.RequestBody;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class EasyExcelUtils {
//不创建对象的导出
public void noModleWrite(@RequestBody NoModelWriteData data, HttpServletResponse response) throws IOException {
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(data.getFileName(), "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream()).head(head(data.getHeadMap())).registerWriteHandler(new ExcelWidthStyleStrategy()).registerWriteHandler(ExcelWidthStyleStrategy.getStyleStrategy()).sheet(data.getFileName()).doWrite(dataList(data.getDataList(), data.getDataStrMap()));
}catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
//创建对象的导出
public <T> void simpleWrite(@RequestBody SimpleWriteData data, Class<T> clazz, HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
// response.setContentType("application/vnd.ms-excel");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(data.getFileName(), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz).sheet(data.getFileName()).doWrite(data.getDataList());
}
//设置表头
private List<List<String>> head(String[] headMap) {
List<List<String>> list = new ArrayList<List<String>>();
for (String head : headMap) {
List<String> headList = new ArrayList<String>();
headList.add(head);
list.add(headList);
}
return list;
}
//设置导出的数据内容
private List<List<Object>> dataList(List<Map<String, Object>> dataList, String[] dataStrMap) {
List<List<Object>> list = new ArrayList<List<Object>>();
for (Map<String, Object> map : dataList) {
List<Object> data = new ArrayList<Object>();
for (int i = 0; i < dataStrMap.length; i++) {
data.add(map.get(dataStrMap[i]));
}
list.add(data);
}
return list;
}
}
参考说明:
如有疑问,可以参考以下文档:
https://www.yuque.com/easyexcel/doc/easyexcel (EasyExcel 官网)
(Excel文档的字体,背景色,自动列宽的设置参考)