首先看下效果:
一、导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.3</version>
</dependency>
二、准备工具类
1.操作的工具类
package com.yuncheng.utils.excel;
import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.yuncheng.utils.excel.handler.AutoWidthHandler;
import com.yuncheng.utils.excel.handler.SelectSheetWriteHandler;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
@Component
@Slf4j
public class DynamicExcelUtils {
public void excelDownloadLink(HttpServletResponse response, String title,List<String> names, List<String> fieldEn ,List list ,Map<Integer,
List<String>> selectMap,String sheetName, String fileName) {
try {
List<List<String>> datas = setData(list,fieldEn);
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
response.setCharacterEncoding("UTF-8");
EasyExcel.write(response.getOutputStream())
.excelType(ExcelTypeEnum.XLSX)
.head(head(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0], title))
.inMemory(true)
.registerWriteHandler(new TitleStyleUtils(names))
.registerWriteHandler(new SelectSheetWriteHandler(selectMap))
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))
.sheet(sheetName)
.doWrite(CollectionUtil.isNotEmpty(datas) ? datas:new ArrayList());
} catch (IOException e) {
e.printStackTrace();
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/json");
try {
response.getWriter().println("打印失败");
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
public void excelExportData(HttpServletResponse response,List<String> names,List<String> fieldEn,List list ,String sheetName, String fileName){
try {
List<List<String>> datas = setData(list,fieldEn);
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
response.setCharacterEncoding("UTF-8");
EasyExcel.write(response.getOutputStream())
.excelType(ExcelTypeEnum.XLSX)
.head(headData(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0]))
.registerWriteHandler(new AutoWidthHandler())
.registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 25))
.sheet(sheetName)
.doWrite(CollectionUtil.isNotEmpty(datas) ? datas:new ArrayList());
} catch (IOException e) {
e.printStackTrace();
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/json");
try {
response.getWriter().println("打印失败");
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
public void excelExportData(HttpServletResponse response, List<String> names, List list, String sheetName, String fileName){
try {
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
response.setCharacterEncoding("UTF-8");
EasyExcel.write(response.getOutputStream())
.excelType(ExcelTypeEnum.XLSX)
.head(headData(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0]))
.registerWriteHandler(new AutoWidthHandler())
.registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 25))
.sheet(sheetName)
.doWrite(CollectionUtil.isNotEmpty(list) ? list:new ArrayList());
} catch (IOException e) {
e.printStackTrace();
response.reset();
response.setCharacterEncoding("utf-8")