把HTML无嵌套表格完美导出为Excel文件（转）

此方法生成xls文件，不是那种简单的通过文件头来实现的那种。而是真正的Excel文件。

此方法支持那种合并的单元格，需要jxl类库的支持。

jxl 的下载地址 http://sourceforge.net/project/showfiles.php?group_id=79926

1 我们先看页面的部分

＜SCRIPT type=text/javascript＞
function exportExcel(id){
var forum = document.forms["ExcelExportor"];
forum.c.value=eval(id+".innerHTML");
forum.submit();
}
＜/SCRIPT＞
＜FORM name="ExcelExportor" method="OST" action="/export/excel.jsp"＞
＜INPUT type=hidden name=c＞
＜INPUT class=mybutton onclick=exportExcel(' MAIN_TABLE' ) type=button value=导出Excel＞
＜/FORM＞
＜!-- 下面是你要导出的表格,其id必须和前面的相同 --＞
＜TABLE id=MAIN_TABLE cellSpacing=1 cellPadding=2 border=0＞＜TBODY＞＜TR class=title＞＜TD＞行号＜/TD＞＜TD＞客户编号＜/TD＞＜TD＞合同类型＜/TD＞＜TD＞客户名称＜/TD＞＜TD＞事业部＜/TD＞＜TD＞签订日期＜/TD＞＜TD＞有效期起始＜/TD＞＜TD＞有效期终止＜/TD＞＜TD＞信用额度＜/TD＞＜TD＞收货人＜/TD＞＜TD＞收货人身份证＜/TD＞＜TD＞收货省＜/TD＞＜TD＞收货城市＜/TD＞＜TD＞收货地址＜/TD＞＜TD＞备注＜/TD＞＜/TR＞...
＜/TBODY＞＜/TABLE＞


行号 客户编号 合同类型 客户名称 事业部 签订日期 有效期起始 有效期终止 信用额度 收货人 收货人身份证 收货省 收货城市 收货地址 备注 ...

唯一需要修改的，就是你的要导出的Table的ID 必须和Form里面的ID相同，比如都叫 "MAIN_TABLE";
当然，你换成任何其它名字都是可以的。

2 下面我们来看那个被影射成的/export/excel.jsp 的 servlet

view plaincopy to clipboardprint?
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
* 解析页面并导出为Excel格式。
*
* @author JAVA世纪网,www.java2000.net,赵学庆
*
*/
public class ExcelExportor extends HttpServlet {

private static final long serialVersionUID = 8563623076707865788L;

@Override
public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException {
request.setCharacterEncoding("GBK");
String content = request.getParameter("c");
if (content == null) {
Writer out = response.getWriter();
response.setCharacterEncoding("GBK");
out.write("＜HTML＞No Content");
return;
}
try {
export(content, response);
} catch (Exception ex) {
ex.printStackTrace();
}
}

private WritableWorkbook wwb = null;

private WritableSheet sheet = null;

private void export(String content, HttpServletResponse response) throws IOException, RowsExceededException, WriteException {
response.setContentType("application/ms-excel");
String sheetName = getCaption(content);
if (sheetName == null) {
sheetName = "Sheet1";
}
sheetName = sheetName.replaceAll(":", "").replaceAll("[)]", "").replaceAll("[(]", "");
response.addHeader("Content-Disposition", "attachment; filename=" + new String(sheetName.getBytes("GBK"), "ISO-8859-1")
+ ".xls");

OutputStream os = response.getOutputStream();
wwb = Workbook.createWorkbook(os);
wwb.setProtected(true);

sheet = wwb.createSheet(sheetName, 0);
int row = 0;
int col = 0;
Label label = null;

if (sheetName.trim().length() ＞ 30) {
label = new Label(col, row, sheetName);
sheet.addCell(label);
row++;
}

List＜TD＞ listBody = getContent(content);
Map＜STRING, Boolean＞ map = new HashMap＜STRING, Boolean＞();
for (TD td : listBody) {
if (td == null) {
row++;
col = 0;
continue;
}

while (map.get(col + "-" + row) != null) {
col++;
}

if (td.colspan ＞ 1 || td.rowspan ＞ 1) {
sheet.mergeCells(col, row, col + td.colspan - 1, row + td.rowspan - 1);
for (int i = col; i ＜= col + td.colspan - 1; i++) {
for (int j = row; j ＜= row + td.rowspan - 1; j++) {
map.put(i + "-" + j, true);
}
}
}

label = new Label(col, row, td.content);
sheet.addCell(label);

map.put(col + "-" + row, true);
col += td.colspan;
}
wwb.write();
wwb.close();
}

private String getCaption(String content) {
int begin = content.indexOf("＜CAPTION"); int end='' ' ' content.indexOf("＜/CAPTION' ' ' '＞");
if (begin == -1 || end == -1) {
return null;
}
begin = content.indexOf("＞", begin);
if (begin == -1) {
return null;
}
return content.substring(begin + 1, end);
}

public List＜TD＞ getContent(String content) throws UnsupportedEncodingException {
int begin = -1;
int end = -1;
int index = -1;
String numberStr;
int number;
String[] tables = content.split("＜/TABLE＞");
List＜TD＞ list = new ArrayList＜TD＞();
for (String table : tables) {
String[] trs = table.split("＜/TR＞");
for (String tr : trs) {

number = 1;
String[] ss = tr.split("＜/TD＞");
for (String s : ss) {
begin = s.indexOf("＜TD"); if (begin="=" -1) { continue; } s="s.substring(begin" + 3); index='' ' ' s.indexOf("' ' ' '＞");
TD td = new TD();
begin = s.indexOf("rowSpan=");
if (begin != -1) {
end = s.indexOf(" ", begin);

if (end == -1) {
end = index;
}
numberStr = s.substring(begin + 8, end).replace(' "' , ' ' ).replace(' \' ' , ' ' ).trim();
number = Integer.parseInt(numberStr);
td.rowspan = number;
}

begin = s.indexOf("colSpan=");
if (begin != -1) {
end = s.indexOf(" ", begin);
index = s.indexOf("＞", begin);
if (end == -1) {
end = index;
}
if (end ＞ index) {
end = index;
}
numberStr = s.substring(begin + 8, end).replace(' "' , ' ' ).replace(' \' ' , ' ' ).trim();
number = Integer.parseInt(numberStr);
td.colspan = number;
}
td.content = s.substring(index + 1).replaceAll("\\＜.*?\\＞", "").replaceAll(" ", "").trim();
list.add(td);
}
list.add(null);
}
list.add(null);
list.add(null);
}
return list;
}
}

