前言:
如今我们对“javaexcel数据库”大致比较看重,同学们都想要了解一些“javaexcel数据库”的相关资讯。那么小编也在网络上网罗了一些对于“javaexcel数据库””的相关文章,希望兄弟们能喜欢,你们快快来学习一下吧!java导出excel并单元格自动合并
最近公司有一个需求,就是按到模版导出数据报表,并内容相同的单元格实现自动合并.具体业务设计图如下所示
需求分析由于头部是固定不变的,先使用excel创建数据模版单元格合并,前三列需要根据内容相同自动合并单元格根据状态来判断,状态异常的红色字体,并且已红色×标识*情况,都是多选一的,数据库只会记录一种状态,后台需要判断并在指定的单元格添加标识符√代码开发创建*情况对应的魔法值对比索引
/** * @ClassName PatrolExportEnum * @Description 方便单元格选择勾选 * @Author xiongchao * @Date 2020/10/20 10:02 **/public class PatrolExportEnum { // 进水情况 private static Map<String,Integer> waterInCondition; // 出水情况 private static Map<String,Integer> waterOutCondition; // 电力情况 private static Map<String,Integer> electryCondition; // 是否与现场一直 private static Map<String,Integer> liveCondition; // 管网情况 private static Map<String,Integer> pipeCondition; // 配水情况 private static Map<String,Integer> waterCondition; static { waterInCondition = new HashMap<>(); waterOutCondition = new HashMap<>(); electryCondition = new HashMap<>(); liveCondition = new HashMap<>(); pipeCondition = new HashMap<>(); waterCondition = new HashMap<>(); waterInCondition.put("无",0); waterInCondition.put("少",1); waterInCondition.put("正常",2); waterInCondition.put("有溢流",3); waterOutCondition.put("正常",0); waterOutCondition.put("无水",1); electryCondition.put("正常",0); electryCondition.put("故障",1); liveCondition.put("一致",0); liveCondition.put("不一致",1); pipeCondition.put("管道",0); pipeCondition.put("井盖",1); pipeCondition.put("沉沙井",2); waterCondition.put("均匀",0); waterCondition.put("堵塞",1); } public static Integer getWaterInCondition(String waterIn) { return waterInCondition.get(waterIn); } public static Integer getWaterOutCondition(String waiterOut) { return waterOutCondition.get(waiterOut); } public static Integer getElectryCondition(String electry) { return electryCondition.get(electry); } public static Integer getLiveCondition(String live) { return liveCondition.get(live); } public static Integer getPipeCondition(String pipe) { return pipeCondition.get(pipe); } public static Integer getWaterCondition(String water) { return waterCondition.get(water); }}复制代码导出数据实体类
public class PatrolTaskExport { private String street; private String xVillage; private String zVlillage; private String patrolTime; private String patrolPerson; private String content; private String waterInCondition; private String waterOutCondition; private String electryCondition; private List<equCondition> equConditionList; private String isSame; private List<equCondition> pipeCondition; private String waterSupport; private String afforestCondition; public String getStreet() { return street; } public void setStreet(String street) { this.street = street; } public String getxVillage() { return xVillage; } public void setxVillage(String xVillage) { this.xVillage = xVillage; } public String getzVlillage() { return zVlillage; } public void setzVlillage(String zVlillage) { this.zVlillage = zVlillage; } public String getPatrolTime() { return patrolTime; } public void setPatrolTime(String patrolTime) { this.patrolTime = patrolTime; } public String getPatrolPerson() { return patrolPerson; } public void setPatrolPerson(String patrolPerson) { this.patrolPerson = patrolPerson; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } public String getWaterInCondition() { return waterInCondition; } public void setWaterInCondition(String waterInCondition) { this.waterInCondition = waterInCondition; } public String getWaterOutCondition() { return waterOutCondition; } public void setWaterOutCondition(String waterOutCondition) { this.waterOutCondition = waterOutCondition; } public String getElectryCondition() { return electryCondition; } public void setElectryCondition(String electryCondition) { this.electryCondition = electryCondition; } public String getIsSame() { return isSame; } public void setIsSame(String isSame) { this.isSame = isSame; } public List<equCondition> getPipeCondition() { return pipeCondition; } public void setPipeCondition(List<equCondition> pipeCondition) { this.pipeCondition = pipeCondition; } public String getWaterSupport() { return waterSupport; } public void setWaterSupport(String waterSupport) { this.waterSupport = waterSupport; } public String getAfforestCondition() { return afforestCondition; } public void setAfforestCondition(String afforestCondition) { this.afforestCondition = afforestCondition; } public static class equCondition { public equCondition (String name,String value){ this.name = name; this.value = value; } private String name; private String value; public String getName() { return name; } public void setName(String name) { this.name = name; } public String getValue() { return value; } public void setValue(String value) { this.value = value; } } public List<equCondition> getEquConditionList() { return equConditionList; } public void setEquConditionList(List<equCondition> equConditionList) { this.equConditionList = equConditionList; }}复制代码对应的服务类
/** * @ClassName ExcelExportService * @Description TODO * @Author xiongchao * @Date 2020/10/20 10:42 **/public interface ExcelExportService { /** * 数据导出,格式转化 * @param fileName * @param rootPath * @param list * @return */ FileData exportFile(String fileName, String rootPath, List<PatrolTaskExport> list); /** * 获取导出数据 * @param param * @return */ List<PatrolTaskExport> getExportData(Map<String,Object> param);}复制代码接口实现类
@Servicepublic class ExcelExportServiceImpl implements ExcelExportService { private static final String yes ="√"; private static final String no ="×"; private Logger log = LoggerFactory.getLogger(ExcelExportServiceImpl.class); private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); @Override public FileData exportFile(String fileName, String rootPath, List<PatrolTaskExport> list) { if (CollectionUtils.isEmpty(list)) { log.error("导出数据不能为空!"); return null; } FileData fileData = null; Sheet sheet = null; Workbook workbook = null; Row row = null; try { workbook= ExcelUtil.readExcel(rootPath); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); Font font = workbook.createFont(); font.setColor(HSSFColor.RED.index); //导出月份时间 String month = getMonth(); if(workbook != null) { sheet = workbook.getSheetAt(0); } if(sheet != null){ //开始日期 row= sheet.getRow(1); row.getCell(0).setCellValue(month); // 第四行开始写入数据 int rowNum = 4; int index = 1; //明确只有 2,3,4 需要根据重复内容合并 //上一个街镇名称 String perPurpose = ""; String perxVillage = ""; String perzVliiage= ""; //要融合的第一行 int startMergeCol = 4; int xStartMergeCol = 4; int zStartMergeCol = 4; int xendMergeCol = 3; int zendMergeCol = 3; //要融合的最后行 int endMergeCol = 3; //勾选的数字 Integer selectIndex = null; //用来记录是否是第一次循环 boolean flag = true; int cellNum = sheet.getRow(3).getLastCellNum(); //先建单元格 for(PatrolTaskExport vo :list){ row = sheet.createRow(rowNum); for(int i=0;i<cellNum;i++){ row.createCell(i); } rowNum++; } rowNum =4; for(PatrolTaskExport vo :list){ row = sheet.getRow(rowNum); //开始写入数据 createCell(row,cellStyle,0,String.valueOf(index)); createCell(row,cellStyle,1,vo.getStreet()); if(flag){ perPurpose = vo.getStreet(); perzVliiage = vo.getzVlillage(); perxVillage =vo.getxVillage(); flag = false; } if(perzVliiage.equalsIgnoreCase(vo.getzVlillage())){ zendMergeCol ++; } else { if(zendMergeCol > zStartMergeCol){ sheet.addMergedRegion(new CellRangeAddress(zStartMergeCol,zendMergeCol,3,3)); } zStartMergeCol = rowNum; zendMergeCol = zStartMergeCol; perzVliiage = vo.getzVlillage(); } if(perxVillage.equalsIgnoreCase(vo.getxVillage())){ xendMergeCol ++; } else { if(xendMergeCol > xStartMergeCol){ sheet.addMergedRegion(new CellRangeAddress(xStartMergeCol,xendMergeCol,2,2)); } xStartMergeCol = rowNum; xendMergeCol = xStartMergeCol; perxVillage = vo.getxVillage(); } if(perPurpose.equals(vo.getStreet())){ endMergeCol ++; } else { //并且之前相同的单元格 if(startMergeCol < endMergeCol){ sheet.addMergedRegion(new CellRangeAddress(startMergeCol,endMergeCol,1,1)); } startMergeCol = rowNum; endMergeCol = startMergeCol; perPurpose = vo.getStreet(); } createCell(row,cellStyle,2,vo.getxVillage()); createCell(row,cellStyle,3,vo.getzVlillage()); createCell(row,cellStyle,4,vo.getPatrolTime()); selectIndex = PatrolExportEnum.getWaterInCondition(vo.getWaterInCondition()); if(selectIndex != null) { if(selectIndex.equals(3)){ CellStyle style = colorRed(workbook,font); createCell(row,style,5 + selectIndex,no); }else{ createCell(row,cellStyle,5 + selectIndex,yes); } selectIndex = null; } selectIndex = PatrolExportEnum.getWaterOutCondition(vo.getWaterOutCondition()); if(selectIndex != null){ if(selectIndex.equals(1)){ CellStyle style = colorRed(workbook,font); createCell(row,style,9 + selectIndex,no); }else { createCell(row,cellStyle,9 + selectIndex,yes); } selectIndex = null; } selectIndex = PatrolExportEnum.getElectryCondition(vo.getElectryCondition()); if(selectIndex != null) { if(selectIndex.equals(1)){ CellStyle style = colorRed(workbook,font); createCell(row,style,11 + selectIndex,no); }else{ createCell(row,cellStyle,11 + selectIndex,yes); } selectIndex = null; } List<PatrolTaskExport.equCondition> conditions = vo.getEquConditionList(); if(!CollectionUtils.isEmpty(conditions)){ for(PatrolTaskExport.equCondition e : conditions){ String value = e.getValue(); String name = e.getName(); if ("格栅".equalsIgnoreCase(name)) { CellStyle style = setColor(value,workbook,font); createCell(row,style,13,value); } if ("水泵".equalsIgnoreCase(name)) { CellStyle style = setColor(value,workbook,font); createCell(row,style,14 ,value); } if ("风机".equalsIgnoreCase(name)) { CellStyle style = setColor(value,workbook,font); createCell(row,style,15 ,value); } if ("液位计".equalsIgnoreCase(name)) { CellStyle style = setColor(value,workbook,font); createCell(row,style,16 ,value); } } } selectIndex = PatrolExportEnum.getLiveCondition(vo.getIsSame()); if(selectIndex != null) { if (selectIndex.equals(1)) { CellStyle style = colorRed(workbook,font); createCell(row,style,17 + selectIndex,no); }else { createCell(row,cellStyle,17 + selectIndex,yes); } selectIndex = null; } List<PatrolTaskExport.equCondition> pipeCodintion = vo.getPipeCondition(); if(!CollectionUtils.isEmpty(pipeCodintion)){ for(PatrolTaskExport.equCondition e : pipeCodintion){ String value = e.getValue(); String name = e.getName(); if ("管道".equalsIgnoreCase(name)) { CellStyle style = setColor(value,workbook,font); createCell(row,style,19,value); } if ("井盖".equalsIgnoreCase(name)) { CellStyle style = setColor(value,workbook,font); createCell(row,style,20 ,value); } if ("沉沙井".equalsIgnoreCase(name)) { CellStyle style = setColor(value,workbook,font); createCell(row,style,21 ,value); } } } selectIndex = PatrolExportEnum.getWaterCondition(vo.getWaterSupport()); if(selectIndex != null){ if (selectIndex.equals(1)) { CellStyle style = colorRed(workbook,font); createCell(row,style,22 + selectIndex,no); }else { createCell(row,cellStyle,22 + selectIndex,yes); } selectIndex = null; } createCell(row,cellStyle,24 ,vo.getAfforestCondition()); createCell(row,cellStyle,25 ,vo.getPatrolPerson()); createCell(row,cellStyle,26 ,vo.getContent()); rowNum++; index ++; } if(startMergeCol < endMergeCol){ sheet.addMergedRegion(new CellRangeAddress(startMergeCol,endMergeCol,1,1)); } if(xendMergeCol > xStartMergeCol){ sheet.addMergedRegion(new CellRangeAddress(xStartMergeCol,xendMergeCol,2,2)); } if(zendMergeCol > zStartMergeCol){ sheet.addMergedRegion(new CellRangeAddress(zStartMergeCol,zendMergeCol,3,3)); } } ByteArrayOutputStream os = new ByteArrayOutputStream(); fileData = new FileData(); workbook.write(os); os.flush(); fileData.setBytes(os.toByteArray()); fileData.setFileName(fileName); } catch (IOException e) { log.error("partrol_report_export_error"); e.printStackTrace(); } return fileData; } @Override public List<PatrolTaskExport> getExportData(Map<String, Object> param) { List<PatrolTaskExport> list = new ArrayList<>(); for (int i = 0; i < 100 ; i++) { PatrolTaskExport pa = new PatrolTaskExport(); if(i == 6) { pa.setStreet("公孙街道"); }else{ pa.setStreet("关山街道"); } pa.setzVlillage("黄龙山村"+i); pa.setPatrolTime("2020-12-" + i); if(i%2 == 0){ pa.setWaterInCondition("正常"); pa.setWaterOutCondition("无水"); pa.setIsSame("一致"); pa.setPatrolPerson("张三" + i); pa.setxVillage("木槿村委会"); }else{ pa.setWaterInCondition("少"); pa.setWaterOutCondition("正常"); pa.setIsSame("不一致"); pa.setPatrolPerson("张三" + i); pa.setxVillage("轰隆声村委会"); } pa.setElectryCondition("故障"); List<PatrolTaskExport.equCondition> conditions = new ArrayList<>(); conditions.add(new PatrolTaskExport.equCondition("格栅","正常")); conditions.add(new PatrolTaskExport.equCondition("水泵","异常")); conditions.add(new PatrolTaskExport.equCondition("风机","正常")); conditions.add(new PatrolTaskExport.equCondition("液位计","异常")); pa.setEquConditionList(conditions); List<PatrolTaskExport.equCondition> pipeconditions = new ArrayList<>(); pipeconditions.add(new PatrolTaskExport.equCondition("管道","正常")); pipeconditions.add(new PatrolTaskExport.equCondition("井盖","异常")); pipeconditions.add(new PatrolTaskExport.equCondition("沉沙井","正常")); pa.setPipeCondition(pipeconditions); pa.setWaterSupport("堵塞"); pa.setAfforestCondition("除杂草"); pa.setContent("这是备注信息"); list.add(pa); } return list; } private String getMonth(){ Calendar date = Calendar.getInstance(); String year = String.valueOf(date.get(Calendar.YEAR)); String month = String.valueOf(date.get(Calendar.MONTH) + 1); return year + "年" + month + "月"; } private void createCell(Row row,CellStyle cellStyle ,int rowNum,String value){ Cell cell = row.getCell(rowNum); cell.setCellStyle(cellStyle); cell.setCellValue(value); } private CellStyle setColor(String value ,Workbook workbook,Font font){ CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); if(value.equalsIgnoreCase("异常")){ cellStyle.setFont(font); return cellStyle; } return cellStyle; } private CellStyle colorRed(Workbook workbook,Font font){ CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setFont(font); return cellStyle; }}复制代码暴露接口并测试 controller层
@Api(tags = "巡检任务报表导出")@RequestMapping("/api")@RestControllerpublic class PatrolTaskResource { @Autowired private ExcelExportService exportService; @ApiOperation(value = "巡检任务报表导出") @GetMapping("/partorl-task/export") public void patrolReportExport( @ApiParam(value = "查询条件") @RequestParam(value = "param",required = false) String param, HttpServletRequest request, HttpServletResponse response ){ long start =System.currentTimeMillis(); Map<String,Object> map = new HashMap<>(); if(StringUtils.isNotEmpty(param)){ map.put("param",param); } List<PatrolTaskExport> list = exportService.getExportData(map); String rootPath = ImportTypeEnum.REPORT.getFilePath(); String fileName = ImportTypeEnum.REPORT.getFileName(); FileData file = exportService.exportFile(fileName,rootPath,list); try { ExcelUtil.setResponse(request, response,file); } catch (Exception e) { e.printStackTrace(); } }}复制代码最终展示结果
版权声明:
本站文章均来自互联网搜集,如有侵犯您的权益,请联系我们删除,谢谢。
标签: #javaexcel数据库