Java导出数据到Excle

 

ExcelReporter.java

package com.pub;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExcelReporter {
 public boolean exportToExcel(HttpServletResponse response, String filename,
   String sheetname, String[] titles, List<List> rows) throws Exception {
  OutputStream os = null;
  boolean b1 = true;
  try {
   os = response.getOutputStream(); // 取得输出流
   response.reset(); // 清空输出流
   response.setHeader("Content-disposition", "attachment;  filename="
     + filename + ".xls"); // 设定输出文件头
   response.setContentType("application/msexcel"); // 定义输出类型
  } catch (IOException ex) {
   b1 = false;
   System.out.println("流操作错误:" + ex.getMessage());
  }
  WritableWorkbook workbook = null;
  try {
   // 创建新的Excel 工作簿
   workbook = Workbook.createWorkbook(os);
   // 在Excel工作簿中建一工作表，其名为:第一页
   jxl.write.WritableSheet wsheet = workbook.createSheet(sheetname, 0); // sheet();
   WritableFont font = new WritableFont(WritableFont.ARIAL, 14,
     WritableFont.BOLD, false,
     jxl.format.UnderlineStyle.NO_UNDERLINE,
     jxl.format.Colour.BLACK);
   WritableCellFormat format = new WritableCellFormat(font);
   
   
   //循环标头
   for (int i = 0; i < titles.length; i++) {
    Label wlabel1 = new Label(i, 0, titles[i], format); // 列、行单元格中的文本、文本格式
    wsheet.addCell(wlabel1);
   }
   font = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
     12, WritableFont.NO_BOLD, false,
     jxl.format.UnderlineStyle.NO_UNDERLINE,
     jxl.format.Colour.BLACK);
   format = new jxl.write.WritableCellFormat(font);
   

   //循环表内数据
   for(int i =0;i<rows.size();i++){
    List row = rows.get(i);
    for(int j =0;j<row.size();j++){
     Object o = row.get(j);
     Label label = new Label(j,i+1,o.toString(),format);//输出表内数据时，必须行号加1，让出标题否则会覆盖标题
     wsheet.addCell(label);
    }
   }
   
/*   Iterator key = table.keySet().iterator();
   for (int i = 0; i < table.size(); i++) { // 在索引0的位置创建行（最顶端的行）
     Object o = key.next();
     System.out.println(o.toString()+"%%%%%%%"+table.size());
     System.out.println(table.get(o).toString());
     Label wlabel1 = new Label(0, i+1, String.valueOf(i+1), format); // 行、列、单元格中的文本、文本格式
     Label wlabel2 = new Label(1, i+1, o.toString(), format);
     Label wlabel3 = new Label(2, i+1, table.get(o).toString(), format);
     wsheet.addCell(wlabel1);
     wsheet.addCell(wlabel2);
     wsheet.addCell(wlabel3);
   }*/
   workbook.write(); // 写入文件
  } catch (WriteException ex1) {
   b1 = false;
   System.out.println("WriteException:" + ex1.getMessage());
  } catch (IOException ex2) {
   b1 = false;
   System.out.println("IOException:" + ex2.getMessage());
  }
  workbook.close();
  os.close();
  return b1;
 }
}


Action中添加:

/**
  * 导出数据
  */
 public ActionForward exportExcel(ActionMapping mapping, ActionForm form,
   HttpServletRequest request, HttpServletResponse response)
   throws Exception {
  // 实例化一个范性集合
  List<List> rows = new ArrayList<List>();
  //获取所有的用户
  List list= this.userBiz.getAllUsers();
  
  for(int i=0;i<list.size();i++){
   List row = new ArrayList();
   Users user=(Users)list.get(i);
   row.add(user.getUname());
   row.add(user.getUpass());
   row.add(user.getUage());
   rows.add(row);   
  }
  
  // Excel文件名
  Date date = new Date();
  SimpleDateFormat df = new SimpleDateFormat("yyMMddHHmmss");// 得到当前时间
  String da = df.format(date);
  String fileName = da + "";
  // Excel表头
  String[] titles = { "姓名", "性别", "年龄", };
  String sheetName = "简历表";
  // 实例化工具类
  ExcelReporter ep = new ExcelReporter();
  // 调用工具类方法，导出
  ep.exportToExcel(response, fileName, sheetName, titles, rows);
  return null;
 }