报表导出Excel 二 ：将业务报表数据写入特定Excel模板

    按照上篇博客《报表导出Excel 一 ：根据业务需求设计Excel导出模板》，已经做好了报表模板，接下来简单串一下我们用到的具体service类。

    首先是，单元格cell对应的实体对象ReplaceItem， d通过这个实体类，可以具体定义每一个cell。

public class ReplaceItem {    private int rowspan;// Excel单元格行    private int colspan;// Excel单元格列    private String backgroundColor;//背景色    private String key;//变量名    private String value;//文本值    public ReplaceItem() {    }    public ReplaceItem(String key, String value) {        this.key = key;        this.value = value;    }    public int getRowspan() {        return rowspan;    }    public void setRowspan(int rowspan) {        this.rowspan = rowspan;    }    public int getColspan() {        return colspan;    }    public void setColspan(int colspan) {        this.colspan = colspan;    }    public String getBackgroundColor() {        return backgroundColor;    }    public void setBackgroundColor(String backgroundColor) {        this.backgroundColor = backgroundColor;    }    public String getKey() {        return key;    }    public void setKey(String key) {        this.key = key;    }    public String getValue() {        return value;    }    public void setValue(String value) {        this.value = value;    }}

    下面是主要的写excel的service，genExcelReport方法会根据传入的queryMap生成一个文件流到response中。

public static void genExcelReport(OutputStream os, Map<String, String> queryMap, String plantId, String localeName ,String userId) throws Exception {        List<ReplaceItem[]> datas = new ArrayList<ReplaceItem[]>();        String reportType = queryMap.get("reportType");        if ("DL1".equalsIgnoreCase(reportType)) {//DL1 管理程序列表清单            queryMap.put(MetadataConstants.NUCLEAR_STATUS, "Available");            managementProcedureStatementExport(os, queryMap, reportType, plantId);        }}

     这里要根据具体业务，来将查询到的数据组合成ReplaceItemList。

private static void managementProcedureStatementExport(OutputStream os, Map<String, String> queryMap,String reportType , String plantId){      List<ReplaceItem[]> datas = new ArrayList<ReplaceItem[]>();      //下面是查询FileNet CE 中文档数据，Excel中数据来源于DocumentList      String pattern = "yyyy-mm-dd";      String startNuclearYear = queryMap.get("startNuclearYear");      String endNuclearYear = queryMap.get("endNuclearYear");      String bizCode = "ManageProcess";      String osName = BizCodeConstants.getObjectNameByBizCode(bizCode);      String whereClause = "",propertyCondition = "",statusCondition = "",timeCondition="";      String symbolicClassName = "P"+plantId+"_ManagementProcedure";        propertyCondition = SqlBuildCommonService.addCondition(propertyCondition, true, MetadataConstants.RECORD_CODE, true, queryMap);        propertyCondition = SqlBuildCommonService.addCondition(propertyCondition,true,MetadataConstants.NUCLEAR_DOMAIN, true, queryMap);        propertyCondition = SqlBuildCommonService.addCondition(propertyCondition,true,MetadataConstants.RECORD_CHINESE_TITLE, true, queryMap);        propertyCondition = SqlBuildCommonService.addCondition(propertyCondition,true,MetadataConstants.CREATED_UNIT, true, queryMap);        statusCondition = SqlBuildCommonService.addCondition(statusCondition, true, MetadataConstants.NUCLEAR_STATUS,true, queryMap);        timeCondition = SqlBuildCommonService.addTimeCondition(timeCondition, MetadataConstants.NUCLEAR_YEAR,                        queryMap.get("startNuclearYear"), queryMap.get("endNuclearYear"));                      whereClause = SqlBuildCommonService.addWhereClause(whereClause, propertyCondition);        whereClause = SqlBuildCommonService.addWhereClause(whereClause, statusCondition);        whereClause = SqlBuildCommonService.addWhereClause(whereClause, timeCondition);      List<Document> docList = P8DocumentDao.searchForDocuments(ObjectStoreHelper.getObjectStore(osName), symbolicClassName, true, whereClause);                     ReplaceItem vo = new ReplaceItem();        vo.setKey("reportDate");        Date startDate = DateFormatUtils.parseDate(startNuclearYear, pattern);        Date endDate = DateFormatUtils.parseDate(endNuclearYear, pattern);        vo.setValue("发布时间："+new SimpleDateFormat("yyyy年MM月dd日").format(startDate)+"-"+                  new SimpleDateFormat("yyyy年MM月dd日").format(endDate));        ReplaceItem[] reportDateArr = {vo};        datas.add(reportDateArr);//数组第一行必须要放报表日期，切记！！！        for (Document doc : docList) {            BaseDocumentMapper baseDocMapper = new BaseDocumentMapper();            BaseDocument baseDoc = baseDocMapper.map(doc);                        ReplaceItem[] arr = new ReplaceItem[12];            ReplaceItem vo1 = new ReplaceItem();            vo1.setKey("RecordCode");            vo1.setValue(baseDoc.getProperties().get(MetadataConstants.RECORD_CODE).toString());            ReplaceItem vo2 = new ReplaceItem();            vo2.setKey("RecordChineseTitle");            vo2.setValue(baseDoc.getProperties().get(MetadataConstants.RECORD_CHINESE_TITLE).toString());            ReplaceItem vo3 = new ReplaceItem();            vo3.setKey("NuclearVersion/NuclearRevision");            vo3.setValue(baseDoc.getProperties().get(MetadataConstants.NUCLEAR_VERSION)+"/"+                        baseDoc.getProperties().get(MetadataConstants.NUCLEAR_REVISION));            ReplaceItem vo4 = new ReplaceItem();            vo4.setKey("NuclearDomain");            vo4.setValue(baseDoc.getProperties().get(MetadataConstants.NUCLEAR_DOMAIN).toString());            ReplaceItem vo5 = new ReplaceItem();            vo5.setKey("CreatedUnit");            vo5.setValue(baseDoc.getProperties().get(MetadataConstants.CREATED_UNIT).toString());            ReplaceItem vo6 = new ReplaceItem();            vo6.setKey("NuclearStatus");            vo6.setValue(baseDoc.getProperties().get(MetadataConstants.NUCLEAR_STATUS).toString());            ReplaceItem vo7 = new ReplaceItem();            vo7.setKey("NuclearCreator");            vo7.setValue(baseDoc.getProperties().get(MetadataConstants.NUCLEAR_CREATOR).toString());            ReplaceItem vo8 = new ReplaceItem();            vo8.setKey("Checker");            vo8.setValue(baseDoc.getProperties().get(MetadataConstants.CHECKER).toString());            ReplaceItem vo9 = new ReplaceItem();            vo9.setKey("Examiner");            vo9.setValue(baseDoc.getProperties().get(MetadataConstants.EXAMINER).toString());            ReplaceItem vo10 = new ReplaceItem();            vo10.setKey("Approver");            vo10.setValue(baseDoc.getProperties().get(MetadataConstants.APPROVER).toString());            ReplaceItem vo11 = new ReplaceItem();            vo11.setKey("NuclearYear");            vo11.setValue(baseDoc.getProperties().get(MetadataConstants.NUCLEAR_YEAR).toString());            ReplaceItem vo12 = new ReplaceItem();            vo12.setKey("NuclearAnnotation");            vo12.setValue(baseDoc.getProperties().get(MetadataConstants.NUCLEAR_ANNOTATION).toString());            arr[0] = vo1; arr[1] = vo2; arr[2] = vo3; arr[3] = vo4; arr[4] = vo5; arr[5] = vo6;            arr[6] = vo7; arr[7] = vo8; arr[8] = vo9; arr[9] = vo10; arr[10] = vo11; arr[11] = vo12;            datas.add(arr);        }        JexcelFiles.excelReportGenerator(os, datas, reportType, 5);}public static boolean excelReportGenerator(OutputStream os, List<ReplaceItem[]> datas, String sheetName, intdataRowNum) {        boolean bool = true;        if (datas == null) {            return false;        }        String sourceFilePath = Thread.currentThread().getContextClassLoader().getResource("").getPath() + "conf"                + File.separator + "template" + File.separator + "report_template_v1.5.xls";        try {            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(sourceFilePath));            HSSFWorkbook wb = new HSSFWorkbook(fs);            HSSFSheet sheet = null;            int activeSheet = 0;            for (int i = 0; i < wb.getNumberOfSheets(); i++) {//找到模板正确的sheet页                sheet = wb.getSheetAt(i);                if (sheet.getSheetName() != null && sheet.getSheetName().startsWith(sheetName)) {//入库信息统计表                    activeSheet = i;                    break;                }            }            //wb.setActiveSheet(activeSheet);            for (int i = wb.getNumberOfSheets() - 1; i >= 0 ; i--) {//裁剪多余的sheet页                if (i != 0 && i != activeSheet) {                    wb.removeSheetAt(i);                }            }            //1、填充报表输出日期            Row row = sheet.getRow(3);            if (datas.get(0) != null && datas.get(0)[0] != null) {                for (int i = 0; i < row.getLastCellNum(); i++) {                    Cell cell = row.getCell(i);                    String name = cell.getStringCellValue();                    ReplaceItem data = datas.get(0)[0];//这里规定第一行为报表输出日期                    if (name != null && name.indexOf("$" + data.getKey()) != -1) {                        cell.setCellValue(name.replaceAll("\\$" + data.getKey(), data.getValue()  == null ? "" : data.getValue()));                        break;                    }                }                datas = datas.subList(1, datas.size());//使用完毕后，抛弃第一行            }            Map<Integer, String> relMap = new HashMap<Integer, String>();//列数与变量的对应关系            int startRow = dataRowNum, startCol = 0;            HSSFCellStyle tableStyle = NFExcelStyle.getTableRow(wb, "");            row = sheet.getRow(startRow);            //2、填充数据行中的第一行，并且找到列数与变量的对应关系            for (int i = 0; i < row.getLastCellNum(); i++) {                sheet.autoSizeColumn(i, true);//表格内容自适应                Cell cell = row.getCell(i);                if(cell == null) continue;                String name = cell.getStringCellValue();                for (int j = 0;  j < datas.size(); j++) {                    ReplaceItem[] data = datas.get(j);                    for (int k = 0; k < data.length; k++) {                        if (name != null && name.equals("$" + data[k].getKey())) {                            relMap.put(startCol + data[k].getColspan(), data[k].getKey());                            NFExcelStyle.createMergedCell(wb, sheet, startRow, startRow + data[k].getRowspan(),                                    startCol, startCol + data[k].getColspan(), (i == 0), tableStyle,                                    (data[k].getValue() == null || "null".equals(data[k].getValue())) ? "" : data[k].getValue());                            break;                        }                    }                    startCol++;                    break;                }            }            Iterator it= relMap.keySet().iterator();            StringBuffer excelVar = new StringBuffer();            while(it.hasNext()){                excelVar.append(",").append(relMap.get(it.next()));            }            System.out.println("Excel $Var  List +++++++++++++++++++     "+ excelVar.toString() + "         +++++++++++++++++++Excel $Var  List");            startRow++;            //3、开始后面数据行内容填充            for (int i = 1; i < datas.size(); i++) {                ReplaceItem[] rowData = datas.get(i);                for (int j = 0; j < rowData.length; j++) {                    startCol = j;                    for (int k = 0; k < rowData.length; k++) {                        if (relMap.get(j).equals(rowData[k].getKey())) {                            System.out.println("key:"+rowData[k].getKey()+"    value:"+rowData[k].getValue());                            long d = new Date().getTime();                            NFExcelStyle.createMergedCell(wb, sheet, startRow, startRow + rowData[k].getRowspan(),                                    startCol, startCol + rowData[k].getColspan(), (j == 0), tableStyle,                                    (rowData[k].getValue() == null || "null".equals(rowData[k].getValue()) ) ? "" : rowData[k].getValue());                            long d2 = new Date().getTime();                            System.out.println("执行时间:"+(d2-d));                            break;                        }                    }                }                startRow++;            }            // 输出流            wb.write(os);        } catch (Exception e) {            bool = false;            e.printStackTrace();        }        return bool;}

    基本的报表导出excel的功能点都满足了，有数据、有模板，然后构造一个数据写入excel的桥梁，最后把excel类型文件流输出到response中，就能在页面上弹出“Excel 下载提示“了。