Excel解析easyexcel工具类
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.1</version> </dependency>
1、ExcelModel——Java模型映射
package com.xinyartech.erp.system.model;
import java.io.Serializable;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
/**
*
*
* @author Lynch
*/
@SuppressWarnings("serial")
@Data
public class ExcelModel extends BaseRowModel implements Serializable{
@ExcelProperty(value = "姓名" ,index = 0)
private String name;
@ExcelProperty(value = "年龄",index = 1)
private String age;
@ExcelProperty(value = "邮箱",index = 2)
private String email;
@ExcelProperty(value = "地址",index = 3)
private String address;
@ExcelProperty(value = "性别",index = 4)
private String sax;
@ExcelProperty(value = "高度",index = 5)
private String heigh;
@ExcelProperty(value = "备注",index = 6)
private String last;
}
2、EasyExcelUtil——easyexcel工具类 -- Excel解析
package com.xinyartech.erp.core.util;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.excel.ExcelReader;
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.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
/**
* easyexcel工具类 -- Excel解析
*
* @author Lynch
*/
public class EasyExcelUtil {
/**
* StringList 解析监听器
*/
private static class StringExcelListener extends AnalysisEventListener {
/**
* 自定义用于暂时存储data
* 可以通过实例获取该值
*/
private List<List<String>> datas = new ArrayList<>();
/**
* 每解析一行都会回调invoke()方法
*
* @param object
* @param context
*/
@Override
public void invoke(Object object, AnalysisContext context) {
List<String> stringList= (List<String>) object;
//数据存储到list，供批量处理，或后续自己业务逻辑处理。
datas.add(stringList);
//根据自己业务做处理
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//解析结束销毁不用的资源
//注意不要调用datas.clear(),否则getDatas为null
}
public List<List<String>> getDatas() {
return datas;
}
public void setDatas(List<List<String>> datas) {
this.datas = datas;
}
}
/**
* 使用 StringList 来读取Excel
* @param inputStream Excel的输入流
* @param excelTypeEnum Excel的格式(XLS或XLSX)
* @return 返回 StringList 的列表
*/
public static List<List<String>> readExcelWithStringList(InputStream inputStream,ExcelTypeEnum excelTypeEnum) {
StringExcelListener listener = new StringExcelListener();
ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
excelReader.read();
return listener.getDatas();
}
/**
* 使用 StringList 来写入Excel
* @param outputStream Excel的输出流
* @param data 要写入的以StringList为单位的数据
* @param table 配置Excel的表的属性
* @param excelTypeEnum Excel的格式(XLS或XLSX)
*/
public static void writeExcelWithStringList(OutputStream outputStream, List<List<String>> data, Table table,ExcelTypeEnum excelTypeEnum) {
//这里指定不需要表头，因为String通常表头已被包含在data里
ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,false);
//写第一个sheet, sheet1 数据全是List<String> 无模型映射关系,无表头
Sheet sheet1 = new Sheet(0, 0);
writer.write0(data, sheet1,table);
writer.finish();
}
/**
* 模型解析监听器 -- 每解析一行会回调invoke()方法，整个excel解析结束会执行doAfterAllAnalysed()方法
*/
private static class ModelExcelListener<E> extends AnalysisEventListener<E> {
private List<E> dataList = new ArrayList<E>();
@Override
public void invoke(E object, AnalysisContext context) {
dataList.add(object);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
public List<E> getDataList() {
return dataList;
}
@SuppressWarnings("unused")
public void setDataList(List<E> dataList) {
this.dataList = dataList;
}
}
/**
* 使用 模型 来读取Excel
*
* @param inputStream Excel的输入流
* @param clazz 模型的类
* @param excelTypeEnum Excel的格式(XLS或XLSX)
*
* @return 返回 模型 的列表
*/
public static <E> List<E> readExcelWithModel(InputStream inputStream, Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) {
// 解析每行结果在listener中处理
ModelExcelListener<E> listener = new ModelExcelListener<E>();
ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
//默认只有一列表头
excelReader.read(new Sheet(1, 1, clazz));
return listener.getDataList();
}
/**
* 使用 模型 来写入Excel
*
* @param outputStream Excel的输出流
* @param data 要写入的以 模型 为单位的数据
* @param table 配置Excel的表的属性
* @param clazz 模型的类
* @param excelTypeEnum Excel的格式(XLS或XLSX)
*/
public static void writeExcelWithModel(OutputStream outputStream, List<? extends BaseRowModel> data,
Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) {
//这里指定需要表头，因为model通常包含表头信息
ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);
//写第一个sheet, sheet1 数据全是List<String> 无模型映射关系
Sheet sheet1 = new Sheet(1, 0, clazz);
writer.write(data, sheet1);
writer.finish();
}
}
3、EasyExcelTest测试类
package com.xinyartech.erp.system.service;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.xinyartech.erp.core.util.EasyExcelUtil;
import com.xinyartech.erp.system.model.ExcelModel;
/**
* 单元测试
*
* @author Lynch
*/
public class EasyExcelTest {
public static void main(String[] args) throws Exception {
writeExcel();
readExcel();
}
/**
* 写入Excel
*
* @throws FileNotFoundException
* @author Lynch
*/
private static void writeExcel() throws FileNotFoundException {
List<ExcelModel> excelModelList = new ArrayList<>();
for (int i = 0; i < 1000000; i++) {
ExcelModel excelModel = new ExcelModel();
excelModel.setAddress("address" + i);
excelModel.setAge(i + "");
excelModel.setEmail("email" + i);
excelModel.setHeigh("heigh" + i);
excelModel.setLast("last" + i);
excelModel.setName("name" + i);
excelModel.setSax("sax" + i);
excelModelList.add(excelModel);
}
long beginTime = System.currentTimeMillis();
OutputStream out = new FileOutputStream("D:/aaa.xlsx");
EasyExcelUtil.writeExcelWithModel(out, excelModelList, ExcelModel.class, ExcelTypeEnum.XLSX);
long endTime = System.currentTimeMillis();
System.out.println(String.format("总共耗时 %s 毫秒", (endTime - beginTime)));
excelModelList = null;
}
/**
* 读取Excel
*
* @throws FileNotFoundException
* @author Lynch
*/
private static void readExcel() throws FileNotFoundException {
try {
InputStream inputStream=new FileInputStream("D:/aaa.xlsx");
//读入文件，每一行对应一个 Model，获取 Model 列表
List<ExcelModel> objectList = EasyExcelUtil.readExcelWithModel(inputStream, ExcelModel.class, ExcelTypeEnum.XLSX);
for(ExcelModel excelModel: objectList) {
System.out.println(excelModel);
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
4、springMVC+easypoi做excel的导入导出
package com.xinyartech.erp.system.web; import java.io.IOException; import java.util.ArrayList; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import com.alibaba.excel.support.ExcelTypeEnum; import com.xinyartech.erp.core.base.BaseUserController; import com.xinyartech.erp.core.base.ResponseWrapper; import com.xinyartech.erp.core.util.EasyExcelUtil; import com.xinyartech.erp.system.model.ExcelModel; import com.xinyartech.erp.system.model.SysUser; import com.xinyartech.erp.system.service.UserService; @Controller @RequestMapping("/api/hello") public class HelloWorldController extends BaseUserController<UserService, SysUser> { private static final Logger log = LogManager.getLogger(HelloWorldController.class); @Autowired protected HttpServletResponse response; /** * 导入Excel * * @param file * @author Lynch */ @RequestMapping(value = "/importExcel", method = RequestMethod.POST) @ResponseBody public ResponseWrapper<?> importExcel(@RequestParam(value = "uploadFile", required = false) MultipartFile file) { try { List<ExcelModel> excelModelList = EasyExcelUtil.readExcelWithModel(file.getInputStream(), ExcelModel.class, ExcelTypeEnum.XLSX); return ResponseWrapper.ok(excelModelList); } catch (IOException e) { log.error("Excel导入失败", e); } return ResponseWrapper.ok(); } /** * 导出Excel * * @param response * @author Lynch */ @RequestMapping(value="/writeExcel")
@ResponseBody public ResponseWrapper<String>writeExcel(HttpServletResponse response){ //初始化模拟数据 List<ExcelModel> excelModelList = new ArrayList<>(); for (int i = 0; i < 700; i++) { ExcelModel excelModel = new ExcelModel(); excelModel.setAddress("address" + i); excelModel.setAge(i + ""); excelModel.setEmail("email" + i); excelModel.setHeigh("heigh" + i); excelModel.setLast("last" + i); excelModel.setName("name" + i); excelModel.setSax("sax" + i); excelModelList.add(excelModel); } return writeExcel("abc", excelModelList); } /** * 导出Excel工具类 * * @param fileName 导出文件名，不填默认"abbot.xlsx" * @param data 导出数据 * @author Lynch */ protected ResponseWrapper<String> writeExcel(String fileName, List<? extends BaseRowModel> data) { try { if(CollectionUtils.isEmpty(data)) { log.warn("Excel导出数据为空"); return ResponseWrapper.ok(SystemEnum.ERROR_COLLECTION_EMPTY, "Excel导出数据为空"); } if(StringUtils.isEmpty(fileName)) { fileName = "abbot.xlsx"; } String filename = new String(fileName.getBytes("UTF-8"), "ISO-8859-1"); response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE); response.setContentType(MediaType.APPLICATION_JSON_UTF8_VALUE); response.setHeader("Content-disposition", String.format("attachment; filename=%s.xlsx", filename)); // 将文件输出 EasyExcelUtil.writeExcelWithModel(response.getOutputStream(), data, data.get(0).getClass(), ExcelTypeEnum.XLSX); data = null; } catch (Exception e) { log.error("Excel导出失败", e); return ResponseWrapper.error("Excel导出失败"); } return ResponseWrapper.ok(); } }
