poi實現從資料庫到excel的匯出

 

網上下載poi的jar包:http://apache.justdn.org/jakarta/poi/

2.在sturts的Action裡的代碼如下 ：

本例子實現把list中的資料匯出到excel

/*
 * Generated by MyEclipse Struts
 * Template path: templates/java/JavaClass.vtl
 */
package sso.admin.action;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionMessage;
import org.apache.struts.action.ActionMessages;

import sso.admin.form.DataOutForm;
import sso.admin.recruitinfo.*;
import sso.admin.subject.*;
import sso.admin.department.*;
import sso.common.CommonUtil;
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 java.io.FileOutputStream;
import java.util.*;

/**
 * design by Mr.Chan

 * Creation date: 12-28-2007
 *
 * XDoclet definition:
 * @struts.action path="/dataOut" name="dataOutForm" input="/form/dataOut.jsp" scope="request" validate="true"
 */
public class DataOutAction extends Action {
 /*
  * Generated Methods
  */

 /**
  * Method execute
  * @param mapping
  * @param form
  * @param request
  * @param response
  * @return ActionForward
  */
 public ActionForward execute(ActionMapping mapping, ActionForm form,
   HttpServletRequest request, HttpServletResponse response) {
  DataOutForm dof = (DataOutForm) form;// TODO Auto-generated method stub
  ActionMessages errors = new ActionMessages() ;
  errors.add("error",new ActionMessage("download_file")) ;
  super.saveErrors(request, errors);
  String yxdm="";
  String zydm="";
  if(dof.getYxdm()!=null){
   yxdm=dof.getYxdm();
  }
  if(dof.getZydm()!=null){
   zydm=dof.getZydm();
  }
  String realPath=request.getRealPath("");
  int checkNum=Integer.parseInt(dof.getCheckNum());
  List l=null;
  RecruitInfoOperation rio=new RecruitInfoOperation();
  if(yxdm.equals("")&&zydm.equals("")){
   l=rio.queryAllRegStuInfo(checkNum);
  }else{
   l=rio.queryRegStuInfoBySubject(checkNum,zydm, yxdm);
  }
  //Excel 檔案要存放的位置，假定在sso的download目錄下
  String outputFile=realPath+"/download/temp.xls";
  SubjectOperation so=new SubjectOperation();
  DepartmentOperation deo=new DepartmentOperation();
  try{
   //建立新的Excel 活頁簿
   HSSFWorkbook workbook = new HSSFWorkbook();
   //在Excel活頁簿中建一名為"報到新生"的工作表，其語句為：
   //HSSFSheet sheet = workbook.createSheet("新生報到狀況表");
   HSSFSheet sheet=null;
   if(checkNum==1){
    sheet = workbook.createSheet("報到新生");
   }else{
    sheet = workbook.createSheet("未報到新生");
   }
   //在索引0的位置建立行（最頂端的行）
   HSSFRow row = sheet.createRow((short)0);
   //在索引0的位置建立儲存格（左上端）
   row.createCell((short)0).setCellValue("考生號");
   row.createCell((short)1).setCellValue("姓名");
   row.createCell((short)2).setCellValue("學號");
   row.createCell((short)3).setCellValue("系別");
   row.createCell((short)4).setCellValue("專業");
   row.createCell((short)5).setCellValue("考分");
   row.createCell((short)6).setCellValue("家庭住址");
   for(int i=0;i<l.size();i++){
    HSSFRow row1 = sheet.createRow((short)(i+1));
    Object temp=(Object)l.get(i);
    String ksh=temp.toString();
    YX_RegBean yx_reg=(YX_RegBean)rio.queryXsByKsh(ksh);
    String subject=null;
      String department=null;
    SubjectBean sb=new SubjectBean();
    DepartmentBean db=new DepartmentBean();
    if(yx_reg.getZydm()==null)
    {
     subject="未添加";
    }
    else{
     sb=(SubjectBean)so.queryById(yx_reg.getZydm());
     subject=CommonUtil.isoToGb(sb.getName());
    }
    if(yx_reg.getYxdm()==null)
    {
     department="未添加";
    }
    else{
     db=(DepartmentBean)deo.queryById(yx_reg.getYxdm());
     department=CommonUtil.isoToGb(db.getGroupName());
    } 
    row1.createCell((short)0).setCellValue(yx_reg.getKsh());
    row1.createCell((short)1).setCellValue(CommonUtil.isoToGb(yx_reg.getXm()));
    row1.createCell((short)2).setCellValue(yx_reg.getXh());
    row1.createCell((short)3).setCellValue(department);
    row1.createCell((short)4).setCellValue(subject);
    row1.createCell((short)5).setCellValue(yx_reg.getZf());
    row1.createCell((short)6).setCellValue(CommonUtil.isoToGb(yx_reg.getJtzz()));
    subject=null;
    department=null;
    System.gc();
    yx_reg=null;
    System.gc();
   }
   //建立一輸出檔案流
   FileOutputStream fOut = new FileOutputStream(outputFile);
   //把相應的Excel 活頁簿存檔
   workbook.write(fOut);
   fOut.flush();
   //操作結束，關閉檔案
   fOut.close();
   return mapping.getInputForward();
  }catch(Exception e) {
   System.out.println(e+"出錯拉");
   return null;
  }
  
 }
}