easyExcel的gitHub地址:https://github.com/alibaba/easyexcel
开发环境:springboot
1.导入依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.2-beta5</version> </dependency>
注意:easyexcel需要依赖 poi和poi-ooxml 这里easyexcel的版本是1.1.2,对应poi版本是3.1.7
2.导出带标题的excel
如果导出的只是有一个标题,不需要合并单元格之类的,类型这样的效果
新建一个实体类集成 BaseRowModel 然后使用@ExcelProperty 注解来绑定对象,value位标题中文,index位列的序号从0开始,format可以格式化日期。
/**
* @author: lockie
* @Date: 2019/8/5 17:11
* @Description:
*/
@Data
@ToString
public class OrderExportDTO extends BaseRowModel {
@ExcelProperty(value = "订单流水号", index = 0)
private String orderNo;
@ExcelProperty(value = "订单生成时间", index = 1, format = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
@ExcelProperty(value = "订单状态", index = 2)
private String orderStatusShowStr;
}
controller层新增一个导出接口,根据条件查询出数据,然后调用 EasyExcelUtils.writeExcelOneSheet 方法导出,我这里设置了订单号的列宽所以自己new了一个Sheet对象,如果不需要设置列宽可以调用 EasyExcelUtils.writeExcelOneSheet 方法的时候sheet传null。
/**
* 导出excel
* @param orderInfoEx
* @param response
*/
@GetMapping("/exportChargeExcel")
public void exportPhpExcel(OrderInfoEx orderInfoEx, HttpServletResponse response) {
if (null == orderInfoEx) {
Assert.isTrue(true,"查询参数为空");
}
if(null == orderInfoEx.getDealStartDateStr()){
Assert.isTrue(true,"请选择开始时间");
}
if(null == orderInfoEx.getDealEndDateStr()){
Assert.isTrue(true,"请选择结束时间");
}
HashMap<String, Object> queryMap = new HashMap<>();
queryMap.put("dealStartDateStr", orderInfoEx.getDealStartDateStr());
queryMap.put("dealEndDateStr", orderInfoEx.getDealEndDateStr());
queryMap.put("phoneNumber", orderInfoEx.getPhoneNumber());
queryMap.put("cardNum", orderInfoEx.getCardNum());
queryMap.put("cardType", orderInfoEx.getCardType());
queryMap.put("orgName", orderInfoEx.getOrgName());
queryMap.put("orderStatusShow", orderInfoEx.getOrderStatusShow());
queryMap.put("city", orderInfoEx.getCity());
queryMap.put("csCode", orderInfoEx.getCsCode());
try {
// 设置列宽
Map columnWidth = new HashMap();
columnWidth.put(0, 8000);
String fileName = "正常订单";
if (StringUtils.isNotEmpty(orderInfoEx.getExportType())) {
if ("1".equals(orderInfoEx.getExportType())) {
// 查询正常订单导出数据
List<OrderExportDTO> orderExportDTOList = orderService.getOrderExportByParam(queryMap);
Sheet sheet1 = new Sheet(1, 0, OrderExportDTO.class);
sheet1.setColumnWidthMap(columnWidth);
sheet1.setSheetName(fileName);
// 导出
EasyExcelUtils.writeExcelOneSheet(response, orderExportDTOList, sheet1, fileName);
} else {
fileName = "异常订单";
// 查询异常订单导出数据
List<ExceptionOrderExportDTO> exceptionOrderExportDTOList = orderService.getExceptionOrderExportByParam(queryMap);
Sheet sheet1 = new Sheet(1, 0, ExceptionOrderExportDTO.class);
sheet1.setColumnWidthMap(columnWidth);
sheet1.setSheetName(fileName);
// 导出
EasyExcelUtils.writeExcelOneSheet(response, exceptionOrderExportDTOList, sheet1, fileName);
}
} else {
throw new RuntimeException("导出类型为空");
}
} catch (Exception e) {
logger.error("订单导出异常",e);
throw new RuntimeException(e);
}
}
封装的 EasyExcelUtils 工具类,如果导出单个Sheet的excel则使用 writeExcelOneSheet 方法,如果导出多个sheet则使用 writeExcelMutilSheet 方法
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.util.StringUtils;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* @author: lockie
* @Date: 2019/8/6 10:18
* @Description:
*/
public class EasyExcelUtils {
private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtils.class);
private static Sheet initSheet;
static {
initSheet = new Sheet(1, 0);
initSheet.setSheetName("sheet");
// 设置自适应宽度
initSheet.setAutoWidth(Boolean.TRUE);
}
/**
* 读取少于1000行数据
*
* @param filePath 文件绝对路径
* @return
*/
public static List<Object> readLessThan1000Row(String filePath) {
return readLessThan1000RowBySheet(filePath, null);
}
/**
* 读取少于1000行数据,带样式的
*
* @param filePath 文件绝对路径
* @param sheet
* @return
*/
public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet) {
if (!StringUtils.hasText(filePath)) {
return null;
}
sheet = sheet != null ? sheet : initSheet;
InputStream inputStream = null;
try {
inputStream = new FileInputStream(filePath);
return EasyExcelFactory.read(inputStream, sheet);
} catch (FileNotFoundException e) {
logger.error("找不到文件或者文件路径错误", e);
} finally {
try {
if (inputStream != null) {
inputStream.close();
}
} catch (IOException e) {
logger.error("excel文件读取失败,失败原因:{}", e);
}
}
return null;
}
/**
* 读取大于1000行数据
* @param filePath
* @return
*/
public static List<Object> readMoreThan1000Row(String filePath) {
return readMoreThan1000RowBySheet(filePath, null);
}
/**
* 读取大于1000行数据
* @param filePath
* @param sheet
* @return
*/
public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet) {
if (!StringUtils.hasText(filePath)) {
return null;
}
sheet = sheet != null ? sheet : initSheet;
InputStream inputStream = null;
try {
inputStream = new FileInputStream(filePath);
ExcelListener excelListener = new ExcelListener();
EasyExcelFactory.readBySax(inputStream, sheet, excelListener);
return excelListener.getDatas();
} catch (FileNotFoundException e) {
logger.error("找不到文件或者文件路径错误");
} finally {
try {
if (inputStream != null) {
inputStream.close();
}
} catch (IOException e) {
logger.error("excel文件读取失败,失败原因:{}", e);
}
}
return null;
}
/**
* 导出单个sheet
* @param response
* @param dataList
* @param sheet
* @param fileName
* @throws UnsupportedEncodingException
*/
public static void writeExcelOneSheet(HttpServletResponse response, List<? extends BaseRowModel> dataList, Sheet sheet, String fileName) throws UnsupportedEncodingException {
if (CollectionUtils.isEmpty(dataList)) {
return;
}
// 如果sheet为空,则使用默认的
if (null == sheet) {
sheet = initSheet;
}
try {
String value = "attachment; filename=" + new String(
(fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition", value);
ServletOutputStream out = response.getOutputStream();
ExcelWriter writer = EasyExcelFactory.getWriter(out, ExcelTypeEnum.XLSX, true);
// 设置属性类
sheet.setClazz(dataList.get(0).getClass());
writer.write(dataList, sheet);
writer.finish();
out.flush();
} catch (IOException e) {
logger.error("导出失败,失败原因:{}", e);
}
}
/**
* @Author lockie
* @Description 导出excel 支持一张表导出多个sheet
* @Param OutputStream 输出流
* Map<String, List> sheetName和每个sheet的数据
* ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
* @Date 上午12:16 2019/1/31
*/
public static void writeExcelMutilSheet(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> dataList, String fileName) throws UnsupportedEncodingException {
if (CollectionUtils.isEmpty(dataList)) {
return;
}
try {
String value = "attachment; filename=" + new String(
(fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition", value);
ServletOutputStream out = response.getOutputStream();
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
// 设置多个sheet
setMutilSheet(dataList, writer);
writer.finish();
out.flush();
} catch (IOException e) {
logger.error("导出异常", e);
}
}
/**
* @Author lockie
* @Description //setSheet数据
* @Date 上午12:39 2019/1/31
*/
private static void setMutilSheet(Map<String, List<? extends BaseRowModel>> dataList, ExcelWriter writer) {
int sheetNum = 1;
for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : dataList.entrySet()) {
Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
sheet.setSheetName(stringListEntry.getKey());
writer.write(stringListEntry.getValue(), sheet);
sheetNum++;
}
}
/**
* 导出监听
*/
@Data
public static class ExcelListener extends AnalysisEventListener {
private List<Object> datas = new ArrayList<>();
/**
* 逐行解析
* @param object 当前行的数据
* @param analysisContext
*/
@Override
public void invoke(Object object, AnalysisContext analysisContext) {
// 当前行
// analysisContext.getCurrentRowNum()
if (object != null) {
datas.add(object);
}
}
/**
* 解析完所有数据后会调用该方法
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
/**
* test
*
* @param args
* @param response
* @throws UnsupportedEncodingException
*/
public static void main(String[] args, HttpServletResponse response) throws UnsupportedEncodingException {
// 导出多个sheet
// List<OrderExportDTO> orderExportDTOList = new ArrayList<>();
// Map<String, List<? extends BaseRowModel>> map = new HashMap<>();
// map.put("自营订单", orderExportDTOList);
// map.put("互联互通", orderExportDTOList);
// String fileName = new String(("测试导出2019").getBytes(), "UTF-8");
// writeExcelMutilSheet(response, map, fileName);
// 导出单个sheet
// writeExcelOneSheet(response, orderExportDTOList, null, fileName);
}
}
3.导出动态excel标题
创建标题,也可以使用model创建
public static List<List<String>> createTestListStringHead(){
// 模型上没有注解,表头数据动态传入
List<List<String>> head = new ArrayList<List<String>>();
List<String> headCoulumn1 = new ArrayList<String>();
List<String> headCoulumn2 = new ArrayList<String>();
List<String> headCoulumn3 = new ArrayList<String>();
List<String> headCoulumn4 = new ArrayList<String>();
List<String> headCoulumn5 = new ArrayList<String>();
headCoulumn1.add("第一列");headCoulumn1.add("第一列");headCoulumn1.add("第一列");
headCoulumn2.add("第一列");headCoulumn2.add("第一列");headCoulumn2.add("第一列");
headCoulumn3.add("第二列");headCoulumn3.add("第二列");headCoulumn3.add("第二列");
headCoulumn4.add("第三列");headCoulumn4.add("第三列2");headCoulumn4.add("第三列2");
headCoulumn5.add("第一列");headCoulumn5.add("第3列");headCoulumn5.add("第4列");
head.add(headCoulumn1);
head.add(headCoulumn2);
head.add(headCoulumn3);
head.add(headCoulumn4);
head.add(headCoulumn5);
return head;
}
导出
导出效果
4.自定义表头以及内容样式
public static TableStyle createTableStyle() {
TableStyle tableStyle = new TableStyle();
// 设置表头样式
Font headFont = new Font();
// 字体是否加粗
headFont.setBold(true);
// 字体大小
headFont.setFontHeightInPoints((short)12);
// 字体
headFont.setFontName("楷体");
tableStyle.setTableHeadFont(headFont);
// 背景色
tableStyle.setTableHeadBackGroundColor(IndexedColors.BLUE);
// 设置表格主体样式
Font contentFont = new Font();
contentFont.setBold(true);
contentFont.setFontHeightInPoints((short)12);
contentFont.setFontName("黑体");
tableStyle.setTableContentFont(contentFont);
tableStyle.setTableContentBackGroundColor(IndexedColors.GREEN);
return tableStyle;
}
5.合并单元格
注意下标是从 0 开始的,也就是说合并了第六行到第七行,其中的第一列到第五列,跑下代码,看下效果:
6.自定义处理
对于更复杂的处理,EasyExcel 预留了 WriterHandler
接口来,允许你自定义处理代码:
接口中定义了三个方法:
sheet()
: 在创建每个 sheet 后自定义业务逻辑处理;row()
: 在创建每个 row 后自定义业务逻辑处理;cell()
: 在创建每个 cell 后自定义业务逻辑处理;
我们自定义一个Handler类实现了WriteHandler接口后,编写自定义逻辑处理代码,然后调用 getWriterWithTempAndHandler()
静态方法获取 ExcelWriter
对象时,传入 WriterHandler
的实现类即可。
ExcelWriter writer = EasyExcelFactory.getWriterWithTempAndHandler(null, out, ExcelTypeEnum.XLSX, true, new MyWriterHandler());