java按需导出Excel并自动合同单元格

java导出excel并单元格自动合并

最近公司有一个需求,就是按到模版导出数据报表,并内容相同的单元格实现自动合并.具体业务设计图如下所示

需求分析

1. 由于头部是固定不变的,先使用excel创建数据模版
2. 单元格合并,前三列需要根据内容相同自动合并单元格
3. 根据状态来判断,状态异常的红色字体,并且已红色×标识
4. *情况,都是多选一的,数据库只会记录一种状态,后台需要判断并在指定的单元格添加标识符√

代码开发

1. 创建*情况对应的魔法值对比索引

/** * @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);
    }

}

2. 导出数据实体类

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;
    }
}

3. 对应的服务类

/** * @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);
}

4. 接口实现类

@Service
public 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;
    }
}

5. 暴露接口并测试 controller层

@Api(tags = "巡检任务报表导出")
@RequestMapping("/api")
@RestController
public 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();
        }
    }
}

最终展示结果