前端html表格导出到Excel中

要把一个表格数据导出到Excel中，有很多种实现思路，由于前端的表格数据是后台经数据库查询得到的，所以在导出Excel时没有必要再去访问数据库了，直接在前端把表格数据传到后台就行。后台的实现思路打致是：把前台传来的表格信息转成Document（org.dom4j.Document），然后获取到表头<thead>和表体<tbody>部分，分别循环插入到Excel中。

关于引用的jar包

<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi</artifactId>
		    <version>3.15</version>
</dependency>
<dependency>
		  <groupId>com.crsc.common</groupId>
		  <artifactId>xml-apis</artifactId>
		  <version>2.8.0</version>
</dependency>

直接上代码！

前端部分：

var tableCostStrhtml = $("#tableCostStr").html();
var tableCategoryhtml = $("#tableCategory").html();
var tableExpectedGainhtml = $("#tableExpectedGain").html();
var tableProjectTypehtml = $("#tableProjectType").html();
//获取表格内容  js 和  jquary 的两种写法
//var tableProjectTypehtml = document.getElementById('tableProjectType').innerHTML;
/* 模拟表单提交 */
document.write("<form action='${adminPath}/budgetStatistic/export/exportForEchartsImg' method=post name='form1' style='display:none'>");  
document.write("<input type=hidden name='tableCostStrhtml' value='"+tableCostStrhtml+"'/>");
document.write("<input type=hidden name='tableCategoryhtml' value='"+tableCategoryhtml+"'/>");
document.write("<input type=hidden name='tableExpectedGainhtml' value='"+tableExpectedGainhtml+"'/>");
document.write("<input type=hidden name='tableProjectTypehtml' value='"+tableProjectTypehtml+"'/>");
document.write("</form>");  
document.form1.submit();

在前端获取表格信息时，有两种写法：

1是js的：document.getElementById('tableProjectType').innerHTML

2是jquary的：$("#tableProjectType").html();

注意，jquary是没有.innerHTML这个方法的，jquary获取内容只有.val(),.html(),.test()这三种方法

js的.innerHTML 和jquary的.html() 方法的区别具体参考博客

https://www.cnblogs.com/lovebing/p/6609903.html

前端获取到的table数据信息为

如果在后台直接Document data = DocumentHelper.parseText(tablehtml);这样把表格转成Document，会报错。因为在转换时，需要一个最大的根节点把整个表信息包起来。所以需要在表信息字符串前后加上<table></table>

后台代码：

@RequestMapping(value = "/export/exportForEchartsImg")
	public void exportForEchartsImg(
			@RequestParam(value = "tableCostStrhtml") String tableCostStrhtml,
			@RequestParam(value = "tableCategoryhtml") String tableCategoryhtml,
			@RequestParam(value = "tableExpectedGainhtml") String tableExpectedGainhtml,
			@RequestParam(value = "tableProjectTypehtml") String tableProjectTypehtml,HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		
		// 创建Excel工作簿
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建sheet页
		HSSFSheet sheet = wb.createSheet("可视数据");
		//table表格
		createTableInExcel(wb, sheet, tableCostStrhtml,1,15);
		//table表格
		createTableInExcel(wb, sheet, tableCategoryhtml,28,15);
		//table表格
		createTableInExcel(wb, sheet, tableExpectedGainhtml,56,15);
		//table表格
		createTableInExcel(wb, sheet, tableProjectTypehtml,84,15);
		// Excel下载
		OutputStream out = response.getOutputStream();
		response.setContentType("application/vnd.ms-excel");
		//处理导出时文件名中文乱码
		String userAgent = request.getHeader("User-Agent");
		String filename = "";
		if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
			filename = java.net.URLEncoder.encode("可视数据图表", "UTF-8");
        } else {
            // 非IE浏览器的处理：
        	filename = new String("可视数据图表".getBytes("UTF-8"), "ISO-8859-1");
        }
		response.setHeader("Content-Disposition","attachment;filename=" + filename + ".xls");
		wb.write(out);
		wb.close();
	}

	/**
	 * 将表格保存到Excel中
	 * 
	 * @param wb-----Excel工作簿
	 * @param sheet-----Excel的sheet页
	 * @param tablehtml-----前台传来的表格内容
	 * @param rowIndex------起始行索引
	 * @param startcellIndex-----起始列索引
	 */
	public void createTableInExcel(HSSFWorkbook wb,HSSFSheet sheet,String tablehtml,int rowIndex,int startcellIndex){
		/*
		 * html表格转Excel
		 */
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		//List<CrossRangeCellMeta> crossRowEleMetaLs = new ArrayList<>();
		try {
			//处理前台传参中文乱码
			tablehtml = new String(tablehtml.getBytes("ISO8859-1"), "utf-8");
			//在获取到的表格内容前后加上根节点，不然会报错
			tablehtml = "<table>" + tablehtml + "</table>";
			Document data = DocumentHelper.parseText(tablehtml);
			// 获取表头(<thead>部分)
			Element thead = data.getRootElement().element("thead");
			//设置表头(<thead>)样式
			HSSFCellStyle titleStyle = getTitleStyle(wb);
			if (thead != null) {
				//循环生成表头
				List<Element> trLs = thead.elements("tr");
				for (Element trEle : trLs) {
					HSSFRow row = sheet.createRow(rowIndex);
					List<Element> thLs = trEle.elements("th");
					makeRowCell(thLs, rowIndex, row, startcellIndex, titleStyle);
					rowIndex++;
				}
			}
			// 获取表体(<tbody>部分)
			Element tbody = data.getRootElement().element("tbody");
			if (tbody != null) {
				//设置表体(<tbody>)样式
				HSSFCellStyle contentStyle = getContentStyle(wb);
				//循环生成表体
				List<Element> trLs = tbody.elements("tr");
				for (Element trEle : trLs) {
					HSSFRow row = sheet.createRow(rowIndex);
					List<Element> thLs = trEle.elements("th");
					int cellIndex = makeRowCell(thLs, rowIndex, row, startcellIndex, titleStyle);
					List<Element> tdLs = trEle.elements("td");
					makeRowCell(tdLs, rowIndex, row, cellIndex, contentStyle);
					rowIndex++;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		// 自动调整列宽，这里设置初始值为15，表示在Excel中的第15列开始根据内容调整列宽
		for (int i = 15; i < 50; i++) {
			sheet.autoSizeColumn((short) i);
		}
	}
	
	/**
     * 获得标题样式
     *
     * @param workbook
     * @return
     */
    @SuppressWarnings("deprecation")
	private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
        short titlebackgroundcolor = HSSFColor.WHITE.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
     */
    @SuppressWarnings("deprecation")
	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;
    }
	
    /**
     * 生成行内容
     *
     * @return 最后一列的cell index
     * @param tdLs th或者td集合
     * @param rowIndex 起始行索引
     * @param row POI行对象
     * @param startCellIndex  起始列索引
     * @param cellStyle 样式
     * @return  返回表格最后一行的索引
     */
	@SuppressWarnings("deprecation")
	private static int makeRowCell(List<Element> tdLs, int rowIndex, HSSFRow row, int startCellIndex,
			HSSFCellStyle cellStyle) {
		int i = startCellIndex;
		//循环th或者td集合，把th或者td的内容通过setCellValue塞进Excel中
		for (int eleIndex = 0; eleIndex < tdLs.size(); i++, eleIndex++) {
			Element thEle = tdLs.get(eleIndex);
			String val = thEle.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);
		}
		return i;
	}

具体的代码含义参考注释，最后导出的效果如下