html页面表格导出为excel表格实现 excel POI

/**
 * 下载文件，通过html页面直接将表导出
 * 
 * @param response
 * @param request
 * @param fileName
 * @param html
 * @throws Exception
 */
@RequestMapping(params = "downloadFile")
public void downloadFile(HttpServletResponse response, HttpServletRequest request,String fileName,String html,String moneyCol,int headEndRow)
        throws Exception {

    final String userAgent = request.getHeader("USER-AGENT");
    if(StringUtils.contains(userAgent, "MSIE")){//IE浏览器
        fileName = URLEncoder.encode(fileName,"UTF-8");
    }else if(StringUtils.contains(userAgent, "Mozilla")){//google,火狐浏览器
        fileName = new String(fileName.getBytes(), "ISO8859-1");
    }else{
        fileName = URLEncoder.encode(fileName,"UTF-8");//其他浏览器
    }
    response.setContentType("application/octet-stream");
    response.addHeader("Content-Disposition", "attachment;" + "filename=\"" + fileName + "\"");
    ServletOutputStream outputStream = response.getOutputStream();
    String cnt =  "\n";
    List<Integer> c =new ArrayList<>();
    if(StringUtils.isNotBlank(moneyCol)){
        String[] split = moneyCol.split(",");
        for (String s : split) {
            c.add(Integer.parseInt(s));
        }
    }
    html = removeBlank(html);
    html = html.replaceAll("&nbsp;", " ");
    html = html.replaceAll("<[\\s]*?br[^>]*?>|<[\\s]*?\\/[\\s]*?br[\\s]*?>", cnt);
    HSSFWorkbook hssfWorkbook = ConvertHtml2Excel.table2Excel(html,c,headEndRow);
    hssfWorkbook.write(outputStream);

}

工具类代码如下：

package cn.piesat.utils;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;


/****
 * html转excel
 * @author user
 *
 */
