EsayPoi单个sheet导入导出表格

//单sheet导出 { //查询出要导出的数据集合 List<T> list = new ArrayList<>(); //导出 表格数据拼写 list.add(导入的对象放到list 里 就好了) ExportParams exportParams = new ExportParams(); exportParams.setSheetName("遥测参数"); // 生成workbook 并导出 Workbook workbook = ExcelExportUtil.exportExcel(exportParams, T.class, list); // 组装excel文件name String fileName = "文件名称.xls"; // 下载excel ExcelUtil.downLoadExcel(fileName, response, workbook); return CheckResult.success(”OK“); } //单sheet导入 导入 { ImportParams importParams = new ImportParams(); // 因为Excel是从第2行开始才有正式数据,所以我们这里从第1行开始读取数据 importParams.setHeadRows(1); // 需要校验 importParams.setNeedVerify(true); // 获取模板 ExcelImportResult<T> data = new ExcelImportResult<>(); try { data = ExcelImportUtil.importExcelMore(file.getInputStream(), T.class, importParams); } catch (Exception e) { log.error(e.getMessage(), e); return CheckResult.fail("文件解析失败,可能原因文件格式不正确"); } // 获取模板数据(通过校验的数据) List<T> successList = data.getList(); if (successList.size() == 0) { // 如果数据量为0直接返回错误信息,不继续执行 log.info(MsgPrefixKit.genMgt("错误信息,不继续执行")); return CheckResult.fail("错误信息,不继续执行,请检查表格信息是否完整"); } // 校验失败的数据集合 List<T> failList = data.getFailList(); if (failList.size() > 0) { // 如果此数据量大于0,直接返回错误信息 log.info(MsgPrefixKit.genMgt("有" + data.getFailList().size() + "条数据不合格,无法导入")); } // 循环数据 List<T> listmsg = new ArrayList<>(successList); listmsg 就是导入的对象集合， 然后遍历进行C R U D } //另外上传文件默认大小1M 需要再 yml文件里配置 spring: servlet: multipart: max-request-size: 10MB max-file-size: 10MB /******************************下面是工具类**************************************************/ /** * @author SunGaoyu * createTime 2020/4/11 */ public class ExcelUtil { public static boolean isExcel2003(String filePath) { return filePath.matches("^.+\\.(?i)(xls)$"); } public static boolean isExcel2007(String filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } /** * 得到Workbook对象 * @param file * @return * @throws IOException */ 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; } /** * 得到错误信息 * @param sb * @param list * @param i * @param obj * @param name 用哪个属性名去表明不和规定的数据 * @param msg * @throws Exception */ public static void getWrongInfo(StringBuilder sb, List list, int i, Object obj, String name, String msg) throws Exception{ Class clazz=obj.getClass(); Object str=null; //得到属性名数组 Field[] fields = clazz.getDeclaredFields(); for(Field f : fields){ if(f.getName().equals(name)){ //用来得到属性的get和set方法 PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz); //得到get方法 Method getMethod=pd.getReadMethod(); str = getMethod.invoke(obj); } } if(i==0) { sb.append(msg + str + ";"); } else if(i==(list.size()-1)) { sb.append(str + "</br>"); } else { sb.append(str + ";"); } } 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); } } /** * 下载 * @param fileName * @param response * @param workbook */ public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) { try { // 告诉浏览器用什么软件可以打开此文件 response.setHeader("content-Type", "application/vnd.ms-excel"); //设置浏览器响应头对应的Content-disposition response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); //编码 response.setCharacterEncoding("UTF-8"); workbook.write(response.getOutputStream()); } catch (IOException e) { log.error(e.getMessage()); }finally { try { workbook.close(); } catch (IOException e) { log.error(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){ log.error(e.getMessage()); } catch (Exception e) { log.error(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){ log.error(e.getMessage()); } catch (Exception e) { log.error(e.getMessage()); } return list; } /** * 导出Excel * * @return org.apache.poi.ss.usermodel.Workbook */ public static Workbook exportExcel(List<Map<String, Object>> list) { Workbook workbook = new HSSFWorkbook(); for (Map<String, Object> map : list) { createSheetWithList(workbook, (ExportParams) map.get("title"), ExportParams.class, (List<ExcelExportEntity>) map.get("entityList"), (Collection<?>) map.get("data")); } return workbook; } public static void createSheetWithList(Workbook workbook, ExportParams entity, Class<?> pojoClass, List<ExcelExportEntity> entityList, Collection<?> dataSet) { if (workbook == null || entity == null || pojoClass == null || dataSet == null) { throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR); } try { List<ExcelExportEntity> excelParams = entityList; // 得到所有字段 Field[] fileds = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); String targetId = etarget == null ? null : etarget.value(); new ExcelExportService().getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass,null, null); //获取所有参数后,后面的逻辑判断就一致了 new ExcelExportService().createSheetForMap(workbook, entity, excelParams, dataSet); } catch (Exception e) { log.error(e.getMessage(), e); } } }