easyexcel根据模板下载文件

为什么要使用EasyExcel

easyExcel是阿里巴巴下在POI的基础上二次开发的开源api，以使用简单、节省内存著称。
POI由于在操作excel时是先将所有数据都读入内存后，再写入文件，比较消耗内存，特别是大数据量时，容易出现OOM
EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析
而使用EasyExcel进行导出Excel时候，可以根据模板进行生成Excel，具体操作如下
maven依赖：

com.alibabaeasyexcel2.2.4

注意easyexcel2.2.4版本的依赖包含poi的3.1.7版本的依赖

最终下载的Excel文件效果：

1.根据要生成的文件格式创建模板文件，放到resource目录下

2.创建对应数据实体类，注意字段排序要和标题头排列顺序一致

package cn.test.user.excel;import com.alibaba.excel.annotation.format.DateTimeFormat;import com.alibaba.excel.annotation.format.NumberFormat;import com.alibaba.excel.annotation.write.style.ContentFontStyle;import com.alibaba.excel.annotation.write.style.ContentRowHeight;import com.alibaba.excel.annotation.write.style.ContentStyle;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.VerticalAlignment;import java.math.BigDecimal;import java.util.Date;/** * @Author mark * @CreateTime: 2023-12-1214:59 */@Data@NoArgsConstructor@AllArgsConstructor@ContentRowHeight(value = (short)13.5)@ContentFontStyle(fontName = "宋体",fontHeightInPoints = 11)public class TransactionDto {/** * 付款单号 */@ContentStyle(horizontalAlignment = HorizontalAlignment.LEFT,verticalAlignment = VerticalAlignment.CENTER,wrapped = true)private String paymentId;/** * 交易状态 */@ContentStyle(horizontalAlignment = HorizontalAlignment.LEFT,verticalAlignment = VerticalAlignment.CENTER,wrapped = true)@ContentFontStyle(color = 10,fontHeightInPoints = 11)//红色private String tranState;/** * 交易金额 */@ContentStyle(horizontalAlignment = HorizontalAlignment.RIGHT,verticalAlignment = VerticalAlignment.CENTER,wrapped = true)@NumberFormat("###,###,##0.00")private BigDecimal transAmt;/** * 交易时间 */@ContentStyle(horizontalAlignment = HorizontalAlignment.LEFT,verticalAlignment = VerticalAlignment.CENTER,wrapped = true)@DateTimeFormat("yyyy-MM-dd HH:mm:ss")private Date transDate;}

3.编写下载excel代码

package cn.test.user.excel;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.support.ExcelTypeEnum;import com.sun.deploy.net.URLEncoder;import javax.servlet.http.HttpServletResponse;import java.io.InputStream;import java.math.BigDecimal;import java.util.ArrayList;import java.util.Date;/** * @Author mark * @CreateTime: 2023-12-1215:06 */public class DownloadExcel {public static void main(String[] args) {}public static void downExcel(HttpServletResponse response){//构造数据ArrayList<TransactionDto> list = new ArrayList<>();list.add(new TransactionDto("FX202311051000001", "交易成功", new BigDecimal(12202.15), new Date()));list.add(new TransactionDto("FX202311051000002", "交易失败", new BigDecimal(156.15), new Date()));list.add(new TransactionDto("FX202311051000003", "处理中", new BigDecimal(212202.15), new Date()));list.add(new TransactionDto("FX202311051000004", "交易成功", new BigDecimal(1889.15), new Date()));list.add(new TransactionDto("FX202311051000005", "交易成功", new BigDecimal(65.15), new Date()));//获取模板文件InputStream inputStream = null;try {inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("template/交易信息下载模板.xlsx");} catch (Exception e) {//读取模板文件失败}//根据模板生成excel文件try {response.setHeader("Connection","keep-alive");response.addHeader("Content-Type","application/octet-stream;charset=utf-8");response.addHeader("Content-Disposition","attachment:filename="+ URLEncoder.encode("交易信息下载.xlsx","utf-8"));EasyExcel.write(response.getOutputStream(), TransactionDto.class).autoCloseStream(true).withTemplate(inputStream).excelType(ExcelTypeEnum.XLSX)//excel格式.needHead(false).sheet("交易信息").doWrite(list);} catch (Exception e) {//出错}}}