Java 利用EasyPoi做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();
结束语
这就是我在项目中遇到的问题。第一次写，如有说的不清，或者不对的地方，希望大家指出错误，谢谢！
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
