主要实现通过模板,导出对应数据,生成excel。
1.版本:
Maven: com.alibaba:easyexcel:3.0.5
2.代码实现
ExportExcelBase:是定义一些非列表的字段,如下:导出时间等
工具类:
;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
/**
* @author fei.chen
* @description: 导出excel工具类
* @date 2022/12/20下午 5:57
*/
public class EasyExcelUtil {
/**
* @param resourceAsStream 模板流
* @param fileName 文件名
* @param data 导出数据
* @param exportExcelBase 基础对象
* @param response
*/
public static void excel(InputStream resourceAsStream, String fileName, List data,
ExportExcelBase exportExcelBase, HttpServletResponse response) {
try {
ServletOutputStream out = response.getOutputStream();
//设置文件类型
response.setContentType("application/vnd.ms-excel");
//设置编码格式
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
//创建excel
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(resourceAsStream).build();
// 创建sheet
WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(data, fillConfig, writeSheet);
excelWriter.fill(exportExcelBase, writeSheet);
//填充完成
excelWriter.finish();
out.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
调用:
respList 是你要导出的数据字段 集合。
InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("excel/信息.xlsx");
ExportExcelBase exportExcelBase = ExportExcelBase.builder().
exportUserName("小飞").
exportUserCompany("公司部1").
exportDate(DateUtils.formatDate(new Date(), "yyyy-MM-dd HH:mm:ss")).build();
EasyExcelUtil.excel(resourceAsStream,"信息",respList,exportExcelBase,response);
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class AreasAccessInfoExportResp {
/**
* 序号
*/
private Integer orderNum;
/**
* 名称
*/
private String name;
}
public class ExportExcelBase {
/**
* 导出人
*/
private String exportUserName;
/**
* 导出单位
*/
private String exportUserCompany;
/**
* 导出时间
*/
private String exportDate;
}
ok完事。