记录一次复杂的Excel导出（一）

描述：由于导出的表格过于复杂，使用了bel格式的文件赋值，然后由html页面转Excel格式导出；后期想要实现Excel多sheet导出，直接挠头了。。。

引入easypoi

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>3.3.0</version>
        </dependency>

1.封装数据，把复杂的数据封装成自己想要的，数据可以根据需求自己封装

        数据封装--略
        //数据导出，名称
        String sheetName = "测试表格";
        String filePath = beetlProperties.getFilePathIn()+sheetName+".html";//html文件地址
        //绑定生成模板方法 ExportHtmlUtil().generateFile(dailyReport,null,filePath,sheetName,"/html/test.html.btl");

        DateFormat df = new SimpleDateFormat("yyyy_MM_dd_HH_mm");
        String now = df.format(DateTimeKit.date());
        //html转Excel方法
        htmlToExcelToStr(response, filePath, sheetName, granaryInfo.getStoreCode() + "测试-" + now);
        

 

2.写html页面，然后用beetle赋值，数据比较麻烦，费了很大的功夫

   页面模板：

//sheetName 是必须要有的属性
<table sheetName="${tempData.grName!''}" style="width:100%;border-collapse:collapse;text-align: center;" border="1" cellspacing='0' >
        <thead>
            <tr>
                //这里合并是按照数据数据，更加灵活，从后台返回
                <td  colspan="${parseInt(colspanTotal)}" style="font: bold 22pt 'Microsoft YaHei';height:40%">${tempData.grName!''}测试表</td>
            </tr>
</thead>
 <tbody>
    <%  if(null != dayCheckList) {   %>
            <% for(temp in dayCheckList) { %>
            <tr>
                <td  rowspan="2" style="font-size: 12px;">层</td>
                <td  colspan="11" style="font-size: 12px;">无(第${temp.channelNum}行)</td>
            </tr>
            <tr>
              <% for(rootNum in temp.rootNumList) { %>
                <td style="font-size: 12px;">${rootNum.rootNum}(列)</td>
              <% } %>
            </tr>
            <% for(layerNumList in temp.layerTempList) { %>
            <% var total = 0; %>
            <tr>
                <% for(layerNum in layerNumList) {  %>
                   <% total = total +1; %>
                   <% if( strutil.startWith(layerNum.temp,"*") && parseDouble(tempData.maxTemp) == parseDouble(strutil.subString(layerNum.temp,1))) { %>
                        <td style="font-size: 12px;color:red;">${layerNum.temp}</td>
                    <% }else if( strutil.startWith(layerNum.temp,"#") && parseDouble(tempData.minTemp) == parseDouble(strutil.subString(layerNum.temp,1))) { %>
                        <td style="font-size: 12px;color:blue;">${layerNum.temp}</td>
                    <% }else{ %>
                        <td style="font-size: 12px;">${layerNum.temp}</td>
                    <% } %>
                <% } %>
                <% for(var i=0;i<12-total;i++) { %>
                    <td style="font-size: 12px;"></td>
                <% } %>
            </tr>
            <% } %>
            <% } %>
           <% } %>
            <tr>
                <td style="font-size: 12px;">仓</td>
                <td colspan="2" style="font-size: 14px;">${tempData.inTemp}</td>
                <td style="font-size: 12px;">仓</td>
                <td colspan="2" style="font-size: 12px;">${tempData.inHum}</td>
                <td style="font-size: 12px;">气</td>
                <td colspan="2" style="font-size: 12px;">${tempData.outTemp}</td>
                <td style="font-size: 12px;">气</td>
                <td colspan="2" style="font-size: 12px;">${tempData.outHum}</td>
            </tr>
            <tr>
                <td style="font-size: 12px;">数</td>
                <td colspan="3" style="font-size: 12px;">最高</td>
                <td colspan="4" style="font-size: 12px;">最低</td>
                <td colspan="4" style="font-size: 12px;">平均</td>
            </tr>
            <tr>
                <td style="font-size: 12px;">所有</td>
                <td colspan="3" style="font-size: 12px;">${tempData.maxTemp}</td>
                <td colspan="4" style="font-size: 12px;">${tempData.minTemp}</td>
                <td colspan="4" style="font-size: 12px;">${tempData.avgTemp}</td>
            </tr>
          <% if(null != layerTempList) { %>
            <% for(layer in layerTempList) { %>
            <tr>
                <td style="font-size: 12px;">${layer.layerNum}</td>
                <td colspan="3" style="font-size: 12px;">${layer.maxTemp}</td>
                <td colspan="4" style="font-size: 12px;">${layer.minTemp}</td>
                <td colspan="4" style="font-size: 12px;">${layer.avgTemp}</td>
            </tr>
            <% } %>
          <% } %>
 </tbody>
