生成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();
%>
<%@ 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();
%>