easyExcel实现读写excel
1：前言
最重要的东西：easyExcel官方文档
https://www.yuque.com/easyexcel/doc/easyexcel
关于读取excel，之前写过读取excel的文章，这里再优化下读取方法。
主要是在easyExcel提供的方法上包了一层。简单的读取不用去写监听。
pom依赖
<!-- 读写excel 之前使用过1.1.2 beat1会产生精度问题，建议使用稳定版本的 easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
2：读excel
包装easyExcel的api，减少监听类
ps：文章最后会把整个工具类贴出来。这里只用到了这两个进行demo.
//这里对easyExcel提供的api做了封装 提供两个方法。效率都是一样的。
//参数：inputStream-文件流,clazz-excel内sheet对应的对象,sheetNo-sheet下标（从0开始），headRowNum-sheet有效数据行数（从0开始）
public static List<T> syncReadModel(InputStream inputStream, Class clazz, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
//参数：file-文件,clazz-excel内sheet对应的对象,sheetNo-sheet下标（从0开始），headRowNum-sheet有效数据行数（从0开始）
public static List<T> syncReadModel(File file, Class clazz, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(file).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
读取excel的代码
//入参MultipartFile ，使用文件流进行解析
@Override
public BaseResponse analysisFirstSheet(MultipartFile file) {
try {
List<T> ts = EasyExcelUtil.syncReadModel(file.getInputStream(), CustEnterpriseExcel.class, 0, 4);
List<CustEnterpriseExcel> list = JSONObject.parseArray(JSONObject.toJSONString(ts), CustEnterpriseExcel.class);
for (CustEnterpriseExcel custEnterpriseExcel : list) {
log.info("处理数据后，每条数据有返回结果，那么不能使用.stream()");
log.info(custEnterpriseExcel.toString());
}
}catch (IOException e){
log.error("读取excel异常"+e);
return new BaseResponse(Msg.ERROR,"读取excel失败。");
}
return new BaseResponse(Msg.SUCCESS);
}
//入参File，使用文件进行解析
@Override
public BaseResponse analysisFirstSheet(File file) {
try {
List<T> ts = EasyExcelUtil.syncReadModel(file, CustEnterpriseExcel.class, 0, 4);
List<CustEnterpriseExcel> list = JSONObject.parseArray(JSONObject.toJSONString(ts), CustEnterpriseExcel.class);
for (CustEnterpriseExcel custEnterpriseExcel : list) {
log.info("处理数据后，每条数据有返回结果，那么不能使用.stream()");
log.info(custEnterpriseExcel.toString());
}
}catch (Exception e){
log.error("读取excel异常"+e);
return new BaseResponse(Msg.ERROR,"读取excel失败。");
}
return new BaseResponse(Msg.SUCCESS);
}
3：写excel
最简单的生成excel
/**
* @Describe 生成一个excel 文件，只有一个sheet sheet名字为:测试模板
**/
@Override
public BaseResponse writerCustExcel(List<CustEnterpriseExcel> list) {
log.info("开始写入excel");
String fileName = "E:/1.IDEA/Workspace/helloFile/writerExcel_test_2.xls";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, CustEnterpriseExcel.class).sheet("测试模板").doWrite(list);
return new BaseResponse(Msg.SUCCESS);
}
/**
* @Describe 生成一个excel文件，复制template
* 如果 公司信息1111不存在，则在最后新增一个sheet
* 如果 公司信息1111存在，则在存在的sheet后。空白行开始新增 *excel头和数据*
**/
public BaseResponse writerCustExcel2(List<CustEnterpriseExcel> list) {
log.info("开始写入excel");
String templateFileName = "E:/1.IDEA/Workspace/helloFile/writerExcel.xls";
String fileName = "E:/1.IDEA/Workspace/helloFile/writerExcel_test_1.xls";
// // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, CustEnterpriseExcel.class).withTemplate(templateFileName).sheet("公司信息1111").doWrite(list);
return new BaseResponse(Msg.SUCCESS);
}
看下效果：
看下writerCustExcel（）的效果，一般来说这个效果是能用的，哈哈哈
A列为空，是因为在excel对应的对象，index为0是没有对应属性的。
@Data
//行高
@ContentRowHeight(10)
//头高
@HeadRowHeight(20)
//默认列宽
@ColumnWidth(25)
public class CustEnterpriseExcel {
// 主键id
@ExcelIgnore
private Long id;
//列宽
@ColumnWidth(40)
@ExcelProperty(value = "企业名称", index = 1)
private String name;
@ColumnWidth(20)
@ExcelProperty(value = "信用代码", index = 2)
private String companyCode;
@ColumnWidth(40)
@ExcelProperty(value = "上级公司", index = 3)
private String companyPName;
@ColumnWidth(20)
@ExcelProperty(value = "上级公司信用代码", index = 4)
private String companyPCode;
@ColumnWidth(20)
@ExcelProperty(value = "企业联系人", index = 5)
private String contactName;
@ColumnWidth(20)
@ExcelProperty(value = "联系人电话", index = 6)
private String phone;
@ColumnWidth(40)
@ExcelProperty(value = "地址", index = 7)
private String address;
@ColumnWidth(20)
@ExcelProperty(value = "企业LOGO", index = 8)
private String logo;
@ColumnWidth(10)
@ExcelProperty(value = "省份", index = 9)
private String province;
@ColumnWidth(10)
@ExcelProperty(value = "市", index = 10)
private String city;
}
至于复杂的excel，需要设置excel头，背景色等等详见easyExcel官方文档：
https://www.yuque.com/easyexcel/doc/easyexcel
4.分享easyExcel的包装类
public class EasyExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(EasyExcelUtil.class);
private static final String EXCEL_FOLDER = "excel/";
private static final String EXCEL_WRITE = "WRITE";
private static final String EXCEL_FILL = "FILL";
public EasyExcelUtil() {
}
public static List<Map<Integer, String>> syncRead(String filePath) {
return EasyExcelFactory.read(filePath).sheet().doReadSync();
}
public static List<Map<Integer, String>> syncRead(String filePath, Integer sheetNo) {
return EasyExcelFactory.read(filePath).sheet(sheetNo).doReadSync();
}
public static List<Map<Integer, String>> syncRead(InputStream inputStream, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();
}
public static List<Map<Integer, String>> syncRead(File file, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(file).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();
}
public static List<Map<Integer, String>> syncRead(String filePath, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(filePath).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();
}
public static List<T> syncReadModel(String filePath, Class clazz) {
return EasyExcelFactory.read(filePath).sheet().head(clazz).doReadSync();
}
public static List<T> syncReadModel(String filePath, Class clazz, Integer sheetNo) {
return EasyExcelFactory.read(filePath).sheet(sheetNo).head(clazz).doReadSync();
}
public static List<T> syncReadModel(InputStream inputStream, Class clazz, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
public static List<T> syncReadModel(File file, Class clazz, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(file).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
public static List<T> syncReadModel(String filePath, Class clazz, Integer sheetNo, Integer headRowNum) {
return EasyExcelFactory.read(filePath).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
public static void asyncRead(String filePath, ReadListener readListener) {
EasyExcelFactory.read(filePath, readListener).sheet().doRead();
}
public static void asyncRead(String filePath, ReadListener readListener, Integer sheetNo) {
EasyExcelFactory.read(filePath, readListener).sheet(sheetNo).doRead();
}
public static void asyncRead(InputStream inputStream, ReadListener readListener, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(inputStream, readListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
public static void asyncRead(InputStream inputStream, Class classHead, ReadListener readListener, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(inputStream, classHead, readListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
public static void asyncRead(File file, ReadListener readListener, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(file, readListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
public static void asyncRead(String filePath, ReadListener readListener, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(filePath, readListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
public static void asyncReadModel(String filePath, ReadListener readListener, Class clazz) {
EasyExcelFactory.read(filePath, clazz, readListener).sheet().doRead();
}
public static void asyncReadModel(String filePath, ReadListener readListener, Class clazz, Integer sheetNo) {
EasyExcelFactory.read(filePath, clazz, readListener).sheet(sheetNo).doRead();
}
public static void asyncReadModel(InputStream inputStream, ReadListener readListener, Class clazz, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(inputStream, clazz, readListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
public static void asyncReadModel(File file, ReadListener readListener, Class clazz, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(file, clazz, readListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
public static void asyncReadModel(String filePath, ReadListener readListener, Class clazz, Integer sheetNo, Integer headRowNum) {
EasyExcelFactory.read(filePath, clazz, readListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
public static void write(String filePath, List<List<String>> head, List<List<Object>> data) {
EasyExcel.write(filePath).head(head).sheet().doWrite(data);
}
public static void write(String filePath, List<List<String>> head, List<List<Object>> data, Integer sheetNo, String sheetName) {
EasyExcel.write(filePath).head(head).sheet(sheetNo, sheetName).doWrite(data);
}
public static void writeTemplate(String filePath, String templateFileName, Class headClazz, List data) {
EasyExcel.write(filePath, headClazz).withTemplate(templateFileName).sheet().doWrite(data);
}
public static void writeTemplate(String filePath, String templateFileName, List data) {
EasyExcel.write(filePath).withTemplate(templateFileName).sheet().doWrite(data);
}
public static void write(String filePath, Class headClazz, List data) {
EasyExcel.write(filePath, headClazz).sheet().doWrite(data);
}
public static void write(String filePath, Class headClazz, List data, Integer sheetNo, String sheetName) {
EasyExcel.write(filePath, headClazz).sheet(sheetNo, sheetName).doWrite(data);
}
public static void write(HttpServletResponse response, Class headClazz, List data, WriteHandler writeHandler, String sheetName, String fileName) throws Exception {
EasyExcel.write(getOutputStream(fileName, response), headClazz).registerWriteHandler(writeHandler).sheet(sheetName).doWrite(data);
}
public static void write(HttpServletResponse response, Class headClazz, List data, String sheetName, String fileName) throws Exception {
EasyExcel.write(getOutputStream(fileName, response), headClazz).sheet(sheetName).doWrite(data);
}
public static void write(String filePath, Class headClazz, List data, WriteHandler writeHandler, Integer sheetNo, String sheetName) {
EasyExcel.write(filePath, headClazz).registerWriteHandler(writeHandler).sheet(sheetNo, sheetName).doWrite(data);
}
public static void writeInclude(String filePath, Class headClazz, List data, Set<String> includeCols, String sheetName) {
EasyExcel.write(filePath, headClazz).includeColumnFiledNames(includeCols).sheet(sheetName).doWrite(data);
}
public static void writeExclude(String filePath, Class headClazz, List data, Set<String> excludeCols, Integer sheetNo, String sheetName) {
EasyExcel.write(filePath, headClazz).excludeColumnFiledNames(excludeCols).sheet(sheetNo, sheetName).doWrite(data);
}
public static void writeWithPullDown(HttpServletResponse response, Class classHead, String fileName, List excelData, String sheetName, final Integer firstRow, final Integer lastRow) throws Exception {
final Map<Integer, String[]> explicitListConstraintMap = new HashMap();
Field[] declaredFields = classHead.getDeclaredFields();
for(int i = 0; i < declaredFields.length; ++i) {
Field field = declaredFields[i];
ExcelPullDownConstraint explicitConstraint = (ExcelPullDownConstraint)field.getAnnotation(ExcelPullDownConstraint.class);
String[] explicitArray = resolveExplicitConstraint(explicitConstraint);
if (explicitArray != null && explicitArray.length > 0) {
explicitListConstraintMap.put(i, explicitArray);
}
}
write((HttpServletResponse)response, classHead, excelData, new SheetWriteHandler() {
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
explicitListConstraintMap.forEach((k, v) -> {
CellRangeAddressList rangeList = new CellRangeAddressList();
CellRangeAddress addr = new CellRangeAddress(firstRow == null ? 1 : firstRow, lastRow == null ? 500 : lastRow, k, k);
rangeList.addCellRangeAddress(addr);
DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
DataValidation validation = helper.createValidation(constraint, rangeList);
sheet.addValidationData(validation);
});
}
}, (String)sheetName, fileName);
}
private static String[] resolveExplicitConstraint(ExcelPullDownConstraint explicitConstraint) {
if (explicitConstraint == null) {
return null;
} else {
String[] source = explicitConstraint.source();
if (source.length > 0) {
return source;
} else {
Class<? extends ExcelPullDownExtension>[] classes = explicitConstraint.sourceClass();
if (classes.length > 0) {
ExcelPullDownExtension explicitInterface = null;
try {
explicitInterface = (ExcelPullDownExtension)classes[0].newInstance();
String[] source1 = explicitInterface.source();
if (source1.length > 0) {
return source1;
}
} catch (InstantiationException var5) {
var5.printStackTrace();
} catch (IllegalAccessException var6) {
var6.printStackTrace();
}
}
return null;
}
}
}
public static void writeWithFillTemplate(String templateNameWithSuffix, String fileName, Integer sheetNo, HttpServletResponse response, Map<String, Object> fillMap, List data) {
try {
ExcelWriter excelWriter = getExcelWriter(templateNameWithSuffix, fileName, response);
WriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(sheetNo).build();
if (fillMap != null) {
excelWriter.fill(fillMap, writeSheet);
}
if (data != null) {
excelWriter.fill(data, writeSheet);
}
excelWriter.finish();
} catch (Exception var8) {
resetResponse(response, var8);
}
}
public static <T> void asyncReadWithoutReadListener(InputStream inputStream, Class classHead, int sheetNo, int headRowNum, final int threshold, final String methodName, final Object objectInstance) {
if (objectInstance == null) {
throw new RuntimeException("对象实例不能为空!");
} else {
EasyExcelFactory.read(inputStream, classHead, new AnalysisEventListener<T>() {
private LinkedList<T> linkedList = new LinkedList();
@Override
public void invoke(T t, AnalysisContext context) {
this.linkedList.add(t);
if (this.linkedList.size() == threshold) {
EasyExcelUtil.LOGGER.info("达到指定阀值:{}执行批量操作", threshold);
EasyExcelUtil.getMethodAndInvoke(objectInstance, methodName, this.linkedList);
this.linkedList.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
EasyExcelUtil.LOGGER.info("执行结束 linkedList.size:{}", this.linkedList.size());
if (this.linkedList.size() > 0) {
EasyExcelUtil.LOGGER.info("处理最后的数据:{}", this.linkedList.size());
EasyExcelUtil.getMethodAndInvoke(objectInstance, methodName, this.linkedList);
this.linkedList.clear();
}
}
}).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
}
public static void writeWithTemplate(List data, String templateNameWithSuffix, String fileName, Integer sheetNo, HttpServletResponse response, WriteHandler writeHandler) {
try {
OutputStream outputStream = getOutputStream(fileName, response);
String templateFileAbsoluteUrl = getUrlPath(templateNameWithSuffix);
if (writeHandler == null) {
EasyExcel.write(outputStream).withTemplate(templateFileAbsoluteUrl).sheet(sheetNo).doWrite(data);
} else {
EasyExcel.write(outputStream).withTemplate(templateFileAbsoluteUrl).registerWriteHandler(writeHandler).sheet(sheetNo).doWrite(data);
}
} catch (Exception var8) {
resetResponse(response, var8);
}
}
private static String getUrlPath(String templateNameWithSuffix) throws Exception {
URL url = Thread.currentThread().getContextClassLoader().getResource("excel/" + templateNameWithSuffix);
if (url == null) {
throw new FileNotFoundException("在类路径下excel/文件夹中未获取到该模板名称【" + templateNameWithSuffix + "】的模板文件");
} else {
return url.getPath();
}
}
public static void write(HttpServletResponse response, Class headClazz, String fileName, String sheetName, String methodName, Object serviceObjectInstance, Object requestParams, WriteHandler writeHandler) throws Exception {
if (serviceObjectInstance == null) {
throw new RuntimeException("对象实例不能为空!");
} else {
ExcelWriter excelWriter = EasyExcel.write(getOutputStream(fileName, response), headClazz).build();
WriteSheet writeSheet;
if (writeHandler == null) {
writeSheet = EasyExcel.writerSheet().sheetName(sheetName).build();
} else {
writeSheet = EasyExcel.writerSheet().sheetName(sheetName).registerWriteHandler(writeHandler).build();
}
executeQueryAndWrite(methodName, serviceObjectInstance, requestParams, excelWriter, writeSheet, "WRITE");
}
}
private static void executeQueryAndWrite(String methodName, Object objectInstance, Object requestParams, ExcelWriter excelWriter, WriteSheet writeSheet, String methodType) {
int pageNo = 1;
short pageSize = 500;
while(true) {
List result = (List)getMethodAndInvoke(objectInstance, methodName, requestParams, pageNo, Integer.valueOf(pageSize));
if (CollectionUtils.isEmpty(result)) {
if (pageNo == 1) {
if (methodType.equals("WRITE")) {
excelWriter.write(new ArrayList(1), writeSheet);
} else if (methodType.equals("FILL")) {
excelWriter.fill(new ArrayList(1), writeSheet);
}
}
excelWriter.finish();
return;
}
++pageNo;
if (methodType.equals("WRITE")) {
excelWriter.write(result, writeSheet);
} else if (methodType.equals("FILL")) {
excelWriter.fill(result, writeSheet);
}
result.clear();
}
}
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException var3) {
resetResponse(response, var3);
return null;
}
}
private static Object getMethodAndInvoke(Object instance, String methodName, List data) {
try {
Method m = instance.getClass().getDeclaredMethod(methodName, List.class);
return m.invoke(instance, data);
} catch (NoSuchMethodException var4) {
throw new RuntimeException("根据方法名:[" + methodName + "],未匹配到参数类型为List的方法");
} catch (Exception var5) {
throw new RuntimeException("调用目标方法[" + methodName + "]异常:", var5);
}
}
private static Object getMethodAndInvoke(Object instance, String methodName, Object requestParams, Integer pageNo, Integer pageSize) {
try {
Method m = instance.getClass().getDeclaredMethod(methodName, requestParams.getClass(), Integer.class, Integer.class);
return m.invoke(instance, requestParams, pageNo, pageSize);
} catch (NoSuchMethodException var6) {
throw new RuntimeException("根据方法名:[" + methodName + "],未匹配到参数类型为[" + requestParams.getClass() + "、Integer、Integer]的方法");
} catch (Exception var7) {
throw new RuntimeException("调用目标方法[" + methodName + "]异常:", var7);
}
}
private static void resetResponse(HttpServletResponse response, Exception e) {
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
throw new RuntimeException("导出Excel异常:", e);
}
public static void writeWithTemplate(String templateNameWithSuffix, String fileName, Integer sheetNo, HttpServletResponse response, WriteHandler writeHandler, Object objectInstance, String methodName, Object requestParams) throws Exception {
if (objectInstance == null) {
throw new RuntimeException("对象实例不能为空!");
} else {
try {
ExcelWriter excelWriter = getExcelWriter(templateNameWithSuffix, fileName, response);
WriteSheet writeSheet;
if (writeHandler == null) {
writeSheet = EasyExcel.writerSheet().sheetNo(sheetNo).build();
} else {
writeSheet = EasyExcel.writerSheet().sheetNo(sheetNo).registerWriteHandler(writeHandler).build();
}
executeQueryAndWrite(methodName, objectInstance, requestParams, excelWriter, writeSheet, "WRITE");
} catch (Exception var10) {
resetResponse(response, var10);
}
}
}
public static void writeWithFillTemplate(String templateNameWithSuffix, String fileName, Integer sheetNo, HttpServletResponse response, Map<String, Object> fillMap, Object objectInstance, String methodName, Object requestParams) {
if (objectInstance == null) {
throw new RuntimeException("对象实例不能为空!");
} else {
try {
ExcelWriter excelWriter = getExcelWriter(templateNameWithSuffix, fileName, response);
WriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(sheetNo).build();
if (fillMap != null) {
excelWriter.fill(fillMap, writeSheet);
}
executeQueryAndWrite(methodName, objectInstance, requestParams, excelWriter, writeSheet, "FILL");
} catch (Exception var10) {
resetResponse(response, var10);
}
}
}
private static ExcelWriter getExcelWriter(String templateNameWithSuffix, String fileName, HttpServletResponse response) throws Exception {
OutputStream outputStream = getOutputStream(fileName, response);
String templateFileAbsoluteUrl = getUrlPath(templateNameWithSuffix);
return EasyExcel.write(outputStream).withTemplate(templateFileAbsoluteUrl).build();
}
}
727
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
