EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。它能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。官网地址
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
Excel工具类
设置表格样式
/**
* 设置表格样式（头是头的样式、内容是内容的样式）
* @author cheng-qiang
* @param fillForegroundColorHead 头部背景色 IndexedColors.PINK.getIndex()
* @param fontHeightInPointsHead 头部字体大小
* @param fillForegroundColorContent 内容背景色 IndexedColors.LEMON_CHIFFON.getIndex()
* @param fontHeightInPointsContent 内容字体大小
* @param borderStyle 边框样式 BorderStyle.DASHED
* @return com.alibaba.excel.write.style.HorizontalCellStyleStrategy
*/
public static HorizontalCellStyleStrategy createTableStyle(Short fillForegroundColorHead,
int fontHeightInPointsHead,
Short fillForegroundColorContent,
int fontHeightInPointsContent,
BorderStyle borderStyle) {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(fillForegroundColorHead);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)fontHeightInPointsHead);
headWriteCellStyle.setWriteFont(headWriteFont);
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setFillForegroundColor(fillForegroundColorContent);
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short)fontHeightInPointsContent);
contentWriteCellStyle.setWriteFont(contentWriteFont);
contentWriteCellStyle.setBorderBottom(borderStyle);
contentWriteCellStyle.setBorderLeft(borderStyle);
contentWriteCellStyle.setBorderRight(borderStyle);
contentWriteCellStyle.setBorderTop(borderStyle);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
导出单个sheet表格
/**
* 导出单个sheet表格
* @author cheng-qiang
* @param response HttpServletResponse
* @param list 数据列表
* @param fileName 文件名称
* @param sheetName sheet名称
* @param clazz 数据类型
* @param fillForegroundColorHead 头部背景色 IndexedColors.PINK.getIndex()
* @param fontHeightInPointsHead 头部字体大小
* @param fillForegroundColorContent 内容背景色 IndexedColors.LEMON_CHIFFON.getIndex()
* @param fontHeightInPointsContent 内容字体大小
* @param borderStyle 边框样式 BorderStyle.DASHED
*/
public static void writeSingleExcel(HttpServletResponse response,
List<?> list,
String fileName,
String sheetName,
Class<?> clazz,
Short fillForegroundColorHead,
int fontHeightInPointsHead,
Short fillForegroundColorContent,
int fontHeightInPointsContent,
BorderStyle borderStyle) {
try {
response.setCharacterEncoding("utf8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
EasyExcel.write(response.getOutputStream(), clazz)
.sheet(sheetName).registerWriteHandler(createTableStyle(
fillForegroundColorHead,
fontHeightInPointsHead,
fillForegroundColorContent,
fontHeightInPointsContent,
borderStyle
))
.doWrite(list);
}catch (Exception ignored){
}
}
导出多个sheet表格
/**
* 导出多个sheet表格
* @author cheng-qiang
* @param response HttpServletResponse
* @param listMap key 是 sheet名称,value 是 数据列表
* @param fileName 文件名称
* @param classMap key是listMap中的key,value是对应的类型
* @param fillForegroundColorHead 头部背景色 IndexedColors.PINK.getIndex()
* @param fontHeightInPointsHead 头部字体大小
* @param fillForegroundColorContent 内容背景色 IndexedColors.LEMON_CHIFFON.getIndex()
* @param fontHeightInPointsContent 内容字体大小
* @param borderStyle 边框样式 BorderStyle.DASHED
*/
public static void writeMultiExcel(HttpServletResponse response,
Map<String,List<?>> listMap,
String fileName,
Map<String,Class<?>> classMap,
Short fillForegroundColorHead,
int fontHeightInPointsHead,
Short fillForegroundColorContent,
int fontHeightInPointsContent,
BorderStyle borderStyle){
try {
response.setCharacterEncoding("utf8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
ExcelWriter writeMultiExcel = EasyExcel.write(response.getOutputStream()).build();
Set<String> keySet = listMap.keySet();
int count = 0;
for (String key : keySet) {
List<?> list = listMap.get(key);
WriteSheet writeSheet = EasyExcel.writerSheet(count, key).head(classMap.get(key)).registerWriteHandler(createTableStyle(
fillForegroundColorHead,
fontHeightInPointsHead,
fillForegroundColorContent,
fontHeightInPointsContent,
borderStyle
)).build();
writeMultiExcel.write(list,writeSheet);
count++;
}
writeMultiExcel.finish();
}catch (Exception ignored){
}
}
导出Excel文件
/**
* 导出Excel文件
* @author cheng-qiang
* @param headColumnMap 有序列头部
* @param dataList 数据体
* @param response HttpServletResponse
* @param fileName 文件名称
*/
public static void exportExcelFile(LinkedHashMap<String, String> headColumnMap, List<Map<String, Object>> dataList, HttpServletResponse response,String fileName){
try {
List<List<String>> excelHead = new ArrayList<>();
if(MapUtils.isNotEmpty(headColumnMap)){
headColumnMap.forEach((key, value) -> excelHead.add(Lists.newArrayList(Arrays.stream(value.split(",")).iterator())));
}
List<List<Object>> excelRows = new ArrayList<>();
if(MapUtils.isNotEmpty(headColumnMap) && CollectionUtils.isNotEmpty(dataList)){
for (Map<String, Object> dataMap : dataList) {
List<Object> rows = new ArrayList<>();
headColumnMap.forEach((key, value) -> {
if (dataMap.containsKey(key)) {
Object data = dataMap.get(key);
rows.add(data);
}
});
excelRows.add(rows);
}
}
createExcelFile(excelHead, excelRows,response,fileName);
}catch (Exception ignored){
}
}
private static void createExcelFile(List<List<String>> excelHead, List<List<Object>> excelRows, HttpServletResponse response,String fileName){
try {
if(CollectionUtils.isNotEmpty(excelHead)){
response.setCharacterEncoding("utf8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
EasyExcel.write(response.getOutputStream()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.head(excelHead)
.sheet(DEFAULT_SHEET_NAME)
.doWrite(excelRows);
}
} catch (Exception ignored) {
}
}
读取Excel数据--监听器
public static ArrayList<AnalysisEventCustomListener> readEasyExcel(MultipartFile multipartFile){
try {
InputStream inputStream = multipartFile.getInputStream();
ExcelReader excelReader = EasyExcel.read(inputStream).build();
List<ReadSheet> sheetList = excelReader.excelExecutor().sheetList();
ArrayList<AnalysisEventCustomListener> listenerList = new ArrayList<>();
for (int i = 0; i < sheetList.size(); i++) {
AnalysisEventCustomListener customListener = new AnalysisEventCustomListener();
ReadSheet readSheet = EasyExcel.readSheet(i).registerReadListener(customListener).build();
excelReader.read(readSheet);
listenerList.add(customListener);
}
excelReader.finish();
return listenerList;
}catch (Exception ignored){
}
return new ArrayList<>();
}
AnalysisEventCustomListener 监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
@EqualsAndHashCode(callSuper = true)
@Data
public class AnalysisEventCustomListener extends AnalysisEventListener<Map<Integer, String>> {
private List<List<String>> dataList;
public AnalysisEventCustomListener(){
this.dataList = new ArrayList<>();
}
@Override
public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
LinkedList<String> linkedList = new LinkedList<>();
integerStringMap.forEach((k,v) -> linkedList.add(v));
this.dataList.add(linkedList);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
读取Excel数据--数据映射
public static List<?> readExcelModel(MultipartFile multipartFile,Class<?> clazz){
try {
return EasyExcel.read(multipartFile.getInputStream()).head(clazz).sheet().doReadSync();
}catch (Exception ignored){
}
return new ArrayList<>();
}
