1、easyexcel[导出]
1、需引入依赖<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.6</version></dependency>2、Demo：public class ExcelUtils {/*** 导出文件* @param response 文件写入流* @param fileName 文件名* @param sheetName 分栏名* @param excelClass excel映射类* @param excelData 导出数据* @throws IOException*/public static void exportExcelData(HttpServletResponse response, String fileName, String sheetName, Class excelClass, List excelData) throws IOException {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");String name = URLEncoder.encode(fileName, "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");WriteCellStyle headWriteCellStyle = new WriteCellStyle();headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());WriteFont headWriteFont = new WriteFont();headWriteFont.setFontName("宋体");headWriteFont.setFontHeightInPoints((short) 14);headWriteFont.setBold(true);headWriteCellStyle.setWriteFont(headWriteFont);WriteCellStyle writeCellStyle = new WriteCellStyle();writeCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, Arrays.asList(writeCellStyle));EasyExcel.write(response.getOutputStream(), excelClass).autoCloseStream(Boolean.FALSE).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(excelData);}}
2、apache-poi[导入]
public RealtimeResultModel readExcel(MultipartFile multipartFile, Long projectId, String labelSource, String userId, Long tenantId, Long sceneId){//工作簿Workbook workbook = null;InputStream inputstream = null;//获取当前方法的方法名称String methodName = Thread.currentThread().getStackTrace()[1].getMethodName();//返回客户端文件系统中的原始文件名,可能包含路径信息String fileName = multipartFile.getOriginalFilename();try{// 获取excel后缀名inputstream = multipartFile.getInputStream();//Excel 2007, 最大支持1048576行if (fileName.endsWith("xlsx")){workbook = new XSSFWorkbook(inputstream);}//Excel 2003, 最大支持65536行else if (fileName.endsWith("xls")){workbook = new HSSFWorkbook(inputstream);}if(Objects.isNull(workbook)){LoggerUtil.warn(LOGGER, "readExcel()中, 构建workbook对象为空, 文件名：{0}", fileName);return new RealtimeResultModel < Long > (false, 0 L, "构建workbook对象为空");}Map < String, Object > resultMap = new HashMap < > ();// 解析sheet[存在多个sheet时]int numberOfSheets = workbook.getNumberOfSheets();LoggerUtil.warn(LOGGER, "readexcel()中, 上传的excel文件中, sheet数目={0}, 文件名：{1}", numberOfSheets, fileName);for(int sheetnum = 0; sheetnum < numberOfSheets; sheetnum++){//得到一个工作表Sheet sheet = workbook.getSheetAt(sheetnum);// 校验sheet是否合法if(sheet == null){continue;}//获得表头Row rowHead = sheet.getRow(sheetnum);//判断表头是否正确【getPhysicalNumberOfCells():获取有记录的列数，比如：最后有数据的列是第n列，前面有m列是空列没数据，则返回n-m；】if(4 != rowHead.getPhysicalNumberOfCells()){LoggerUtil.warn(LOGGER, "readexcel()中, 第{0}sheet, 的表头数量不为4, 文件名：{1}", sheetnum, fileName);continue;}//获得数据的总行数【如果sheet中一行数据都没有则返回-1，只有第一行有数据则返回0，最后有数据的行是第n行则返回 n-1】int totalRowNum = sheet.getLastRowNum() + 1;if(totalRowNum > 2000){LoggerUtil.warn(LOGGER, "excel文件中的数据不允许超过两千条, 超出数目：{0}, 文件名：{1}", totalRowNum - 2000, fileName);}//获得第一行的数据的总行数【第一行0开始】int firstRowNum = sheet.getFirstRowNum() + 1;//获取有记录的行数【最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m】int rowEnd = sheet.getPhysicalNumberOfRows();//要获得属性String platform = "";String projectName = "";String jobName = "";String customName = "";//满足校验条件的任务名称集合List < DqDatalinkRealtimeJobDO > withJobNameList = new ArrayList < > ();//不满足校验条件的任务名称集合List < String > noWithJobNameList = new ArrayList < > ();//总共insert or update 表【dq_datalink_realtime_job】的总数目int totalOperateCount = 0;//遍历获取每一行的数据for(int rowNum = firstRowNum; rowNum < rowEnd; rowNum++){//获得第i行对象Row row = sheet.getRow(rowNum);if(Objects.isNull(row)){LoggerUtil.warn(LOGGER, "readexcel()中, 第{0}行数据为空, 文件名：{1}", rowNum, fileName);continue;}//获得获得第i行第0列的 String类型对象【任务归属平台】Cell cell = row.getCell((short) 0);platform = cell.getStringCellValue();//获得获得第i行第0列的 String类型对象【任务所属项目名】cell = row.getCell((short) 1);projectName = cell.getStringCellValue();//获得获得第i行第2列的 String类型对象【任务名称】cell = row.getCell((short) 2);jobName = cell.getStringCellValue();//获得获得第i行第3列的 String类型对象【关联标签】cell = row.getCell((short) 3);customName = cell.getStringCellValue();JobDTO jobInfo = new JobDTO();String subjectUrl = "";Long customId = 0 L;boolean validateResult = validateCellData(platform, projectName, jobName, customName, projectId, labelSource, customId, jobInfo, subjectUrl);if(validateResult){//构建表[dq_datalink_realtime_job]操作实体对象DqDatalinkRealtimeJobDO dqDatalinkRealtimeJobDO = buildInsertRealtimeInfo(jobInfo, subjectUrl, userId, jobName, projectName, platform, methodName, tenantId, projectId, sceneId, customId);withJobNameList.add(dqDatalinkRealtimeJobDO);totalOperateCount += doRealtimeTaskTableDDL(dqDatalinkRealtimeJobDO, projectName, sceneId, jobName);}else{noWithJobNameList.add(jobName);}}LoggerUtil.info(LOGGER, "上传文件中：{0}, 新增或更新数据的总数目：{1}", fileName, totalOperateCount);Set < String > collectSet = withJobNameList.stream().map(DqDatalinkRealtimeJobDO::getDpJobName).collect(Collectors.toSet());resultMap.put("检验通过的任务名称", StringUtils.join(collectSet, ","));resultMap.put("检验不通过的任务名称", StringUtils.join(noWithJobNameList, ","));}return new RealtimeResultModel < Map < String, Object >> (true, resultMap, "构建workbook对象成功");}catch(Exception e){LoggerUtil.warn(LOGGER, "解析excel失败，文件名：{0}, 错误信息：{1}", fileName, e.getMessage());return new RealtimeResultModel < Long > (false, 0 L, "上传文件处理失败");}finally{try{if(null != workbook){workbook.close();}if(null != inputstream){inputstream.close();}}catch(Exception e){LoggerUtil.warn(LOGGER, "关闭数据流出错！错误信息：{0}", e.getMessage());}}
3、详细资料
1、支持.xlsx/.xls批量导入 1.1、poi、 https://blog.csdn.net/m0_61601521/article/details/123215591 https://www.jb51.net/article/119071.htm http://www.jszja.com/contents/14/2707.html http://www.zzvips.com/article/177224.html http://www.360doc.com/content/21/0527/16/75508165_979253054.shtml
1.2、easyexcel、[https://www.csdn.net/tags/MtjaggxsMjQwNzgtYmxvZwO0O0OO0O0O.html](https://www.csdn.net/tags/MtjaggxsMjQwNzgtYmxvZwO0O0OO0O0O.html)[https://wenku.baidu.com/view/d2768cebd938376baf1ffc4ffe4733687e21fc6f.html](https://wenku.baidu.com/view/d2768cebd938376baf1ffc4ffe4733687e21fc6f.html)[https://blog.csdn.net/gongzi_9/article/details/123104402](https://blog.csdn.net/gongzi_9/article/details/123104402)
若有收获，就点个赞吧
0 人点赞
