1.要导出的table
<table>
<tr>
<td>姓名</td>
<td>邮箱</td>
<td>电话</td>
</tr>
<tr>
<td>高sss</td>
<td>xxx@xxx.com.cn</td>
<td>xxxx</td>
</tr>
<tr>
<td>Allen</td>
<td>xxx@foxmail.com</td>
<td>xxxx</td>
</tr>
</table>
2.导出的脚本实现
<a class="foot_btn mt11 ml10 fl" id="exportExcel" style="margin-top: 10px;margin-left:5px;">导出Excel</a>
<form action="${ctx }/common/exportExcel" id ="exportExcelForm" method="post">
<input type="hidden" name = "fileName" value="${year }年${month }月日报提交情况统计.xls">
<input type="hidden" name = "sheetName" value="${year }年${month }月">
<input type="hidden" name = "html"/>
</form>
<script type="text/javascript">
$("#exportExcel").click(function(){
$("input[name='html']").val($("#dataTable").prop("outerHTML"));
$("#exportExcelForm").submit();
});
</script>
3.后台Java
@Controller
@RequestMapping(value = "common/exportExcel")
public class CommonUtilController {
private static Logger logger = Logger.getLogger(CommonUtilController.class);
@RequestMapping(method = RequestMethod.POST)
@ResponseBody
public void exportExcel(String fileName,String sheetName,String html,
HttpServletResponse response){
logger.info("fileName,sheetName" + fileName + "," + sheetName);
try {
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
HTML2Excel.excute(html, sheetName,response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
4.HTML2Excel工具类
public class HTML2Excel {
private static Document getDocByHtml(String html) throws IOException {
Document doc = Jsoup.parse(html);
return doc;
}
private static void mergeColRow(Elements trs, WritableSheet sheet)
throws RowsExceededException, WriteException {
int[][] rowhb = new int[300][50];
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
Elements cells = new Elements();
Elements tds = tr.getElementsByTag("td");
if(tds == null || tds.size() > 0){
cells.addAll(tds);
}
Elements ths = tr.getElementsByTag("th");
if(ths == null || ths.size() > 0){
cells.addAll(ths);
}
int realColNum = 0;
for (int j = 0; j < cells.size(); j++) {
Element td = cells.get(j);
if (rowhb[i][realColNum] != 0) {
realColNum = getRealColNum(rowhb, i, realColNum);
}
int rowspan = 1;
int colspan = 1;
if (td.attr("rowspan") != "") {
rowspan = Integer.parseInt(td.attr("rowspan"));
}
if (td.attr("colspan") != "") {
colspan = Integer.parseInt(td.attr("colspan"));
}
String text = td.text();
String title = td.attr("title");
drawMegerCell(rowspan, colspan, sheet, realColNum, i, text,title, rowhb);
realColNum = realColNum + colspan;
}
}
}
private static void drawMegerCell(int rowspan, int colspan,
WritableSheet sheet, int realColNum, int realRowNum, String text,String title,
int[][] rowhb) throws RowsExceededException, WriteException {
for (int i = 0; i < rowspan; i++) {
for (int j = 0; j < colspan; j++) {
if (i != 0 || j != 0) {
text = "";
}
Label label = new Label(realColNum + j, realRowNum + i, text);
WritableFont countents = new WritableFont(WritableFont.TIMES,10);
WritableCellFormat cellf = new WritableCellFormat(countents);
cellf.setAlignment(jxl.format.Alignment.CENTRE);
cellf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
label.setCellFormat(cellf);
sheet.addCell(label);
rowhb[realRowNum + i][realColNum + j] = 1;
}
}
sheet.mergeCells(realColNum, realRowNum, realColNum + colspan - 1,realRowNum + rowspan - 1);
}
private static int getRealColNum(int[][] rowhb, int i, int realColNum) {
while (rowhb[i][realColNum] != 0) {
realColNum++;
}
return realColNum;
}
private static void setColWidth(Elements colgroups, WritableSheet sheet) {
if (colgroups.size() > 0) {
Element colgroup = colgroups.get(0);
Elements cols = colgroup.getElementsByTag("col");
for (int i = 0; i < cols.size(); i++) {
Element col = cols.get(i);
String strwd = col.attr("width");
if (col.attr("width") != "") {
int wd = Integer.parseInt(strwd);
sheet.setColumnView(i, wd / 8);
}
}
}
}
public static void excute(String html, String sheetName, OutputStream os)
throws IOException {
html = html.replaceAll("<", "<").replaceAll(">", ">")
.replaceAll("'", "\\").replaceAll("&", "&")
.replaceAll(""", "\"");
Document doc = getDocByHtml(html);
Elements tables = doc.getElementsByTag("table");
if (tables.size() == 0) {
return;
}
Element table = tables.get(0);
Elements trs = table.getElementsByTag("tr");
Elements colgroups = table.getElementsByTag("colgroup");
try {
WritableWorkbook book = Workbook.createWorkbook(os);
WritableSheet sheet = book.createSheet(sheetName, 0);
setColWidth(colgroups, sheet);
mergeColRow(trs, sheet);
book.write();
book.close();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}