Chinaunix首页 | 论坛 | 博客
  • 博客访问: 421012
  • 博文数量: 77
  • 博客积分: 2303
  • 博客等级: 大尉
  • 技术积分: 808
  • 用 户 组: 普通用户
  • 注册时间: 2004-11-30 09:15
文章存档

2015年(1)

2013年(3)

2012年(2)

2011年(46)

2009年(4)

2008年(2)

2005年(12)

2004年(7)

我的朋友

分类: 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;

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

  24. import jxl.Workbook;
  25. import jxl.write.*;

  26. import com.kdza.db.DBMS;
  27. import oracle.jdbc.rowset.*;

  28. public class ExcelMaker
  29. {

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

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

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

  92.         for (int i = 0; i < 30; i++)
  93.         {
  94.             sheet.setColumnView(i, 12);
  95.         }

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

  100.         //把数据写入第10列,第6行 Label(column, row)数字为未合并的计数
  101.         cellText = new Label(0, 3, "导出时间:" + dateTime, cellFmt10);
  102.         sheet.mergeCells(0, 3, 14, 3);
  103.         sheet.addCell(cellText);

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

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

  110.         cellText = new Label(0, 5, "员工: " + operator, cellFmt10);
  111.         sheet.mergeCells(0, 5, 8, 5);
  112.         sheet.addCell(cellText);

  113.         sheet.setRowView(6, 400);
  114.         cellText = new Label(0, 6, "时间", cellFmt10);
  115.         sheet.addCell(cellText);

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

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

  120.         sheet.setColumnView(3, 10);
  121.         cellText = new Label(3, 6, "签入次数", cellFmt10);
  122.         sheet.addCell(cellText);

  123.         sheet.setColumnView(4, 10);
  124.         cellText = new Label(4, 6, "签出次数", cellFmt10);
  125.         sheet.addCell(cellText);

  126.         sheet.setColumnView(5, 14);
  127.         cellText = new Label(5, 6, "工作时长", cellFmt10);
  128.         sheet.addCell(cellText);

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

  131.         sheet.setColumnView(7, 14);
  132.         cellText = new Label(7, 6, "通话时长", cellFmt10);
  133.         sheet.addCell(cellText);

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

  136.         sheet.setColumnView(9, 14);
  137.         cellText = new Label(9, 6, "呼出时长", cellFmt10);
  138.         sheet.addCell(cellText);

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

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

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

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

  147.         cellText = new Label(14, 6, "工时利用率", cellFmt10);
  148.         sheet.addCell(cellText);
  149.         //workbook.write();
  150.     }

  151.     /**
  152.      * 写Excel表数据部分
  153.      * @param ocrs rowset数据集
  154.      * @param ocrs 包含条件查询的数据
  155.      * @throws java.lang.Exception
  156.      */
  157.     public void writeData(OracleCachedRowSet ocrs) throws Exception
  158.     {
  159.         String workDates = "";
  160.         int operaNO = 0;
  161.         String operaName = "";
  162.         int loginCnt = 0;
  163.         int logoutCnt = 0;
  164.         String workTime = "";
  165.         int workTimeM = 0;
  166.         String callTime = "";
  167.         int callTimeM = 0;
  168.         int calloutCnt = 0;
  169.         int calloutSuCnt = 0;
  170.         int calloutAVG = 0;
  171.         String workRatio = "";
  172.         //宋体12号字
  173.         WritableFont font12 = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false);
  174.         WritableCellFormat cellFmt12 = new WritableCellFormat(font12);
  175.         cellFmt12.setAlignment(jxl.format.Alignment.CENTRE);
  176.         cellFmt12.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

  177.         //设置百分数格式
  178.         WritableFont percentFont12 = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false);
  179.         NumberFormat percent = new NumberFormat("0.00%");
  180.         WritableCellFormat cellFmtPercent12 = new WritableCellFormat(percent);
  181.         cellFmtPercent12.setFont(percentFont12);
  182.         cellFmtPercent12.setAlignment(jxl.format.Alignment.CENTRE);
  183.         cellFmtPercent12.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

  184.         WritableFont numberFont12 = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD, false);
  185.         WritableCellFormat cellFmtNumbert12 = new WritableCellFormat(numberFont12);
  186.         cellFmtNumbert12.setAlignment(jxl.format.Alignment.CENTRE);
  187.         cellFmtNumbert12.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

  188.         int row = 7;
  189.         ocrs.beforeFirst();
  190.         while (ocrs.next())
  191.         {
  192.             workDates = ocrs.getString("DATES");
  193.             operaNO = ocrs.getInt("OPERATORNO");
  194.             operaName = ocrs.getString("OPERATORNAME");
  195.             loginCnt = ocrs.getInt("LOGINCNT");
  196.             logoutCnt = ocrs.getInt("LOGOUTCNT");
  197.             workTime = ocrs.getString("WORKTIME");
  198.             workTimeM = ocrs.getInt("WORKTIMEM");
  199.             callTime = ocrs.getString("CALLTIME");
  200.             callTimeM = ocrs.getInt("CALLTIMEM");
  201.             calloutCnt = ocrs.getInt("CALLOUTCNT");
  202.             calloutSuCnt = ocrs.getInt("CALLOUTSUCNT");
  203.             calloutAVG = ocrs.getInt("CALLOUTAVG");
  204.             // float ratio = (float)callTimeM/workTimeM*100;
  205.             // DecimalFormat rtoFormat = new DecimalFormat( "0.00 ");
  206.             // workRatio = rtoFormat.format(ratio);

  207.             cellText = new Label(0, row, workDates, cellFmt12);
  208.             sheet.addCell(cellText);

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

  211.             cellText = new Label(2, row, operaName, cellFmt12);
  212.             sheet.addCell(cellText);

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

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

  217.             cellText = new Label(5, row, workTime, cellFmt12);
  218.             sheet.addCell(cellText);

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

  221.             cellText = new Label(7, row, callTime, cellFmt12);
  222.             sheet.addCell(cellText);

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

  225.             cellText = new Label(9, row, callTime, cellFmt12);
  226.             sheet.addCell(cellText);

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

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

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

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

  235.             //row需要加1,excel从1行开始.jxl从0行计数
  236.             formula = "SUM(" + "I" + (row + 1) + "/G" + (row + 1) + ")";
  237.             //System.out.println(formula);
  238.             //cellText = new Formula(0, row, formula, cellFmt12);
  239.             sheet.addCell(new Formula(14, row, formula, cellFmtPercent12));

  240.             row++;
  241.         }
  242.         cellText = new Label(0, row, "总计:", cellFmt12);
  243.         sheet.addCell(cellText);
  244.         //excel中的row计数从1开始,jxl从0开始,至使引用excel中的row时总比jxl多1
  245.         formula = "SUM(" + "D8:" + "D" + row + ")";
  246.         sheet.addCell(new Formula(3, row, formula, cellFmtNumbert12));
  247.         
  248.         formula = "SUM(" + "E8:" + "E" + row + ")";
  249.         sheet.addCell(new Formula(4, row, formula, cellFmtNumbert12));
  250.         
  251.         formula = "SUM(" + "G8:" + "G" + row + ")";
  252.         sheet.addCell(new Formula(6, row, formula, cellFmtNumbert12));
  253.         
  254.         formula = "SUM(" + "I8:" + "I" + row + ")";
  255.         sheet.addCell(new Formula(8, row, formula, cellFmtNumbert12));
  256.         
  257.         formula = "SUM(" + "K8:" + "K" + row + ")";
  258.         sheet.addCell(new Formula(10, row, formula, cellFmtNumbert12));
  259.         
  260.         formula = "SUM(" + "L8:" + "L" + row + ")";
  261.         sheet.addCell(new Formula(11, row, formula, cellFmtNumbert12));
  262.         
  263.         formula = "SUM(" + "M8:" + "M" + row + ")";
  264.         sheet.addCell(new Formula(12, row, formula, cellFmtNumbert12));
  265.         // workbook.write();
  266.     }

  267.     /**
  268.      * 写入数据,并闭文件
  269.      * @throws java.io.IOException
  270.      * @throws java.lang.Exception
  271.      */
  272.     public void closeExcel() throws IOException, Exception
  273.     {
  274.         workbook.write();
  275.         workbook.close();
  276.         os.close();
  277.     }

  278.     public static void main(String args[])
  279.     {
  280.         try
  281.         {
  282.             DBMS db = new DBMS();
  283.             db.connDB();
  284.             Report rep = new Report(db);
  285.             rep.makeReport("1000", "", "2011-04-01", "2011-04-15");
  286.             OracleCachedRowSet repList = rep.getReport();
  287.             db.closeDB();

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

  292.         } catch (Exception e)
  293.         {
  294.             System.out.println(e.toString());
  295.         }
  296.     }
  297. }
阅读(7269) | 评论(0) | 转发(0) |
0

上一篇:lsof使用技巧

下一篇:jxl颜色表

给主人留下些什么吧!~~