把table表格内容导出为excel
?package com.chinahrt.report.export;import java.io.ByteArrayInputStream;import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;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.hssf.util.CellRangeAddress;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Workbook;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 */public void createExcelFormTable(String sheetName,String html,int headNum) throws FileNotFoundException{HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet(sheetName);CellStyle headStyle = this.createHeadStyle(wb);CellStyle bodyStyle = this.createBodyStyle(wb);FileOutputStream os = new FileOutputStream("c:\\table.xls");SAXBuilder sb = new SAXBuilder();ByteArrayInputStream is = new ByteArrayInputStream(html.getBytes());try {org.jdom.Document document = sb.build(is);//获取table节点Element root = document.getRootElement();//获取tr的listList<Element> trList = root.getChildren("tr");int[][] area = getCellArea(trList);//循环创建行for(int i=0;i<trList.size();i++){HSSFRow row = sheet.createRow(i);List<Element> tdList = trList.get(i).getChildren("td");//该行td的序号int tdIndex = 0;for(int ii=0;ii<area[i].length;ii++){row.createCell(ii);HSSFCell cell = row.getCell(ii);//判断是否为表头，使用对应的excel格式if(i<headNum){cell.setCellStyle(headStyle);}else{cell.setCellStyle(bodyStyle);}//如果对应的矩阵数字为1，则和横向前一单元格合并if(area[i][ii]==1){sheet.addMergedRegion(new CellRangeAddress(i,i,ii-1,ii));}else if(area[i][ii]==2){//如果对应的矩阵数字为2，则和纵向的前一单元格合并sheet.addMergedRegion(new CellRangeAddress(i-1,i,ii,ii));}else{//如果为0，显示td中对应的文字,td序号加1cell.setCellValue(this.getInnerText(tdList.get(tdIndex)));tdIndex ++;}}}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 int[][] getCellArea(List<Element> trList){//获取table单元格矩阵Element headtr = trList.get(0);List<Element> headTdList = headtr.getChildren("td");//每行的未经合并的单元格个数int cols = 0;for(Element e:headTdList){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];for(int i=0;i<trList.size();i++){Element tr = trList.get(i);List<Element> tdList = tr.getChildren("td");//该行到ii个单元格为止被合并的单元格个数int rowColspan = 0;for(int ii=0;ii<tdList.size();ii++){//本单元格跨度计算前的td数int oldIndex = ii+rowColspan;Element td = tdList.get(ii);int colspan = Integer.valueOf(null==td.getAttributeValue("colspan")?"0":td.getAttributeValue("colspan"));//colspan为0或者1证明未合并colspan = colspan>1?colspan:1;rowColspan += colspan-1;//单元格需要被横向合并声明为1for(int m=1;m<colspan;m++){area[i][oldIndex+m]=1;}int rowspan = Integer.valueOf(null==td.getAttributeValue("rowspan")?"0":td.getAttributeValue("rowspan"));rowspan = rowspan>1?rowspan:1;//单元格需要被纵向向合并声明为2for(int m=1;m<rowspan;m++){area[m+i][oldIndex] = 2;}}}/*for(int a=0;a<area.length;a++){for(int b =0;b<area[0].length;b++){System.out.print(area[a][b]);}System.out.println("");}*/return area;}/**- * 设置表头样式 * @param wb * @return */private CellStyle createHeadStyle(Workbook wb){ CellStyle style = wb.createCellStyle();Font headerFont = wb.createFont();headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);style.setAlignment(CellStyle.ALIGN_CENTER);style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);style.setFont(headerFont); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); return style; }/**- * 设置表单记录样式 * @param wb * @return */private CellStyle createBodyStyle(Workbook wb){ CellStyle style = wb.createCellStyle();Font headerFont = wb.createFont();headerFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);style.setAlignment(CellStyle.ALIGN_CENTER);style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);style.setFont(headerFont); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); return style; }private 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 {// TODO Auto-generated method stubTableToExcelUtil tu = new TableToExcelUtil();//System.out.println(tu.getInnerHtml("<td><a>1</a></td>"));tu.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);}}?用jdom解析table的html结构，用poi生成excel，html效果如下：
?
导出的excel效果如下：
?