将Html字符串导出成excel的方法和遇到的坑

由于业务场景较复杂,导出内容需要和邮件内容同步。
最终选择了将邮件table导成excel的形式。
差不多是通过dom4j这个工具把excel画出来。

<dependency>
      <groupId>org.dom4j</groupId>
      <artifactId>dom4j</artifactId>
      <version>2.1.3</version>
  </dependency>


import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 将html table 转成 excel
 *
 * 记录下来所占的行和列,然后填充合并
 */
@Slf4j
public class ConvertHtml2Excel {


    /**
     * 文件编码
     */
    private static final String ENCODING = "utf-8";
    /**
     * response的头的key
     */
    private static final String HEADER_KEY = "Content-disposition";
    /**
     * response的头的value
     */
    private static final String HEADER_VALUE = "attachment;filename={0}.xls";


    /**
     * html表格转excel
     *
     * @param tableHtml 如
     *            <table>
     *            ..
     *            </table>
     * @return
     */
    public static HSSFWorkbook table2Excel(String tableHtml) {
        tableHtml = StringUtils.replace(tableHtml, "&", "&amp;");
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();

        List<CrossRangeCellMeta> crossRowEleMetaLs = new ArrayList<>();
        int rowIndex = 0;
        try {
            Document data = DocumentHelper.parseText(tableHtml);
            // 生成表头
            Element thead = data.getRootElement().element("thead");
            HSSFCellStyle titleStyle = getTitleStyle(wb);
            if (thead != null) {
                List<Element> trLs = thead.elements("tr");
                for (Element trEle : trLs) {
                    HSSFRow row = sheet.createRow(rowIndex);
                    List<Element> thLs = trEle.elements("th");
                    makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
                    row.setHeightInPoints(17);
                    rowIndex++;
                }
            }
            // 生成表体
            Element tbody = data.getRootElement().element("tbody");
            if (tbody != null) {
                HSSFCellStyle contentStyle = getContentStyle(wb);
                List<Element> trLs = tbody.elements("tr");
                for (Element trEle : trLs) {
                    HSSFRow row = sheet.createRow(rowIndex);
                    List<Element> thLs = trEle.elements("th");
                    int cellIndex = makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
                    List<Element> tdLs = trEle.elements("td");
                    makeRowCell(tdLs, rowIndex, row, cellIndex, contentStyle, crossRowEleMetaLs);
                    row.setHeightInPoints(18);
                    rowIndex++;
                }
            }
            // 合并表头
            for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {
                sheet.addMergedRegion(new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol()));
            }
        } catch (DocumentException e) {
            e.printStackTrace();
        }
        //自动调整列宽
        for (int i = 0; i < 15; i++) {
            sheet.autoSizeColumn((short)i);
        }
        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) {
        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.getTextTrim();
            if (StringUtils.isBlank(val)) {
                Element e = thEle.element("a");
                if (e != null) {
                    val = e.getTextTrim();
                }
            }
            HSSFCell c = row.createCell(i);
            if (NumberUtils.isNumber(val)) {
                c.setCellValue(Double.parseDouble(val));
                c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            } else {
                c.setCellValue(val);
            }
            c.setCellStyle(cellStyle);
            int rowSpan = NumberUtils.toInt(thEle.attributeValue("rowspan"), 1);
            int colSpan = NumberUtils.toInt(thEle.attributeValue("colspan"), 1);
            if (rowSpan > 1 || colSpan > 1) { // 存在跨行或跨列
                crossRowEleMetaLs.add(new CrossRangeCellMeta(rowIndex, i, rowSpan, colSpan));
            }
            if (colSpan > 1) {// 当前行跨列处理(补单元格)
                for (int j = 1; j < colSpan; j++) {
                    i++;
                    row.createCell(i);
                }
            }
        }
        return i;
    }

    /**
     * 获得因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 fontSize = 14;
        String fontName = "宋体";
        // 设置样式
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
        style.setAlignment(HorizontalAlignment.CENTER);// 水平

        HSSFFont font = workbook.createFont();
        font.setFontName(fontName);
        font.setFontHeightInPoints(fontSize);
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    /**
     * 获得内容样式
     *
     * @param wb
     * @return
     */
    private static HSSFCellStyle getContentStyle(HSSFWorkbook wb) {
        short fontSize = 12;
        String fontName = "宋体";
        HSSFCellStyle style = wb.createCellStyle();
        // 设置样式
        HSSFFont font = wb.createFont();
        font.setFontName(fontName);
        font.setFontHeightInPoints(fontSize);
        style.setFont(font);
        return style;
    }


    public static void convertAndExport(String fileName, HttpServletResponse response, String html) {
        HSSFWorkbook sheets = table2Excel(html);
        try {
            fileName = URLEncoder.encode(fileName + DateUtil.formatDate(new Date(), DateConstant.DEFAULT_DATE_PATTERN), ENCODING);
        } catch (UnsupportedEncodingException e) {
            log.error("文件名格式化异常", e);
            throw SystemException.FILE_READ_ERROR;
        }
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding(ENCODING);
        response.setHeader(HEADER_KEY, MessageFormat.format(HEADER_VALUE, fileName));
        try {
            sheets.write(response.getOutputStream());
            response.getOutputStream().flush();
        } catch (IOException e) {
            log.error(e.toString(), e);
        }finally {
            try {
                sheets.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }




//    public static void main(String[] args) {
//
//        String c = new String("<table width=\"95%\" border=\"1\" style=\"border-collapse:collapse;text-align:center\"><thead><tr><th>项目编码</th><th>项目类型</th><th>项目名称</th><th>申办方</th><th>收票方</th><th>实收币种</th><th>实收金额(外币)</th><th>实收金额(RMB)</th><th>开票币种</th><th>开票金额(外币)</th><th>开票金额(RMB)</th><th>收款& 日期</th><th>开票& 日期</th><th>收款说明</th><th>备注</th></tr></thead><tbody><tr><td>HZ201507DZY014</td><td></td><td>Study9&8</td><td>71,72,73</td><td>测试用户1454</td><td>CNY</td><td></td><td>3006.00</td><td rowspan=\"3\">CNY</td><td rowspan=\"3\"></td><td rowspan=\"3\">10000.00</td><td>2021-11-10</td><td rowspan=\"3\">2021-11-01</td><td rowspan=\"3\">study98主-节&点1</td><td></td></tr><tr><td>HZ201507DZY014</td><td></td><td>Study98</td><td>71,72,73</td><td>测试用户1454</td><td>CNY</td><td></td><td>2004.00</td><td>2021-11-10</td><td>已有收款记录;收款日期{2021-11-08},收款金额{1000.00};收款日期{2021-11-10},收款金额{3006.00};</td></tr><tr><td>HZ201507DZY014</td><td></td><td>Study98</td><td>71,72,73</td><td>测试用户1454</td><td>CNY</td><td></td><td>1000.00</td><td>2021-11-08</td><td>已有收款记录;收款日期{2021-11-08},收款金额{1000.00};收款日期{2021-11-10},收款金额{3006.00};</td></tr><tr><td>HZ201507DZY014</td><td></td><td>Study98</td><td>71,72,73</td><td>测试用户1454</td><td>SEK</td><td></td><td>1500.00</td><td rowspan=\"2\">CNY</td><td rowspan=\"2\"></td><td rowspan=\"2\">400.00</td><td>2021-11-08</td><td rowspan=\"2\">2021-11-02</td><td rowspan=\"2\">study98补R-节点1</td><td>已有收款记录;收款日期{2021-11-08},收款金额{1500.00};</td></tr><tr><td>HZ201507DZY014</td><td></td><td>Study98</td><td>71,72,73</td><td>测试用户1454</td><td>CNY</td><td></td><td>700.00</td><td>2021-11-09</td><td>已有收款记录;收款日期{2021-11-08},收款金额{1500.00};</td></tr><tr><td>HZ201507DZY014</td><td></td><td>Study98</td><td>71,72,73</td><td>4</td><td>AUD</td><td>9000.00</td><td>34200.00</td><td rowspan=\"1\">ENG</td><td rowspan=\"1\">900.00</td><td rowspan=\"1\">8100.00</td><td>2021-11-08</td><td rowspan=\"1\">2021-11-02</td><td rowspan=\"1\">1st PTF</td><td></td></tr></tbody></table>");
//        HSSFWorkbook wb = table2Excel(c);
//        try {
//            FileOutputStream fos = new FileOutputStream(new File("4.xls"));
//            wb.write(fos);
//            fos.flush();
//            fos.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//
//    }

    static class CrossRangeCellMeta {

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

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

        int getFirstRow() {
            return firstRowIndex;
        }

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

        int getFirstCol() {
            return firstColIndex;
        }

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

        int getColSpan(){
            return colSpan;
        }
    }


}


其中在转换的过程中,加了这么一条过滤&特殊字符,否则在html字符串存在&特殊符号就会报错。

tableHtml = StringUtils.replace(tableHtml, "&", "&amp;");

在这里插入图片描述
目前我这边只有到&符号的问题,有其他的符号同样可以replace掉。

参考:https://blog.youkuaiyun.com/sunguoqiang1213/article/details/110949393

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值