阿里Excel导出,支持通过实体类导出,自定义表头动态导出,导出简单容易上手
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
/**
* @Author: LiSaiHang
* @Date: 2022/6/29 4:24 下午
*/
@Slf4j
public class ExcelUtils {
/**
* excel 根据自定义表头 导出
* @param response
* @param list
* @param sheetName
* @param headList 自定义表头
*/
public static void export(HttpServletResponse response, List<?> list, String sheetName, List<List<String>> headList) {
String fileName = System.currentTimeMillis() + ".xlsx";
response.setHeader("content-Type","application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(response.getOutputStream()).build();
final WriteSheet sheet0 = EasyExcel.writerSheet(0, sheetName).head(headList).build();
excelWriter.write(list, sheet0);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (excelWriter != null) {
log.info("执行完毕");
excelWriter.finish();
}
}
}
/**
* 根据实体类 导出excel
* @param response
* @param list
* @param sheetName
* @param clazz
*/
public static void export(HttpServletResponse response, List<?> list, String sheetName, Class clazz) {
String fileName = System.currentTimeMillis() + ".xlsx";
response.setHeader("content-Type","application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(response.getOutputStream()).build();
final WriteSheet sheet0 = EasyExcel.writerSheet(0, sheetName).head(clazz).build();
excelWriter.write(list, sheet0);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (excelWriter != null) {
log.info("执行完毕");
excelWriter.finish();
}
}
}
}
1. 自定义表头导出
params: "name,phone,age,icCard"
@GetMapping("/export")
@ApiOperation(value = "Excel模板导出", notes = "")
public void exportExcel(@RequestParam String params, HttpServletResponse response) {
// 写法1
log.info("根据参数{}导出", params);
final List<List<String>> headList = new ArrayList<>();
final List<String> paramList = Arrays.asList(params.split(","));
for (String param : paramList) {
final List<String> tempList = new ArrayList<>();
tempList.add(param);
headList.add(tempList);
}
ExcelUtils.export(response, new ArrayList(), "模板", headList);
}
2. 实体类导出
@GetMapping("/records/export")
@ApiOperation(value = "发送记录导出", notes = "")
public void recordsExportExcel(QueryRecordsVo param, HttpServletResponse response) {
// 根据业务查询,返回list
final List<Entity> list = result.getList();
if (CollectionUtil.isEmpty(list)) {
throw new ApiException("查询数据失败!");
}
List<SmsRecordsExcelVo> tempList = list.stream().map(item -> {
return BeanUtil.copyProperties(item, Entity.class);
}).collect(Collectors.toList());
ExcelUtils.export(response, tempList, "模板", Entity.class);
}