在J2EE應用開發中，由於各種各樣的原因，經常會需要處理一些舊的Excel格式試算表資料，或者是產生試算表。 處理Excel試算表的方法比如多，比如可以使用jdbc來像讀資料庫中的資料一樣來讀取試算表的內容。這裡示範的是使用開源的試算表處理工具jxl，即Java Excel API來進行處理。關於jxl及相關使用，可以在網上搜尋到很多資料。這裡只是簡單示範在EasyJWeb中的使用方法，這是前段時間公司一個項目中的應用，需要產生一特定的Excel表格，供用戶端下載。 WEB MVC中Action中的代碼如下：public class PersonChangeAction extends BaseCrudAction {private ExcelReportService excelReportService=ExcelReportService.getInstance();public Page doDownloadMenu(WebForm form, Module module)throws Exception {
String town=CommUtil.null2String(form.get("queryTown"));
String village=CommUtil.null2String(form.get("queryVillage"));
String team=CommUtil.null2String(form.get("queryTeam"));
String belongDept="";
ActiveUser user=(ActiveUser)this.getCurrentUser(form);
if(!userService.checkAdmin(user))
{
belongDept=user.getDept();
}
ActionContext.getContext().getResponse().setContentType("application/x-msdownload");
String fileName="XXXXX人員名單";
if(!"".equals(town))fileName+="-"+town;
if(!"".equals(village))fileName+="-"+village;
if(!"".equals(team))fileName+="-"+team;
fileName+=".xls";
ActionContext.getContext().getResponse().setHeader("Content-Disposition","attachment; filename=/""+new String(fileName.getBytes("gbk"),"iso8859-1")+"/"");
java.io.OutputStream out=com.easyjf.web.ActionContext.getContext().getResponse().getOutputStream();
excelReportService.personChangeExcelList(out, belongDept, town, village, team);
return null;
}..} Action中的doDownloadMenu對應downloadMenu命令，這段代碼注要實現了向用戶端返回二進位流檔案的功能，注意一些細節。其中流中的內容，即Excel表格的內容，通過ExcelReportService的personChangeExcelList方法來處理，該方法屬於商務邏輯層，主要功能是把合格對象，添加到Excel表格中，這個方法內容大致如下： public synchronized void personChangeExcelList(java.io.OutputStream out,String belongDept,String town,String village,String team)
{
String scope = "1=1";
String scope1="1=1";
Collection paras = new ArrayList();
//省略部分查詢代碼 if(!"1=1".equals(scope))scope1+=" and personId in (select cid as personId from person where "+scope+") ";
DbPageList pList = new DbPageList(PersonChange.class, scope1, paras);
pList.doList(1, 500);
try{
jxl.write.WritableWorkbook book=jxl.Workbook.createWorkbook(out);
jxl.write.WritableSheet sheet=book.createSheet("XXXX人員名單", 0);
int pages=pList.getPages();
String[] lables={"姓名","鎮(鄉、街道)","村(居委)","組","戶主","就業方式","就業轉移時間","就業轉移地點","就業單位","就業工資","就業工種","聯絡電話"};
for(int l=0;l<lables.length;l++)
sheet.addCell(new jxl.write.Label(l,0,lables[l]));
int row=1;
for(int p=1;p<=pages;p++)
{
pList = new DbPageList(PersonChange.class, scope1, paras);
pList.doList(p, 500);
List list=pList.getResult();
for(int i=0;i<list.size();i++)
{
PersonChange change=(PersonChange)list.get(i);
Person person=(Person)this.dao.get(Person.class,change.getPersonId());
sheet.addCell(new jxl.write.Label(0,row,person.getTrueName()));
sheet.addCell(new jxl.write.Label(1,row,person.getTown()));
sheet.addCell(new jxl.write.Label(2,row,person.getVillage()));
sheet.addCell(new jxl.write.Label(3,row,person.getTeam()));
sheet.addCell(new jxl.write.Label(4,row,person.getHouseholderName()));
sheet.addCell(new jxl.write.Label(5,row,logic.showTitle("getWorkType", change.getWorkType())));
sheet.addCell(new jxl.write.Label(6,row,CommUtil.format(change.getTransferTime())));
String workPlace=logic.showTitle("workplace", change.getWorkPlace());
if("".equals(workPlace))workPlace= change.getWorkPlace();
sheet.addCell(new jxl.write.Label(7,row,workPlace));
sheet.addCell(new jxl.write.Label(8,row,change.getWorkDept()));
sheet.addCell(new jxl.write.Label(9,row,logic.showTitle("workSalary", change.getWorkSalary())));
sheet.addCell(new jxl.write.Label(10,row,logic.showTitle("skills", change.getWorkSubject())));
sheet.addCell(new jxl.write.Label(11,row,change.getTel()));
row++;
}
}
book.write();
book.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
