把HTML无嵌套表格完美导出为Excel文件的方法

来源：互联网发布：淘宝网alexa排名多少编辑：程序博客网时间：2024/06/05 20:02

原文地址：http://www.java2000.net/viewthread.jsp?tid=1977

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

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

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

如果打不开了，我想CSDN的下载频道一定有。或者到我的网站【下载资源】里面下载。

如果代码看不清楚，或者有问题，请到原始地址，或者到底部看截图

1 我们先看页面的部分

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

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

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

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(&apos;"&apos;, &apos; &apos;).replace(&apos;/&apos;&apos;, &apos; &apos;).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(&apos;"&apos;, &apos; &apos;).replace(&apos;/&apos;&apos;, &apos; &apos;).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的影射我就顺便给了，其实大家都知道怎么做

    ExcelExportor  ExcelExportor    ExcelExportor  /export/excel.jsp

========================================================================
============================以下是截图，不是文字 ========================
========================================================================