Java 利用EasyPoi做Excel模板的导入导出操作
项目背景加入pom依赖项目Excel模板图代码实现首先是实体类定义Excel 实现导入Excel的导出结束语项目背景
作为一名传统业务的程序员,每天都要不得不面对对各种类型的Excel的操作。想想都让人头疼。尤其是那种表头有的要纵向合并单元格,有的横向单元格合并的Excel,听听就让人头脑发麻。
最近,在做这方面的Excel操作的时候,就接到一个这样的场景。费了一番心思和各种模拟操作。终于大功告成。
加入pom依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>1.1.2-beta5</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>3.2.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency>
加入以上这些依赖(时间有些久,大概记得这些),这里面有几个坑,大家请认真对待。首先就是最后两个依赖的版本必须是一致性的,其次。这两个依赖版本必须一致,否则会出现问题。
项目Excel模板图
在实现时,先来看看我们的模板样式吧。
图1模板就是项目中遇到的一个第一种情况的模板。
图2的模板开始有些复杂了。接下来是图3模板。
图3这就是我遇到的第三种类型的模板,比第二种稍微复杂些。
针对这些固定不变的类型模板,就可以给模板的表头取出定义成实体类对象,然后通过EasyPoi操作对象,将问题简单化。
代码实现
首先是实体类定义
由于有三张不同的Excel表,所以,我定义了三个实体类(get和set就不写了)。
public class CalendarIndexEntity extends BaseRowModel {@ExcelProperty(value = {"Cycle Name"}, index = 0)private String cycleName;@ExcelProperty(value = {"Tenant ID"}, index = 1)private String tenantID;@ExcelProperty(value = {"League Entity"}, index = 2)private String leagueEntity;@ExcelProperty(value = {"Year"}, index = 3)private String year;@ExcelProperty(value = {"Payroll Frequency"}, index = 4)private String payrollFrequency;}
这是针对图1建立的实体。
public class CalendarMonthlyEntity extends BaseRowModel {@ExcelProperty(value = {"Step","Step"}, index = 0)private String payrollProcessNo;@ExcelProperty(value = {"Process Name","Process Name"}, index = 1)private String payrollProcessName;@ExcelProperty(value = {"Jan","1"}, index = 2)private String jan;@ExcelProperty(value = {"Feb","1"}, index = 3)private String feb;@ExcelProperty(value = {"Mar","1"}, index = 4)private String mar;@ExcelProperty(value = {"Apr","1"}, index = 5)private String apr;@ExcelProperty(value = {"May","1"}, index = 6)private String may;@ExcelProperty(value = {"Jun","1"}, index = 7)private String jun;@ExcelProperty(value = {"Jul","1"}, index = 8)private String jul;@ExcelProperty(value = {"Aug","1"}, index = 9)private String aug;@ExcelProperty(value = {"Sep","1"}, index = 10)private String sep;@ExcelProperty(value = {"Oct","1"}, index = 11)private String oct;@ExcelProperty(value = {"Nov","1"}, index = 12)private String nov;@ExcelProperty(value = {"Dec","1"}, index = 13)private String dec;@ExcelProperty(value = {"Mail To","Mail To"}, index = 14)private String sendMail;@ExcelProperty(value = {"Mail CC","Mail CC"}, index = 15)private String ccMail;}
这是针对图2建立的实体类。
public class CalendarMultipleEntity extends BaseRowModel { @ExcelProperty(value = {"Step","Step"}, index = 0)private String payrollProcessNo;@ExcelProperty(value = {"Process Name","Process Name"}, index = 1)private String payrollProcessName;@ExcelProperty(value = {"Jan","1"}, index = 2)private String jan;@ExcelProperty(value = {"Jan","2"}, index = 3)private String jan1;@ExcelProperty(value = {"Feb","1"}, index = 4)private String feb;@ExcelProperty(value = {"Feb","2"}, index = 5)private String feb1;@ExcelProperty(value = {"Mar","1"}, index = 6)private String mar;@ExcelProperty(value = {"Mar","2"}, index = 7)private String mar1;@ExcelProperty(value = {"Apr","1"}, index = 8)private String apr;@ExcelProperty(value = {"Apr","2"}, index = 9)private String apr1;@ExcelProperty(value = {"May","1"}, index = 10)private String may;@ExcelProperty(value = {"May","2"}, index = 11)private String may1;@ExcelProperty(value = {"Jun","1"}, index = 12)private String jun;@ExcelProperty(value = {"Jun","2"}, index = 13)private String jun1;@ExcelProperty(value = {"Jul","1"}, index = 14)private String jul;@ExcelProperty(value = {"Jul","2"}, index = 15)private String jul1;@ExcelProperty(value = {"Aug","1"}, index = 16)private String aug;@ExcelProperty(value = {"Aug","2"}, index = 17)private String aug1;@ExcelProperty(value = {"Sep","1"}, index = 18)private String sep;@ExcelProperty(value = {"Sep","2"}, index = 19)private String sep1;@ExcelProperty(value = {"Oct","1"}, index = 20)private String oct;@ExcelProperty(value = {"Oct","2"}, index = 21)private String oct1;@ExcelProperty(value = {"Nov","1"}, index = 22)private String nov;@ExcelProperty(value = {"Nov","2"}, index = 23)private String nov1;@ExcelProperty(value = {"Dec","1"}, index = 24)private String dec;@ExcelProperty(value = {"Dec","2"}, index = 25)private String dec1;@ExcelProperty(value = {"Mail To","Mail To"}, index = 26)private String sendMail;@ExcelProperty(value = {"Mail CC","Mail CC"}, index = 27)private String ccMail;}
这是针对图3建立的实体内。
这次该介绍下些实体类的具体细节了。
实体类要继承 BaseRowModel@ExcelProperty(value = {“Process Name”,“Process Name”}, index = 1)这个注解表示对应表中的表头。value值可以任意多个,但实际表述不一样,一个值表示表头占一个单元格的表头,两个一样的值表示,单元格竖向合并,三个一样的值就是值竖向合并三个单元格。 另外两个不一样的值,表示竖向放着表头顺序,三个类似。如何横向合并单元格,可以定义两个@ExcelProperty(value = {“Process Name”,""}, index = 1),但是index要值要大一位。因为index表示在表中的哪一列(注意。默认0为第一列)
Excel 实现导入
public class ExcelUtils {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();}}
这是我用到的一个单表的导入读取。
下面就是开始读取Excel了。
Object objects = ExcelUtils.readExcel(file, new CalendarIndexEntity(), 1, 1);
List calendarIndexEntities= (List)objects;
这就是读取到的Excel数据,其中参数分别是文件模板,模板中的实体类,该表是第几个sheet(sheet默认为1),从第几行开始读取数据(默认为0)。
Excel的导出
ServletOutputStream out = response.getOutputStream();ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);//1:表示第几页sheet,CalendarIndexEntity.class,这个表示要写入的实体操作Sheet sheet = new Sheet(1, 0,CalendarIndexEntity.class);//设置自适应宽度sheet.setAutoWidth(Boolean.TRUE);// 第一个 sheet 名称sheet.setSheetName("Index");//list为CalendarIndexEntity.class类实体集合writer.write(list, sheet);response.setCharacterEncoding("utf-8");//设置文件ContentType类型,这样设置,会自动判断下载文件类型response.setContentType("multipart/form-data");//设置文件名中文编码,防止乱码new String(fileName.getBytes("utf-8"), "ISO8859-1");response.setHeader("Pragma", "No-cache");response.setHeader("Access-Control-Allow-Origin", "*");response.setHeader("Access-Control-Expose-Headers", "content-disposition");response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xlsx");writer.finish();response.setContentType("multipart/form-data");response.setCharacterEncoding("utf-8");out.flush();
结束语
这就是我在项目中遇到的问题。第一次写,如有说的不清,或者不对的地方,希望大家指出错误,谢谢!