批量导入--EasyPOIPOI
easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法
教程地址:http://easypoi.mydoc.io
1.EasyPOIUtil
import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.ImportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import cn.aid.common.utils.exception.NormalException; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.net.URLEncoder; import java.util.List; import java.util.Map; import java.util.NoSuchElementException; /** * 导入 导出通用工具 */ public class EasyPOIUtil { public static int titleRows = 0; public static int headerRows = 1; 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; } public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,int sheetNum,Class<T> pojoClass){ if (file == null){ return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); params.setStartSheetIndex(sheetNum); 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; } public static Workbook getWorkBook(MultipartFile file) throws IOException{ //这样写 excel 能兼容03和07 InputStream is = file.getInputStream(); Workbook hssfWorkbook = null; try { hssfWorkbook = new HSSFWorkbook(is); } catch (Exception ex) { is =file.getInputStream(); hssfWorkbook = new XSSFWorkbook(is); } return hssfWorkbook; } }
2.maven坐标
<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.2.0</version> </dependency>
3.java代码
@RequestMapping(value = "/import", method = RequestMethod.POST) public APIResult<ImportResultVo> importData( @NeedUser UserDetails user, @RequestBody MultipartFile file) throws IOException { if (null == file) { return APIResult.error(APICode.NOT_FOUND); } //根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页 Workbook hssfWorkbook = EasyPOIUtil.getWorkBook(file); try { int numberOfSheets = hssfWorkbook.getNumberOfSheets(); for (int numSheet = 0; numSheet < numberOfSheets; numSheet++) { Sheet sheetAt = hssfWorkbook.getSheetAt(numSheet); log.info("sheet name is {}",sheetAt.getSheetName()); courseImportDTOList = EasyPOIUtil.importExcel(file, EasyPOIUtil.titleRows, EasyPOIUtil.headerRows, numSheet, CourseImportDTO.class); for (CourseImportDTO courseImportDTO : courseImportDTOList) { ......
} } catch ( Exception e) { log.error(e.getMessage()); e.printStackTrace(); }