可以免费商用国外印花图案设计网站,软件开发方法有哪些,推荐几个做网页设计的网站,聊城专业建wap网站jie
这是我在工作中遇到的业务
用户导入压缩包#xff0c;压缩包里面有多个excel,每个excel有多个sheet,我需要解压#xff0c;合并所有excel然后读取每一个sheet#xff0c;最终写入数据库
PostMapping(/importExcelForPVT)ApiOperationSupport(order 9)Ap…jie
这是我在工作中遇到的业务
用户导入压缩包压缩包里面有多个excel,每个excel有多个sheet,我需要解压合并所有excel然后读取每一个sheet最终写入数据库
PostMapping(/importExcelForPVT)ApiOperationSupport(order 9)ApiOperation(value 导入excel数据, notes 传入excel文件)public R importExcelForPVT(RequestParam(file) MultipartFile file,RequestParam(batchName) String batchName) throws IOException {ListPvtTotalBatchEntity list pvtTotalBatchService.list(new QueryWrapperPvtTotalBatchEntity().lambda().eq(PvtTotalBatchEntity::getName, batchName));if (list.size() 0) {return R.fail(批次名已存在);}if (file.isEmpty()) {//判断文件是否为空return R.fail(上传数据非法);}String fileName file.getOriginalFilename(); //获得文件名if(!fileName.endsWith(zip)){return R.fail(fileName 不是zip压缩文件);}//这行代码构造了目标文件路径将文件保存在名为import_pvt的目录下文件名保持不变。Path dest Paths.get(files, fileName);//保存文件在本地try {if(!dest.toFile().exists()){Files.createDirectories(dest);}Files.copy(file.getInputStream(), dest, StandardCopyOption.REPLACE_EXISTING);} catch (IOException e) {log.error(上传文件异常,e.getMessage());return R.fail(fileName :保存文件异常);}String uuid UUID.randomUUID().toString();// String batchName FileNameUtil.getBaseName(fileName);Path dir Paths.get(files, uuid);UnzipUtils.unzipFile(dest.toFile().getAbsolutePath(), dir.toFile().getAbsolutePath());//现在五个excel文件都在dir目录下接下来要做的是将这五个文件合成一个excelsheet名不变File mergeExcelFile MergeExcelUtil.mergeExcelFiles(dir);
//将合并后的excel文件导入数据库ETLExecutionThreadLocal.setStartTime(System.currentTimeMillis());String s odsPvtService.importAllSheet(mergeExcelFile,batchName);dwPvtServiceImpl.ODdsToDw();if (s.equals(success)) {return R.success(导入成功);} else {throw new RuntimeException(s);}} public class UnzipUtils {/*** 解压zip压缩文件到指定目录** param zipPath zip压缩文件绝对路径* param descDir 指定的解压目录*/public static void unzipFile(String zipPath, String descDir) throws IOException {try {File zipFile new File(zipPath);if (!zipFile.exists()) {throw new IOException(要解压的压缩文件不存在);}File pathFile new File(descDir);if (!pathFile.exists()) {pathFile.mkdirs();}InputStream input new FileInputStream(zipPath);unzipWithStream(input, descDir);} catch (Exception e) {throw new IOException(e);}}/*** 解压** param inputStream* param descDir*/public static void unzipWithStream(InputStream inputStream, String descDir) {if (!descDir.endsWith(File.separator)) {descDir descDir File.separator;}try (ZipInputStream zipInputStream new ZipInputStream(inputStream, Charset.forName(GBK))) {ZipEntry zipEntry;while ((zipEntry zipInputStream.getNextEntry()) ! null) {String zipEntryNameStr zipEntry.getName();String zipEntryName zipEntryNameStr;if (zipEntryNameStr.contains(/)) {String str1 zipEntryNameStr.substring(0, zipEntryNameStr.indexOf(/));zipEntryName zipEntryNameStr.substring(str1.length() 1);}String outPath (descDir zipEntryName).replace(\\\\, /);File outFile new File(outPath.substring(0, outPath.lastIndexOf(\\)));if (!outFile.exists()) {outFile.mkdirs();}if (new File(outPath).isDirectory()) {continue;}writeFile(outPath, zipInputStream);zipInputStream.closeEntry();}System.out.println(解压成功);} catch (IOException e) {System.out.println(压缩包处理异常异常信息{} e);}}//将流写到文件中public static void writeFile(String filePath, ZipInputStream zipInputStream) {try (OutputStream outputStream new FileOutputStream(filePath)) {byte[] bytes new byte[4096];int len;while ((len zipInputStream.read(bytes)) ! -1) {outputStream.write(bytes, 0, len);}} catch (IOException ex) {System.out.println(解压文件时写出到文件出错);}}//测试方法public static void main(String[] args) throws IOException {String zipPath D:/test/测试文件.zip;String descDir D:/test/解压/;unzipFile(zipPath, descDir);}
}public class MergeExcelUtil {public static File mergeExcelFiles(Path dir) {Workbook mergedWorkbook new XSSFWorkbook();File mergedExcelFile null;try {// Get all files in the directoryFile[] files dir.toFile().listFiles();if (files ! null) {for (File file : files) {if (file.getName().endsWith(.xlsx)) {FileInputStream fileInputStream new FileInputStream(file);Workbook workbook WorkbookFactory.create(fileInputStream);for (int i 0; i workbook.getNumberOfSheets(); i) {Sheet sheet workbook.getSheetAt(i);Sheet newSheet mergedWorkbook.createSheet(sheet.getSheetName());// Copy content from original sheet to the merged workbookIteratorRow rowIterator sheet.rowIterator();while (rowIterator.hasNext()) {Row row rowIterator.next();Row newRow newSheet.createRow(row.getRowNum());IteratorCell cellIterator row.cellIterator();while (cellIterator.hasNext()) {Cell cell cellIterator.next();Cell newCell newRow.createCell(cell.getColumnIndex());String cellValue ;switch (cell.getCellTypeEnum()) {case STRING:cellValue cell.getStringCellValue();break;case BOOLEAN:cellValue String.valueOf(cell.getBooleanCellValue());break;case NUMERIC:if (DateUtil.isCellDateFormatted(cell)) {Date date cell.getDateCellValue();// 操作日期值的代码cellValue date.toString();}double excelDateValue cell.getNumericCellValue();if (excelDateValue % 1 0) {//判断是否有小数点后面带0// 如果小数部分为 0则转换为整数形式cellValue String.valueOf((int) excelDateValue);} else {// 如果有非零小数部分则保留小数部分cellValue String.valueOf(excelDateValue);}
// cellValue String.valueOf(excelDateValue);break;case ERROR:cellValue String.valueOf(cell.getErrorCellValue());break;case FORMULA:cellValue cell.getCellFormula(); // Handle formula cellbreak;case BLANK:cellValue ; // Handle blank cellbreak;default:cellValue Not supported data type;}// 设置新单元格的值newCell.setCellValue(cellValue);}}}fileInputStream.close();}}Path mergedExcelFilePath dir.resolve(mergedExcel.xlsx);mergedExcelFile new File(mergedExcelFilePath.toString());FileOutputStream fileOut new FileOutputStream(mergedExcelFile);mergedWorkbook.write(fileOut);fileOut.close();}} catch (IOException e) {e.printStackTrace();} catch (InvalidFormatException e) {throw new RuntimeException(e);}return mergedExcelFile;}}EtlLogTransactionalOverridepublic String importAllSheet(File file, String batchName) {// try (InputStream inputStream file.getInputStream()) {// 创建临时文件File tempFile file;// 读取Excel数据ListLunchDataMultiSheetListener listenerList readExcelData(tempFile);//新建 pvt的总批次号PvtTotalBatchEntity pvtTotalBatchEntity new PvtTotalBatchEntity();pvtTotalBatchEntity.setName(batchName);pvtTotalBatchService.save(pvtTotalBatchEntity);// 处理Excel数据MapString, ListObject stringListMap handleExcelData(listenerList,pvtTotalBatchEntity.getId());for (String s : stringListMap.keySet()) {ListObject objects stringListMap.get(s);switch (s) {case 特质焦虑问卷:ListOdsPvtSasEntity entities (ListOdsPvtSasEntity) (List?) objects;odsPvtSasService.saveBatch(entities);break;case 压力知觉量表:ListOdsPvtPssEntity pssEntities (ListOdsPvtPssEntity) (List?) objects;odsPvtPssService.saveBatch(pssEntities);break;case 生活满意度量表:ListOdsPvtSwlsEntity shsEntities (ListOdsPvtSwlsEntity) (List?) objects;odsPvtSwlsService.saveBatch(shsEntities);break;case 主观幸福感量表:ListOdsPvtShsEntity sdsEntities (ListOdsPvtShsEntity) (List?) objects;odsPvtShsService.saveBatch(sdsEntities);break;case 抑郁自评量表:ListOdsPvtSdsEntity mlqEntities (ListOdsPvtSdsEntity) (List?) objects;odsPvtSdsService.saveBatch(mlqEntities);break;case 生命意义感量表:ListOdsPvtMlqEntity mlqEntities1 (ListOdsPvtMlqEntity) (List?) objects;odsPvtMlqService.saveBatch(mlqEntities1);break;case 自尊量表:ListOdsPvtSesEntity sesEntities (ListOdsPvtSesEntity) (List?) objects;odsPvtSesService.saveBatch(sesEntities);break;case 人格特质维度问卷:ListOdsPvtDyEntity dyEntities (ListOdsPvtDyEntity) (List?) objects;odsPvtDyService.saveBatch(dyEntities);break;case 大五人格问卷:ListOdsPvtNeoFfiEntity neoFfiEntity (ListOdsPvtNeoFfiEntity) (List?) objects;odsPvtNeoFfiService.saveBatch(neoFfiEntity);break;case TKI冲突模式量表:ListOdsPvtTkiEntity tkiFfiEntity (ListOdsPvtTkiEntity) (List?) objects;odsPvtTkiService.saveBatch(tkiFfiEntity);break;case 人际关系量表:ListOdsPvtNriEntity nriEntity (ListOdsPvtNriEntity) (List?) objects;odsPvtNriService.saveBatch(nriEntity);break;case 亲社会行为倾向量表:ListOdsPvtPtmEntity ptmEntity (ListOdsPvtPtmEntity) (List?) objects;odsPvtPtmService.saveBatch(ptmEntity);break;case Buss-Warren攻击问卷:ListOdsPvtBwaqEntity waqEntity (ListOdsPvtBwaqEntity) (List?) objects;odsPvtBwaqService.saveBatch(waqEntity);break;case 加工速度测评:ListOdsPvtSgEntity waqEntity1 (ListOdsPvtSgEntity) (List?) objects;odsPvtSgService.saveBatch(waqEntity1);break;case 执行功能测评:ListOdsPvtTsEntity tsEntity (ListOdsPvtTsEntity) (List?) objects;odsPvtTsService.saveBatch(tsEntity);break;case 情景记忆测评:ListOdsPvtEmEntity waqEntity2 (ListOdsPvtEmEntity) (List?) objects;odsPvtEmService.saveBatch(waqEntity2);break;case 工作记忆测评:ListOdsPvtWmEntity waqEntity3 (ListOdsPvtWmEntity) (List?) objects;odsPvtWmService.saveBatch(waqEntity3);break;case 注意力测评:ListOdsPvtSzhxEntity waqEntity4 (ListOdsPvtSzhxEntity) (List?) objects;odsPvtSzhxService.saveBatch(waqEntity4);break;case 逻辑推理测评:ListOdsPvtRpmsEntity waqEntity5 (ListOdsPvtRpmsEntity) (List?) objects;odsPvtRpmsService.saveBatch(waqEntity5);break;case 视空间测评:ListOdsPvtMrEntity waqEntity6 (ListOdsPvtMrEntity) (List?) objects;odsPvtMrService.saveBatch(waqEntity6);break;case 特质应对方式问卷:ListOdsPvtTcsqEntity waqEntity7 (ListOdsPvtTcsqEntity) (List?) objects;odsPvtTcsqService.saveBatch(waqEntity7);break;case 情绪调节量表:ListOdsPvtErqEntity waqEntity8 (ListOdsPvtErqEntity) (List?) objects;odsPvtErqService.saveBatch(waqEntity8);break;case 心理弹性量表:ListOdsPvtCdRiscEntity waqEntity9 (ListOdsPvtCdRiscEntity) (List?) objects;odsPvtCdRiscService.saveBatch(waqEntity9);break;case 自我效能感量表:ListOdsPvtGsesEntity waqEntity10 (ListOdsPvtGsesEntity) (List?) objects;odsPvtGsesService.saveBatch(waqEntity10);break;case 基本信息调查表:ListOdsPvtBaseInfoEntity odsPvtBaseInfoEntities (ListOdsPvtBaseInfoEntity) (List?) objects;odsPvtBaseInfoEntities.forEach(entity -{if (entity.getTestTime().contains(.)){// 将日期值转换为 Date 类型Date date DateUtil.getJavaDate(Double.parseDouble(entity.getTestTime()));
// 创建 SimpleDateFormat 实例指定日期时间格式SimpleDateFormat sdf new SimpleDateFormat(yyyy:MM:dd HH:mm:ss);
// 将日期值格式化为指定格式的字符String formattedDateTime sdf.format(date);entity.setTestTime(formattedDateTime);}});odsPvtBaseInfoService.saveBatch(odsPvtBaseInfoEntities);break;default:// 可选如果存在无法识别的实体类名称可以选择抛出异常或进行适当处理break;}}return success;
// } catch (IOException e) {
// e.printStackTrace();
// return e.getMessage();
// }}/*** 读取Excel数据*/private ListLunchDataMultiSheetListener readExcelData(File file) {ExcelReader excelReader EasyExcel.read(file).build();ListReadSheet sheets excelReader.excelExecutor().sheetList();ListLunchDataMultiSheetListener listenerList sheets.stream().map(sheet - {LunchDataMultiSheetListener listener new LunchDataMultiSheetListener();ReadSheet readSheet EasyExcel.readSheet(sheet.getSheetNo()).registerReadListener(listener).build();excelReader.read(readSheet);ListListString dataList listener.getDataList();return listener;}).collect(Collectors.toList());excelReader.finish();return listenerList;}/*** 创建临时文件*/private File createTempFile(MultipartFile file) throws IOException {String originalFilename file.getOriginalFilename();String[] filename originalFilename.split(\\.);File tempFile File.createTempFile(filename[0], . filename[1] .);file.transferTo(tempFile);tempFile.deleteOnExit();return tempFile;}//业务层private MapString, ListObject handleExcelData(ListLunchDataMultiSheetListener listenerList, Long batchId) {DateTimeFormatter formatter DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss);MapString, ListObject entityMap new HashMap();listenerList.forEach(listener - listener.getDataList().forEach(lineData - {System.out.println(lineData lineData);// 其它业务处理。。String type lineData.get(2);Class entityClassBySheetName getEntityClassBySheetName(type);if (entityClassBySheetName null) {return;}// 使用反射创建新的对象try {Object entity entityClassBySheetName.newInstance();// 设置属性值Field[] fields entityClassBySheetName.getDeclaredFields();for (int i 0; i lineData.size(); i) {if (i lineData.size() - 1) {try {Field field fields[i];field.setAccessible(true);String dateTimeString lineData.get(i);LocalDateTime localDateTime LocalDateTime.parse(dateTimeString, formatter);field.set(entity, localDateTime);continue;} catch (Exception e) {//最后一个字段类型可能是String类型如果出问题那就直接填字符串Field field fields[i];field.setAccessible(true);field.set(entity, lineData.get(i));continue;}}Field field fields[i];field.setAccessible(true);field.set(entity, lineData.get(i));}Field importTimeField fields[lineData.size()];importTimeField.setAccessible(true);importTimeField.set(entity, LocalDateTime.now());// 找到名为 totalBatch 的属性然后统一设置属性值Field totalBatchField entityClassBySheetName.getDeclaredField(totalBatch);totalBatchField.setAccessible(true);totalBatchField.set(entity, batchId);// 将对象添加到对应的列表中ListObject entityList entityMap.getOrDefault(type, new ArrayList());entityList.add(entity);entityMap.put(type, entityList);} catch (InstantiationException | IllegalAccessException e) {e.printStackTrace();// 处理异常} catch (NoSuchFieldException e) {throw new RuntimeException(e);}}));return entityMap;}// 根据 sheetName 返回对应的实体类型private Class getEntityClassBySheetName(String sheetName) {// 在此根据 sheetName 返回对应的实体类型例如if (sheetName.equals(特质焦虑问卷)) {return OdsPvtSasEntity.class;} else if (sheetName.equals(压力知觉量表)) {return OdsPvtPssEntity.class;} else if (sheetName.equals(生活满意度量表)) {return OdsPvtSwlsEntity.class;} else if (sheetName.equals(主观幸福感量表)) {return OdsPvtShsEntity.class;} else if (sheetName.equals(抑郁自评量表)) {return OdsPvtSdsEntity.class;} else if (sheetName.equals(生命意义感量表)) {return OdsPvtMlqEntity.class;} else if (sheetName.equals(自尊量表)) {return OdsPvtSesEntity.class;} else if (sheetName.equals(人格特质维度问卷)) {return OdsPvtDyEntity.class;} else if (sheetName.equals(大五人格问卷)) {return OdsPvtNeoFfiEntity.class;} else if (sheetName.equals(TKI冲突模式量表)) {return OdsPvtTkiEntity.class;} else if (sheetName.equals(人际关系量表)) {return OdsPvtNriEntity.class;} else if (sheetName.equals(亲社会行为倾向量表)) {return OdsPvtPtmEntity.class;} else if (sheetName.equals(Buss-Warren攻击问卷)) {return OdsPvtBwaqEntity.class;} else if (sheetName.equals(加工速度测评)) {return OdsPvtSgEntity.class;} else if (sheetName.equals(执行功能测评)) {return OdsPvtTsEntity.class;} else if (sheetName.equals(情景记忆测评)) {return OdsPvtEmEntity.class;} else if (sheetName.equals(工作记忆测评)) {return OdsPvtWmEntity.class;} else if (sheetName.equals(注意力测评)) {return OdsPvtSzhxEntity.class;} else if (sheetName.equals(逻辑推理测评)) {return OdsPvtRpmsEntity.class;} else if (sheetName.equals(视空间测评)) {return OdsPvtMrEntity.class;} else if (sheetName.equals(特质应对方式问卷)) {return OdsPvtTcsqEntity.class;} else if (sheetName.equals(情绪调节量表)) {return OdsPvtErqEntity.class;} else if (sheetName.equals(心理弹性量表)) {return OdsPvtCdRiscEntity.class;} else if (sheetName.equals(自我效能感量表)) {return OdsPvtGsesEntity.class;} else if (sheetName.equals(基本信息调查表)){return OdsPvtBaseInfoEntity.class;}return null;//返回空为了后续做准备}
}
在读取sheet的时候你需要弄一个表格监听器
package org.springblade.common.listener;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;Slf4j
Data
public class LunchDataMultiSheetListener extends AnalysisEventListenerMapInteger, String {private ListListString dataList;public LunchDataMultiSheetListener() {this.dataList new ArrayList();}/*** 每读到一行数据都调用invoke方法** param integerObjectMap* param context*/Overridepublic void invoke(MapInteger, String integerObjectMap, AnalysisContext context) {Integer rowIndex context.readRowHolder().getRowIndex();System.out.println(rowIndex rowIndex);// key为列号value为单元格的内容log.info(解析到数据:{}, integerObjectMap);// 获取当前解析的sheet的信息String sheetName context.readSheetHolder().getSheetName();
// Integer sheetIndex context.readSheetHolder().getSheetNo();
// System.out.println(当前解析的sheet名称 sheetName);
// System.out.println(当前解析的sheet索引 sheetIndex);// 把数据放到dataList里面便于统一处理LinkedListString strings new LinkedList();integerObjectMap.forEach((k, v) - {strings.add(v);});this.dataList.add(strings);}Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {// 读完所有数据做统一处理。// 当然还可以拿到listener之外处理log.info(数据读取完成);}}
