参考 https://www.jianshu.com/p/5d67fb720ece
pom引入
<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等,此处我们用不到,可以去官方查看更多
import cn.afterturn.easypoi.excel.annotation.Excel; import java.util.Date; public class Person { @Excel(name = "姓名", orderNum = "0") private String name; @Excel(name = "性别", replace = {"男_1", "女_2"}, orderNum = "1") private String sex; @Excel(name = "生日", exportFormat = "yyyy-MM-dd", orderNum = "2") private Date birthday; public Person(String name, String sex, Date birthday) { this.name = name; this.sex = sex; this.birthday = birthday; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.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); } public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){ defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName)); } public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){ defaultExport(list, fileName, response); } private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) { Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list); if (workbook != null); downLoadExcel(fileName, response, workbook); } private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) { try { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); workbook.write(response.getOutputStream()); } catch (IOException e) { throw new 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); } public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){ if (StringUtils.isBlank(filePath)){ return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params); }catch (NoSuchElementException e){ throw new NormalException("模板不能为空"); } catch (Exception e) { e.printStackTrace(); throw new NormalException(e.getMessage()); } return list; } public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){ if (file == null){ return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params); }catch (NoSuchElementException e){ throw new NormalException("excel文件不能为空"); } catch (Exception e) { throw new NormalException(e.getMessage()); } return list; }
测试
@RequestMapping("export") public void export(HttpServletResponse response){ //模拟从数据库获取需要导出的数据 List<Person> personList = new ArrayList<>(); Person person1 = new Person("路飞","1",new Date()); Person person2 = new Person("娜美","2", DateUtils.addDate(new Date(),3)); Person person3 = new Person("索隆","1", DateUtils.addDate(new Date(),10)); Person person4 = new Person("小狸猫","1", DateUtils.addDate(new Date(),-10)); personList.add(person1); personList.add(person2); personList.add(person3); personList.add(person4); //导出操作 FileUtil.exportExcel(personList,"花名册","草帽一伙",Person.class,"海贼王.xls",response); } @RequestMapping("importExcel") public void importExcel(){ String filePath = "F:\海贼王.xls"; //解析excel, List<Person> personList = FileUtil.importExcel(filePath,1,1,Person.class); //也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入 System.out.println("导入数据一共【"+personList.size()+"】行"); //TODO 保存数据库 }