java 导入导出 插件_JAVA项目实战-阿里巴巴easyexcel导出导入工具使用

packagecom.sf.vsolution.hb.sfce.util.excel;importcom.alibaba.excel.EasyExcelFactory;importcom.alibaba.excel.ExcelWriter;importcom.alibaba.excel.context.AnalysisContext;importcom.alibaba.excel.event.AnalysisEventListener;importcom.alibaba.excel.metadata.BaseRowModel;importcom.alibaba.excel.metadata.Sheet;importcom.alibaba.excel.support.ExcelTypeEnum;importcom.alibaba.excel.util.CollectionUtils;importlombok.Data;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importorg.springframework.util.StringUtils;importjavax.servlet.ServletOutputStream;importjavax.servlet.http.HttpServletResponse;import java.io.*;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;importjava.util.Map;/*** @description: 阿里巴巴EasyExcel工具

*@author: zhucj

* @date: 2019-11-05 13:22*/

public classEasyExcelUtils {private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtils.class);private staticSheet initSheet;static{

initSheet= new Sheet(1, 0);

initSheet.setSheetName("sheet");//设置自适应宽度

initSheet.setAutoWidth(Boolean.TRUE);

}/*** 导入

* 少于1000行数据 默认样式

*@paramfilePath 文件绝对路径

*@return

*/

public static ListreadLessThan1000Row(String filePath) {return readLessThan1000RowBySheet(filePath, null);

}/*** 导入

* 少于1000行数据，带样式的

*@paramfilePath 文件绝对路径

*@paramsheet

*@return

*/

public static ListreadLessThan1000RowBySheet(String filePath, Sheet sheet) {if (!StringUtils.hasText(filePath)) {return null;

}

sheet= sheet != null ?sheet : initSheet;

InputStream inputStream= null;try{

inputStream= newFileInputStream(filePath);returnEasyExcelFactory.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行数据 默认样式

*@paramfilePath

*@return

*/

public static ListreadMoreThan1000Row(String filePath) {return readMoreThan1000RowBySheet(filePath, null);

}/*** 导入

* 大于1000行数据 自定义样式

*@paramfilePath

*@paramsheet

*@return

*/

public static ListreadMoreThan1000RowBySheet(String filePath, Sheet sheet) {if (!StringUtils.hasText(filePath)) {return null;

}

sheet= sheet != null ?sheet : initSheet;

InputStream inputStream= null;try{

inputStream= newFileInputStream(filePath);

ExcelListener excelListener= newExcelListener();

EasyExcelFactory.readBySax(inputStream, sheet, excelListener);returnexcelListener.getDatas();

}catch(FileNotFoundException e) {

logger.error("找不到文件或者文件路径错误");

}finally{try{if (inputStream != null) {

inputStream.close();

}

}catch(IOException e) {

logger.error("excel文件读取失败，失败原因：{}", e);

}

}return null;

}/*** 导出单个sheet

*@paramresponse

*@paramdataList

*@paramsheet

*@paramfileName

*@throwsUnsupportedEncodingException*/

public static void writeExcelOneSheet(HttpServletResponse response, List extends BaseRowModel>dataList, Sheet sheet, String fileName) {if(CollectionUtils.isEmpty(dataList)) {throw new RuntimeException("导出的表格数据为空!");

}//如果sheet为空，则使用默认的

if (null ==sheet) {

sheet=initSheet;

}try{

String value= "attachment; filename=" + newString(

(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 sheetName和每个sheet的数据

* ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx

* @Date 上午12:16 2019/1/31*/

public static void writeExcelMutilSheet(HttpServletResponse response, Map> dataList, String fileName) throwsUnsupportedEncodingException {if(CollectionUtils.isEmpty(dataList)) {throw new RuntimeException("导出的表格数据为空!");

}try{

String value= "attachment; filename=" + newString(

(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>dataList, ExcelWriter writer) {int sheetNum = 1;for (Map.Entry>stringListEntry : dataList.entrySet()) {

Sheet sheet= new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());

sheet.setSheetName(stringListEntry.getKey());

writer.write(stringListEntry.getValue(), sheet);

sheetNum++;

}

}/*** 导出监听*/@Datapublic static class ExcelListener extendsAnalysisEventListener {private List datas = new ArrayList<>();/*** 逐行解析

*@paramobject 当前行的数据

*@paramanalysisContext*/@Overridepublic voidinvoke(Object object, AnalysisContext analysisContext) {if (object != null) {

datas.add(object);

}

}/*** 解析完所有数据后会调用该方法

*@paramanalysisContext*/@Overridepublic voiddoAfterAllAnalysed(AnalysisContext analysisContext) {

}

}

}