java使用原生poi导出复杂Excel

POIExcel文件导出

思路: 现在很多Excel导出都有Excel模板,直接将数据封装好,直接调用即可, 但是我们项目变动较大,所以我选择使用原生POI进行文件导出,方面后期改动灵活, 也可选用固定Template模板,读取固定表头,写入数据...

POM依赖配置

POM文件配置: 这里选用的是3.14版本

<!-- poi 文件上传,解析excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.14</version>
        </dependency>

Excel导出问题汇总

`java实现Excel导出普遍有两种: XSSFWorkbook / HSSFWorkbook , 这里有一个问题,头一次写的同学需要额外注意: 
 1. HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
 2. XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx`
 根据个人需要导出不同格式的Excel

A.踩坑历程:

B. 导出Excel后出现这个问题就是因为对应选取的XSSFWorkbook / HSSFWorkbook支持不同的格式,新手小白一定要额外注意

导出的Excel单元格左上角有绿色小角标问题解决

		//设置数据格式
        XSSFDataFormat dataFormat = workBook.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("#,#0"));

设置单元格的数据接收类型,因为没有指定类型直接写入会在单元格左上角出现绿色角标

C. 下拉选处理,部分表头导出可能需要筛选功能:

        //设置表头过滤条件 , 此处需要特别注意,要放在设置完sheet表头数据之后,否则会出现单元格左上角有绿色三角标
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 10);
        sheet.setAutoFilter(cellRangeAddress);

D. 数据格式化问题: 导出的类型如果有小数或者时间格式,需要做追加处理,以及是否使用科学计数法问题解决,此处不涉及,不一一列举,可以查找相关博客,也很好操作