public class ConvertHtml2Excel {
    /**
     * html表格转excel
     *
     * @param tableHtml 如
     *                  <table>
     *                  ..
     *                  </table>
     * @return
     */
    public static HSSFWorkbook table2Excel(String tableHtml, List<Integer> moneyCols,int headEndRow) {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        List<CrossRangeCellMeta> crossRowEleMetaLs = new ArrayList<CrossRangeCellMeta>();
        int rowIndex = 0;
        try {
            Document data = DocumentHelper.parseText(tableHtml);

            HSSFCellStyle contentStyle = getContentStyle(wb);

//            // 生成信息列
//            Element info = data.getRootElement().element("info");
//            if (info != null) {
//                List<Element> trLsb = info.elements("tr");
//                for (Element trEle : trLsb) {
//                    HSSFRow row = sheet.createRow(rowIndex);
//                    List<Element> thLs = trEle.elements("th");
//                    int cellIndex = makeRowCell(thLs, rowIndex, row, 0, contentStyle, crossRowEleMetaLs, false);
//                    List<Element> tdLs = trEle.elements("td");
//                    makeRowCell(tdLs, rowIndex, row, cellIndex, contentStyle, crossRowEleMetaLs, false);
//                    rowIndex++;
//                }
//            }
            // 生成表头
            Element thead = data.getRootElement().element("thead");
            HSSFCellStyle titleStyle = getTitleStyle(wb);
            int ls = 0;//列数
            if (thead != null) {
                List<Element> trLs = thead.elements("tr");
                for (Element trEle : trLs) {
                    HSSFCellStyle style = rowIndex<=headEndRow?titleStyle:contentStyle;

                    HSSFRow row = sheet.createRow(rowIndex);
                    List<Element> thLs = trEle.elements("th");
                    if(ls<thLs.size())
                    ls = thLs.size();
                    int cellIndex = makeRowCell(thLs, rowIndex, row, 0, style, crossRowEleMetaLs, true,moneyCols);
                    List<Element> tdLs = trEle.elements("td");
                    if(ls<ls+tdLs.size())
                    ls = ls + tdLs.size();
                    makeRowCell(tdLs, rowIndex, row, cellIndex, style, crossRowEleMetaLs, true,moneyCols);
                    rowIndex++;
                }
            }


            // 生成表体
            Element tbody = data.getRootElement().element("tbody");
            if (tbody != null) {
                List<Element> trBody = tbody.elements("tr");
                for (Element trEle : trBody) {
                    HSSFCellStyle style = rowIndex<=headEndRow?titleStyle:contentStyle;
                    HSSFRow row = sheet.createRow(rowIndex);
                    List<Element> thLs = trEle.elements("th");
                    if(ls<thLs.size())
                        ls = thLs.size();
                    int cellIndex = makeRowCell(thLs, rowIndex, row, 0, style, crossRowEleMetaLs, false,moneyCols);
                    List<Element> tdLs = trEle.elements("td");
                    if(ls<ls+tdLs.size())
                        ls = ls + tdLs.size();
                    makeRowCell(tdLs, rowIndex, row, cellIndex, style, crossRowEleMetaLs, false,moneyCols);
                    rowIndex++;
                }
            }
            // 生成表体
            Element tfoot = data.getRootElement().element("tfoot");
            if (tfoot != null) {
                List<Element> trFoot = tfoot.elements("tr");
                for (Element trEle : trFoot) {
                    HSSFCellStyle style = rowIndex<=headEndRow?titleStyle:contentStyle;
                    HSSFRow row = sheet.createRow(rowIndex);
                    List<Element> thLs = trEle.elements("th");
                    if(ls<thLs.size())
                        ls = thLs.size();
                    int cellIndex = makeRowCell(thLs, rowIndex, row, 0, style, crossRowEleMetaLs, false,moneyCols);
                    List<Element> tdLs = trEle.elements("td");
                    if(ls<ls+tdLs.size())
                        ls = ls + tdLs.size();
                    makeRowCell(tdLs, rowIndex, row, cellIndex, style, crossRowEleMetaLs, false,moneyCols);
                    rowIndex++;
                }
            }
            // 合并表头
            for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {
                sheet.addMergedRegion(new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol()));
                HSSFCellStyle mergeStyle;
                if (crcm.isInHead()) {
                    mergeStyle = titleStyle;
                } else {
                    mergeStyle = contentStyle;
                }
                setRegionStyle(sheet, new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol()), mergeStyle);
            }
            for (int i = 0; i < ls; i++) {
                sheet.autoSizeColumn(i, true);//设置列宽
                sheet.setColumnWidth(i,sheet.getColumnWidth(i)*15/10);
            }
        } catch (DocumentException e) {
            e.printStackTrace();
        }

        return wb;
    }

    /**
     * 生产行内容
     *
     * @return 最后一列的cell index
     */
    /**
     * @param tdLs              th或者td集合
     * @param rowIndex          行号
     * @param row               POI行对象
     * @param startCellIndex
     * @param cellStyle         样式
     * @param crossRowEleMetaLs 跨行元数据集合
     * @return
     */
    private static int makeRowCell(List<Element> tdLs, int rowIndex, HSSFRow row, int startCellIndex, HSSFCellStyle cellStyle,
                                   List<CrossRangeCellMeta> crossRowEleMetaLs, boolean inHead, List<Integer> moneyCols) {
        int i = startCellIndex;
        for (int eleIndex = 0; eleIndex < tdLs.size(); i++, eleIndex++) {
            int captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
            while (captureCellSize > 0) {
                for (int j = 0; j < captureCellSize; j++) {// 当前行跨列处理（补单元格）
                    row.createCell(i);
                    i++;
                }
                captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
            }
            Element thEle = tdLs.get(eleIndex);
            String val = thEle.getText();
            if (StringUtils.isBlank(val)) {
                Element e = thEle.element("a");
                if (e != null) {
                    val = e.getText();
                }
            }
            HSSFCell c = row.createCell(i);
            if (NumberUtils.isNumber(val)) {
                if (moneyCols != null && moneyCols.contains(i)) {
                    c.setCellType(HSSFCell.CELL_TYPE_STRING);
                    c.setCellValue(val);
                } else {
                    c.setCellValue(Double.parseDouble(val));
                    c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                }

            } else {
                c.setCellType(HSSFCell.CELL_TYPE_STRING);
                c.setCellValue(new HSSFRichTextString(val));
            }
            int rowSpan = NumberUtils.toInt(thEle.attributeValue("rowspan"), 1);
            int colSpan = NumberUtils.toInt(thEle.attributeValue("colspan"), 1);
            c.setCellStyle(cellStyle);
            if (rowSpan > 1 || colSpan > 1) { // 存在跨行或跨列
                crossRowEleMetaLs.add(new CrossRangeCellMeta(rowIndex, i, rowSpan, colSpan, inHead));
            }
            if (colSpan > 1) {// 当前行跨列处理（补单元格）
                for (int j = 1; j < colSpan; j++) {
                    i++;
                    row.createCell(i);
                }
            }
        }
        return i;
    }

    /**
     * 设置合并单元格的边框样式
     *
     * @param sheet
     * @param region
     * @param cs
     */
    public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region, HSSFCellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            HSSFRow row = HSSFCellUtil.getRow(i, sheet);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
                cell.setCellStyle(cs);
            }
        }
    }

    /**
     * 获得因rowSpan占据的单元格
     *
     * @param rowIndex          行号
     * @param colIndex          列号
     * @param crossRowEleMetaLs 跨行列元数据
     * @return 当前行在某列需要占据单元格
     */
    private static int getCaptureCellSize(int rowIndex, int colIndex, List<CrossRangeCellMeta> crossRowEleMetaLs) {
        int captureCellSize = 0;
        for (CrossRangeCellMeta crossRangeCellMeta : crossRowEleMetaLs) {
            if (crossRangeCellMeta.getFirstRow() < rowIndex && crossRangeCellMeta.getLastRow() >= rowIndex) {
                if (crossRangeCellMeta.getFirstCol() <= colIndex && crossRangeCellMeta.getLastCol() >= colIndex) {
                    captureCellSize = crossRangeCellMeta.getLastCol() - colIndex + 1;
                }
            }
        }
        return captureCellSize;
    }

    /**
     * 获得标题样式
     *
     * @param workbook
     * @return
     */
    private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
        short titlebackgroundcolor = HSSFColor.GREY_25_PERCENT.index;
        short fontSize = 11;
        String fontName = "宋体";
        HSSFCellStyle style = workbook.createCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(titlebackgroundcolor);// 背景色
        HSSFFont font = workbook.createFont();
