Chinaunix首页 | 论坛 | 博客

燕去燕来燕复还feifan.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

非凡公子

  • 博客访问： 420595
  • 博文数量： 77
  • 博客积分： 2303
  • 博客等级： 大尉
  • 技术积分： 808
  • 用 户 组： 普通用户
  • 注册时间： 2004-11-30 09:15
文章分类

全部博文（77）

文章存档

2015年（1）

2013年（3）

2012年（2）

2011年（46）

2009年（4）

2008年（2）

2005年（12）

2004年（7）

我的朋友
最近访客
推荐博文
相关博文
jxl从数据库生成excel

分类： Java

2011-04-29 23:59:22


  1. /**
  2.  * Title :ExcelMaker.java
  3.  * Project :kdza
  4.  * Description :Excel报表生成
  5.  * Call Module :
  6.  * File :
  7.  * Copyright :Copyright (c) 2011-2011
  8.  * Company :mdc
  9.  * Create Date :2011.04.27
  10.  *
  11.  * Revision history
  12.  * Name Date         Description
  13.  * ----      ----     -----------
  14.  *    Chenqh     2011.04.27 初步实现功能
  15.  *
  16.  *
  17.  * @Author :ChenQH
  18.  * @version 1.0 版本
  19.  *
  20.  */
  21. package com.kdza.bl.report;

  23. import java.io.*;
  24. import java.text.SimpleDateFormat;

  26. import jxl.Workbook;
  27. import jxl.write.*;

  29. import com.kdza.db.DBMS;
  30. import oracle.jdbc.rowset.*;

  32. public class ExcelMaker
  33. {

  35.     /** 类变量 */
  36.     SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  37.     String dateTime = df.format(new java.util.Date());
  38.     String xlsFile = "";
  39.     OutputStream os = null;
  40.     WritableWorkbook workbook = null;
  41.     WritableSheet sheet = null;
  42.     String formula = "";
  43.     Label cellText = null;
  44.     jxl.write.Number cellInt = null;

  46.     /**
  47.      * 构造函数
  48.      */
  49.     public ExcelMaker()
  50.     {
  51.     }
  52.     /*
  53.      * 构造函数
  54.      * @param file 文件名
  55.      * @throws java.io.IOException
  56.      * @throws java.lang.Exception
  57.      */
  58.     public ExcelMaker(String file) throws IOException, Exception
  59.     {
  60.         this.xlsFile = file;
  61.         os = new FileOutputStream(xlsFile);
  62.         workbook = Workbook.createWorkbook(os);
  63.         sheet = workbook.createSheet("sheet1", 0);
  64.     }

  66.     /**
  67.      * 设置文件名
  68.      * @param file 文件名
  69.      * @throws java.io.IOException
  70.      * @throws java.lang.Exception
  71.      */
  72.     public void setFile(String file) throws IOException, Exception
  73.     {
  74.         this.xlsFile = file;
  75.         os = new FileOutputStream(xlsFile);
  76.         workbook = Workbook.createWorkbook(os);
  77.         sheet = workbook.createSheet("sheet1", 0);
  78.     }
  79.     /**
  80.      * 写Excel表头(不含数据部分)
  81.      * @param type 业务类型
  82.      * @param stime 统计时间
  83.      * @param operator 统计涉及工号
  84.      * @throws java.lang.Exception
  85.      */
  86.     public void writeHeader(String type, String stime, String operator) throws Exception
  87.     {
  88.         //格式设置
  89.         //宋体18号字粗体
  90.         WritableFont font18 = new WritableFont(WritableFont.createFont("宋体"), 18, WritableFont.BOLD, false);
  91.         WritableCellFormat cellFmt18 = new WritableCellFormat(font18);
  92.         cellFmt18.setAlignment(jxl.format.Alignment.CENTRE);
  93.         cellFmt18.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
  94.         //宋体10号字粗体
  95.         WritableFont font10 = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.BOLD, false);
  96.         WritableCellFormat cellFmt10 = new WritableCellFormat(font10);
  97.         cellFmt10.setAlignment(jxl.format.Alignment.CENTRE);
  98.         cellFmt10.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

  100.         for (int i = 0; i < 30; i++)
  101.         {
  102.             sheet.setColumnView(i, 12);
  103.         }

  105.         cellText = new Label(0, 0, "话务员工作情况报表(东亭)", cellFmt18);
  106.         //把单元格（column, row）到单元格（column1, row1）进行合并;mergeCells(column, row, column1, row1)
  107.         sheet.mergeCells(0, 0, 14, 2);
  108.         sheet.addCell(cellText);

  110.         //把数据写入第10列,第6行 Label（column, row）数字为未合并的计数
  111.         cellText = new Label(0, 3, "导出时间：" + dateTime, cellFmt10);
  112.         sheet.mergeCells(0, 3, 14, 3);
  113.         sheet.addCell(cellText);

  115.         cellText = new Label(0, 4, "业务类型:" + type + " 开始时间:" + stime, cellFmt10);
  116.         sheet.mergeCells(0, 4, 8, 4);
  117.         sheet.addCell(cellText);

  119.         cellText = new Label(13, 4, "部门: 无锡东亭外呼中心", cellFmt10);
  120.         sheet.mergeCells(13, 4, 14, 4);
  121.         sheet.addCell(cellText);

  123.         cellText = new Label(0, 5, "员工: " + operator, cellFmt10);
  124.         sheet.mergeCells(0, 5, 8, 5);
  125.         sheet.addCell(cellText);

  127.         sheet.setRowView(6, 400);
  128.         cellText = new Label(0, 6, "时间", cellFmt10);
  129.         sheet.addCell(cellText);

  131.         cellText = new Label(1, 6, "工号", cellFmt10);
  132.         sheet.addCell(cellText);

  134.         cellText = new Label(2, 6, "姓名", cellFmt10);
  135.         sheet.addCell(cellText);

  137.         sheet.setColumnView(3, 10);
  138.         cellText = new Label(3, 6, "签入次数", cellFmt10);
  139.         sheet.addCell(cellText);

  141.         sheet.setColumnView(4, 10);
  142.         cellText = new Label(4, 6, "签出次数", cellFmt10);
  143.         sheet.addCell(cellText);

  145.         sheet.setColumnView(5, 14);
  146.         cellText = new Label(5, 6, "工作时长", cellFmt10);
  147.         sheet.addCell(cellText);

  149.         cellText = new Label(6, 6, "工作时长(秒)", cellFmt10);
  150.         sheet.addCell(cellText);

  152.         sheet.setColumnView(7, 14);
  153.         cellText = new Label(7, 6, "通话时长", cellFmt10);
  154.         sheet.addCell(cellText);

  156.         cellText = new Label(8, 6, "通话时长(秒)", cellFmt10);
  157.         sheet.addCell(cellText);

  159.         sheet.setColumnView(9, 14);
  160.         cellText = new Label(9, 6, "呼出时长", cellFmt10);
  161.         sheet.addCell(cellText);

  163.         cellText = new Label(10, 6, "呼出时长(秒)", cellFmt10);
  164.         sheet.addCell(cellText);

  166.         cellText = new Label(11, 6, "呼出次数", cellFmt10);
  167.         sheet.addCell(cellText);

  169.         cellText = new Label(12, 6, "呼出成功次数", cellFmt10);
  170.         sheet.addCell(cellText);

  172.         cellText = new Label(13, 6, "呼出成功均长", cellFmt10);
  173.         sheet.addCell(cellText);

  175.         cellText = new Label(14, 6, "工时利用率", cellFmt10);
  176.         sheet.addCell(cellText);
  177.         //workbook.write();
  178.     }

  180.     /**
  181.      * 写Excel表数据部分
  182.      * @param ocrs rowset数据集
  183.      * @param ocrs 包含条件查询的数据
  184.      * @throws java.lang.Exception
  185.      */
  186.     public void writeData(OracleCachedRowSet ocrs) throws Exception
  187.     {
  188.         String workDates = "";
  189.         int operaNO = 0;
  190.         String operaName = "";
  191.         int loginCnt = 0;
  192.         int logoutCnt = 0;
  193.         String workTime = "";
  194.         int workTimeM = 0;
  195.         String callTime = "";
  196.         int callTimeM = 0;
  197.         int calloutCnt = 0;
  198.         int calloutSuCnt = 0;
  199.         int calloutAVG = 0;
  200.         String workRatio = "";
  201.         //宋体12号字
  202.         WritableFont font12 = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false);
  203.         WritableCellFormat cellFmt12 = new WritableCellFormat(font12);
  204.         cellFmt12.setAlignment(jxl.format.Alignment.CENTRE);
  205.         cellFmt12.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

  207.         //设置百分数格式
  208.         WritableFont percentFont12 = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false);
  209.         NumberFormat percent = new NumberFormat("0.00%");
  210.         WritableCellFormat cellFmtPercent12 = new WritableCellFormat(percent);
  211.         cellFmtPercent12.setFont(percentFont12);
  212.         cellFmtPercent12.setAlignment(jxl.format.Alignment.CENTRE);
  213.         cellFmtPercent12.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

  215.         WritableFont numberFont12 = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false);
  216.         WritableCellFormat cellFmtNumbert12 = new WritableCellFormat(numberFont12);
  217.         cellFmtNumbert12.setAlignment(jxl.format.Alignment.CENTRE);
  218.         cellFmtNumbert12.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

  220.         int row = 7;
  221.         ocrs.beforeFirst();
  222.         while (ocrs.next())
  223.         {
  224.             workDates = ocrs.getString("DATES");
  225.             operaNO = ocrs.getInt("OPERATORNO");
  226.             operaName = ocrs.getString("OPERATORNAME");
  227.             loginCnt = ocrs.getInt("LOGINCNT");
  228.             logoutCnt = ocrs.getInt("LOGOUTCNT");
  229.             workTime = ocrs.getString("WORKTIME");
  230.             workTimeM = ocrs.getInt("WORKTIMEM");
  231.             callTime = ocrs.getString("CALLTIME");
  232.             callTimeM = ocrs.getInt("CALLTIMEM");
  233.             calloutCnt = ocrs.getInt("CALLOUTCNT");
  234.             calloutSuCnt = ocrs.getInt("CALLOUTSUCNT");
  235.             calloutAVG = ocrs.getInt("CALLOUTAVG");
  236.             // float ratio = (float)callTimeM/workTimeM*100;
  237.             // DecimalFormat rtoFormat = new DecimalFormat( "0.00 ");
  238.             // workRatio = rtoFormat.format(ratio);

  240.             cellText = new Label(0, row, workDates, cellFmt12);
  241.             sheet.addCell(cellText);

  243.             cellInt = new jxl.write.Number(1, row, operaNO, cellFmt12);
  244.             sheet.addCell(cellInt);

  246.             cellText = new Label(2, row, operaName, cellFmt12);
  247.             sheet.addCell(cellText);

  249.             cellInt = new jxl.write.Number(3, row, loginCnt, cellFmt12);
  250.             sheet.addCell(cellInt);

  252.             cellInt = new jxl.write.Number(4, row, logoutCnt, cellFmt12);
  253.             sheet.addCell(cellInt);

  255.             cellText = new Label(5, row, workTime, cellFmt12);
  256.             sheet.addCell(cellText);

  258.             cellInt = new jxl.write.Number(6, row, workTimeM, cellFmt12);
  259.             sheet.addCell(cellInt);

  261.             cellText = new Label(7, row, callTime, cellFmt12);
  262.             sheet.addCell(cellText);

  264.             cellInt = new jxl.write.Number(8, row, callTimeM, cellFmt12);
  265.             sheet.addCell(cellInt);

  267.             cellText = new Label(9, row, callTime, cellFmt12);
  268.             sheet.addCell(cellText);

  270.             cellInt = new jxl.write.Number(10, row, callTimeM, cellFmt12);
  271.             sheet.addCell(cellInt);

  273.             cellInt = new jxl.write.Number(11, row, calloutCnt, cellFmt12);
  274.             sheet.addCell(cellInt);

  276.             cellInt = new jxl.write.Number(12, row, calloutSuCnt, cellFmt12);
  277.             sheet.addCell(cellInt);

  279.             cellInt = new jxl.write.Number(13, row, calloutAVG, cellFmt12);
  280.             sheet.addCell(cellInt);

  282.             //row需要加1,excel从1行开始.jxl从0行计数
  283.             formula = "SUM(" + "I" + (row + 1) + "/G" + (row + 1) + ")";
  284.             //System.out.println(formula);
  285.             //cellText = new Formula(0, row, formula, cellFmt12);
  286.             sheet.addCell(new Formula(14, row, formula, cellFmtPercent12));

  288.             row++;
  289.         }
  290.         cellText = new Label(0, row, "总计:", cellFmt12);
  291.         sheet.addCell(cellText);
  292.         //excel中的row计数从1开始,jxl从0开始,至使引用excel中的row时总比jxl多1
  293.         formula = "SUM(" + "D8:" + "D" + row + ")";
  294.         sheet.addCell(new Formula(3, row, formula, cellFmtNumbert12));
  295.         
  296.         formula = "SUM(" + "E8:" + "E" + row + ")";
  297.         sheet.addCell(new Formula(4, row, formula, cellFmtNumbert12));
  298.         
  299.         formula = "SUM(" + "G8:" + "G" + row + ")";
  300.         sheet.addCell(new Formula(6, row, formula, cellFmtNumbert12));
  301.         
  302.         formula = "SUM(" + "I8:" + "I" + row + ")";
  303.         sheet.addCell(new Formula(8, row, formula, cellFmtNumbert12));
  304.         
  305.         formula = "SUM(" + "K8:" + "K" + row + ")";
  306.         sheet.addCell(new Formula(10, row, formula, cellFmtNumbert12));
  307.         
  308.         formula = "SUM(" + "L8:" + "L" + row + ")";
  309.         sheet.addCell(new Formula(11, row, formula, cellFmtNumbert12));
  310.         
  311.         formula = "SUM(" + "M8:" + "M" + row + ")";
  312.         sheet.addCell(new Formula(12, row, formula, cellFmtNumbert12));
  313.         // workbook.write();
  314.     }

  316.     /**
  317.      * 写入数据,并闭文件
  318.      * @throws java.io.IOException
  319.      * @throws java.lang.Exception
  320.      */
  321.     public void closeExcel() throws IOException, Exception
  322.     {
  323.         workbook.write();
  324.         workbook.close();
  325.         os.close();
  326.     }

  328.     public static void main(String args[])
  329.     {
  330.         try
  331.         {
  332.             DBMS db = new DBMS();
  333.             db.connDB();
  334.             Report rep = new Report(db);
  335.             rep.makeReport("1000", "", "2011-04-01", "2011-04-15");
  336.             OracleCachedRowSet repList = rep.getReport();
  337.             db.closeDB();

  339.             ExcelMaker em = new ExcelMaker("d:/tt.xls");
  340.             em.writeHeader("障碍维修", "2011-04-07", "6613 钱燕敏|6620 沈丹雯|6661 邵海燕");
  341.             em.writeData(repList);
  342.             em.closeExcel();

  344.         } catch (Exception e)
  345.         {
  346.             System.out.println(e.toString());
  347.         }
  348.     }
  349. }
阅读(7268) | 评论(0) | 转发(0) |
0

上一篇：lsof使用技巧

下一篇：jxl颜色表

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6