由于业务场景较复杂,导出内容需要和邮件内容同步。
最终选择了将邮件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, "&", "&");
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, "&", "&");
目前我这边只有到&符号的问题,有其他的符号同样可以replace掉。
参考:https://blog.youkuaiyun.com/sunguoqiang1213/article/details/110949393