java html table 转 excel，给予jdom 和 poi
maven 引入
工具类
import java.io.ByteArrayInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
/**根据table的html代码生成excel
* @param args
* zyn
* 2012-12-19 上午11:35:30
*/
public class TableToExcelUtil {
/\*\*
\*
\* @param sheetName
\* @param html
\* @param headNum表头的行数
\* @throws FileNotFoundException
\* zyn
\* 2012-12-21 下午1:44:02
\*/
@SuppressWarnings("unchecked")
public static void createExcelFormTable(String sheetName,String html,int headNum) throws FileNotFoundException{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
CellStyle headStyle = createHeadStyle(wb);
CellStyle bodyStyle = createBodyStyle(wb);
FileOutputStream os = new FileOutputStream("/Users/apple/Downloads/aa.xls");
SAXBuilder sb = new SAXBuilder();
html = html.replaceAll("\\n", "");
ByteArrayInputStream is = new ByteArrayInputStream(html.getBytes());
try {
Document document = sb.build(is);
//获取table节点
Element root = document.getRootElement();
//获取tr的list
List<Element> children = root.getChildren();
List<Element> trList = new ArrayList<>();
List<Element> head = children.get(0).getChildren();
trList.addAll(head);
List<Element> body = children.get(1).getChildren();
trList.addAll(body);
//循环创建行
for(int i=0;i<trList.size();i++){
HSSFRow row = sheet.createRow(i);
List<Element> tdList = trList.get(i).getChildren("td");
//该行td的序号
for(int ii=0;ii< tdList.size();ii++){
row.createCell(ii);
HSSFCell cell = row.getCell(ii);
//判断是否为表头，使用对应的excel格式
if(i<headNum){
cell.setCellStyle(headStyle);
}else{
cell.setCellStyle(bodyStyle);
}
cell.setCellValue(getInnerText(tdList.get(ii)));
}
}
List<CellRangeAddress> cellArea = getCellArea(trList);
if(!cellArea.isEmpty()) {
for (CellRangeAddress cellRangeAddress : cellArea) {
sheet.addMergedRegion(cellRangeAddress);
}
}
wb.write(os);
} catch (JDOMException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/\*\*
\* 导出excel表格二维数组：0为文字占用格，1为横向被合并格，2为纵向合并格
\* @param trList
\* @return
\* zyn
\* 2012-12-21 下午1:35:40
\*/
private static List<CellRangeAddress> getCellArea(List<Element> trList){
//获取table单元格矩阵
Element headtr = trList.get(0);
List<Element> headTdList = headtr.getChildren("td");
//每行的未经合并的单元格个数
int cols = 0;
for(Element e:headTdList){
System.out.println("#"+e.getText());
int colspan = Integer.valueOf(null==e.getAttributeValue("colspan")?"0":e.getAttributeValue("colspan"));
if(colspan==0){
colspan =1;
}
cols += colspan;
}
//初始化单元格矩阵
int\[\]\[\] area = new int\[trList.size()\]\[cols\];
List<CellRangeAddress> cellRangeAddresses = new ArrayList<>();
Element tr;
List<Element> tdList;
Element td;
int trsize = trList.size();
int tdsize;
int colspan;
int rowspan;
CellRangeAddress cellRangeAddress;
for(int row=0;row < trsize;row++){
tr = trList.get(row);
tdList = tr.getChildren("td");
tdsize = tdList.size();
for(int col=0;col<tdsize;col++){
td = tdList.get(col);
colspan = Integer.valueOf(null==td.getAttributeValue("colspan")?"0":td.getAttributeValue("colspan"));
if(colspan==0){
colspan =1;
}
rowspan = Integer.valueOf(null==td.getAttributeValue("rowspan")?"0":td.getAttributeValue("rowspan"));
if(rowspan==0){
rowspan = 1;
}
if(rowspan > 1) {
cellRangeAddress = new CellRangeAddress(row,row + rowspan - 1 ,col,col );
cellRangeAddresses.add(cellRangeAddress);
}
if(colspan > 1) {
cellRangeAddress = new CellRangeAddress(row,row ,col,col+colspan - 1 );
cellRangeAddresses.add(cellRangeAddress);
}
}
}
return cellRangeAddresses;
}
/\*\*-
\* 设置表头样式
\* @param wb
\* @return
\*/
private static CellStyle createHeadStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
Font headerFont = wb.createFont();
headerFont.setBold(true);
style.setAlignment(HorizontalAlignment.CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT\_CORNFLOWER\_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID\_FOREGROUND);
style.setFont(headerFont);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
/\*\*-
\* 设置表单记录样式
\* @param wb
\* @return
\*/
private static CellStyle createBodyStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
Font headerFont = wb.createFont();
headerFont.setBold(true);
style.setAlignment(HorizontalAlignment.CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT\_GREEN.getIndex());
style.setFillPattern(FillPatternType.SOLID\_FOREGROUND);
style.setFont(headerFont);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
private static String getInnerText(Element td){
String txt = "";
if(td.getText()==null || td.getText().equals("")){
if(null != td.getChildren()){
for(int i=0;i<td.getChildren().size();i++){
Element e = (Element)td.getChildren().get(i);
txt += getInnerText(e);
}
}
}else{
txt = td.getText();
}
return txt; }
public static void main(String\[\] args) throws FileNotFoundException {
TableToExcelUtil.createExcelFormTable("缴费统计", "<table><tr class=\\"titlebg\\"><td align=\\"center\\" nowrap=\\"nowrap\\" rowspan=\\"2\\" colspan=\\"1\\">序号</td><td align=\\"center\\" nowrap=\\"nowrap\\" rowspan=\\"2\\" colspan=\\"1\\">计划</td><td align=\\"center\\" nowrap=\\"nowrap\\" rowspan=\\"2\\" colspan=\\"1\\">部门名称</td><td align=\\"center\\" nowrap=\\"nowrap\\" colspan=\\"6\\">线上缴费</td><td align=\\"center\\" nowrap=\\"nowrap\\" colspan=\\"3\\">线下缴费</td><td align=\\"center\\" nowrap=\\"nowrap\\" rowspan=\\"2\\" colspan=\\"1\\">总计</td></tr><tr class=\\"titlebg\\"><td align=\\"center\\" nowrap=\\"nowrap\\">线上总计</td><td align=\\"center\\" nowrap=\\"nowrap\\">快钱</td><td align=\\"center\\" nowrap=\\"nowrap\\">支付宝</td><td align=\\"center\\" nowrap=\\"nowrap\\">平台余款</td><td align=\\"center\\" nowrap=\\"nowrap\\">激活卡</td><td align=\\"center\\" nowrap=\\"nowrap\\">其他</td><td align=\\"center\\" nowrap=\\"nowrap\\">线下总计</td><td align=\\"center\\" nowrap=\\"nowrap\\">本地缴费</td><td align=\\"center\\" nowrap=\\"nowrap\\">中心收费</td></tr><tr class=\\"whbg\\" orgPath=\\"01.25.01.\\" planId=\\"9e508516-5409-4b5d-a0d6-f86ba77eb79f\\" ><td align=\\"center\\" nowrap=\\"nowrap\\">1</td><td align=\\"center\\" nowrap=\\"nowrap\\">盐城2013年培训计划</td><td align=\\"center\\" nowrap=\\"nowrap\\">盐城市</td><td align=\\"center\\" nowrap=\\"nowrap\\">0</td><td align=\\"center\\" nowrap=\\"nowrap\\">0</td><td align=\\"center\\" nowrap=\\"nowrap\\">0</td><td align=\\"center\\" nowrap=\\"nowrap\\">0</td><td align=\\"center\\" nowrap=\\"nowrap\\">0</td><td align=\\"center\\" nowrap=\\"nowrap\\">0</td><td align=\\"center\\" nowrap=\\"nowrap\\"><a id=\\"0-12\\" href=\\"javascript:showDetail('0-12');\\">3</a></td><td align=\\"center\\" nowrap=\\"nowrap\\">0</td><td align=\\"center\\" nowrap=\\"nowrap\\">0</td><td align=\\"center\\" nowrap=\\"nowrap\\"><a id=\\"0-15\\" href=\\"javascript:showDetail('0-15');\\">3</a></td></tr><tr class=\\"whbg\\" orgPath=\\"all\\" planId=\\"all\\"><td align=\\"center\\" nowrap=\\"nowrap\\" colspan=\\"3\\" >总计</td><td align=\\"center\\" nowrap=\\"nowrap\\" >0</td><td align=\\"center\\" nowrap=\\"nowrap\\" >0</td><td align=\\"center\\" nowrap=\\"nowrap\\" >0</td><td align=\\"center\\" nowrap=\\"nowrap\\" >0</td><td align=\\"center\\" nowrap=\\"nowrap\\" >0</td><td align=\\"center\\" nowrap=\\"nowrap\\" >0</td><td align=\\"center\\" nowrap=\\"nowrap\\" ><a id=\\"4-6\\" href=\\"javascript:showDetail('4-6');\\">3</a></td><td align=\\"center\\" nowrap=\\"nowrap\\" >0</td><td align=\\"center\\" nowrap=\\"nowrap\\" >0</td><td align=\\"center\\" nowrap=\\"nowrap\\" ><a id=\\"4-9\\" href=\\"javascript:showDetail('4-9');\\">3</a></td></tr></table>", 2);
} }
手机扫一扫
移动阅读更方便
你可能感兴趣的文章