浏览 1736 次
锁定老帖子 主题:数据的导出报表
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2008-09-02
最后修改:2009-02-04
package com.xjj.supervise.instance.util; import jxl.write.*; import jxl.write.biff.RowsExceededException; import jxl.Workbook; import jxl.read.biff.BiffException; import javax.servlet.http.HttpServletRequest; import javax.naming.NamingException; import java.io.File; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.util.List; import java.util.ArrayList; import java.sql.Timestamp; import java.sql.SQLException; import com.xjj.sf.util.SfxzspClient; import com.xjj.sf.util.TreeItem; import com.xjj.sf.util.SfUtil; import com.xjj.sf.item.service.SpIntanceItemService; import com.xjj.sf.item.dao.ServiceItemAndInstDataDao; import com.xjj.sf.item.util.IntanceItemUtil; import com.xjj.sf.ouser.dao.SpUserDao; import com.xjj.sf.ouser.model.SpUserModel; import com.xjj.sf.xbase.platform.spring.SpringContext; import com.xjj.util.SfConstants; import com.xjj.supervise.instance.model.IntanceModel; import com.xjj.supervise.instance.service.InstanceProcessService; import com.xjj.oa.util.OaUtil; /** * Created by IntelliJ IDEA. * User: Administrator_xusg * Date: 2008-7-29 * Time: 14:26:52 * DESP: * To change this template use File | Settings | File Templates. */ public class InstanceEducelist { public static boolean educef = false; private static InstanceEducelist instance; static{ instance = new InstanceEducelist(); } public static InstanceEducelist getInstance(){ if(instance == null) instance = new InstanceEducelist(); return instance; } public synchronized String createJxl(HttpServletRequest request){ educef = false; SpIntanceItemService iService = (SpIntanceItemService) SpringContext.getBean("spIntanceItemService"); InstanceProcessService instanceProcessService = (InstanceProcessService)SpringContext.getBean("instanceProcessService"); ServiceItemAndInstDataDao itemInstDaoImpl = (ServiceItemAndInstDataDao)SpringContext.getBean("itemInstDaoImpl"); SpUserDao spUserImpl = (SpUserDao)SpringContext.getBean("spUserImpl"); String resourcePath = request.getSession().getServletContext().getRealPath(File.separator+"jxlt"+File.separator+"intancelistt.xlt"); String targetPath = resourcePath.substring(0,resourcePath.lastIndexOf(File.separator)); targetPath += (File.separator+"xls"+File.separator+System.currentTimeMillis()+".xls"); File sourceF = new File(resourcePath); File targetF = new File(targetPath); if(targetF.exists()) targetF.delete(); //条件开始 String orgId = "0"; SfxzspClient client = new SfxzspClient(); List orglist = client.getAllDepartMentInfo(SfConstants.getString("GROUPID")); //所有单位 for(int i=0;i<orglist.size();i++){ TreeItem orgIds = (TreeItem)orglist.get(i); orgId = orgIds.getId(); break; } String toOrgId = ""; if(request.getParameter("toOrgId")!=null){ toOrgId = request.getParameter("toOrgId"); orgId = toOrgId; }else toOrgId = orgId; List orgItemlist = new ArrayList(); try { orgItemlist = itemInstDaoImpl.getItemlistByOrgId(new Integer(orgId),null,null); //取出第一个单位的事项 } catch (Exception e) { e.printStackTrace(); } IntanceModel imodel = new IntanceModel(); if(!orgId.equals("0")&&orgId.length()>0) imodel.setOrgId(Integer.parseInt(orgId)); String search_name = request.getParameter("search_name"); String search_instNumber = request.getParameter("search_instNumber"); String itemId = request.getParameter("itemId"); String state = request.getParameter("state") ; String search_userName = request.getParameter("search_userName"); String search_declarer = request.getParameter("search_declarer"); String declareTime = request.getParameter("declareTime"); String endtime = request.getParameter("endtime"); String search_all = request.getParameter("search_all"); if(search_name!=null&&search_name.length()>0){ search_name = search_name.trim(); try { search_name = new String(search_name.getBytes(),"GBK"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } imodel.setName(search_name); } if(search_instNumber!=null&&search_instNumber.length()>0) imodel.setInstNumber(search_instNumber); //事项编号 if(itemId!=null&&itemId.length()>0&&!itemId.equals("0")) imodel.setServiceId(itemId); //事项ID if(state!=null&&state.length()>0&&!state.equals("0")) imodel.setState(Integer.parseInt(state)); //状态 else imodel.setBy1("1,2,4,6,8,9,11,20,21"); if(search_userName!=null&&search_userName.length()>0){ search_userName = search_userName.trim(); try { search_userName = new String(search_userName.getBytes(),"GBK"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } imodel.setUserName(search_userName); } if(search_declarer!=null&&search_declarer.length()>0) imodel.setDeclarer(search_declarer); if(declareTime!=null&&declareTime.length()>0){ String st = declareTime+" 00:00"; imodel.setDeclareTime(SfUtil.stringToTimestamp(st,null)); } if(endtime!=null&&endtime.length()>0){ String et = endtime+" 23:58"; imodel.setDeclareEndTime(SfUtil.stringToTimestamp(et,null)); } if(search_all!=null&&search_all.length()>0){ search_all = search_all.trim(); try { search_all = new String(search_all.getBytes(),"GBK"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } imodel.setBy6(search_all); } //条件完成 Workbook handExcel = null; try { handExcel = Workbook.getWorkbook(sourceF); WritableWorkbook educeExcel = Workbook.createWorkbook(targetF,handExcel); WritableSheet targetExcelSheet = educeExcel.getSheet(0); //标题头 String title = "省人民政府政务服务中心办件情况表"; jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES,20, WritableFont.BOLD,false); WritableCellFormat MainCellFormat = new WritableCellFormat(wf); MainCellFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); //设置 border 格式 MainCellFormat.setAlignment(jxl.format.Alignment.CENTRE); MainCellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); MainCellFormat.setWrap(true); Label label = new Label(0,0,title,MainCellFormat); targetExcelSheet.addCell(label); setTimeLabelFormat(1,imodel,targetExcelSheet,label); //设置时间 imodel.setWillDate(SfUtil.getDateFormat(new Timestamp(System.currentTimeMillis()),2)); List datalist = iService.getDataListByModel(imodel,-1,-1,null); //写入过程数据 int position = 3; for(int i=0;i<datalist.size();i++){ IntanceModel model = (IntanceModel)datalist.get(i); model.setProcessModel(instanceProcessService.getInstanceProcesslistByIntanceIdState(model.getId(),imodel.getState())); model.setInstNumber(model.getInstNumber()==null||model.getInstNumber().equals("null")?"":model.getInstNumber()); //model.setStateName(IntanceItemUtil.getIntance().getStateImageByTime(model,1)); model.setBy1(IntanceItemUtil.getIntance().showStateName(model.getState())); //model.setBy2(IntanceItemUtil.getIntance().getImageText(model.getStateName())); SpUserModel sumodel = spUserImpl.getSpUserModelByCardId(model.getDeclarer());//机构代码取用户类型 model.setBy7(sumodel==null?"":(sumodel.getCardType().equals("1")?"身份证":"机构代码")); model.setBy8(sumodel==null?"":sumodel.getCardId()==null?"":sumodel.getCardId()); model.setBy9((i+1)+""); setDataLabelFormat(position,model,targetExcelSheet,label); model.setUserIdName(OaUtil.getUserNameById(model.getUserId())); position++; } educeExcel.write(); //写入Excel对象 educeExcel.close(); handExcel.close(); } catch (IOException e) { e.printStackTrace(); } catch (BiffException e) { e.printStackTrace(); } catch (RowsExceededException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (NamingException e) { e.printStackTrace(); } return targetPath; } /** * 设定表格格式 * @param position * @param targetExcelSheet * @param label * @throws IOException * @throws BiffException * @throws jxl.write.WriteException */ private void setDataLabelFormat(int position,IntanceModel model,WritableSheet targetExcelSheet,Label label) throws IOException,BiffException, WriteException { label = new Label(0,position,model.getBy9()); targetExcelSheet.addCell(label); label = new Label(1,position,model.getDeclarerName()); targetExcelSheet.addCell(label); label = new Label(2,position,model.getName()); targetExcelSheet.addCell(label); label = new Label(3,position,model.getBy5()); targetExcelSheet.addCell(label); label = new Label(4,position,model.getDeclareTime()==null?"":SfUtil.getDateFormat(model.getDeclareTime(),2)); targetExcelSheet.addCell(label); label = new Label(5,position,model.getUserName()); targetExcelSheet.addCell(label); label = new Label(6,position,model.getUserPhone()); targetExcelSheet.addCell(label); label = new Label(7,position,model.getBy7()); targetExcelSheet.addCell(label); label = new Label(8,position,model.getBy8()); targetExcelSheet.addCell(label); label = new Label(9,position,""); targetExcelSheet.addCell(label); WritableCellFormat BodyCellFormat = new WritableCellFormat(); BodyCellFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); //设置 border 格式 BodyCellFormat.setAlignment(jxl.format.Alignment.CENTRE); BodyCellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); BodyCellFormat.setWrap(true); targetExcelSheet.getWritableCell(0,position).setCellFormat(BodyCellFormat); targetExcelSheet.getWritableCell(1,position).setCellFormat(BodyCellFormat); targetExcelSheet.getWritableCell(2,position).setCellFormat(BodyCellFormat); targetExcelSheet.getWritableCell(3,position).setCellFormat(BodyCellFormat); targetExcelSheet.getWritableCell(4,position).setCellFormat(BodyCellFormat); targetExcelSheet.getWritableCell(5,position).setCellFormat(BodyCellFormat); targetExcelSheet.getWritableCell(6,position).setCellFormat(BodyCellFormat); targetExcelSheet.getWritableCell(7,position).setCellFormat(BodyCellFormat); targetExcelSheet.getWritableCell(8,position).setCellFormat(BodyCellFormat); targetExcelSheet.getWritableCell(9,position).setCellFormat(BodyCellFormat); } private void setTimeLabelFormat(int position,IntanceModel model,WritableSheet targetExcelSheet,Label label) throws IOException,BiffException, WriteException { String startTime = model.getDeclareTime()==null?"":SfUtil.getDateFormat(model.getDeclareTime(),2); String endTime = model.getDeclareEndTime()==null?"":SfUtil.getDateFormat(model.getDeclareEndTime(),2); label = new Label(1,position,startTime); targetExcelSheet.addCell(label); label = new Label(3,position,endTime); targetExcelSheet.addCell(label); WritableCellFormat BodyCellFormat = new WritableCellFormat(); BodyCellFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); //设置 border 格式 BodyCellFormat.setAlignment(jxl.format.Alignment.CENTRE); BodyCellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); BodyCellFormat.setWrap(true); targetExcelSheet.getWritableCell(1,position).setCellFormat(BodyCellFormat); targetExcelSheet.getWritableCell(3,position).setCellFormat(BodyCellFormat); } } 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |