java html table 转 excel，给予jdom 和 poi
maven 引入
<dependency> <groupId>org.jdom</groupId> <artifactId>jdom</artifactId> <version>1.1.3</version> </dependency>
工具类
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); } }