</table>

  给html赋值并生成模板：

/**
     * 赋值beetl模板并生成到指定地址
     * @param dailyReportData
     * @param fileName
     */
    public void generateFile(DailyReportVO dailyReportData,List<DailyReportVO> list,String filePath, String fileName,String templatePrefixPath) {

        Template pageTemplate = groupTemplate.getTemplate(templatePrefixPath);
        if (null != dailyReportData){
            pageTemplate.binding("tempData", dailyReportData);
            pageTemplate.binding("dayCheckList", dailyReportData.getDayCheckShowList());
            pageTemplate.binding("layerTempList", dailyReportData.getLayerTempList());
            //
            pageTemplate.binding("layerTempList", dailyReportData.getLayerTempList());
        }

        //
        if (null != list && !list.isEmpty()){
            pageTemplate.binding("outTemp",list.get(0).getOutTemp());
            pageTemplate.binding("outHum",list.get(0).getOutHum());
            pageTemplate.binding("list",list);
            //最多
            pageTemplate.binding("maxLayer",list.get(0).getStoreWay());
            //单元格总合并数和需要填充的数
            pageTemplate.binding("colspanTotal",list.get(0).getHum());
            pageTemplate.binding("colspanLayer",list.get(0).getGrainOrigin());
            pageTemplate.binding("companyName",list.get(0).getCompanyName());
        }

        if (PlatformUtil.isWindows()) {
            filePath = filePath.replaceAll("/+|\\\\+", "\\\\");
        } else {
            filePath = filePath.replaceAll("/+|\\\\+", "/");
        }
        File file = new File(filePath);
        File parentFile = file.getParentFile();
        if (!parentFile.exists()) {
            parentFile.mkdirs();
        }
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(file);
            pageTemplate.renderTo(fileOutputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ToolUtil.isNotEmpty(fileOutputStream)){
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

  转Excel到指定位置

/**
     * HTML生成xlsx，并读取到指定位置
     * @param response
     * @param filePath
     * @param sheetName
     * @param excelName
     * @return
     * @throws Exception
     */
private Workbook htmlToExcelToStr(HttpServletResponse response,String filePath,String sheetName,String excelName) throws Exception {
        StringBuilder html = new StringBuilder();
        InputStream is= new BufferedInputStream(new FileInputStream(filePath));
        Scanner s = new Scanner(is, "utf-8");
        while (s.hasNext()) {
            html.append(s.nextLine());
        }
        s.close();
        //easypoi方法
        Workbook workbook = ExcelXorHtmlUtil.htmlToExcel(html.toString(), ExcelType.XSSF);
        File savefile = new File(beetlProperties.getHtmlPathIn());
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream(beetlProperties.getHtmlPathIn()+"\\"+sheetName+".xlsx");
        workbook.write(fos);
        fos.close();
//        workbook = ExcelXorHtmlUtil.htmlToExcel(html.toString(), ExcelType.HSSF);
//        fos = new FileOutputStream(beetlProperties.getHtmlPathIn()+"\\"+sheetName+".xls");
//        workbook.write(fos);
//        fos.close();

        return workbook;

    }

未完待续。。。