excel 转 html table,将html table 转成 excel

1 package com.sun.office.excel;

2

3 /**

4 * 跨行元素元数据

5 *

6 */

7 public class CrossRangeCellMeta {

8

9 public CrossRangeCellMeta(int firstRowIndex, int firstColIndex, int rowSpan, int colSpan) {

10 super();

11 this.firstRowIndex = firstRowIndex;

12 this.firstColIndex = firstColIndex;

13 this.rowSpan = rowSpan;

14 this.colSpan = colSpan;

15 }

16

17 private int firstRowIndex;

18 private int firstColIndex;

19 private int rowSpan;// 跨越行数

20 private int colSpan;// 跨越列数

21

22 int getFirstRow() {

23 return firstRowIndex;

24 }

25

26 int getLastRow() {

27 return firstRowIndex + rowSpan - 1;

28 }

29

30 int getFirstCol() {

31 return firstColIndex;

32 }

33

34 int getLastCol() {

35 return firstColIndex + colSpan - 1;

36 }

37

38 int getColSpan(){

39 return colSpan;

40 }

41 }

package com.sun.office.excel;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.math.NumberUtils;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.util.CellRangeAddress;

import org.dom4j.Document;

import org.dom4j.DocumentException;

import org.dom4j.DocumentHelper;

import org.dom4j.Element;

/**

* 将html table 转成 excel

*

* 记录下来所占的行和列，然后填充合并

*/

public class ConvertHtml2Excel {

public static void main(String[] args) {

byte[] bs = null;

try {

FileInputStream fis = new FileInputStream(new File(ConvertHtml2Excel.class.getResource("./a.html").getPath()));

bs = new byte[fis.available()];

fis.read(bs);

fis.close();

} catch (Exception e1) {

e1.printStackTrace();

}

String c = new String(bs);

HSSFWorkbook wb = table2Excel(c);

try {

FileOutputStream fos = new FileOutputStream(new File("1.xls"));

wb.write(fos);

fos.flush();

fos.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

/**

* html表格转excel

*

* @param tableHtml 如

*

* ..

*

* @return

*/

public static HSSFWorkbook table2Excel(String tableHtml) {

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

List 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 trLs = thead.elements("tr");

for (Element trEle : trLs) {

HSSFRow row = sheet.createRow(rowIndex);

List thLs = trEle.elements("th");

makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);

rowIndex++;

}

}

// 生成表体

Element tbody = data.getRootElement().element("tbody");

if (tbody != null) {

HSSFCellStyle contentStyle = getContentStyle(wb);

List trLs = tbody.elements("tr");

for (Element trEle : trLs) {

HSSFRow row = sheet.createRow(rowIndex);

List thLs = trEle.elements("th");

int cellIndex = makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);

List tdLs = trEle.elements("td");

makeRowCell(tdLs, rowIndex, row, cellIndex, contentStyle, crossRowEleMetaLs);

rowIndex++;

}

}

// 合并表头

for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {

sheet.addMergedRegion(new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol()));

}

} 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 tdLs, int rowIndex, HSSFRow row, int startCellIndex, HSSFCellStyle cellStyle,

List 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 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 = 12;

String fontName = "宋体";

HSSFCellStyle style = workbook.createCellStyle();

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style.setBorderBottom((short) 1);

style.setBorderTop((short) 1);

style.setBorderLeft((short) 1);

style.setBorderRight((short) 1);

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 = 12;

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);

return style;

}

}

基本思路：

逐行遍历，记录下单元格所占的行和列，根据行列去填充空格，合并单元格

来源：https://www.cnblogs.com/sun-space/p/5696986.html