- 开发中经常会遇到excel的处理,导入导出解析等等,java中比较流行的用poi,但是每次都要写大段工具类来搞定这事儿,此处推荐一个别人造好的轮子easypoi,下面介绍下“轮子”的使用。
pom引入
- 不再需要其他jar
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
编写实体类
- 此处注意必须要有空构造函数,否则会报错“对象创建错误”
- 关于注解@Excel,其他还有@ExcelCollection,@ExcelEntity ,@ExcelIgnore,@ExcelTarget等,此处用不到,可以去官方查看更多
属性 | 类型 | 类型 | 说明 |
---|---|---|---|
name | String | null | 列名 |
needMerge | boolean | fasle | 纵向合并单元格 |
orderNum | String | "0" | 列的排序,支持name_id |
replace | String[] | {} | 值得替换 导出是{a_id,b_id} 导入反过来 |
savePath | String | "upload" | 导入文件保存路径 |
type | int | 1 | 导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本 |
width | double | 10 | 列宽 |
height | double | 10 | 列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意 |
isStatistics | boolean | fasle | 自动统计数据,在追加一行统计,把所有数据都和输出这个处理会吞没异常,请注意这一点 |
isHyperlink | boolean | false | 超链接,如果是需要实现接口返回对象 |
isImportField | boolean | true | 校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id |
exportFormat | String | "" | 导出的时间格式,以这个是否为空来判断是否需要格式化日期 |
importFormat | String | "" | 导入的时间格式,以这个是否为空来判断是否需要格式化日期 |
format | String | "" | 时间格式,相当于同时设置了exportFormat 和 importFormat |
databaseFormat | String | "yyyyMMddHHmmss" | 导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出 |
numFormat | String | "" | 数字格式化,参数是Pattern,使用的对象是DecimalFormat |
imageType | int | 1 | 导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的 |
suffix | String | "" | 文字后缀,如% 90 变成90% |
isWrap | boolean | true | 是否换行 即支持\n |
mergeRely | int[] | {} | 合并单元格依赖关系,比如第二列合并是基于第一列 则{1}就可以了 |
mergeVertical | boolean | fasle | 纵向合并内容相同的单元格 |
import cn.afterturn.easypoi.excel.annotation.Excel;
import java.util.Date;
public class Person {
<span class="hljs-meta">@Excel</span>(name = <span class="hljs-string">"姓名"</span>, orderNum = <span class="hljs-string">"0"</span>)
<span class="hljs-keyword">private</span> String name;
<span class="hljs-meta">@Excel</span>(name = <span class="hljs-string">"性别"</span>, replace = {<span class="hljs-string">"男_1"</span>, <span class="hljs-string">"女_2"</span>}, orderNum = <span class="hljs-string">"1"</span>)
<span class="hljs-keyword">private</span> String sex;
<span class="hljs-meta">@Excel</span>(name = <span class="hljs-string">"生日"</span>, exportFormat = <span class="hljs-string">"yyyy-MM-dd"</span>, orderNum = <span class="hljs-string">"2"</span>)
<span class="hljs-keyword">private</span> Date birthday;
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-title">Person</span><span class="hljs-params">(String name, String sex, Date birthday)</span> </span>{
<span class="hljs-keyword">this</span>.name = name;
<span class="hljs-keyword">this</span>.sex = sex;
<span class="hljs-keyword">this</span>.birthday = birthday;
}
<span class="hljs-function"><span class="hljs-keyword">public</span> String <span class="hljs-title">getName</span><span class="hljs-params">()</span> </span>{
<span class="hljs-keyword">return</span> name;
}
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setName</span><span class="hljs-params">(String name)</span> </span>{
<span class="hljs-keyword">this</span>.name = name;
}
<span class="hljs-function"><span class="hljs-keyword">public</span> String <span class="hljs-title">getSex</span><span class="hljs-params">()</span> </span>{
<span class="hljs-keyword">return</span> sex;
}
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setSex</span><span class="hljs-params">(String sex)</span> </span>{
<span class="hljs-keyword">this</span>.sex = sex;
}
<span class="hljs-function"><span class="hljs-keyword">public</span> Date <span class="hljs-title">getBirthday</span><span class="hljs-params">()</span> </span>{
<span class="hljs-keyword">return</span> birthday;
}
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setBirthday</span><span class="hljs-params">(Date birthday)</span> </span>{
<span class="hljs-keyword">this</span>.birthday = birthday;
}
}
导入导出公用方法
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> void exportExcel(<span class="hljs-keyword">List</span><span class="hljs-meta"><?</span>> <span class="hljs-keyword">list</span>, String title, String sheetName, <span class="hljs-class"><span class="hljs-keyword">Class</span><?> <span class="hljs-title">pojoClass</span>,<span class="hljs-title">String</span> <span class="hljs-title">fileName</span>, <span class="hljs-title">HttpServletResponse</span> <span class="hljs-title">response</span>)</span>{
defaultExport(<span class="hljs-keyword">list</span>, pojoClass, fileName, response, <span class="hljs-keyword">new</span> ExportParams(title, sheetName));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
defaultExport(list, fileName, response);
}
<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> void defaultExport(<span class="hljs-keyword">List</span><span class="hljs-meta"><?</span>> <span class="hljs-keyword">list</span>, <span class="hljs-class"><span class="hljs-keyword">Class</span><?> <span class="hljs-title">pojoClass</span>, <span class="hljs-title">String</span> <span class="hljs-title">fileName</span>, <span class="hljs-title">HttpServletResponse</span> <span class="hljs-title">response</span>, <span class="hljs-title">ExportParams</span> <span class="hljs-title">exportParams</span>) </span>{
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,<span class="hljs-keyword">list</span>);
<span class="hljs-keyword">if</span> (workbook != <span class="hljs-keyword">null</span>);
downLoadExcel(fileName, response, workbook);
}
<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
<span class="hljs-keyword">try</span> {
response.setCharacterEncoding(<span class="hljs-string">"UTF-8"</span>);
response.setHeader(<span class="hljs-string">"content-Type"</span>, <span class="hljs-string">"application/vnd.ms-excel"</span>);
response.setHeader(<span class="hljs-string">"Content-Disposition"</span>,
<span class="hljs-string">"attachment;filename="</span> + URLEncoder.encode(fileName, <span class="hljs-string">"UTF-8"</span>));
workbook.write(response.getOutputStream());
} <span class="hljs-keyword">catch</span> (IOException e) {
<span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> NormalException(e.getMessage());
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <T> <span class="hljs-keyword">List</span><T> importExcel(String filePath,Integer titleRows,Integer headerRows, <span class="hljs-class"><span class="hljs-keyword">Class</span><<span class="hljs-title">T</span>> <span class="hljs-title">pojoClass</span>)</span>{
<span class="hljs-keyword">if</span> (StringUtils.isBlank(filePath)){
<span class="hljs-keyword">return</span> <span class="hljs-keyword">null</span>;
}
ImportParams params = <span class="hljs-keyword">new</span> ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
<span class="hljs-keyword">List</span><T> <span class="hljs-keyword">list</span> = <span class="hljs-keyword">null</span>;
<span class="hljs-keyword">try</span> {
<span class="hljs-keyword">list</span> = ExcelImportUtil.importExcel(<span class="hljs-keyword">new</span> File(filePath), pojoClass, params);
}<span class="hljs-keyword">catch</span> (NoSuchElementException e){
<span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> NormalException(<span class="hljs-string">"模板不能为空"</span>);
} <span class="hljs-keyword">catch</span> (<span class="hljs-keyword">Exception</span> e) {
e.printStackTrace();
<span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> NormalException(e.getMessage());
}
<span class="hljs-keyword">return</span> <span class="hljs-keyword">list</span>;
}
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <T> <span class="hljs-keyword">List</span><T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, <span class="hljs-class"><span class="hljs-keyword">Class</span><<span class="hljs-title">T</span>> <span class="hljs-title">pojoClass</span>)</span>{
<span class="hljs-keyword">if</span> (file == <span class="hljs-keyword">null</span>){
<span class="hljs-keyword">return</span> <span class="hljs-keyword">null</span>;
}
ImportParams params = <span class="hljs-keyword">new</span> ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
<span class="hljs-keyword">List</span><T> <span class="hljs-keyword">list</span> = <span class="hljs-keyword">null</span>;
<span class="hljs-keyword">try</span> {
<span class="hljs-keyword">list</span> = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}<span class="hljs-keyword">catch</span> (NoSuchElementException e){
<span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> NormalException(<span class="hljs-string">"excel文件不能为空"</span>);
} <span class="hljs-keyword">catch</span> (<span class="hljs-keyword">Exception</span> e) {
<span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> NormalException(e.getMessage());
}
<span class="hljs-keyword">return</span> <span class="hljs-keyword">list</span>;
}
对的,没看错,这就可以导出导入了,看起来代码挺多,其实是提供了多个导入导出方法而已
测试
@RequestMapping("export")
public void export(HttpServletResponse response){
<span class="hljs-comment">//模拟从数据库获取需要导出的数据</span>
List<Person> personList = <span class="hljs-keyword">new</span> ArrayList<>();
Person person1 = <span class="hljs-keyword">new</span> Person(<span class="hljs-string">"路飞"</span>,<span class="hljs-string">"1"</span>,<span class="hljs-keyword">new</span> Date());
Person person2 = <span class="hljs-keyword">new</span> Person(<span class="hljs-string">"娜美"</span>,<span class="hljs-string">"2"</span>, DateUtils.addDate(<span class="hljs-keyword">new</span> Date(),<span class="hljs-number">3</span>));
Person person3 = <span class="hljs-keyword">new</span> Person(<span class="hljs-string">"索隆"</span>,<span class="hljs-string">"1"</span>, DateUtils.addDate(<span class="hljs-keyword">new</span> Date(),<span class="hljs-number">10</span>));
Person person4 = <span class="hljs-keyword">new</span> Person(<span class="hljs-string">"小狸猫"</span>,<span class="hljs-string">"1"</span>, DateUtils.addDate(<span class="hljs-keyword">new</span> Date(),<span class="hljs-number">-10</span>));
personList.<span class="hljs-keyword">add</span>(person1);
personList.<span class="hljs-keyword">add</span>(person2);
personList.<span class="hljs-keyword">add</span>(person3);
personList.<span class="hljs-keyword">add</span>(person4);
<span class="hljs-comment">//导出操作</span>
FileUtil.exportExcel(personList,<span class="hljs-string">"花名册"</span>,<span class="hljs-string">"草帽一伙"</span>,Person.class,<span class="hljs-string">"海贼王.xls"</span>,response);
}
@RequestMapping(<span class="hljs-string">"importExcel"</span>)
<span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">importExcel</span>(<span class="hljs-params"></span>)</span>{
String filePath = <span class="hljs-string">"F:\\海贼王.xls"</span>;
<span class="hljs-comment">//解析excel,</span>
List<Person> personList = FileUtil.importExcel(filePath,<span class="hljs-number">1</span>,<span class="hljs-number">1</span>,Person.class);
<span class="hljs-comment">//也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入</span>
System.<span class="hljs-keyword">out</span>.println(<span class="hljs-string">"导入数据一共【"</span>+personList.size()+<span class="hljs-string">"】行"</span>);
<span class="hljs-comment">//TODO 保存数据库</span>
}
导出结果
导出结果
测试导入
导出结果再添加一行,执行,输出导入数据行数
导入结果,OVER
</div>
</div>