目录
- 0 EasyExcel简介
- 1 Excel导出工具类
- 设置自定义表头样式
- 设置自适应列宽
- 添加合计行
- 2 调用导出工具类导出Excel表
- 3 测试结果
0 EasyExcel简介
在数据处理和报表生成的过程中，Excel是一个非常常用的工具。特别是在Java开发中，EasyExcel库因其简单高效而备受欢迎。
EasyExcel是阿里巴巴开源的一个Excel处理库，特别适用于大数据量的Excel文件的读写。它的优点在于使用简单、性能高效，特别适合在Java项目中处理Excel文件。
1 Excel导出工具类
Excel导出工具类ExcelUtil类代码如下：
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
@Data
public class ExcelUtil{
public static void downloadExcelWithHead(HttpServletResponse response, List<?> list, String sheetName, List<List<String>> rowHead) throws IOException {
if (list.isEmpty()) {
throw new IllegalArgumentException("列表不能为空！");
}
Class<?> clazz = list.get(0).getClass();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).build();
// 创建 ExcelWriterBuilder
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), clazz)
.autoCloseStream(true)
.needHead(true);
excelWriterBuilder.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
excelWriterBuilder.useDefaultStyle(false);
// 创建 WriteSheet 并设置动态标题行
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
.head(rowHead) // 设置标题行
.registerWriteHandler(setCellStyle()) // 设置表头样式
.registerWriteHandler(new CustomCellWriteUtil()) // 设置列宽自适应
.build();
// 写入数据
excelWriter.write(list, writeSheet);
excelWriter.finish();
}
}
设置自定义表头样式
其中，downloadExcelWithHead()中调用setCellStyle()方法用于设置表头样式。setCellStyle()方法的代码如下：
public static HorizontalCellStyleStrategy setCellStyle() {
// 表头策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为白色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//边框
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
//自动换行
headWriteCellStyle.setWrapped(true);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setBold(true);
headWriteFont.setFontName("宋体");
headWriteFont.setFontHeightInPoints((short)12);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//边框
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
//自动换行
contentWriteCellStyle.setWrapped(true);
//文字
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short)12);
contentWriteFont.setFontName("宋体");
contentWriteCellStyle.setWriteFont(contentWriteFont);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
设置自适应列宽
自适应列宽通过CustomCellWriteUtil类设置，CustomCellWriteUtil类的代码如下：
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* excel自适应列宽
*/
public class CustomCellWriteUtil extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
public CustomCellWriteUtil() {
}
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = (Map) CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap(16);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), 7250);
}
}
}
}
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = (CellData) cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
添加合计行
合计行用于展示数据的总和或其他统计信息。在Controller中调用downloadExcelWithHead()方法导出Excel时，添加合计行和自定义表头形式。
2 调用导出工具类导出Excel表
此处以导出产品明细表为例，新建产品表如下：
entity、mapper、service层省略，在Controller层中添加一个导出产品明细表接口，代码如下：
import com.z.entity.Product;
import com.z.service.ProductService;
import com.z.util.ConvertUtil;
import com.z.util.ExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@RestController
@RequestMapping("/product")
public class ProductController {
@Autowired
private ProductService productService;
@PostMapping("/export")
public void export(HttpServletResponse response) throws IOException {
List<Product> dataList = productService.list();
if(null == dataList || dataList.isEmpty()){
throw new RuntimeException("数据为空，导出失败！");
}
// 计算合计
double totalProductCost = dataList.stream()
.mapToDouble(data -> data.getProductCost() != null ? data.getProductCost() : 0.0)
.sum();
int totalProductNum = dataList.stream()
.mapToInt(data -> data.getProductNum() != null ? data.getProductNum() : 0)
.sum();
// 创建合计行
Product totalRow = new Product();
totalRow.setId("合计");
totalRow.setProductCost(totalProductCost);
totalRow.setProductNum(totalProductNum);
// 添加合计行到列表
dataList.add(totalRow);
// 创建自定义表头
List<List<String>> rowHead = createHead();
// 导出 Excel(带标题列，带合计列)
ExcelUtil.downloadExcelWithHead(response, dataList, "产品明细表", rowHead);
}
/**
* 设置Excel头
* @return
*/
private List<List<String>> createHead() {
List<List<String>> list = new ArrayList<>();
// 第一列表头
List<String> headRow1 = new ArrayList<>();
headRow1.add("产品明细表报表");
headRow1.add("test");
headRow1.add("产品序号");
list.add(headRow1);
// 第二列表头
List<String> headRow2 = new ArrayList<>();
headRow2.add("产品明细表报表");
headRow2.add("备注："+"此处可调用方法取值");
headRow2.add("产品编号");
list.add(headRow2);
// 第三列表头
List<String> headRow3 = new ArrayList<>();
headRow3.add("产品明细表报表");
headRow3.add("备注："+"此处可调用方法取值");
headRow3.add("产品名称");
list.add(headRow3);
// 第四列表头
List<String> headRow4 = new ArrayList<>();
headRow4.add("产品明细表报表");
headRow4.add("无");
headRow4.add("产品进价");
list.add(headRow4);
// 第五列表头
List<String> headRow5 = new ArrayList<>();
headRow5.add("产品明细表报表");
headRow5.add("测试");
headRow5.add("产品库存");
list.add(headRow5);
return list;
}
其中，在使用createHead()方法设置Excel头时，可以调用service层中的方法，自定义显示特定值。
3 测试结果
最终使用postman调用该导出接口，导出的表格结果如下：