//        font.setFontName(fontName);
        font.setFontHeightInPoints(fontSize);
//        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        return style;
    }

    /**
     * 获得内容样式
     *
     * @param wb
     * @return
     */
    private static HSSFCellStyle getContentStyle(HSSFWorkbook wb) {
        short fontSize = 11;
//        String fontName = "宋体";
        HSSFCellStyle style = wb.createCellStyle();
        style.setBorderBottom((short) 1);
        style.setBorderTop((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);
        HSSFFont font = wb.createFont();
//        font.setFontName(fontName);
        font.setFontHeightInPoints(fontSize);
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        style.setWrapText(true);
        return style;
    }

    public static void main(String[] args) throws IOException {
        String cnt = "\n";
        List<Integer> c =new ArrayList<>();
        c.add(1);
        File file = new File("D:\\\\test\\\\test.html");
        String tableHtml = FileUtils.readFileToString(file, "utf-8");
        tableHtml = tableHtml.replaceAll("<[\\s]*?br[^>]*?>|<[\\s]*?\\/[\\s]*?br[\\s]*?>", cnt);
        HSSFWorkbook hssfWorkbook = ConvertHtml2Excel.table2Excel(tableHtml,c,1);
        hssfWorkbook.write(FileUtils.openOutputStream(new File("D:\\test\\test1.xls")));
    }
}

package cn.piesat.utils;

/**
 * table转excel
 * 跨行元素元数据
 */
public class CrossRangeCellMeta {

    public CrossRangeCellMeta(int firstRowIndex, int firstColIndex, int rowSpan, int colSpan, boolean inHead) {
        super();
        this.firstRowIndex = firstRowIndex;
        this.firstColIndex = firstColIndex;
        this.rowSpan = rowSpan;
        this.colSpan = colSpan;
        this.inHead = inHead;
    }

    private int firstRowIndex;
    private int firstColIndex;
    private int rowSpan;// 跨越行数
    private int colSpan;// 跨越列数
    private boolean inHead;

    public boolean isInHead() {
        return inHead;
    }

    public CrossRangeCellMeta setInHead(boolean inHead) {
        this.inHead = inHead;
        return this;
    }

    public int getFirstRow() {
        return firstRowIndex;
    }

    public int getLastRow() {
        return firstRowIndex + rowSpan - 1;
    }

    public int getFirstCol() {
        return firstColIndex;
    }

    public int getLastCol() {
        return firstColIndex + colSpan - 1;
    }

    public int getColSpan() {
        return colSpan;
    }
}

js代码实现,若无法将页面表格导出，在页面的表格<table></table>中加<thead></thead>标签进行调试

function exportHtml2Excel(fileName,moneyCol,headEndRow) {
    var dataform = $("<form id='export' method='post' action='/testController.do?downloadFile'>" +
        " <input type='hidden' id='fileName' name='fileName'>" +
        " <input type='hidden' id='html' name='html'>" +
        " <input type='hidden' id='moneyCol' name='moneyCol'>" +
        " <input type='hidden' id='headEndRow' name='headEndRow'><form>");
    dataform.appendTo($("body"));
    if (fileName)
        $("#fileName").val(fileName);
    else
    $("#fileName").val("test.xls");
    $("#headEndRow").val(headEndRow);
    $("#moneyCol").val(moneyCol);
    $("#html").val($("table").get(0).outerHTML);
    $("#export").submit()
    dataform.remove()
}

页面添加导出按钮调用，方法参数说明，参数1为设置导出表格名称，参数2为设置第几列的表格数保留一位小数，可设置多列，这里设置第7列保留一位小数，可设置多个如‘1,2,3’，参数3为表头结束行，表格下标从0开始，所以这里表头只占一行，结束行为0      此方法可修改做参考页面直接导出excel，简化复杂表格行列计算，导出

<button onclick="exportHtml2Excel('测试表格.xls','7',0)">导出</button>

实现效果如下：