EasyExcel 操作 Excel

1.环境准备

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

2.导出

2.1模型映射

import java.util.Date;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

/**
	注解讲解：
	@HeadRowHeight(int)：
	设置 表头 高度（标记在类上）
	@ContentRowHeight(int):
	设置 row 高度，不包含表头（标记在类上）
	@ColumnWidth(int)：
	设置列宽
	@ExcelProperty(value = String[], index = int):
	设置表头信息
	value: 表名称
	index: 列号
	@DateTimeFormat(value)
	@NumberFormat(value)
*/
@Data
public class OrderExcelModel extends BaseRowModel {

	@ExcelProperty(value = {"合并单元格", "订单编号"}, index = 0)
	@ColumnWidth(15)
	private String orderSerialNo;

	@ColumnWidth(15)
	@ExcelProperty(value = {"合并单元格", "订单类型"}, index = 1)
	private String transferType;

	@ColumnWidth(15)
	@ExcelProperty(value = "订单状态", index = 2)
	private String effective;

	@ColumnWidth(15)
	@ExcelProperty(value = "交易状态", index = 3)
	private String payStatus;
	
	@ColumnWidth(15)
	// 保留小数点后三位不足的补零,整数部分每隔四位用 “,” 合隔开
	@NumberFormat("#,####.000")
	@ExcelProperty(value = "金额(元)", index = 4)
	private BigDecimal priceAmount;
	
	@ColumnWidth(20)
	@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
	@ExcelProperty(value = "服务日期", index = 5)
	private Date predictArriveTime;

}

2.2调用方法

try {
	List<OrdersVO> list = ordersService.getOrderList(entity, ipage, queryWrapper).getList();
	if (list.isEmpty()) {
		throw new ResultException("没有订单导出数据！");
	}

	List<OrderExcelModel> data = new ArrayList<>();
	for (int i = 0; i < list.size(); i++) {
		OrderExcelModel item = new OrderExcelModel();
		item.setOrderSerialNo(list.get(i).getOrderSerialNo());
		item.setTransferType(list.get(i).getTransferType());
		item.setEffective(list.get(i).getEffective());
		item.setPayStatus(list.get(i).getPayStatus());
		item.setPriceAmount(list.get(i).getPriceAmount());
		item.setPredictArriveTime(list.get(i).getPredictArriveTime());
		data.add(item);
	}
	EasyExcelUtils.createExcelStreamMutilByEaysExcel(response, data, "订单列表");
} catch (Exception e) {
	e.printStackTrace();
	throw new ResultException("导出订单失败！");
}

2.3导出工具类

import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
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;

	/**
	 * 
	 * @param response
	 * @param data
	 * @param fileName
	 */
	public static <T> void createExcelStreamMutilByEaysExcel(HttpServletResponse response, List<T> data,
			String fileName) {
		try {
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-disposition",
					"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ExcelTypeEnum.XLSX);
			ServletOutputStream out = response.getOutputStream();
			
			// 初始化单元格样式
			HorizontalCellStyleStrategy horizontalCellStyleStrategy = initStyle();
			ExcelWriter excelWriter = EasyExcel.write(out, (Class<? extends BaseRowModel>) data.get(0).getClass())
					.build();
			WriteSheet writeSheet;
			
			int sheetNum = 3;
			for (int i = 0; i < sheetNum; i++) {
				// 每次都要创建writeSheet 这里注意必须指定sheetName,模型映射可以每次都变，我这里用的同一个class
				writeSheet = EasyExcel.writerSheet(i, "sheet" + i)
						.head((Class<? extends BaseRowModel>) data.get(0).getClass())
						.registerWriteHandler(horizontalCellStyleStrategy).build();
				excelWriter.write(data, writeSheet);
			}
			// 关闭流
			excelWriter.finish();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

2.4设置单元格样式

	/**
	 * 设置样式
	 * @return
	 */
	private static HorizontalCellStyleStrategy initStyle() {
		// 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 15);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
//        contentWriteCellStyle.setBorderLeft(BorderStyle.DASHED);
        contentWriteCellStyle.setBorderTop(BorderStyle.DASHED);
//        contentWriteCellStyle.setBorderRight(BorderStyle.DASHED);
//        contentWriteCellStyle.setBorderBottom(BorderStyle.DASHED);

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
		return horizontalCellStyleStrategy;
	}

3.导入

3.1创建监听对象

public class ExcelListener extends AnalysisEventListener {
	/**
	 * 自定义用于暂时存储data。 可以通过实例获取该值
	 */
	private List<Object> datas = new ArrayList<>();

	/**
	 * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
	 */
	@Override
	public void invoke(Object object, AnalysisContext context) {
		// 数据存储到list，供批量处理，或后续自己业务逻辑处理。
		datas.add(object);
		// 根据业务自行 do something
		doSomething();
		/*
		 * 如数据过大，可以进行定量分批处理 if(datas.size()<=100){ datas.add(object); }else {
		 * doSomething(); datas = new ArrayList<Object>(); }
		 */
	}

	/**
	 * 根据业务自行实现该方法
	 */
	private void doSomething() {
	}

	@Override
	public void doAfterAllAnalysed(AnalysisContext context) {
		/*
		 * datas.clear(); 解析结束销毁不用的资源
		 */
	}

	public List<Object> getDatas() {
		return datas;
	}

	public void setDatas(List<Object> datas) {
		this.datas = datas;
	}
}

3.2读取文件

/**
 * 读取某个 sheet 的 Excel
 * 
 * @param excel       文件
 * @param rowModel    实体类映射，继承 BaseRowModel 类
 * @param sheetNo     sheet 的序号 从1开始
 * @param headLineNum 表头行数 从0开始
 * @return Excel 数据 list
 */
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) {
	ExcelListener excelListener = new ExcelListener();
	ExcelReader reader = getReader(excel, excelListener);
	if (reader == null) {
		return null;
	}
	reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
	return excelListener.getDatas();
}
/**
 * 返回 Exce
 * lReader
 * 
 * @param excel         需要解析的 Excel 文件
 * @param excelListener new ExcelListener()
 */
private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) {
	String filename = excel.getOriginalFilename();
	if (filename == null
			|| (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
		throw new PreException("文件格式错误！");
	}

	try {
		InputStream inputStream = new BufferedInputStream(excel.getInputStream());
		return new ExcelReader(inputStream, null, excelListener, false);
	} catch (IOException e) {
		e.printStackTrace();
	}
	return null;
}

3.3调用方法

@ApiOperation(value = "/导入")
@PostMapping("/import")
public Object importExcel(HttpServletRequest request, MultipartFile file) {
	if (file != null) {
		List<Object> excelData = EasyExcelUtils.readExcel(file, new KnowledgeBaseExcel(), 1, 0);
		// 第一行有表头
		KnowledgeBaseExcel excelHead = (KnowledgeBaseExcel) excelData.get(0);
		boolean checkFlag = excelHead.getBusinessName().equals("业务类型") && excelHead.getTagName().equals("标签")
				&& excelHead.getQuestion().equals("问题") && excelHead.getAnswer().equals("回复");
		if (!checkFlag) {
			throw new PreException("导入文件错误！");
		}
		List<Object> list = excelData.subList(1, excelData.size());
		log.info("知识库导入Size:" + list.size());
		if (list != null && list.size() > 0) {
			return list;
		} else {
			throw new PreException("没有导入数据！");
		}
	} else {
		throw new PreException("导入文件为空！");
	}
}