【springboot+easypoi】一行代码搞定excel导入导出

傷城~ 2022-05-13 08:48 323阅读 0赞

*  开发中经常会遇到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等，此处我们用不到，可以去官方查看更多

<table> 
 <tbody> 
 <tr> 
 <td style="width:111pt;">属性</td> 
 <td style="width:73pt;">类型</td> 
 <td style="width:97pt;">类型</td> 
 <td style="width:445pt;">说明</td> 
 </tr> 
 <tr> 
 <td>name</td> 
 <td>String</td> 
 <td>null</td> 
 <td style="width:445pt;">列名</td> 
 </tr> 
 <tr> 
 <td>needMerge</td> 
 <td>boolean</td> 
 <td>fasle</td> 
 <td style="width:445pt;">纵向合并单元格</td> 
 </tr> 
 <tr> 
 <td>orderNum</td> 
 <td>String</td> 
 <td>0</td> 
 <td style="width:445pt;">列的排序,支持name_id</td> 
 </tr> 
 <tr> 
 <td>replace</td> 
 <td>String[]</td> 
 <td>{}</td> 
 <td style="width:445pt;">值得替换 导出是{a_id,b_id} 导入反过来</td> 
 </tr> 
 <tr> 
 <td>savePath</td> 
 <td>String</td> 
 <td>upload</td> 
 <td style="width:445pt;">导入文件保存路径</td> 
 </tr> 
 <tr> 
 <td>type</td> 
 <td>int</td> 
 <td>1</td> 
 <td style="width:445pt;">导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本</td> 
 </tr> 
 <tr> 
 <td>width</td> 
 <td>double</td> 
 <td>10</td> 
 <td style="width:445pt;">列宽</td> 
 </tr> 
 <tr> 
 <td>height</td> 
 <td>double</td> 
 <td>10</td> 
 <td style="width:445pt;">列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意</td> 
 </tr> 
 <tr> 
 <td>isStatistics</td> 
 <td>boolean</td> 
 <td>fasle</td> 
 <td style="width:445pt;">自动统计数据,在追加一行统计,把所有数据都和输出这个处理会吞没异常,请注意这一点</td> 
 </tr> 
 <tr> 
 <td>isHyperlink</td> 
 <td>boolean</td> 
 <td>FALSE</td> 
 <td style="width:445pt;">超链接,如果是需要实现接口返回对象</td> 
 </tr> 
 <tr> 
 <td>isImportField</td> 
 <td>boolean</td> 
 <td>TRUE</td> 
 <td style="width:445pt;">校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id</td> 
 </tr> 
 <tr> 
 <td>exportFormat</td> 
 <td>String</td> 
 <td>&nbsp;</td> 
 <td style="width:445pt;">导出的时间格式,以这个是否为空来判断是否需要格式化日期</td> 
 </tr> 
 <tr> 
 <td>importFormat</td> 
 <td>String</td> 
 <td>&nbsp;</td> 
 <td style="width:445pt;">导入的时间格式,以这个是否为空来判断是否需要格式化日期</td> 
 </tr> 
 <tr> 
 <td>format</td> 
 <td>String</td> 
 <td>&nbsp;</td> 
 <td style="width:445pt;">时间格式,相当于同时设置了exportFormat 和 importFormat</td> 
 </tr> 
 <tr> 
 <td>databaseFormat</td> 
 <td>String</td> 
 <td>yyyyMMddHHmmss</td> 
 <td style="width:445pt;">导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出</td> 
 </tr> 
 <tr> 
 <td>numFormat</td> 
 <td>String</td> 
 <td>&nbsp;</td> 
 <td style="width:445pt;">数字格式化,参数是Pattern,使用的对象是DecimalFormat</td> 
 </tr> 
 <tr> 
 <td>imageType</td> 
 <td>int</td> 
 <td>1</td> 
 <td style="width:445pt;">导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的</td> 
 </tr> 
 <tr> 
 <td>suffix</td> 
 <td>String</td> 
 <td>&nbsp;</td> 
 <td style="width:445pt;">文字后缀,如% 90 变成90%</td> 
 </tr> 
 <tr> 
 <td>isWrap</td> 
 <td>boolean</td> 
 <td>TRUE</td> 
 <td style="width:445pt;">是否换行 即支持\n</td> 
 </tr> 
 <tr> 
 <td>mergeRely</td> 
 <td>int[]</td> 
 <td>{}</td> 
 <td style="width:445pt;">合并单元格依赖关系,比如第二列合并是基于第一列 则{1}就可以了</td> 
 </tr> 
 <tr> 
 <td>mergeVertical</td> 
 <td>boolean</td> 
 <td>fasle</td> 
 <td style="width:445pt;">纵向合并内容相同的单元格</td> 
 </tr> 
 </tbody> 
