HTML Table to Excel

1.要导出的table

<table>
  <tr>
    <td>姓名</td>
    <td>邮箱</td>
    <td>电话</td>
  </tr>
  <tr>
    <td>高sss</td>
    <td>xxx@xxx.com.cn</td>
    <td>xxxx</td>
  </tr>
  <tr>
    <td>Allen</td>
    <td>xxx@foxmail.com</td>
    <td>xxxx</td>
  </tr>
</table>

2.导出的脚本实现

<a class="foot_btn mt11 ml10 fl" id="exportExcel" style="margin-top: 10px;margin-left:5px;">导出Excel</a>
<form action="${ctx }/common/exportExcel" id ="exportExcelForm" method="post">
    <input type="hidden" name = "fileName" value="${year }年${month }月日报提交情况统计.xls">
    <input type="hidden" name = "sheetName" value="${year }年${month }月">
    <input type="hidden" name = "html"/>
</form>
<script type="text/javascript">
$("#exportExcel").click(function(){
    $("input[name='html']").val($("#dataTable").prop("outerHTML"));
    
    $("#exportExcelForm").submit();
});
</script>

3.后台Java

@Controller
@RequestMapping(value = "common/exportExcel")
public class CommonUtilController {
    private static Logger logger = Logger.getLogger(CommonUtilController.class);
    
    @RequestMapping(method = RequestMethod.POST)
    @ResponseBody
    public void exportExcel(String fileName,String sheetName,String html,
            HttpServletResponse response){
        
        logger.info("fileName,sheetName" + fileName + "," + sheetName);
        
        try {
            response.setContentType("application/octet-stream;charset=UTF-8"); 
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            //客户端不缓存   
            response.addHeader("Pargam", "no-cache");   
            response.addHeader("Cache-Control", "no-cache");
            
            //执行导出
            HTML2Excel.excute(html, sheetName,response.getOutputStream());
            
            response.getOutputStream().flush();
            response.getOutputStream().close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }  
    }
}

4.HTML2Excel工具类

public class HTML2Excel {
    
    //使用JSON获取HTML的DOM树结构
    private static Document getDocByHtml(String html) throws IOException {
        Document doc = Jsoup.parse(html);
        return doc;
    }

   //如果有单元格合并，则处理之
    private static void mergeColRow(Elements trs, WritableSheet sheet)
            throws RowsExceededException, WriteException {
        int[][] rowhb = new int[300][50];
        for (int i = 0; i < trs.size(); i++) {
            Element tr = trs.get(i);
            Elements cells = new Elements();
            Elements tds = tr.getElementsByTag("td");
            if(tds == null || tds.size() > 0){
                cells.addAll(tds);
            }
            
            Elements ths = tr.getElementsByTag("th");
            if(ths == null || ths.size() > 0){
                cells.addAll(ths);
            }

            int realColNum = 0;
            for (int j = 0; j < cells.size(); j++) {
                Element td = cells.get(j);
                if (rowhb[i][realColNum] != 0) {
                    realColNum = getRealColNum(rowhb, i, realColNum);
                }
                int rowspan = 1;
                int colspan = 1;
                if (td.attr("rowspan") != "") {
                    rowspan = Integer.parseInt(td.attr("rowspan"));
                }
                if (td.attr("colspan") != "") {
                    colspan = Integer.parseInt(td.attr("colspan"));
                }
                String text = td.text();
                String title = td.attr("title");
                drawMegerCell(rowspan, colspan, sheet, realColNum, i, text,title, rowhb);
                realColNum = realColNum + colspan;
            }
        }
    }

  //填充表格数据
    private static void drawMegerCell(int rowspan, int colspan,
            WritableSheet sheet, int realColNum, int realRowNum, String text,String title,
            int[][] rowhb) throws RowsExceededException, WriteException {

        for (int i = 0; i < rowspan; i++) {
            for (int j = 0; j < colspan; j++) {
                if (i != 0 || j != 0) {
                    text = "";
                }
                Label label = new Label(realColNum + j, realRowNum + i, text);
                WritableFont countents = new WritableFont(WritableFont.TIMES,10);
                WritableCellFormat cellf = new WritableCellFormat(countents);
                cellf.setAlignment(jxl.format.Alignment.CENTRE);
                cellf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
                label.setCellFormat(cellf);
                sheet.addCell(label);
                rowhb[realRowNum + i][realColNum + j] = 1;
            }
        }
        sheet.mergeCells(realColNum, realRowNum, realColNum + colspan - 1,realRowNum + rowspan - 1);
    }

    private static int getRealColNum(int[][] rowhb, int i, int realColNum) {
        while (rowhb[i][realColNum] != 0) {
            realColNum++;
        }
        return realColNum;
    }

   //设置表格宽度
    private static void setColWidth(Elements colgroups, WritableSheet sheet) {
        if (colgroups.size() > 0) {
            Element colgroup = colgroups.get(0);
            Elements cols = colgroup.getElementsByTag("col");
            for (int i = 0; i < cols.size(); i++) {
                Element col = cols.get(i);
                String strwd = col.attr("width");
                if (col.attr("width") != "") {
                    int wd = Integer.parseInt(strwd);
                    sheet.setColumnView(i, wd / 8);
                }

            }

        }
    }
    
    //主要接口
    public static void excute(String html, String sheetName, OutputStream os)
            throws IOException {
        
        //将转义的字符还原
        html = html.replaceAll("&lt;", "<").replaceAll("&gt;", ">")
                .replaceAll("&apos;", "\\").replaceAll("&amp;", "&")
                .replaceAll("&quot;", "\"");
        
        //获取html的dom树
        Document doc = getDocByHtml(html);
        
        Elements tables = doc.getElementsByTag("table");
        if (tables.size() == 0) {
            return;
        }
        
        //只能取一个表格
        Element table = tables.get(0);
        Elements trs = table.getElementsByTag("tr");
        
        Elements colgroups = table.getElementsByTag("colgroup");

        try {
            WritableWorkbook book = Workbook.createWorkbook(os);
            WritableSheet sheet = book.createSheet(sheetName, 0);
            
            setColWidth(colgroups, sheet);
            mergeColRow(trs, sheet);
            
        book.write();
        book.close();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

}