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

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

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

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

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

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

 

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

 

1 我们先看页面的部分

 

<script type="text/javascript">
function exportExcel(id){
  var forum = document.forms["ExcelExportor"];
  forum.c.value=eval(id+".innerHTML");
  forum.submit();
}
</script>

 
 

  
  
  
  
  
  

 
 

 
 

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

  
  
 
 

唯一需要修改的，就是你的要导出的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
  
  

 
 
 

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

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>