项目背景

作为一名传统业务的程序员，每天都要不得不面对对各种类型的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();

结束语

这就是我在项目中遇到的问题。第一次写，如有说的不清，或者不对的地方，希望大家指出错误，谢谢！