关于Javahtmltable表格转excel
⼤致代码在⽹上百度的，稍微有些改动。完善单元格⽂字居中，列的宽度随单元格内容改变，合并单元格边框显⽰不全的问题。
package com.wisdombud.szxtyms.business.util;
import java.util.ArrayList;
import java.util.List;
import org.apachemons.lang3.StringUtils;
import org.apachemons.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.HSSFCellUtil;
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;
import com.wisdombud.szxtyms.util.CrossRangeCellMeta;
/****
* html转excel
* @author user
*
*/
public class ConvertHtml2Excel {
/**
* html表格转excel
*
* @param tableHtml 如
*            <table>
*            ..
*            </table>
* @return
*/
public static HSSFWorkbook table2Excel(String tableHtml) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
List<CrossRangeCellMeta> crossRowEleMetaLs = new ArrayList<CrossRangeCellMeta>();
int rowIndex = 0;
try {
Document data = DocumentHelper.parseText(tableHtml);
// ⽣成表头
Element thead = RootElement().element("thead");
HSSFCellStyle titleStyle = getTitleStyle(wb);
int ls=0;//列数
if (thead != null) {
List<Element> trLs = thead.elements("tr");
for (Element trEle : trLs) {
HSSFRow row = ateRow(rowIndex);
List<Element> thLs = trEle.elements("th");
ls=thLs.size();
makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
rowIndex++;
}
}
// ⽣成表体
Element tbody = RootElement().element("tbody");
HSSFCellStyle contentStyle = getContentStyle(wb);
if (tbody != null) {
List<Element> trLs = tbody.elements("tr");
for (Element trEle : trLs) {
HSSFRow row = ateRow(rowIndex);
List<Element> thLs = trEle.elements("th");
int cellIndex = makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);table设置内边框
List<Element> tdLs = trEle.elements("td");
makeRowCell(tdLs, rowIndex, row, cellIndex, contentStyle, crossRowEleMetaLs);
rowIndex++;
}
}
// 合并表头
for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {
sheet.addMergedRegion(new FirstRow(), LastRow(), FirstCol(), LastCol()));
setRegionStyle(sheet, new FirstRow(), LastRow(), FirstCol(), LastCol()),contentStyle);            }
for(int i=0;i<ls;i++){
sheet.autoSizeColumn(i, true);//设置列宽
}
} 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) {
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++) {// 当前⾏跨列处理（补单元格）
i++;
}
captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
}
Element thEle = (eleIndex);
String val = TextTrim();
if (StringUtils.isBlank(val)) {
Element e = thEle.element("a");
if (e != null) {
val = e.getTextTrim();
}
}
HSSFCell c = ateCell(i);
if (NumberUtils.isNumber(val)) {
c.setCellValue(Double.parseDouble(val));
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
} else {
c.setCellValue(val);
}
int rowSpan = Int(thEle.attributeValue("rowspan"), 1);
int colSpan = Int(thEle.attributeValue("colspan"), 1);
c.setCellStyle(cellStyle);
if (rowSpan > 1 || colSpan > 1) { // 存在跨⾏或跨列
crossRowEleMetaLs.add(new CrossRangeCellMeta(rowIndex, i, rowSpan, colSpan));
}
if (colSpan > 1) {// 当前⾏跨列处理（补单元格）
for (int j = 1; j < colSpan; j++) {
i++;
}
}
}
return i;
}
/**
* 设置合并单元格的边框样式
*
* @param sheet
* @param region
* @param cs
*/
public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region, HSSFCellStyle cs) {
for (int i = FirstRow(); i <= LastRow(); i++) {
HSSFRow row = Row(i, sheet);
for (int j = FirstColumn(); j <= LastColumn(); j++) {
HSSFCell cell = Cell(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 (FirstRow() < rowIndex && LastRow() >= rowIndex) {
if (FirstCol() <= colIndex && LastCol() >= colIndex) {
captureCellSize = LastCol() - 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 = ateCellStyle();
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 = ateFont();
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);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//⽔平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
return style;
}
}
封装跨⾏元素的信息
package com.wisdombud.szxtyms.util;
/**
* table转excel
* 跨⾏元素元数据
public class CrossRangeCellMeta {
public CrossRangeCellMeta(int firstRowIndex, int firstColIndex, int rowSpan, int colSpan) {        super();
this.firstRowIndex = firstRowIndex;
this.firstColIndex = firstColIndex;
}
private int firstRowIndex;
private int firstColIndex;
private int rowSpan;// 跨越⾏数
private int colSpan;// 跨越列数
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;
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。