</table>

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 保存数据库
 	}

导出结果

![70][]

修改自定义样式

查看源码我发现他内部封装的是：![70 1][]

所以我们只需要设置这个样式就行了如：

public class ShelterIExcelExportStyler extends ExcelExportStylerDefaultImpl implements IExcelExportStyler{
    
    	public ShelterIExcelExportStyler(Workbook workbook) {
    		super(workbook);
    	}
    
        @Override
        public CellStyle getTitleStyle(short color) {
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
            titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            titleStyle.setWrapText(true);
            return titleStyle;
        }
    
    
        @Override
        public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
            CellStyle style = workbook.createCellStyle();
            style.setAlignment(CellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            style.setDataFormat(STRING_FORMAT);
    
            if (isWarp) {
                style.setWrapText(true);
            }
    
            return style;
        }
    
    
        @Override
        public CellStyle getHeaderStyle(short color) {
            CellStyle titleStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 20);
            titleStyle.setFont(font)
            titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
            titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            return titleStyle;
        }
    
        @Override
        public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
            CellStyle style = workbook.createCellStyle();
            Font font = workbook.createFont();
    //        font.setFontHeightInPoints((short) 15);
            style.setFont(font);
            style.setAlignment(CellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            style.setDataFormat(STRING_FORMAT);
    
            if (isWarp) {
                style.setWrapText(true);
            }
            return style;
        }
    
    }

这里我只是简单的修改了他默认字体大小。如果要设置表格的宽度不可以在这里设置。

![70 2][]

private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
 HttpServletResponse response, ExportParams exportParams, String sheetName) {
 
 Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
 Sheet sheet=workbook.getSheet(sheetName);
 // sheet.CreateRow(0).Height = (short)(200*20);
 // sheet.createRow(0);
 sheet.getRow(0).setHeight((short)(50*20));
 sheet.getRow(1).setHeight((short)(30*20));
 
 if (workbook != null); downLoadExcel(fileName, response, workbook);
 
 }

他会调用ExportExcel返回一个Workbook对象，然后通过这个对象获取Sheet才能改变行的宽度千万不腰用CreateRow会覆盖。

有些人会在使用导入的时候出现只导入一条数据或者列缺少的情况，个人推荐可以使用步进指令去调试这段代码改成适用自己的”轮子“。我通过调试发现保存数据是这个方法：

