| | |
| | | import com.dg.core.service.ITransactionEventService; |
| | | |
| | | import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
| | | import org.apache.poi.ss.usermodel.Row; |
| | | import org.apache.poi.ss.usermodel.Sheet; |
| | | import org.apache.poi.ss.usermodel.Workbook; |
| | | import org.apache.poi.ss.usermodel.*; |
| | | import org.apache.poi.xssf.usermodel.XSSFWorkbook; |
| | | import org.springframework.stereotype.Service; |
| | | import org.springframework.transaction.annotation.Transactional; |
| | |
| | | public int deleteConfigById(String Id) { |
| | | List<GuideRepairOrder> guideRepairOrders = guideRepairOrderMapper |
| | | .selectList(new QueryWrapper<GuideRepairOrder>().lambda().eq(GuideRepairOrder::getMatterId, Id)); |
| | | if (guideRepairOrders.size() == 0){ |
| | | if (guideRepairOrders.size() == 0) { |
| | | return baseMapper.deleteConfigById(Id); |
| | | } |
| | | return 0; |
| | |
| | | |
| | | @Override |
| | | public List<TransactionEvent> selectList(String keyWord) { |
| | | if (keyWord!=null&&keyWord!=""){ |
| | | return baseMapper.selectList(new QueryWrapper<TransactionEvent>().lambda().like(TransactionEvent::getMatterName,keyWord)); |
| | | } |
| | | else { |
| | | return baseMapper.selectList(new QueryWrapper<TransactionEvent>().lambda()); |
| | | if (keyWord != null && keyWord != "") { |
| | | return baseMapper.selectList(new QueryWrapper<TransactionEvent>().lambda().like(TransactionEvent::getMatterName, keyWord)); |
| | | } else { |
| | | return baseMapper.selectList(new QueryWrapper<TransactionEvent>().lambda()); |
| | | } |
| | | } |
| | | |
| | | |
| | | @Override |
| | | public boolean batchImport(String fileName, MultipartFile file) { |
| | | public Integer batchImport(String fileName, MultipartFile file) { |
| | | boolean notNull = false; |
| | | if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { |
| | | throw new RuntimeException("选择文件格式不正确,请下载模板上传"); |
| | | throw new RuntimeException("选择文件格式不正确,请下载模板上传"); |
| | | } |
| | | boolean isExcel2003 = true; |
| | | if (fileName.matches("^.+\\.(?i)(xlsx)$")) { |
| | |
| | | throw new RuntimeException(e); |
| | | } |
| | | } |
| | | Integer failNum = 0;//失败数量 |
| | | // 获取excel的sheet页数 |
| | | int numberOfSheets = wb.getNumberOfSheets(); |
| | | //获取excel字段名称进行比较 |
| | | Sheet sheetAt = wb.getSheetAt(0); |
| | | Row row1 = sheetAt.getRow(2); |
| | | TransactionEvent transactionEvent = baseMapper.selectOne(new QueryWrapper<TransactionEvent>().lambda().eq(TransactionEvent::getMatterName, row1.getCell(1).getStringCellValue())); |
| | | boolean isAdd=false; |
| | | if(transactionEvent==null){ |
| | | transactionEvent=new TransactionEvent(); |
| | | isAdd=true; |
| | | } |
| | | transactionEvent.setMatterName(row1.getCell(1).getStringCellValue()); |
| | | Row row2 = sheetAt.getRow(3); |
| | | transactionEvent.setSetGist("<p>"+row2.getCell(1).getStringCellValue()+"</p>"); |
| | | Row row3 = sheetAt.getRow(4); |
| | | Row row4 = sheetAt.getRow(5); |
| | | transactionEvent.setBasicInformation("<p> 事项名称:"+row1.getCell(1).getStringCellValue()+"<br />" |
| | | +row3.getCell(0).getStringCellValue()+":"+row3.getCell(1).getStringCellValue()+"<br />" |
| | | +row3.getCell(3).getStringCellValue()+":"+row3.getCell(4).getStringCellValue()+"<br />" |
| | | +row4.getCell(0).getStringCellValue()+":"+row4.getCell(1).getStringCellValue()+"</p>"); |
| | | int i=7; |
| | | String applicationMaterial="<p>"; |
| | | while (true){ |
| | | Row row = sheetAt.getRow(i); |
| | | if (row.getCell(0).getStringCellValue().equals("办理途径、条件和注意事项")){ |
| | | i=i+2; |
| | | break; |
| | | for (int j = 0; j < numberOfSheets; j++) { |
| | | //获取excel字段名称进行比较 |
| | | Sheet sheetAt = wb.getSheetAt(j); |
| | | if(sheetAt.getRow(2)!=null){ |
| | | Row row1 = sheetAt.getRow(2); |
| | | TransactionEvent transactionEvent = baseMapper.selectOne(new QueryWrapper<TransactionEvent>().lambda().eq(TransactionEvent::getMatterName, new DataFormatter().formatCellValue(row1.getCell(1)))); |
| | | boolean isAdd = false; |
| | | if (transactionEvent == null) { |
| | | transactionEvent = new TransactionEvent(); |
| | | isAdd = true; |
| | | } |
| | | applicationMaterial=applicationMaterial+"办理区域:"+row.getCell(0).getStringCellValue()+"<br />" |
| | | +"咨询电话:"+row.getCell(1).getStringCellValue()+"<br />" |
| | | +"办公地址:"+row.getCell(2).getStringCellValue()+"<br />" |
| | | +"办公时间:"+row.getCell(3).getStringCellValue()+"<br />"; |
| | | applicationMaterial=applicationMaterial+"<br />"; |
| | | i++; |
| | | } |
| | | applicationMaterial=applicationMaterial+"</p>"; |
| | | transactionEvent.setApplicationMaterial(applicationMaterial); |
| | | String acceptConditions="<p>"; |
| | | while (true){ |
| | | Row row = sheetAt.getRow(i); |
| | | if (row.getCell(0).getStringCellValue().equals("(二)网上申报")){ |
| | | i=i+1; |
| | | break; |
| | | transactionEvent.setMatterName(new DataFormatter().formatCellValue(row1.getCell(1))); |
| | | Row row2 = sheetAt.getRow(3); |
| | | transactionEvent.setSetGist("<p>" + new DataFormatter().formatCellValue(row2.getCell(1)) + "</p>"); |
| | | Row row3 = sheetAt.getRow(4); |
| | | Row row4 = sheetAt.getRow(5); |
| | | transactionEvent.setBasicInformation("<p> 事项名称:" + new DataFormatter().formatCellValue(row1.getCell(1)) + "<br />" |
| | | + new DataFormatter().formatCellValue(row3.getCell(0)) + ":" + new DataFormatter().formatCellValue(row3.getCell(1))+ "<br />" |
| | | + new DataFormatter().formatCellValue(row3.getCell(3)) + ":" + new DataFormatter().formatCellValue(row3.getCell(4)) + "<br />" |
| | | + new DataFormatter().formatCellValue(row4.getCell(0)) + ":" + new DataFormatter().formatCellValue(row4.getCell(1)) + "</p>"); |
| | | int i = 7; |
| | | String applicationMaterial = "<p>"; |
| | | while (true) { |
| | | Row row = sheetAt.getRow(i); |
| | | if (row.getCell(0).getStringCellValue().equals("办理途径、条件和注意事项")) { |
| | | i = i + 2; |
| | | break; |
| | | } |
| | | applicationMaterial = applicationMaterial + "办理区域:" + new DataFormatter().formatCellValue(row.getCell(0)) + "<br />" |
| | | + "咨询电话:" + new DataFormatter().formatCellValue(row.getCell(1)) + "<br />" |
| | | + "办公地址:" + new DataFormatter().formatCellValue(row.getCell(2)) + "<br />" |
| | | + "办公时间:" + new DataFormatter().formatCellValue(row.getCell(3)) + "<br />"; |
| | | applicationMaterial = applicationMaterial + "<br />"; |
| | | i++; |
| | | } |
| | | acceptConditions=acceptConditions+row.getCell(0).getStringCellValue()+" " |
| | | +row.getCell(1).getStringCellValue()+" " |
| | | +row.getCell(2).getStringCellValue()+" "; |
| | | acceptConditions=acceptConditions+"<br />"; |
| | | i++; |
| | | } |
| | | acceptConditions=acceptConditions+"</p>"; |
| | | transactionEvent.setAcceptConditions(acceptConditions); |
| | | String rates="<p>"; |
| | | while (true){ |
| | | Row row = sheetAt.getRow(i); |
| | | if (row.getCell(0).getStringCellValue().equals("(三)手机移动申报")){ |
| | | i=i+1; |
| | | break; |
| | | applicationMaterial = applicationMaterial + "</p>"; |
| | | transactionEvent.setApplicationMaterial(applicationMaterial); |
| | | String acceptConditions = "<p>"; |
| | | while (true) { |
| | | Row row = sheetAt.getRow(i); |
| | | if (row.getCell(0).getStringCellValue().equals("(二)网上申报")) { |
| | | i = i + 1; |
| | | break; |
| | | } |
| | | acceptConditions = acceptConditions + new DataFormatter().formatCellValue(row.getCell(0)) + " " |
| | | +new DataFormatter().formatCellValue( row.getCell(1)) + " " |
| | | + new DataFormatter().formatCellValue(row.getCell(2)) + " "; |
| | | acceptConditions = acceptConditions + "<br />"; |
| | | i++; |
| | | } |
| | | rates=rates+row.getCell(0).getStringCellValue()+" " |
| | | +row.getCell(1).getStringCellValue()+" " |
| | | +row.getCell(2).getStringCellValue()+" "; |
| | | rates=rates+"<br />"; |
| | | i++; |
| | | } |
| | | rates=rates+"</p>"; |
| | | transactionEvent.setRates(rates); |
| | | String transactionArea="<p>"; |
| | | while (true){ |
| | | Row row = sheetAt.getRow(i); |
| | | if (row.getCell(0).getStringCellValue().equals("(四)经营许可(备案)事项程序运行图谱(附电子版)")){ |
| | | i=i+1; |
| | | break; |
| | | acceptConditions = acceptConditions + "</p>"; |
| | | transactionEvent.setAcceptConditions(acceptConditions); |
| | | String rates = "<p>"; |
| | | while (true) { |
| | | Row row = sheetAt.getRow(i); |
| | | if (row.getCell(0).getStringCellValue().equals("(三)手机移动申报")) { |
| | | i = i + 1; |
| | | break; |
| | | } |
| | | rates = rates + new DataFormatter().formatCellValue(row.getCell(0)) + " " |
| | | + new DataFormatter().formatCellValue(row.getCell(1)) + " " |
| | | + new DataFormatter().formatCellValue(row.getCell(2)) + " "; |
| | | rates = rates + "<br />"; |
| | | i++; |
| | | } |
| | | transactionArea=transactionArea+row.getCell(0).getStringCellValue()+" " |
| | | +row.getCell(1).getStringCellValue()+" " |
| | | +row.getCell(2).getStringCellValue()+" "; |
| | | transactionArea=transactionArea+"<br />"; |
| | | i++; |
| | | } |
| | | transactionArea=transactionArea+"</p>"; |
| | | transactionEvent.setTransactionArea(transactionArea); |
| | | String handlingProcedures="<p>"; |
| | | while (true){ |
| | | Row row = sheetAt.getRow(i); |
| | | if (row.getCell(0).getStringCellValue().equals("(五)经营许可(备案)事项实施内容一览表(附电子版)")){ |
| | | i=i+1; |
| | | break; |
| | | rates = rates + "</p>"; |
| | | transactionEvent.setRates(rates); |
| | | String transactionArea = "<p>"; |
| | | while (true) { |
| | | Row row = sheetAt.getRow(i); |
| | | if (row.getCell(0).getStringCellValue().equals("(四)经营许可(备案)事项程序运行图谱(附电子版)")) { |
| | | i = i + 1; |
| | | break; |
| | | } |
| | | transactionArea = transactionArea + new DataFormatter().formatCellValue(row.getCell(0)) + " " |
| | | + new DataFormatter().formatCellValue(row.getCell(1)) + " " |
| | | + new DataFormatter().formatCellValue(row.getCell(2)) + " "; |
| | | transactionArea = transactionArea + "<br />"; |
| | | i++; |
| | | } |
| | | handlingProcedures=handlingProcedures+row.getCell(0).getStringCellValue()+" " |
| | | +row.getCell(1).getStringCellValue()+" " |
| | | +row.getCell(2).getStringCellValue()+" "; |
| | | handlingProcedures=handlingProcedures+"<br />"; |
| | | i++; |
| | | transactionArea = transactionArea + "</p>"; |
| | | transactionEvent.setTransactionArea(transactionArea); |
| | | String handlingProcedures = "<p>"; |
| | | while (true) { |
| | | Row row = sheetAt.getRow(i); |
| | | if (row.getCell(0).getStringCellValue().equals("(五)经营许可(备案)事项实施内容一览表(附电子版)")) { |
| | | i = i + 1; |
| | | break; |
| | | } |
| | | handlingProcedures = handlingProcedures + new DataFormatter().formatCellValue(row.getCell(0)) + " " |
| | | + new DataFormatter().formatCellValue(row.getCell(1)) + " " |
| | | + new DataFormatter().formatCellValue(row.getCell(2)) + " "; |
| | | handlingProcedures = handlingProcedures + "<br />"; |
| | | i++; |
| | | } |
| | | handlingProcedures = handlingProcedures + "</p>"; |
| | | transactionEvent.setHandlingProcedures(handlingProcedures); |
| | | int ans; |
| | | if (isAdd) { |
| | | ans = baseMapper.insert(transactionEvent); |
| | | } else { |
| | | ans = baseMapper.updateById(transactionEvent); |
| | | } |
| | | if (ans <= 0) { |
| | | failNum++; |
| | | } |
| | | } |
| | | |
| | | } |
| | | handlingProcedures=handlingProcedures+"</p>"; |
| | | transactionEvent.setHandlingProcedures(handlingProcedures); |
| | | int ans; |
| | | if (isAdd){ |
| | | ans= baseMapper.insert(transactionEvent); |
| | | }else { |
| | | ans =baseMapper.updateById(transactionEvent); |
| | | if (failNum == 0) {// |
| | | return 3;//全部导入成功 |
| | | } else if (failNum < numberOfSheets) { |
| | | return 2;//部分导入成功 |
| | | } else if (failNum.equals(numberOfSheets)) { |
| | | return 0;//导入失败 |
| | | } |
| | | if (ans>0){ |
| | | return true; |
| | | } |
| | | return false; |
| | | return 0; |
| | | } |
| | | |
| | | |
| | | |
| | | |
| | | } |