jsp将数据存入excel文件下载

注意：

1）文中Survey、  User 、  ExcelData 都是我自己开发的类，用于存放要导出的数据。

2）在jsp页面调用此方法后，记得加如下两行

out.clear();
out = pageContext.pushBody();

原因见：http://blog.csdn.net/flyhorse1980/archive/2008/04/14/2291784.aspx

具体类：

package tool;

import java.util.ArrayList;
import java.util.Iterator;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.Region;

import business.*;

public class Excel {
 public static boolean writeToExcel(ArrayList al,
   HttpServletResponse response) {
  boolean ret = false;
  Survey s = (Survey) al.get(0);
  User u = (User) al.get(1);
  ExcelData e = null;
  Iterator it = al.iterator();
  it.next();
  it.next();

  // 初始化导出excel文件需要的内容
  HSSFWorkbook wb = new HSSFWorkbook();
  HSSFSheet sheet = wb.createSheet(s.getName()); // 新建sheet页
  sheet.setGridsPrinted(true); // 打印网格线
  sheet.setAutobreaks(true); // 自动换行
  sheet.setVerticallyCenter(true);
  // sheet.setDefaultColumnWidth((short)10);
  
   sheet.setColumnWidth((short)0,(short)3000);
   sheet.setColumnWidth((short)1,(short)8000);
   sheet.setColumnWidth((short)2,(short)5000);
   sheet.setColumnWidth((short)3,(short)8000);
   sheet.setColumnWidth((short)4,(short)5000);
   sheet.setColumnWidth((short)5,(short)5000);
  

  HSSFRow row = null;
  HSSFCell cell = null;
  // 实例化excel操作类，用这个方法可以解决中文乱码问题
  ExcelPubCom epc = new ExcelPubCom();

  row = sheet.createRow(0); // 报表标题
  cell = row.createCell((short) 0);
  // 设置字体和对齐
  HSSFFont font = wb.createFont();
  font.setFontHeightInPoints((short) 20);
  font.setFontName("Times New Roman");
  font.setBoldweight((short) 4);
  HSSFCellStyle style = wb.createCellStyle();
  style.setFont(font);
  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  cell.setCellStyle(style);
  epc.setCellValue(cell, s.getTitle());
  // 合并单元格
  sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 5));

  row = sheet.createRow(1);
  cell = row.createCell((short) 0);
  font = wb.createFont();
  font.setFontHeightInPoints((short) 12);
  font.setFontName("Times New Roman");
  style = wb.createCellStyle();
  style.setFont(font);
  style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
  cell.setCellStyle(style);
  epc.setCellValue(cell, s.getDesc());
  sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) 5));

  row = sheet.createRow(2);
  cell = row.createCell((short) 0);
  cell.setCellStyle(style);
  epc.setCellValue(cell, s.getInstruction());
  sheet.addMergedRegion(new Region(2, (short) 0, 2, (short) 5));
  
  row = sheet.createRow(3);// 空行
  cell = row.createCell((short) 0);
  cell.setCellStyle(style);
  sheet.addMergedRegion(new Region(3, (short) 0, 3, (short) 5));

  row = sheet.createRow(4);
  cell = row.createCell((short) 0);
  font = wb.createFont();
  font.setFontHeightInPoints((short) 14);
  font.setFontName("Times New Roman");
  font.setBoldweight((short)2);
  style = wb.createCellStyle();
  style.setFont(font);
  style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
  cell.setCellStyle(style);
  epc.setCellValue(cell, "RECEPIENT Information");
  sheet.addMergedRegion(new Region(4, (short) 0, 4, (short) 5));

  row = sheet.createRow(5); // user info
  cell = row.createCell((short) 0);
  font = wb.createFont();
  font.setFontName("Times New Roman");
  style = wb.createCellStyle();
  style.setFont(font);
  cell.setCellStyle(style);
  epc.setCellValue(cell, u.getName());
  cell = row.createCell((short) 1);
  cell.setCellStyle(style);
  epc.setCellValue(cell, u.getEmail());
  sheet.addMergedRegion(new Region(5, (short) 1, 5, (short) 2));
  cell = row.createCell((short) 3);
  cell.setCellStyle(style);
  epc.setCellValue(cell, u.getSchool());
  sheet.addMergedRegion(new Region(5, (short) 3, 5, (short) 4));
  cell = row.createCell((short) 5);
  cell.setCellStyle(style);
  epc.setCellValue(cell, u.getPosition());

  row = sheet.createRow(6); // 空行
  cell = row.createCell((short) 0);
  sheet.addMergedRegion(new Region(6, (short) 0, 6, (short) 5));

  row = sheet.createRow(7);
  font = wb.createFont();
  font.setFontHeightInPoints((short) 14);
  font.setFontName("Times New Roman");
  font.setBoldweight((short)2);
  style = wb.createCellStyle();
  style.setFont(font);
  style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
  cell = row.createCell((short) 0);
  cell.setCellStyle(style);
  epc.setCellValue(cell, "NO.");
  cell = row.createCell((short) 1);
  cell.setCellStyle(style);
  epc.setCellValue(cell, "Subject");
  cell = row.createCell((short) 2);
  cell.setCellStyle(style);
  epc.setCellValue(cell, "Sub Subject");
  cell = row.createCell((short) 3);
  cell.setCellStyle(style);
  epc.setCellValue(cell, "Options");
  cell = row.createCell((short) 4);
  cell.setCellStyle(style);
  epc.setCellValue(cell, "DigitalValue");
  cell = row.createCell((short) 5);
  cell.setCellStyle(style);
  epc.setCellValue(cell, "TextValue");

  font = wb.createFont();
  font.setFontName("Times New Roman");
  style = wb.createCellStyle();
  style.setFont(font);
  int count = 7;
  while (it.hasNext()) {
   e = (ExcelData) it.next();
   count++;
   row = sheet.createRow(count);

   cell = row.createCell((short) 0);
   cell.setCellStyle(style);
   epc.setCellValue(cell, e.getIndex());
   cell = row.createCell((short) 1);
   cell.setCellStyle(style);
   epc.setCellValue(cell, e.getSubject());
   cell = row.createCell((short) 2);
   cell.setCellStyle(style);
   epc.setCellValue(cell, e.getSubsubject());
   cell = row.createCell((short) 3);
   cell.setCellStyle(style);
   epc.setCellValue(cell, e.getOption());
   cell = row.createCell((short) 4);
   cell.setCellStyle(style);
   epc.setCellValue(cell, e.getDigital());
   cell = row.createCell((short) 5);
   cell.setCellStyle(style);
   epc.setCellValue(cell, e.getText());
  }

  try {
   if (wb != null) {
    response.setContentType("application/ms-excel");
    response.setHeader("Content-Disposition",
      "attachment;filename=QuestionarieResult.xls");
    wb.write(response.getOutputStream());
   }
  } catch (Exception ex) {
   try{
      response.getOutputStream().print("<font color='red'>Download Failure!Please contact with us!</font>");
   }
   catch(Exception excep){}
  }
  return ret;
 }

}