class TD {
int rowspan = 1;

int colspan = 1;

String content;
}

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
* 解析页面并导出为Excel格式。
*
* @author JAVA世纪网,www.java2000.net,赵学庆
*
*/
public class ExcelExportor extends HttpServlet {

private static final long serialVersionUID = 8563623076707865788L;

@Override
public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException {
request.setCharacterEncoding("GBK");
String content = request.getParameter("c");
if (content == null) {
Writer out = response.getWriter();
response.setCharacterEncoding("GBK");
out.write("No Content");
return;
}
try {
export(content, response);
} catch (Exception ex) {
ex.printStackTrace();
}
}

private WritableWorkbook wwb = null;

private WritableSheet sheet = null;

private void export(String content, HttpServletResponse response) throws IOException, RowsExceededException, WriteException {
response.setContentType("application/ms-excel");
String sheetName = getCaption(content);
if (sheetName == null) {
sheetName = "Sheet1";
}
sheetName = sheetName.replaceAll(":", "").replaceAll("[)]", "").replaceAll("[(]", "");
response.addHeader("Content-Disposition", "attachment; filename=" + new String(sheetName.getBytes("GBK"), "ISO-8859-1")
+ ".xls");

OutputStream os = response.getOutputStream();
wwb = Workbook.createWorkbook(os);
wwb.setProtected(true);

sheet = wwb.createSheet(sheetName, 0);
int row = 0;
int col = 0;
Label label = null;

if (sheetName.trim().length() ＞ 30) {
label = new Label(col, row, sheetName);
sheet.addCell(label);
row++;
}

List listBody = getContent(content);
Map map = new HashMap();
for (TD td : listBody) {
if (td == null) {
row++;
col = 0;
continue;
}

while (map.get(col + "-" + row) != null) {
col++;
}

if (td.colspan ＞ 1 || td.rowspan ＞ 1) {
sheet.mergeCells(col, row, col + td.colspan - 1, row + td.rowspan - 1);
for (int i = col; i ＜= col + td.colspan - 1; i++) {
for (int j = row; j ＜= row + td.rowspan - 1; j++) {
map.put(i + "-" + j, true);
}
}
}

label = new Label(col, row, td.content);
sheet.addCell(label);

map.put(col + "-" + row, true);
col += td.colspan;
}
wwb.write();
wwb.close();
}

private String getCaption(String content) {
int begin = content.indexOf("");
if (begin == -1 || end == -1) {
return null;
}
begin = content.indexOf("＞", begin);
if (begin == -1) {
return null;
}
return content.substring(begin + 1, end);
}

public List getContent(String content) throws UnsupportedEncodingException {
int begin = -1;
int end = -1;
int index = -1;
String numberStr;
int number;
String[] tables = content.split("");
List list = new ArrayList();
for (String table : tables) {
String[] trs = table.split("");
for (String tr : trs) {

number = 1;
String[] ss = tr.split("");
for (String s : ss) {
begin = s.indexOf("");
TD td = new TD();
begin = s.indexOf("rowSpan=");
if (begin != -1) {
end = s.indexOf(" ", begin);

if (end == -1) {
end = index;
}
numberStr = s.substring(begin + 8, end).replace(' "' , ' ' ).replace(' \' ' , ' ' ).trim();
number = Integer.parseInt(numberStr);
td.rowspan = number;
}

begin = s.indexOf("colSpan=");
if (begin != -1) {
end = s.indexOf(" ", begin);
index = s.indexOf("＞", begin);
if (end == -1) {
end = index;
}
if (end ＞ index) {
end = index;
}
numberStr = s.substring(begin + 8, end).replace(' "' , ' ' ).replace(' \' ' , ' ' ).trim();
number = Integer.parseInt(numberStr);
td.colspan = number;
}
td.content = s.substring(index + 1).replaceAll("\\＜.*?\\＞", "").replaceAll(" ", "").trim();
list.add(td);
}
list.add(null);
}
list.add(null);
list.add(null);
}
return list;
}
}

class TD {
int rowspan = 1;

int colspan = 1;

String content;
}

详细的我就不多解释了，基本就是解析Table的语法，唯一需要注意的，里面的CharacterEncoding() 需要你根据自己的情况进行修改。

3 最后那个web.xml的影射我就顺便给了，其实大家都知道怎么做

＜SERVLET＞
＜SERVLET-NAME＞ExcelExportor＜/SERVLET-NAME＞
＜SERVLET-CLASS＞ExcelExportor＜/SERVLET-CLASS＞
＜/SERVLET＞
＜SERVLET-MAPPING＞
＜SERVLET-NAME＞ExcelExportor＜/SERVLET-NAME＞
＜URL-PATTERN＞/export/excel.jsp＜/URL-PATTERN＞
/SERVLET-MAPPING＞

 

来自：http://blog.csdn.net/java2000_net/archive/2008/07/21/2684994.aspx