JSP生成EXCEL文件

生成Excel文件使用的是apache的开源项目poi,sun的jxl也还不错.

<%@ page language="java" pageEncoding="gb2312"%>
<%@ page errorPage="error.jsp"%>
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.util.*"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.io.*"%>
<%@ page import="org.apache.poi.hssf.usermodel.*,java.text.*,org.apache.poi.poifs.filesystem.POIFSFileSystem"%>

<%
    response.reset();
    String filename = new String(("报表").getBytes(), "ISO-8859-1");
    response.setContentType("APPLICATION/OCTET-STREAM");
    response.addHeader("Content-Disposition", "attachment;filename=/"" + filename + ".xls/"");
   
    DecimalFormat f = new DecimalFormat("#,##0.00");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("sheet1");
//  以下以写表头
    //表头为第一行
  HSSFRow row = sheet.createRow((short) 0);
//定义10列
     HSSFCell cell1 = row.createCell((short) 0);
    HSSFCell cell2 = row.createCell((short) 1);
    HSSFCell cell3 = row.createCell((short) 2);
    HSSFCell cell4 = row.createCell((short) 3);
    HSSFCell cell5 = row.createCell((short) 4);
    HSSFCell cell6 = row.createCell((short) 5);
    HSSFCell cell7 = row.createCell((short) 6);
    HSSFCell cell8 = row.createCell((short) 7);
    HSSFCell cell9 = row.createCell((short) 8);
    HSSFCell cell10 = row.createCell((short) 9);
    HSSFCell cell11 = row.createCell((short) 10);
    HSSFCell cell12 = row.createCell((short) 11);
   
    cell1.setEncoding((short) 1);
    cell1.setCellType(1);
    cell2.setEncoding((short) 1);
    cell2.setCellType(1);
    cell3.setEncoding((short) 1);
    cell3.setCellType(1);
    cell4.setEncoding((short) 1);
    cell4.setCellType(1);
    cell5.setEncoding((short) 1);
    cell5.setCellType(0);
    cell6.setEncoding((short) 1);
    cell6.setCellType(1);
    cell7.setEncoding((short) 1);
    cell7.setCellType(1);
    cell8.setEncoding((short) 1);
    cell8.setCellType(1);
    cell9.setEncoding((short) 1);
    cell9.setCellType(1);
    cell10.setEncoding((short) 1);
    cell10.setCellType(1);
    cell11.setEncoding((short) 1);
    cell11.setCellType(1);
    cell12.setEncoding((short) 1);
    cell12.setCellType(1);

//定义表头的内容
    cell1.setCellValue("产品名称");
    cell2.setCellValue("用户号码");
    cell3.setCellValue("省");
    cell4.setCellValue("城市");
    cell5.setCellValue("注册时间");
    cell6.setCellValue("退订时间");
    cell7.setCellValue("用户当前状态");
    cell8.setCellValue("是否三天免费期");
    cell9.setCellValue("本月MT成功条数");
    cell10.setCellValue("本月MT条数");
    cell11.setCellValue("前月MT成功条数");
    cell12.setCellValue("前月MT条数");
   
    Vector data1 = new Vector();
    data1 = getAllData(sql);//从数据库中查询出的结果集,这部分代码没写
    DBDisconnect();

    for(int i=0;i<data1.size();i++){
          Vector data = new Vector();
          data = (Vector)data1.get(i);
 
          String array[];
          array = new String[data.size()+1];
          for (int j=0;j<data.size();j++){  
               array[j] = data.get(j).toString();
          }
 
          //  定义数据从第二行开始      
          row = sheet.createRow((short) i+1);
                  cell1 = row.createCell((short) 0);
                  cell2 = row.createCell((short) 1);
                  cell3 = row.createCell((short) 2);
                  cell4 = row.createCell((short) 3);
                  cell5 = row.createCell((short) 4);
                  cell6 = row.createCell((short) 5);
                  cell7 = row.createCell((short) 6);
                  cell8 = row.createCell((short) 7);
                  cell9 = row.createCell((short) 8);
                  cell10 = row.createCell((short) 9);
                  cell11 = row.createCell((short) 10);
                  cell12 = row.createCell((short) 11);

                 cell1.setEncoding((short) 1);
                 cell1.setCellType(1);
                 cell2.setEncoding((short) 1);
                 cell2.setCellType(1);
                 cell3.setEncoding((short) 1);
                 cell3.setCellType(1);
                 cell4.setEncoding((short) 1);
                 cell4.setCellType(1);
                 cell5.setEncoding((short) 1);
                 cell5.setCellType(0);
                 cell6.setEncoding((short) 1);
                 cell6.setCellType(1);
                 cell7.setEncoding((short) 1);
                 cell7.setCellType(1);
                 cell8.setEncoding((short) 1);
                 cell8.setCellType(1);
                 cell9.setEncoding((short) 1);
                 cell9.setCellType(1);
                 cell10.setEncoding((short) 1);
                 cell10.setCellType(1);
                 cell11.setEncoding((short) 1);
                 cell11.setCellType(1);
                 cell12.setEncoding((short) 1);
                 cell12.setCellType(1);
                 //  填充内容
                 cell1.setCellValue(array[7]);
                 cell2.setCellValue(array[0]);
                 cell3.setCellValue(array[1]);
                 cell4.setCellValue(array[2]);
                 cell5.setCellValue(array[3]);
                 cell6.setCellValue(array[4]);
                 cell7.setCellValue(array[10]);
                 cell8.setCellValue(array[11]);
                 cell9.setCellValue(array[5]);
                 cell10.setCellValue(array[6]);
                 cell11.setCellValue(array[8]);
                 cell12.setCellValue(array[9]);
         
    }
    wb.write(response.getOutputStream());
    response.getOutputStream().flush();
    response.getOutputStream().close();

%>