Excel导出

    /**
     * Title list
     */
    public static List<String> COMMON_PARAMETER = Lists.newLinkedList();

    /**
     * famWeek list
     */
    public static List<FamWeek> COMMON_FAM_WEEK_LIST = Lists.newLinkedList();
    
	/**
     * PSD Churn Analysis list Excel DownLoad
     * @return
     */
    @GetMapping(value = "/list")
    public Result list(HttpServletResponse response) throws IOException{
        //create workBook Model
        XSSFWorkbook workBook = new XSSFWorkbook();
        //create sheet
        XSSFSheet aiSheet = workBook.createSheet("aiSheet");
        XSSFSheet agumentedSheet = workBook.createSheet("agumentedSheet");
        //设置sheet的默认行距
        aiSheet.setDefaultColumnWidth(15);
        agumentedSheet.setDefaultColumnWidth(15);
        //设置Excel抬头样式,固定值样式
        XSSFCellStyle cellTitleStyle = siteTitleRowStyle(workBook);
        //设置 Integer Bucket样式
        XSSFCellStyle siteIntegerBucketStyle = siteIntegerBucketStyle(workBook);
        //title bucket list
        List<String> rowTitleBucket = getRowTitleBucket();
        log.info("find famWeek bucket list info {} ",rowTitleBucket);
        List<FamWeek> famWeekList =famWeekService.findExportGroupList(rowTitleBucket.get(0),rowTitleBucket.get(rowTitleBucket.size()-1));
        if (null != famWeekList && famWeekList.size()>0){
            //清理脏数据,二次放入新的数据结果集
            COMMON_FAM_WEEK_LIST.clear();
            COMMON_FAM_WEEK_LIST.addAll(famWeekList);
        }else {
            Result.error(ResultCommonConstant.DOWN_LOAD_EXCEL_FAM_WEEK_DATA_ERROR.getCode(), "Down Load Excel Fam Week Data Error");
        }
        //AI /agumented预测值写入不同的sheet
        createSheetTitle(aiSheet,cellTitleStyle,rowTitleBucket);
        createSheetTitle(agumentedSheet,cellTitleStyle,rowTitleBucket);
      	createSheetFcstWeekData(aiSheet,agumentedSheet,cellTitleStyle,siteIntegerBucketStyle,rowTitleBucket);

        //文档输出
        FileOutputStream out = new FileOutputStream("F:\\work\\" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xlsx");
        //此处是用于与前端交互,需要设置相应头,本地开发可以将文件写入至本地文件夹
        response.setContentType("application/vnd.ms-excel");
        response.addHeader("Content-Disposition", "attachment;filename=fileName"+".xls");
        workBook.write(out);
        out.close();
        return Result.success();
    }

    /**
     *  写入FcstWeek内容至表格中
     * @param
     */
    public void createSheetFcstWeekData(XSSFSheet aiSheet,XSSFSheet agumentedSheet,XSSFCellStyle siteTitleStyle,
                                                            XSSFCellStyle siteBucketStyle,List<String> rowTitleBucket){
        //获取过滤条件集合
       List<MtmFcstWeek> filterMtmFcstWeekList = mtmFcstWeekService.findAllFilterCondition(rowTitleBucket.get(0),rowTitleBucket.get(rowTitleBucket.size()-1));
       if (filterMtmFcstWeekList == null && filterMtmFcstWeekList.size()<0){
           return;
       }
        //获取原始数据集合
        List<MtmFcstWeek> mtmFcstWeekList =mtmFcstWeekService.findExportGroupList(rowTitleBucket.get(0),rowTitleBucket.get(rowTitleBucket.size()-1));
        if (mtmFcstWeekList == null && mtmFcstWeekList.size()<0){
            return;
        }
        List<Map<String,Object>> exportFcstWeekAiData = Lists.newLinkedList();
        List<Map<String,Object>> exportFcstWeekAgumentedData = Lists.newLinkedList();
        filterMtmFcstWeekList.forEach(mf ->{
            List<MtmFcstWeek> fcstWeekData = mtmFcstWeekList.stream()
                    .filter(fcstWeek -> fcstWeek.getSubgeo().equals(mf.getSubgeo()) && fcstWeek.getFamily().equals(mf.getFamily()))
                    .collect(Collectors.toList());
            if (null != fcstWeekData && fcstWeekData.size()>0){
                MtmFcstWeek mtmFcstWeek = fcstWeekData.stream().findFirst().get();
                Map<String, Object> aiMap = fcstWeekExportDataTemplate(mtmFcstWeek,ExportType.AI_PREDICTION_NUM);
                Map<String, Object> augmentedMap = fcstWeekExportDataTemplate(mtmFcstWeek,ExportType.AGUMENTED_NUM);
                for (MtmFcstWeek fw : fcstWeekData) {
                    for (String titleName : rowTitleBucket) {
                        if (fw.getBucket().equals(titleName)){
                            aiMap.put(titleName,fw.getAiPerQty());
                            augmentedMap.put(titleName,fw.getForecast() == null?0:fw.getForecast());
                        }
                    }
                }
                exportFcstWeekAiData.add(aiMap);
                exportFcstWeekAgumentedData.add(augmentedMap);
            }
        });
        if (null != exportFcstWeekAiData && exportFcstWeekAiData.size()>0){
            commonExportDataTemplate(aiSheet, exportFcstWeekAiData,siteTitleStyle,siteBucketStyle);
        }
        if (null != exportFcstWeekAgumentedData && exportFcstWeekAgumentedData.size()>0){
            commonExportDataTemplate(agumentedSheet, exportFcstWeekAgumentedData,siteTitleStyle,siteBucketStyle);
        }
    }

   /**
     * 数据写出,获取当前写入的行
     * @param sheet
     * @param exportData
     * @param siteTitleStyle
     * @param siteBucketStyle
     */
    public void commonExportDataTemplate(XSSFSheet sheet, List<Map<String,Object>> exportData, XSSFCellStyle siteTitleStyle, XSSFCellStyle siteBucketStyle){
        Integer currentRowNum =  sheet.getLastRowNum() + 1;
        for (int i= 0; i< exportData.size();i++) {
            XSSFRow famWeekRow = sheet.createRow(currentRowNum++);
            famWeekRow.setHeightInPoints(15);
            Object[] objects = exportData.get(i).values().toArray();
            for (int j= 0; j< objects.length;j++) {
                if (null == objects[j]){
                    continue;
                }
                if (j >10){
                    BigDecimal bd = new BigDecimal(subZeroAndDot(objects[j].toString()));
                    String lastData = bd.setScale(3, RoundingMode.HALF_UP).toString();
                    famWeekRow.createCell(j).setCellValue(Double.parseDouble(lastData));
                    famWeekRow.getCell(j).setCellStyle(siteBucketStyle);//设置自动换行
                }else {
                    famWeekRow.createCell(j).setCellValue(String.valueOf(objects[j]));
                    famWeekRow.getCell(j).setCellStyle(siteTitleStyle);//设置自动换行
                }
            }
        }
    }

 /**
     * 创建Excel表,设置字体以及居中格式
     * @param sheet
     */
    public void createSheetTitle(XSSFSheet sheet, XSSFCellStyle cellStyle,List<String> rowTitleBucket){
        //每次进入清理默认的COMMON_PARAMETER 属性
        COMMON_PARAMETER.clear();
        //创建工作表的行
        XSSFRow row = sheet.createRow(0);//设置第一行，从零开始
        //设置行高
        row.setHeightInPoints(15);
        COMMON_PARAMETER.add("Item");
        COMMON_PARAMETER.add("Char.");
        COMMON_PARAMETER.add("Value");
        COMMON_PARAMETER.add("SBB_ID");
        COMMON_PARAMETER.add("Item  Desc");
        COMMON_PARAMETER.add("Plant");
        COMMON_PARAMETER.add("Geo");
        COMMON_PARAMETER.add("Subgeo");
        COMMON_PARAMETER.add("Item Type");
        COMMON_PARAMETER.add("Product Family");
        COMMON_PARAMETER.add("Wk Fcst");
        LinkedList<String> titleList = Lists.newLinkedList();
        for (String title : rowTitleBucket) {
            titleList.add(title.substring(4,6)+"/"+title.substring(6,title.length())+"/"+title.substring(0,4));
        }
        COMMON_PARAMETER.addAll(titleList);
        for (int i= 0; i< COMMON_PARAMETER.size();i++){
            row.createCell(i).setCellValue(COMMON_PARAMETER.get(i));
            row.getCell(i).setCellStyle(cellStyle);//设置自动换行
        }
        //设置表头过滤条件 , 此处需要特别注意,要放在设置完sheet表头数据之后,否则会出现单元格左上角有绿色三角标
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 10);
        sheet.setAutoFilter(cellRangeAddress);
    }

        /**
     * 设置Excel抬头以及固定值行的样式
     * @param workBook
     * @return
     */
    public XSSFCellStyle siteTitleRowStyle(XSSFWorkbook workBook){
        //设置样式
        XSSFCellStyle cellStyle = workBook.createCellStyle();
        //设置自动换行
        cellStyle.setWrapText(true);
        //设置字体位置
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        cellStyle.setVerticalAlignment(CellStyle.ALIGN_LEFT);
        //设置字体
        XSSFFont font = workBook.createFont();
        font.setFontName("宋体");
        //设置字体高度
        font.setFontHeightInPoints((short)12);
        //设置字体颜色
//        font.setColor(HSSFColor.BLACK.index);
        font.setColor(new XSSFColor(new Color(17, 8, 8)).getIndexed());
        //设置粗体
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(font);
        //设置数据格式  指定对应单元格的类型,否则导出的Excel左上角会有绿色小角标
        XSSFDataFormat dataFormat = workBook.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("#,#0"));
        //设置数据格式
        return cellStyle;
    }
    
  /**
     *  设置Integer Bucket列的样式
     * @param workBook
     * @return
     */
    public XSSFCellStyle siteIntegerBucketStyle(XSSFWorkbook workBook){
        //设置样式
        XSSFCellStyle cellStyle = workBook.createCellStyle();
        //设置自动换行
        cellStyle.setWrapText(true);
        //设置字体位置,水平对齐
        cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        //设置字体垂直对齐
        cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
        //设置字体
        XSSFFont font = workBook.createFont();
        font.setFontName("宋体");
        //设置字体高度
        font.setFontHeightInPoints((short)12);
        //设置字体颜色
        font.setColor(new XSSFColor(new Color(17, 8, 8)).getIndexed());
        //设置粗体
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(font);
        //设置数据格式
        XSSFDataFormat dataFormat = workBook.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("#,#0"));
        return cellStyle;
    }
    /**
     * 使用java正则表达式去掉多余的.与0
     * @param s
     * @return
     */
    public static String subZeroAndDot(String s){
        if(s.indexOf(".") > 0){
            s = s.replaceAll("0+?$", "");//去掉多余的0
            s = s.replaceAll("[.]$", "");//如最后一位是.则去掉
        }
        return s;
    }
    

至此Excel导出完成,可以正常导出Excel文件

至此Excel可以正常导出满足需求