Java導出數據到Excle – JAVA編程語言程序開發技術文章

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; 
 } 

摘自 那年那月那天