private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params,Map<String, PictureData> pictures) throws Exception {
 
 		List collection = new ArrayList();
 
 		Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
 
 		List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();
 
 		String targetId = null;
 
 		if (!Map.class.equals(pojoClass)) {
 
 			Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
 
 			ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
 
 			if (etarget != null) {
 
 				targetId = etarget.value();
 
 			}
 
 			getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null, null);
 
 		}
 
 		Iterator<Row> rows = sheet.rowIterator();
 
 		for (int j = 0; j < params.getTitleRows(); j++) {
 
 			rows.next();
 
 		}
 
 		Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection);
 
 		checkIsValidTemplate(titlemap, excelParams, params, excelCollection);
 
 		Row row = null;
 
 		Object object = null;
 
 		String picId;
 
 		int readRow = 0;
 
 		// 跳过无效行
 
 		for (int i = 0; i < params.getStartRows(); i++) {
 
 			rows.next();
 
 		}
 
 		while (rows.hasNext()
 
 		&& (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
 
 			if (params.getReadRows() > 0 && readRow > params.getReadRows()) {
 
 				break;
 
 			}
 
 			row = rows.next();
 
 			// Fix 如果row为无效行时候跳出
 
 			if (sheet.getLastRowNum() - row.getRowNum() < params.getLastOfInvalidRow()) {
 
 				break;
 
 			}
 
 			// 判断是集合元素还是不是集合元素,如果是就继续加入这个集合,不是就创建新的对象
 
 			// keyIndex 如果为空就不处理,仍然处理这一行
 
 			if (params.getKeyIndex() != null && !(row.getCell(params.getKeyIndex()) == null
 
 			|| StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) {
 
 				for (ExcelCollectionParams param : excelCollection) {
 
 					addListContinue(object, param, row, titlemap, targetId, pictures, params);
 
 				}
 
 			} else {
 
 				object = PoiPublicUtil.createObject(pojoClass, targetId);
 
 				try {
 
 					// 标记为null的次数
 
 					int count = 0;
 
 					int sum = titlemap.size();
 
 					for (int i = row.getFirstCellNum(); i <= sum; i++) {
 
 						Cell cell = row.getCell(i);
 
 						boolean flag = true;
 
 						if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
 
 							count++;
 
 							flag = false;
 
 						}
 
 						String titleString = (String) titlemap.get(i);
 
 						if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {
 
 							if (excelParams.get(titleString) != null && excelParams.get(titleString).getType() == 2) {
 
 								picId = row.getRowNum() + "_" + i;
 
 								saveImage(object, picId, excelParams, titleString, pictures, params);
 
 							} else {
 
 								if (saveFieldValue(params, object, cell, excelParams, titleString, row)) {
 
 									if (flag) // 只有当没有count++过才能添加。
 
 										count++;
 
 								}
 
 							}
 
 						}
 
 					}
 
 					for (ExcelCollectionParams param : excelCollection) {
 
 						addListContinue(object, param, row, titlemap, targetId, pictures, params);
 
 					}
 
 					if (verifyingDataValidity(object, row, params, pojoClass)) {
 
 						// count等于0或者
 
 						if ((count == 0) || (count <= sum - 2))
 
 							collection.add(object);
 
 					} else {
 
 						// 如果为null的次数小于5则添加
 
 						// if (count!=0 || count < sum-3)
 
 						failCollection.add(object);
 
 					}
 
 				} catch (ExcelImportException e) {
 
 					LOGGER.error("excel import error , row num:{},obj:{}", readRow,
 
 					ReflectionToStringBuilder.toString(object));
 
 					if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
 
 						throw new ExcelImportException(e.getType(), e);
 
 					}
 
 				} catch (Exception e) {
 
 					LOGGER.error("excel import error , row num:{},obj:{}", readRow,
 
 					ReflectionToStringBuilder.toString(object));
 
 					throw new RuntimeException(e);
 
 				}
 
 			}
 
 			readRow++;
 
 		}
 
 		return collection;
 
 	}

这个类是ExcelImportServer.java

![70 3][]

修改的位置在这里，我通过修改这段代码使其强制进入else中可以拿到所有数据，然后判断null的次数选择是否添加。

![70 4][]

![70 5][]

[70]: /images/20220513/8d79cdb8e62f47d9a451bdf89fff01d1.png
[70 1]: /images/20220513/b8382c1c6c8041868f4545521e7d2639.png
[70 2]: /images/20220513/233b665c8f1b46e4aad9b225c3004c01.png
[70 3]: /images/20220513/3ba9788a591d46519f703fa98b7883f0.png
[70 4]: /images/20220513/dc089c8e355647038d2c3c9069dbe1e2.png
[70 5]: /images/20220513/01dc464633364613a65e6b1d943a4435.png