예제 #1
0
  private int pro1(int column, int row, WritableSheet sheet)
      throws RowsExceededException, WriteException, SQLException {

    sheet.addCell(new Label(column, row, "ОАО \"Калуганефтепродукт\"", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column, row, column + 11, row);

    column = opt(column, row + 1, sheet, 1);

    column = nadbavka(column, row + 1, sheet);

    ave_k.add(column);
    column = vill(column, row + 1, sheet, 250, "г. Калуга розница");

    ave_obl_all.add(column);
    column = vill(column, row + 1, sheet, 251, "Область розница");

    column = vill(column, row + 1, sheet, 23, "г. Боровск");
    column = vill(column, row + 1, sheet, 212, "г. Обнинск");
    column = vill(column, row + 1, sheet, 58, "Жиздринский р-н");
    column = vill(column, row + 1, sheet, 5, "Бабынинский р-н");
    column = vill(column, row + 1, sheet, 40, "Дзержинский р-н");
    column = vill(column, row + 1, sheet, 90, "Козельский р-н");

    return column;
  }
예제 #2
0
  private int pro5(int column, int row, WritableSheet sheet)
      throws RowsExceededException, WriteException, SQLException {
    sheet.addCell(new Label(column, row, "ИП Палашичев", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column, row, column + 1, row);

    ave_k.add(column);
    column = vill(column, row + 1, sheet, 199, "г. Калуга");

    ave_obl_reg.add(column);
    column = vill(column, row + 1, sheet, 9, "Бабынинский р-н");

    return column;
  }
예제 #3
0
  private int pro8(int column, int row, WritableSheet sheet)
      throws RowsExceededException, WriteException, SQLException {
    sheet.addCell(new Label(column, row, "ИП Пешков", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column, row, column + 2, row);

    ave_obl_reg.add(column);
    column = vill(column, row + 1, sheet, 144, "Сухиничский р-н");
    ave_obl_reg.add(column);
    column = vill(column, row + 1, sheet, 155, "Ульяновский р-н");
    ave_obl_reg.add(column);
    column = vill(column, row + 1, sheet, 138, "Перемышельский р-н");

    return column;
  }
예제 #4
0
  private int pro3(int column, int row, WritableSheet sheet)
      throws RowsExceededException, WriteException, SQLException {
    sheet.addCell(new Label(column, row, "ООО ТД \"Альфа-Трейд\"", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column, row, column + 1, row);

    ave_k.add(column);
    column = vill(column, row + 1, sheet, 195, "г. Калуга");
    // sheet.addCell(new Label(column, row + 1, "в т.ч. мини АЗС",
    // font.tahomaLabelTitle));
    // column++;
    column = vill(column, row + 1, sheet, 254, "в т.ч. мини АЗС");

    return column;
  }
예제 #5
0
  private int pro4(int column, int row, WritableSheet sheet)
      throws RowsExceededException, WriteException, SQLException {

    sheet.addCell(
        new Label(column, row, "ООО \"Луйкойл-Центрнефтепродукт\"", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column, row, column + 1, row);

    ave_k.add(column);
    column = vill(column, row + 1, sheet, 255, "г. Калуга розница");

    ave_obl_all.add(column);
    column = vill(column, row + 1, sheet, 256, "Область розница");

    return column;
  }
예제 #6
0
  private int pro11(int column, int row, WritableSheet sheet)
      throws RowsExceededException, WriteException, SQLException {
    sheet.addCell(new Label(column, row, "ООО \"Восток-Ойл\"", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column, row, column + 3, row);

    ave_obl_reg.add(column);
    column = vill(column, row + 1, sheet, 133, "г. Мосальск");
    ave_obl_reg.add(column);
    column = vill(column, row + 1, sheet, 123, "Малоярославецкий р-н");
    ave_obl_reg.add(column);
    column = vill(column, row + 1, sheet, 130, "Мещовский р-н");
    ave_obl_reg.add(column);
    column = vill(column, row + 1, sheet, 94, "Куйбышевский р-н");

    return column;
  }
예제 #7
0
 /**
  * 合并表格
  *
  * @param sheet 工作表
  * @param mergeInfo 要合并的表格的信息
  * @throws RowsExceededException
  * @throws NumberFormatException
  * @throws WriteException
  */
 private void merge(WritableSheet sheet, String[] mergeInfo)
     throws RowsExceededException, NumberFormatException, WriteException {
   if (VerifyUtil.isNullObject(sheet) || VerifyUtil.isNull1DArray(mergeInfo)) {
     return;
   } else if (!this.isMergeInfo(mergeInfo)) {
     return;
   } else {
     for (String str : mergeInfo) {
       String[] temp = str.split(",");
       sheet.mergeCells(
           Integer.parseInt(temp[1]),
           Integer.parseInt(temp[0]),
           Integer.parseInt(temp[3]),
           Integer.parseInt(temp[2]));
     }
   }
 }
예제 #8
0
  private int pro2(int column, int row, WritableSheet sheet)
      throws RowsExceededException, WriteException, SQLException {
    sheet.addCell(new Label(column, row, "ООО \"Газпромнефть-Центр\"", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column, row, column + 9, row);

    column = opt(column, row + 1, sheet, 2);

    column = nadbavka(column, row + 1, sheet);

    ave_k.add(column);
    column = vill(column, row + 1, sheet, 252, "г. Калуга розница");

    ave_obl_all.add(column);
    column = vill(column, row + 1, sheet, 253, "Область розница");

    column = vill(column, row + 1, sheet, 41, "Дзержинский р-н");
    column = vill(column, row + 1, sheet, 85, "Износковский р-н");
    column = vill(column, row + 1, sheet, 62, "Жиздринский р-н");
    column = vill(column, row + 1, sheet, 14, "Бабынинский р-н");

    return column;
  }
  @Override
  public void writeExcelFile(HttpServletRequest request, WritableSheet sheet) {
    try {
      String start_date = StringUtils.nullToStr(request.getParameter("start_date"));
      String end_date = StringUtils.nullToStr(request.getParameter("end_date"));
      String dept_id = StringUtils.nullToStr(request.getParameter("dept_id"));

      String clientId = StringUtils.nullToStr(request.getParameter("clientId"));
      String client_name = StringUtils.nullToStr(request.getParameter("client_name"));
      String product_name = StringUtils.nullToStr(request.getParameter("product_name"));
      String product_kind = StringUtils.nullToStr(request.getParameter("product_kind"));

      String xsry_name = StringUtils.nullToStr(request.getParameter("xsry_name"));

      String con = "";
      con = "日期:" + start_date + "至" + end_date;
      if (!clientId.equals("")) {
        con += "  客户名称:" + clientId;
      }
      if (!dept_id.equals("")) {
        con += "  部门:" + StaticParamDo.getDeptNameById(dept_id);
      }

      if (!product_kind.equals("")) {
        con += "  商品类别:" + StaticParamDo.getProductKindNameById(product_kind);
      }
      con += " 报表生成时间:" + DateComFunc.getCurTime();

      Label label = null;

      // 写统计表标题
      sheet.mergeCells(0, 0, 7, 0);
      label = new Label(0, 0, "商品序列号销售汇总", this.getFt_title());
      sheet.addCell(label);

      // 写统计条件
      sheet.mergeCells(0, 1, 7, 1);
      label = new Label(0, 1, con, this.getFt_item_center());
      sheet.addCell(label);

      // 写统计表头
      label = new Label(0, 2, "日期", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(1, 2, "序列号", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(2, 2, "商品名称", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(3, 2, "商品规格", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(4, 2, "客户名称", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(5, 2, "客户电话", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(6, 2, "销售员", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(7, 2, "销售价格", this.getFt_item_center_bold());
      sheet.addCell(label);

      List resultList =
          productSerialNumXsHzService.getSerialNumXsList(
              start_date, end_date, product_kind, product_name, dept_id, xsry_name, clientId);

      int hj_nums = 0;
      double hj_je = 0;
      int k = 3;

      // 写统计内容
      if (resultList != null && resultList.size() > 0) {
        for (int i = 0; i < resultList.size(); i++) {
          Map map = (Map) resultList.get(i);
          double je = map.get("xsdj") == null ? 0 : ((Double) map.get("xsdj")).doubleValue();

          label =
              new Label(0, k, StringUtils.nullToStr(map.get("fs_date")), this.getFt_item_left());
          sheet.addCell(label);
          label =
              new Label(1, k, StringUtils.nullToStr(map.get("serial_num")), this.getFt_item_left());
          sheet.addCell(label);
          label =
              new Label(
                  2, k, StringUtils.nullToStr(map.get("product_name")), this.getFt_item_left());
          sheet.addCell(label);
          label =
              new Label(3, k, StringUtils.nullToStr(map.get("product_xh")), this.getFt_item_left());
          sheet.addCell(label);
          label =
              new Label(
                  4, k, StringUtils.nullToStr(map.get("client_name")), this.getFt_item_left());
          sheet.addCell(label);
          label = new Label(5, k, StringUtils.nullToStr(map.get("tel")), this.getFt_item_left());
          sheet.addCell(label);
          label = new Label(6, k, StringUtils.nullToStr(map.get("jsr")), this.getFt_item_left());
          sheet.addCell(label);
          label = new Label(7, k, JMath.round(je, 2), this.getFt_item_right());
          sheet.addCell(label);

          k++;
        }
      }

    } catch (Exception ex) {
      log.info(ex);
    }
  }
예제 #10
0
  public OutputSvod() throws IOException, WriteException, ParseException, SQLException {
    /** Формирование дат отчетности */

    /** текущие время */
    long current = System.currentTimeMillis();

    // TODO Заглушка времени
    //	current = 1366920000000l + 86400000 - 1;

    /** заполнение начинается с последнего элемента */
    time[time.length - 1] = current;
    for (int i = time.length - 2; i >= 0; i--) {
      /**
       * Если i-ая дата не является Сб или Вс записывается предыдущий день Если i-ая дата Сб или Вс
       * - записывается Пятница
       */

      /** коэффицент смещения дней */
      int mod = 1;

      /** День недели i-ого числа */
      String day = new SimpleDateFormat("E", Locale.ENGLISH).format(time[i + 1] - 86400000);

      /** Корректировка коэффицента смещения, если i-ый день Сб или Вс */
      if (day.equals("Sat")) mod = 2;
      else if (day.equals("Sun")) mod = 3;

      /** Сохраняем нужную дату */
      time[i] = time[i + 1] - 86400000 * mod;
    }

    /** Округление времени к 23:59:59 */
    for (int i = 0; i < time.length; i++) {
      SimpleDateFormat formatter = new SimpleDateFormat("dd.MM.yyyy");
      Date date = formatter.parse(formatter.format(new Date(time[i])));
      time[i] = date.getTime() + 86400000 - 1;
    }

    // for (long index : time)
    // System.out.println(new Date(index));

    SimpleDateFormat formatter = new SimpleDateFormat("dd.MM.yyyy");

    WritableWorkbook workbook =
        Workbook.createWorkbook(
            new File(
                "svod_data_" + formatter.format(new Date(System.currentTimeMillis())) + ".xls"));

    WritableSheet sheet = workbook.createSheet("Мнониторинг цен", 0);
    sheet.addCell(
        new Label(
            0,
            0,
            "Мониторинг цен на нефтепродукты, реализуемые на автозаправочных станциях Калуги и Калужской области ",
            font.tahoma14ptBold));
    sheet.mergeCells(0, 0, 22, 0);
    sheet.setRowView(0, 700);

    int column = 1;
    int row = 4;

    column = cape(column, row, sheet);

    // калуганефтепродукт
    column = pro1(column, row, sheet);
    // газпром
    column = pro2(column, row, sheet);
    // Альфа-трейд
    column = pro3(column, row, sheet);

    column = cape(column, row, sheet);

    // лукойл
    column = pro4(column, row, sheet);
    // ИП палашичев
    column = pro5(column, row, sheet);
    // тран азс сервис
    column = pro6(column, row, sheet);
    // октан
    column = pro7(column, row, sheet);
    // ПИ пешков
    column = pro8(column, row, sheet);
    // ИП журавлева
    column = pro9(column, row, sheet);
    // березка
    column = pro10(column, row, sheet);
    // Восток ойл
    column = pro11(column, row, sheet);
    // солид
    column = pro12(column, row, sheet);
    // экоресурс
    column = pro13(column, row, sheet);
    // Белова
    column = pro14(column, row, sheet);
    // Хвастовичи
    column = pro15(column, row, sheet);
    ave_result(5, 65, sheet);

    workbook.write();
    workbook.close();
    JOptionPane.showMessageDialog(null, "Готово");
  }
  private void generateEmployeePerformanceReport(WritableWorkbook workbook, String key)
      throws Exception {
    if (key.length() > 2) {
      WritableFont headerfont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD);
      WritableCellFormat headerFormat = new WritableCellFormat(headerfont);
      headerFormat.setAlignment(Alignment.CENTRE);

      WritableFont feemergefont = new WritableFont(WritableFont.ARIAL, 13, WritableFont.BOLD);
      WritableCellFormat feemergeFormat = new WritableCellFormat(feemergefont);
      feemergeFormat.setAlignment(Alignment.CENTRE);
      feemergeFormat.setBackground(Colour.GRAY_25);
      feemergeFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

      WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
      WritableCellFormat numbercellformat = new WritableCellFormat(NumberFormats.FLOAT);
      numbercellformat.setFont(font);
      numbercellformat.setBorder(Border.ALL, BorderLineStyle.THIN);

      key = key.substring(1, key.length() - 1);

      WritableSheet sheet = workbook.createSheet("Report For Selected Employees", 0);

      JSONArray employeeTourRecords = getEmployeeTourRecords(key);

      HashMap<Integer, HashMap<Integer, Object[]>> employeeTourDetailsMap =
          new HashMap<Integer, HashMap<Integer, Object[]>>();
      HashMap<Integer, HashMap<Integer, Object[]>> employeeTourRequestAndDetailsMap =
          new HashMap<Integer, HashMap<Integer, Object[]>>();
      putRecordsIntoMap(
          employeeTourDetailsMap, employeeTourRecords, employeeTourRequestAndDetailsMap);
      int row = 5;
      int column = 0;
      sheet.mergeCells(column + 2, row - 4, column + 8, row - 4);

      putHeaderDefault(
          feemergeFormat,
          sheet,
          "Employee Tour Expense Sheet For Selected Employees",
          row - 4,
          column + 2);

      putHeaderDefault(feemergeFormat, sheet, "S. No.", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Employee Code", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Employee Name", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Tour Code", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Advance", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "City", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "From Date", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "To Date", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Expected Amount", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Actual Amount", row, column++);

      int serNo = 0;
      for (Integer employeeId : employeeTourRequestAndDetailsMap.keySet()) {
        HashMap<Integer, Object[]> tourRequestDetailsMap =
            employeeTourRequestAndDetailsMap.get(employeeId);
        for (Integer tourId : tourRequestDetailsMap.keySet()) {
          Object[] details = tourRequestDetailsMap.get(tourId);
          if (details != null) {
            column = 0;
            serNo++;
            row++;
            // int tourId = Translator.integerValue(details[0]);
            Object employeeName = details[1];
            Object employeeCode = details[2];
            Object advance = details[3];
            Object departOn = details[4];
            Object ariveOn = details[5];
            Object tourCode = details[6];
            Object totalEstimatedAmount = details[7];
            Object totalActualAmount = details[8];
            putHeaderDefault(headerFormat, sheet, serNo, row, column++);
            putHeaderDefault(headerFormat, sheet, employeeCode, row, column++);
            putHeaderDefault(headerFormat, sheet, employeeName, row, column++);
            putHeaderDefault(headerFormat, sheet, tourCode, row, column++);
            putHeader(numbercellformat, sheet, Translator.doubleValue(advance), row, column++);
            putHeaderDefault(headerFormat, sheet, "", row, column++);
            putHeaderDefault(headerFormat, sheet, departOn, row, column++);
            putHeaderDefault(headerFormat, sheet, ariveOn, row, column++);
            putHeaderDefault(headerFormat, sheet, "", row, column++);
            putHeaderDefault(headerFormat, sheet, "", row, column++);

            HashMap<Integer, Object[]> tourDetailsMap = employeeTourDetailsMap.get(tourId);
            if (tourDetailsMap != null) {
              for (Integer tourDetailKeys : tourDetailsMap.keySet()) {
                details = tourDetailsMap.get(tourDetailKeys);
                // details[0] = fromDate;
                // details[1] = toDate;
                // details[2] = estmateAmount;
                // details[3] = actualAmount;
                // details[4] = cityName;
                column = 5;
                row++;
                putHeaderDefault(headerFormat, sheet, details[4], row, column++);
                putHeaderDefault(headerFormat, sheet, details[0], row, column++);
                putHeaderDefault(headerFormat, sheet, details[1], row, column++);
                putHeader(
                    numbercellformat, sheet, Translator.doubleValue(details[2]), row, column++);
                putHeader(
                    numbercellformat, sheet, Translator.doubleValue(details[3]), row, column++);
              }
              column = 7;
              row++;
              putHeaderDefault(headerFormat, sheet, "Total", row, column++);
              putHeader(
                  numbercellformat,
                  sheet,
                  Translator.doubleValue(totalEstimatedAmount),
                  row,
                  column++);
              putHeader(
                  numbercellformat,
                  sheet,
                  Translator.doubleValue(totalActualAmount),
                  row,
                  column++);
            }
          }
        }
      }
      workbook.write();
      workbook.close();
    }
  }
예제 #12
0
  /**
   * Формирование таблицы "Изменение средней розничной цены"<br>
   * <br>
   * Номера колонок берутся из 3 массивов:<br>
   * - ave_k - по городу Калуга<br>
   * - ave_obl_all - розница в области по сетевым(Калуганефтепродукт, Газпромнефть и Лукойл)<br>
   * - ave_obl_reg - розница в области по АЗС входяших в мониторинг<br>
   * <br>
   * <br>
   * СРЗНАЧ по области берётся от СРЗНАЧ(СРЗНАЧ(мелких АЗС); Калуганефтепродукта; Газпромнефти;
   * Лукойли)
   *
   * @param column - начальная колонка таблицы
   * @param row - начальная строка таблицы
   * @param sheet - в этот лист будет заносится таблица
   * @throws RowsExceededException
   * @throws WriteException
   */
  private void ave_result(int column, int row, WritableSheet sheet)
      throws RowsExceededException, WriteException {

    /** составление шапки */
    sheet.addCell(new Label(column, row, "Изменение средней розничной цены", font.tahomaValue));
    sheet.mergeCells(column, row, column + 6, row++);

    sheet.addCell(new Label(column, row, "г. Калуга", font.tahomaValue));
    sheet.mergeCells(column, row, column + 2, row);

    sheet.addCell(new Label(column + 4, row, "Калужская область", font.tahomaValue));
    sheet.mergeCells(column + 4, row, column + 6, row++);

    /*
     * обработка данных - формирование формул
     */
    for (int i_toplivo = 0; i_toplivo < label_toplivo.length && i_toplivo < 4; i_toplivo++) {

      /** Наименование топлива в описание строки */
      for (int i = 0; i < 2; i++)
        sheet.addCell(
            new Label(column + i * 4, row + i_toplivo, label_toplivo[i_toplivo], font.tahomaValue));

      /** Формирование формул СРЗНАЧ */

      /** По Калуге */
      // в процентах
      String average_kaluga_per = getFormulaAVERAGE(getFormulaCells(50 + i_toplivo, ave_k));
      // в рублях
      String average_kaluga_val = getFormulaAVERAGE(getFormulaCells(58 + i_toplivo, ave_k));

      /** по области */

      // в процентах

      sheet.addCell(
          new Formula(
              column + 9,
              row + i_toplivo,
              getFormulaCOUNT(getFormulaCells(50 + i_toplivo, ave_obl_reg)),
              font.tahomaValue_white));

      sheet.addCell(
          new Formula(
              column + 8,
              row + i_toplivo,
              getFormulaSUM(getFormulaCells(50 + i_toplivo, ave_obl_reg)),
              font.tahomaValue_white));

      String div =
          toColumnExcel(column + 8)
              + (row + i_toplivo + 1)
              + "/"
              + toColumnExcel(column + 9)
              + (row + i_toplivo + 1);

      sheet.addCell(
          new Formula(
              column + 7,
              row + i_toplivo,
              "IF(ISERROR(" + div + "),0," + div + ")",
              font.tahomaValue_white));

      Vector<String> ave_obl_all_per = getFormulaCells(50 + i_toplivo, ave_obl_all);
      ave_obl_all_per.add(toColumnExcel(column + 7) + (row + i_toplivo + 1));

      String average_obl_per = getFormulaAVERAGE(ave_obl_all_per);

      // в рублях

      sheet.addCell(
          new Formula(
              column + 12,
              row + i_toplivo,
              getFormulaCOUNT(getFormulaCells(58 + i_toplivo, ave_obl_reg)),
              font.tahomaValue_white));

      sheet.addCell(
          new Formula(
              column + 11,
              row + i_toplivo,
              getFormulaSUM(getFormulaCells(58 + i_toplivo, ave_obl_reg)),
              font.tahomaValue_white));

      String div2 =
          toColumnExcel(column + 11)
              + (row + i_toplivo + 1)
              + "/"
              + toColumnExcel(column + 12)
              + (row + i_toplivo + 1);

      sheet.addCell(
          new Formula(
              column + 10,
              row + i_toplivo,
              "IF(ISERROR(" + div2 + "),0," + div2 + ")",
              font.tahomaValue_white));

      Vector<String> ave_obl_all_val = getFormulaCells(58 + i_toplivo, ave_obl_all);
      ave_obl_all_val.add(toColumnExcel(column + 10) + (row + i_toplivo + 1));

      String average_obl_val = getFormulaAVERAGE(ave_obl_all_val);

      /*
       * Запись формул в лист
       */

      /** Калуга */
      sheet.addCell(
          new Formula(
              column + 1,
              row + i_toplivo,
              "IF(ISERROR(" + average_kaluga_per + "),0," + average_kaluga_per + ")",
              font.tahomaValuePer));

      sheet.addCell(
          new Formula(
              column + 2,
              row + i_toplivo,
              "IF(ISERROR(" + average_kaluga_val + "),0," + average_kaluga_val + ")",
              font.tahomaValue));

      /** Область */
      sheet.addCell(
          new Formula(
              column + 5,
              row + i_toplivo,
              "IF(ISERROR(" + average_obl_per + "),0," + average_obl_per + ")",
              font.tahomaValuePer));

      sheet.addCell(
          new Formula(
              column + 6,
              row + i_toplivo,
              "IF(ISERROR(" + average_obl_val + "),0," + average_obl_val + ")",
              font.tahomaValue));
    }
  }
예제 #13
0
  /**
   * Создание шапки с датами и марками топлива
   *
   * @param column - номер первой колонки
   * @param row - номер первой строки
   * @param sheet - лист, на котором будет записываться
   * @throws RowsExceededException
   * @throws WriteException
   */
  private int cape(int column, int row, WritableSheet sheet)
      throws RowsExceededException, WriteException {

    /**
     * формат записи даты<br>
     * Пример 26.04.2013 г.
     */
    SimpleDateFormat formatter = new SimpleDateFormat("dd.MM.yyyy");

    /** Список надписей под результатом */
    String[] result = {"Изменение цен %", "Изменение цен руб."};

    /*
     * Поле "Дата"
     */
    sheet.addCell(new Label(column, row, "Дата", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column, row, column, row + 1);
    sheet.setColumnView(column, 15);

    /** Поле "Марка бензина" */
    sheet.addCell(new Label(column + 1, row, "Марка бензина", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column + 1, row, column + 1, row + 1);
    sheet.setColumnView(column + 1, 10);

    /** первая строчка под наименование поставщика */
    sheet.setRowView(row, 900);
    /** вторая строчка под пояснение выбранного значения */
    sheet.setRowView(row + 1, 2000);

    /** Запись даты и марки топлива */
    for (int i = 0; i < time.length; i++) {
      /** дата */
      sheet.addCell(
          new Label(
              column,
              row + 2 + i * 7,
              formatter.format(new Date(time[i])) + " г.",
              font.tahoma9ptBoldMedion));
      /** склеивание со строчками марки топлива */
      sheet.mergeCells(column, row + 2 + i * 7, column, row + 2 + i * 7 + 6);

      /** запись марки топлива */
      for (int p = 0; p < label_toplivo.length; p++) {
        sheet.addCell(
            new Label(column + 1, row + 2 + i * 7 + p, label_toplivo[p], font.tahomaLabelToplivo));
      }
    }

    // для удобства
    int nextRow = row + 2 + 7 * time.length;

    /** Запись результата */
    for (String res : result) {
      /** Титульник */
      sheet.addCell(new Label(column, nextRow, res, font.tahoma9ptBoldMedion));
      sheet.mergeCells(column, nextRow, column + 1, nextRow);

      /** марка топлива */
      for (int p = 0; p < label_toplivo.length; p++) {
        sheet.addCell(
            new Label(column, nextRow + 1 + p, label_toplivo[p], font.tahoma9ptBoldMedion));
        sheet.mergeCells(column, nextRow + 1 + p, column + 1, nextRow + 1 + p);
      }

      /** смещение к след пункту, без потери номера строки */
      nextRow += label_toplivo.length + 1;
    }

    return column + 2;
  }
예제 #14
0
  /** 写Excel文件 */
  public void writeExcelFile(HttpServletRequest request, WritableSheet sheet) {

    try {
      String start_date = StringUtils.nullToStr(request.getParameter("start_date"));
      String end_date = StringUtils.nullToStr(request.getParameter("end_date"));
      String product_kind = StringUtils.nullToStr(request.getParameter("product_kind"));
      String product_name = StringUtils.nullToStr(request.getParameter("product_name"));
      String store_id = StringUtils.nullToStr(request.getParameter("store_id"));

      String isKc0 = StringUtils.nullToStr(request.getParameter("isKc0")); // 是否显示0库存商品
      String isFse0 = StringUtils.nullToStr(request.getParameter("isFse0")); // 是否显示0发生额商品

      String conStr = "";

      conStr += "日期:" + start_date + "至" + end_date;

      if (!store_id.equals("")) {
        conStr += " 库房:" + StaticParamDo.getStoreNameById(store_id);
      }
      if (!product_kind.equals("")) {
        conStr += " 商品类别:" + StaticParamDo.getProductKindNameById(product_kind);
      }

      Label label = null;

      // 写统计表标题
      sheet.mergeCells(0, 0, 7, 0);
      label = new Label(0, 0, "库存数量汇总", this.getFt_title());
      sheet.addCell(label);

      // 写统计条件
      sheet.mergeCells(0, 1, 7, 1);
      label = new Label(0, 1, conStr, this.getFt_item_center());
      sheet.addCell(label);

      // 写统计表头
      label = new Label(0, 2, "商品编码", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(1, 2, "商品名称", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(2, 2, "规格", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(3, 2, "单位", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(4, 2, "期初数量", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(5, 2, "收入数量", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(6, 2, "发出数量", this.getFt_item_center_bold());
      sheet.addCell(label);
      label = new Label(7, 2, "期末结存", this.getFt_item_center_bold());
      sheet.addCell(label);

      List productKcList = kcMxReportService.getProductKcList(product_kind, product_name, store_id);

      int hj_qc_nums = 0; // 期初数量合计
      int hj_ck_nums = 0; // 出库数量合计
      int hj_rk_nums = 0; // 入库数量合计
      int hj_jc_nums = 0; // 合计结存数量(库存总商品数)

      if (productKcList != null && productKcList.size() > 0) {

        int xh = 0;

        int k = 3;
        for (int i = 0; i < productKcList.size(); i++) {
          Map map = (Map) productKcList.get(i);

          String product_id = StringUtils.nullToStr(map.get("product_id"));
          String product_name2 = StringUtils.nullToStr(map.get("product_name"));
          String product_xh = StringUtils.nullToStr(map.get("product_xh"));
          String dw = StringUtils.nullToStr(map.get("dw"));

          // 根据商品编号、开始时间、库房编号取库存期初情况
          Map qcMap = kcMxReportService.getKcqcMxMap(product_id, start_date, store_id);

          String strNums = "0"; // 期初数
          if (qcMap != null) {
            strNums = StringUtils.nullToStr(qcMap.get("nums"));
            if (strNums.equals("")) {
              strNums = "0";
            }
          }

          int qc_nums = new Integer(strNums).intValue(); // 期初数
          int rk_nums =
              kcMxReportService.getRkNums(product_id, start_date, end_date, store_id); // 收入数量
          int ck_nums =
              kcMxReportService.getCkNums(product_id, start_date, end_date, store_id); // 发出数量

          int jc_nums = qc_nums + rk_nums - ck_nums;

          boolean is_kc = true;
          if (isKc0.equals("否")) { // 显示0库存商品
            if (jc_nums == 0) {
              is_kc = false;
            }
          }

          boolean is_fs = true;
          if (isFse0.equals("否")) { // 显示0发生额商品
            if (rk_nums == 0 && ck_nums == 0) {
              is_fs = false;
            }
          }

          if (is_kc && is_fs) {

            hj_qc_nums += qc_nums;
            hj_rk_nums += rk_nums;
            hj_ck_nums += ck_nums;
            hj_jc_nums += jc_nums;

            xh++;

            label = new Label(0, k, product_id, this.getFt_item_center());
            sheet.addCell(label);
            label = new Label(1, k, product_name2, this.getFt_item_left());
            sheet.addCell(label);
            label = new Label(2, k, product_xh, this.getFt_item_left());
            sheet.addCell(label);
            label = new Label(3, k, dw, this.getFt_item_center());
            sheet.addCell(label);
            label = new Label(4, k, qc_nums + "", this.getFt_item_right());
            sheet.addCell(label);
            label = new Label(5, k, rk_nums + "", this.getFt_item_right());
            sheet.addCell(label);
            label = new Label(6, k, ck_nums + "", this.getFt_item_right());
            sheet.addCell(label);
            label = new Label(7, k, jc_nums + "", this.getFt_item_right());
            sheet.addCell(label);

            k++;
          }
        }

        label = new Label(0, k, "合计", this.getFt_item_center_bold());
        sheet.addCell(label);
        label = new Label(1, k, "", this.getFt_item_left());
        sheet.addCell(label);
        label = new Label(2, k, "", this.getFt_item_left());
        sheet.addCell(label);
        label = new Label(3, k, "", this.getFt_item_center());
        sheet.addCell(label);
        label = new Label(4, k, hj_qc_nums + "", this.getFt_item_right());
        sheet.addCell(label);
        label = new Label(5, k, hj_rk_nums + "", this.getFt_item_right());
        sheet.addCell(label);
        label = new Label(6, k, hj_ck_nums + "", this.getFt_item_right());
        sheet.addCell(label);
        label = new Label(7, k, hj_jc_nums + "", this.getFt_item_right());
        sheet.addCell(label);
      }
    } catch (Exception ex) {
      log.info(ex);
    }
  }
예제 #15
0
파일: Excel.java 프로젝트: lianry/MyProject
  public static void main(String args[]) {
    try {
      // 打开文件
      WritableWorkbook book = Workbook.createWorkbook(new File("/home/uc/local/sandy/测试.xls"));
      // 生成名为“第一页”的工作表,参数0表示这是第一页
      WritableSheet sheetOne = book.createSheet("第一页", 0);

      /** 定义单元格样式 */
      WritableFont wf_title =
          new WritableFont(
              WritableFont.ARIAL,
              11,
              WritableFont.NO_BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
      WritableFont wf_head =
          new WritableFont(
              WritableFont.ARIAL,
              11,
              WritableFont.NO_BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
      WritableFont wf_table =
          new WritableFont(
              WritableFont.ARIAL,
              11,
              WritableFont.NO_BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色

      WritableCellFormat wcf_title = new WritableCellFormat(wf_title); // 单元格定义
      wcf_title.setBackground(jxl.format.Colour.WHITE); // 设置单元格的背景颜色
      wcf_title.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
      wcf_title.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框

      WritableCellFormat wcf_title1 = new WritableCellFormat(wf_title); // 单元格定义
      wcf_title1.setBackground(jxl.format.Colour.LIGHT_GREEN); // 设置单元格的背景颜色
      wcf_title1.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
      wcf_title1.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框

      WritableCellFormat wcf_title2 = new WritableCellFormat(wf_title); // 单元格定义
      wcf_title2.setBackground(jxl.format.Colour.YELLOW2); // 设置单元格的背景颜色
      wcf_title2.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
      wcf_title2.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框

      WritableCellFormat wcf_head1 = new WritableCellFormat(wf_head);
      wcf_head1.setBackground(jxl.format.Colour.LIGHT_GREEN);
      wcf_head1.setAlignment(jxl.format.Alignment.CENTRE);
      wcf_head1.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);

      WritableCellFormat wcf_head2 = new WritableCellFormat(wf_head);
      wcf_head2.setBackground(jxl.format.Colour.YELLOW2);
      wcf_head2.setAlignment(jxl.format.Alignment.CENTRE);
      wcf_head2.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);

      WritableCellFormat wcf_table1 = new WritableCellFormat(wf_table);
      wcf_table1.setBackground(jxl.format.Colour.LIGHT_GREEN);
      wcf_table1.setAlignment(jxl.format.Alignment.CENTRE);
      wcf_table1.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);

      WritableCellFormat wcf_table2 = new WritableCellFormat(wf_table);
      wcf_table2.setBackground(jxl.format.Colour.YELLOW2);
      wcf_table2.setAlignment(jxl.format.Alignment.CENTRE);
      wcf_table2.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);

      sheetOne.setColumnView(0, 15); // 设置列的宽度
      sheetOne.setColumnView(1, 15); // 设置列的宽度
      sheetOne.setColumnView(2, 15); // 设置列的宽度
      sheetOne.setColumnView(3, 15); // 设置列的宽度
      sheetOne.setColumnView(4, 15); // 设置列的宽度
      sheetOne.setColumnView(5, 15); // 设置列的宽度
      sheetOne.setColumnView(6, 15); // 设置列的宽度
      sheetOne.setColumnView(7, 15); // 设置列的宽度
      sheetOne.setColumnView(8, 15); // 设置列的宽度
      sheetOne.setColumnView(9, 15); // 设置列的宽度
      sheetOne.setColumnView(10, 15); // 设置列的宽度
      sheetOne.setColumnView(11, 15); // 设置列的宽度
      sheetOne.setColumnView(12, 15); // 设置列的宽度
      sheetOne.setColumnView(13, 15); // 设置列的宽度

      // 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
      // 以及单元格内容为test
      Label title = new Label(0, 0, "统计", wcf_title);
      Label titleOne = new Label(0, 1, "统计1", wcf_title1);
      Label titleTwo = new Label(2, 1, "统计2", wcf_title2);

      Label column1 = new Label(0, 2, "姓名", wcf_head1);
      Label column2 = new Label(1, 2, "所选课程", wcf_head1);

      Label column3 = new Label(2, 2, "姓名", wcf_head2);
      Label column4 = new Label(3, 2, "所选课程", wcf_head2);

      // 或者WritableCell cell =  new jxl.write.Number(column, row, value, wcf)
      // 将定义好的单元格添加到工作表中
      sheetOne.addCell(title);
      sheetOne.addCell(titleOne);
      sheetOne.addCell(titleTwo);

      sheetOne.addCell(column1);
      sheetOne.addCell(column2);
      sheetOne.addCell(column3);
      sheetOne.addCell(column4);

      // 合: 第1列第1行  到 第13列第1行
      sheetOne.mergeCells(0, 0, 3, 0);
      sheetOne.mergeCells(0, 1, 1, 1);
      sheetOne.mergeCells(2, 1, 3, 1);

      /*动态数据   */
      Label content1 = new Label(0, 3, "张三", wcf_table1);
      Label content2 = new Label(0, 4, "张三", wcf_table1);
      Label content3 = new Label(0, 5, "张三", wcf_table1);
      Label kecheg1 = new Label(1, 3, "语文", wcf_table1);
      Label kecheg2 = new Label(1, 4, "数学", wcf_table1);
      Label kecheg3 = new Label(1, 5, "英语", wcf_table1);

      sheetOne.addCell(content1);
      sheetOne.addCell(content2);
      sheetOne.addCell(content3);
      sheetOne.addCell(kecheg1);
      sheetOne.addCell(kecheg2);
      sheetOne.addCell(kecheg3);

      sheetOne.mergeCells(0, 3, 0, 2 + 3);

      Label content11 = new Label(2, 3, "李四", wcf_table2);
      Label content22 = new Label(2, 4, "李四", wcf_table2);
      Label content33 = new Label(2, 5, "李四", wcf_table2);
      Label kecheg11 = new Label(3, 3, "语文", wcf_table2);
      Label kecheg22 = new Label(3, 4, "数学", wcf_table2);
      Label kecheg33 = new Label(3, 5, "英语", wcf_table2);

      sheetOne.addCell(content11);
      sheetOne.addCell(content22);
      sheetOne.addCell(content33);
      sheetOne.addCell(kecheg11);
      sheetOne.addCell(kecheg22);
      sheetOne.addCell(kecheg33);

      sheetOne.mergeCells(2, 3, 2, 2 + 3);

      // 写入数据并关闭文件
      book.write();
      book.close();
    } catch (Exception e) {
      System.out.println(e);
    }
  }
  public void createEmployeesFile(List<Employee> list, OutputStream os) throws Exception {
    WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
    WritableSheet wsheet = wbook.createSheet("Sheet1", 0); // 工作表名称

    // 设置公司名
    WritableFont companyfont =
        new WritableFont(
            WritableFont.createFont("宋体"),
            18,
            WritableFont.BOLD,
            false,
            jxl.format.UnderlineStyle.NO_UNDERLINE,
            jxl.format.Colour.BLACK);
    WritableCellFormat companyFormat = new WritableCellFormat(companyfont);
    companyFormat.setAlignment(jxl.format.Alignment.CENTRE);
    companyFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    Label excelCompany = new Label(0, 0, "桐庐富伟针织有限公司员工花名册", companyFormat);
    wsheet.addCell(excelCompany);
    wsheet.mergeCells(0, 0, 13, 0);
    wsheet.setRowView(0, 800);

    // 设置Excel字体
    WritableFont wfont =
        new WritableFont(
            WritableFont.createFont("宋体"),
            10,
            WritableFont.BOLD,
            false,
            jxl.format.UnderlineStyle.NO_UNDERLINE,
            jxl.format.Colour.BLACK);
    WritableCellFormat titleFormat = new WritableCellFormat(wfont);
    titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
    titleFormat.setBorder(
        jxl.format.Border.ALL,
        jxl.format.BorderLineStyle.THIN,
        jxl.format.Colour.BLACK); // BorderLineStyle边框

    WritableFont wfont2 =
        new WritableFont(
            WritableFont.createFont("宋体"),
            10,
            WritableFont.NO_BOLD,
            false,
            jxl.format.UnderlineStyle.NO_UNDERLINE,
            jxl.format.Colour.BLACK);
    WritableCellFormat titleFormat2 = new WritableCellFormat(wfont2);
    titleFormat2.setAlignment(jxl.format.Alignment.CENTRE);
    titleFormat2.setBorder(
        jxl.format.Border.ALL,
        jxl.format.BorderLineStyle.THIN,
        jxl.format.Colour.BLACK); // BorderLineStyle边框

    String[] title = {
      "编号", "姓名", "性别", "入厂日期", "身份证号码", "联系方式", "岗位", "部门", "家庭住址", "现居住地", "合同期限", "用工形式", "离职时间",
      "时薪"
    };
    // 设置Excel表头
    int col = 0;
    int merge_col = 0;
    int columnBestWidth[] = new int[title.length + 1]; // 保存最佳列宽数据的数组
    for (int i = 0; i < title.length; i++, col++) {
      columnBestWidth[col] = title[i].getBytes().length;
      Label excelTitle = new Label(col, 1, title[i], titleFormat);

      if (title[i].equals("合同期限")) {
        merge_col = col;
        col++;
        columnBestWidth[col] = title[i].getBytes().length;
      }
      wsheet.addCell(excelTitle);
    }

    wsheet.setRowView(1, 400);
    wsheet.mergeCells(merge_col, 1, merge_col + 1, 1);

    int c = 2; // 用于循环时Excel的行号 		

    for (Employee employee : list) {
      wsheet.setRowView(c, 400);
      Label content1 = new Label(0, c, employee.getNumber(), titleFormat2);
      Label content2 = new Label(1, c, employee.getName(), titleFormat2);
      Label content3 = new Label(2, c, employee.getSex(), titleFormat2);
      Label content4 =
          new Label(3, c, DateTool.formatDateYMD(employee.getEnter_at()), titleFormat2);
      Label content5 = new Label(4, c, employee.getId_card(), titleFormat2);
      Label content6 = new Label(5, c, employee.getTel(), titleFormat2);
      Label content7 = new Label(6, c, employee.getJob(), titleFormat2);
      Label content8 =
          new Label(7, c, SystemCache.getDepartmentName(employee.getDepartmentId()), titleFormat2);
      Label content9 = new Label(8, c, employee.getAddress_home(), titleFormat2);
      Label content10 = new Label(9, c, employee.getAddress(), titleFormat2);
      Label content11 =
          new Label(10, c, DateTool.formatDateYMD(employee.getAgreement_at()), titleFormat2);
      Label content12 =
          new Label(11, c, DateTool.formatDateYMD(employee.getAgreement_end()), titleFormat2);
      Label content13 = new Label(12, c, employee.getEmployee_type(), titleFormat2);
      Label content14 =
          new Label(13, c, DateTool.formatDateYMD(employee.getLeave_at(), "/"), titleFormat2);
      Label content15 =
          new Label(
              14,
              c,
              employee.getHour_salary() == null ? "" : String.valueOf(employee.getHour_salary()),
              titleFormat2);

      wsheet.addCell(content1);
      wsheet.addCell(content2);
      wsheet.addCell(content3);
      wsheet.addCell(content4);
      wsheet.addCell(content5);
      wsheet.addCell(content6);
      wsheet.addCell(content7);
      wsheet.addCell(content8);
      wsheet.addCell(content9);
      wsheet.addCell(content10);
      wsheet.addCell(content11);
      wsheet.addCell(content12);
      wsheet.addCell(content13);
      wsheet.addCell(content14);
      wsheet.addCell(content15);

      int width1 = content1.getContents().getBytes().length;
      int width2 = content2.getContents().getBytes().length;
      int width3 = content3.getContents().getBytes().length;
      int width4 = content4.getContents().getBytes().length;
      int width5 = content5.getContents().getBytes().length;
      int width6 = content6.getContents().getBytes().length;
      int width7 = content7.getContents().getBytes().length;
      int width8 = content8.getContents().getBytes().length;
      int width9 = content9.getContents().getBytes().length;
      int width10 = content10.getContents().getBytes().length;
      int width11 = content11.getContents().getBytes().length;
      int width12 = content12.getContents().getBytes().length;
      int width13 = content13.getContents().getBytes().length;
      int width14 = content14.getContents().getBytes().length;
      int width15 = content15.getContents().getBytes().length;
      if (columnBestWidth[0] < width1) {
        columnBestWidth[0] = width1;
      }
      if (columnBestWidth[1] < width2) {
        columnBestWidth[1] = width2;
      }
      if (columnBestWidth[2] < width3) {
        columnBestWidth[2] = width3;
      }
      if (columnBestWidth[3] < width4) {
        columnBestWidth[3] = width4;
      }
      if (columnBestWidth[4] < width5) {
        columnBestWidth[4] = width5;
      }
      if (columnBestWidth[5] < width6) {
        columnBestWidth[5] = width6;
      }
      if (columnBestWidth[6] < width7) {
        columnBestWidth[6] = width7;
      }
      if (columnBestWidth[7] < width8) {
        columnBestWidth[7] = width8;
      }
      if (columnBestWidth[8] < width9) {
        columnBestWidth[8] = width9;
      }
      if (columnBestWidth[9] < width10) {
        columnBestWidth[9] = width10;
      }
      if (columnBestWidth[10] < width11) {
        columnBestWidth[10] = width11;
      }
      if (columnBestWidth[11] < width12) {
        columnBestWidth[11] = width12;
      }
      if (columnBestWidth[12] < width13) {
        columnBestWidth[12] = width13;
      }
      if (columnBestWidth[13] < width14) {
        columnBestWidth[13] = width14;
      }
      if (columnBestWidth[14] < width15) {
        columnBestWidth[14] = width15;
      }
      c++;
    }
    for (int p = 0; p < columnBestWidth.length; ++p) {
      wsheet.setColumnView(p, columnBestWidth[p] + 1);
    }
    wbook.write(); // 写入文件
    wbook.close();
    os.close();
  }
  /** {@inheritDoc} */
  @Override
  public String exportData(String baseUri, List<SecrecyStatisticsDto> list, District district) {
    boolean flag = false;
    int currentRow = 2;
    JExcelUtils excelUtils = new JExcelUtils();
    String path = baseUri + "exportExcel/staticDatas_" + System.currentTimeMillis() + ".xls";
    File file = new File(path);
    String sheetName = district.getDistrictName() + "级机关单位数据录入情况一览表";

    try {
      BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(file));
      WritableWorkbook workbook = Workbook.createWorkbook(bos);
      WritableSheet sheet = workbook.createSheet(sheetName, 0);
      excelUtils.initialSheetSetting(sheet);

      // 第一行 组装
      Label labelIndex = new Label(0, 0, "单位名称", excelUtils.getTitleCellFormat());
      sheet.addCell(labelIndex);
      for (int i = 1; i < 5; i++) {
        Label label = new Label(i, 0, "保密工作机构", excelUtils.getTitleCellFormat());
        sheet.mergeCells(1, 0, 4, 0);
        sheet.addCell(label);
      }
      for (int i = 5; i < 11; i++) {
        Label label = new Label(i, 0, "保密业务", excelUtils.getTitleCellFormat());
        sheet.mergeCells(5, 0, 10, 0);
        sheet.addCell(label);
      }

      // 第二行 组装
      Label label0 = new Label(0, 1, "单位名称", excelUtils.getTitleCellFormat());
      sheet.addCell(label0);
      for (int i = 1; i < 3; i++) {
        Label label = new Label(i, 1, "机构信息", excelUtils.getTitleCellFormat());
        sheet.mergeCells(1, 1, 2, 1);
        sheet.addCell(label);
      }
      Label label3 = new Label(3, 1, "机构成员", excelUtils.getTitleCellFormat());
      sheet.addCell(label3);
      Label label4 = new Label(4, 1, "保密办成员", excelUtils.getTitleCellFormat());
      sheet.addCell(label4);

      for (int i = 5; i < 7; i++) {
        Label label = new Label(i, 1, "要害部门", excelUtils.getTitleCellFormat());
        sheet.mergeCells(5, 1, 6, 1);
        sheet.addCell(label);
      }

      for (int i = 7; i < 9; i++) {
        Label label = new Label(i, 1, "要害部位", excelUtils.getTitleCellFormat());
        sheet.mergeCells(7, 1, 8, 1);
        sheet.addCell(label);
      }
      for (int i = 9; i < 11; i++) {
        Label label = new Label(i, 1, "涉密人员", excelUtils.getTitleCellFormat());
        sheet.mergeCells(9, 1, 10, 1);
        sheet.addCell(label);
      }

      // 第三行标题
      for (int i = 0; i < dataTitles.length; i++) {
        Label label = new Label(i, 2, dataTitles[i], excelUtils.getTitleCellFormat());
        sheet.addCell(label);
      }

      // 统计数字
      Integer num1 = 0;
      Integer num2 = 0;
      Integer num3 = 0;
      Integer num4 = 0;
      Integer num5 = 0;
      Integer num6 = 0;
      Integer num7 = 0;
      Integer num8 = 0;
      Integer num9 = 0;
      Integer num10 = 0;

      // 数据行
      for (SecrecyStatisticsDto ob : list) {
        currentRow += 1;
        String[] data =
            new String[] {
              ob.getOrganName(),
              ob.getNumGroupEntering() + "",
              ob.getNumGroupReprot() + "",
              ob.getNumGroupMember() + "",
              ob.getNumSecrecyWorkOrganMember() + "",
              ob.getNumKeysectionEntering() + "",
              ob.getNumKeyPartReport() + "",
              ob.getNumKeyPartEntering() + "",
              ob.getNumKeyPartReport() + "",
              ob.getNumSecrecyPersonEntering() + "",
              ob.getNumSecrecyPersonReport() + ""
            };

        num1 += ob.getNumGroupEntering();
        num2 += ob.getNumGroupReprot();
        num3 += ob.getNumGroupMember();
        num4 += ob.getNumSecrecyWorkOrganMember();
        num5 += ob.getNumKeysectionEntering();
        num6 += ob.getNumKeyPartReport();
        num7 += ob.getNumKeyPartEntering();
        num8 += ob.getNumKeyPartReport();
        num9 += ob.getNumSecrecyPersonEntering();
        num10 += ob.getNumSecrecyPersonReport();

        excelUtils.insertRowData(
            sheet, currentRow, data, excelUtils.getDataCellFormat(CellType.LABEL));
      }
      // 最后一行 组装
      currentRow = currentRow + 1;
      String[] dataLast =
          new String[] {
            "统计结果", num1 + "", num2 + "", num3 + "", num4 + "", num5 + "", num6 + "", num7 + "",
            num8 + "", num9 + "", num10 + ""
          };
      excelUtils.insertRowData(
          sheet, currentRow, dataLast, excelUtils.getDataCellFormat(CellType.LABEL));

      workbook.write();
      workbook.close();
      flag = true;
    } catch (FileNotFoundException e) {
      LOGGER.error(e.getMessage(), e);
    } catch (IOException e) {
      LOGGER.error(e.getMessage(), e);
    } catch (RowsExceededException e) {
      LOGGER.error(e.getMessage(), e);
    } catch (WriteException e) {
      LOGGER.error(e.getMessage(), e);
    }
    return (flag ? path : null);
  }
  public void generateReport(
      Program selProgram, List<OrganisationUnit> orgUnitList, Date sDate, Date eDate)
      throws Exception {
    String raFolderName = reportService.getRAFolderName();
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    String query = "";
    int rowStart = 3;
    int colStart = 1;
    int rowCount = rowStart;
    int colCount = colStart;

    // String outputReportPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName +
    // File.separator + "output" + File.separator + UUID.randomUUID().toString() + ".xls";

    String outputReportPath =
        System.getenv("DHIS2_HOME") + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER;
    File newdir = new File(outputReportPath);
    if (!newdir.exists()) {
      newdir.mkdirs();
    }
    outputReportPath += File.separator + UUID.randomUUID().toString() + ".xls";

    WritableWorkbook outputReportWorkbook = Workbook.createWorkbook(new File(outputReportPath));
    WritableSheet sheet0 = outputReportWorkbook.createSheet(selProgram.getName(), 0);

    try {
      List<PatientIdentifierType> patientIdentifierTypes =
          new ArrayList<PatientIdentifierType>(
              patientIdentifierTypeService.getAllPatientIdentifierTypes());
      Collections.sort(patientIdentifierTypes, new PatientIdentifierTypeComparator());

      List<PatientAttribute> patientAttributes =
          new ArrayList<PatientAttribute>(patientAttributeService.getAllPatientAttributes());
      Collections.sort(patientAttributes, new PatientAttributeComparator());

      List<ProgramStage> programStages = new ArrayList<ProgramStage>(selProgram.getProgramStages());
      Collections.sort(programStages, new ProgramStageOrderComparator());

      Map<ProgramStage, List<DataElement>> programStageDataElementMap =
          new HashMap<ProgramStage, List<DataElement>>();
      for (ProgramStage programStage : programStages) {
        List<ProgramStageDataElement> programStageDataElements =
            new ArrayList<ProgramStageDataElement>(programStage.getProgramStageDataElements());

        List<DataElement> dataElements = new ArrayList<DataElement>();
        for (ProgramStageDataElement programStageDataElement : programStageDataElements) {
          dataElements.add(programStageDataElement.getDataElement());
        }

        Collections.sort(dataElements, new IdentifiableObjectNameComparator());
        programStageDataElementMap.put(programStage, dataElements);
      }

      // Printing Header Information
      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "OrgUnit Hierarchy", getCellFormat1()));
      colCount++;
      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "OrgUnit", getCellFormat1()));
      colCount++;
      for (PatientIdentifierType patientIdentifierType : patientIdentifierTypes) {
        sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
        sheet0.addCell(
            new Label(colCount, rowCount - 1, patientIdentifierType.getName(), getCellFormat1()));
        colCount++;
      }

      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "Benificiary ID", getCellFormat1()));
      colCount++;

      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "Benificiary Name", getCellFormat1()));
      colCount++;
      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "Gender", getCellFormat1()));
      colCount++;
      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "Age", getCellFormat1()));
      colCount++;
      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "Data of Birth", getCellFormat1()));
      colCount++;
      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "Blood Group", getCellFormat1()));
      colCount++;
      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "Registration Date", getCellFormat1()));
      colCount++;

      for (PatientAttribute patientAttribute : patientAttributes) {
        sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
        sheet0.addCell(
            new Label(colCount, rowCount - 1, patientAttribute.getName(), getCellFormat1()));
        colCount++;
      }

      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "Incident Date", getCellFormat1()));
      colCount++;

      sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
      sheet0.addCell(new Label(colCount, rowCount - 1, "Enrollment Date", getCellFormat1()));
      colCount++;
      for (ProgramStage programStage : programStages) {
        List<DataElement> dataElementList =
            new ArrayList<DataElement>(programStageDataElementMap.get(programStage));
        sheet0.mergeCells(
            colCount, rowCount - 1, colCount + dataElementList.size() + 1, rowCount - 1);
        sheet0.addCell(new Label(colCount, rowCount - 1, programStage.getName(), getCellFormat1()));

        sheet0.addCell(new Label(colCount, rowCount, "Due Date", getCellFormat1()));
        colCount++;
        sheet0.addCell(new Label(colCount, rowCount, "Execution Date", getCellFormat1()));
        colCount++;

        for (DataElement dataElement : dataElementList) {
          sheet0.addCell(
              new Label(
                  colCount,
                  rowCount,
                  dataElement.getName() + "--" + dataElement.getType(),
                  getCellFormat1()));
          colCount++;
        }
      }

      rowCount++;

      for (OrganisationUnit orgUnit : orgUnitList) {
        if (sDate != null && eDate != null) {
          query =
              "SELECT patient.patientid, programinstance.programinstanceid,programinstance.dateofincident,programinstance.enrollmentdate FROM programinstance INNER JOIN patient "
                  + " ON programinstance.patientid = patient.patientid "
                  + " WHERE patient.organisationunitid = "
                  + orgUnit.getId()
                  + " AND programinstance.programid = "
                  + selProgram.getId()
                  + " AND patient.registrationdate >= '"
                  + startDate
                  + "'"
                  + " AND patient.registrationdate <= '"
                  + endDate
                  + "' "
                  + " AND enddate IS NULL";
        } else {
          query =
              "SELECT patient.patientid, programinstance.programinstanceid,programinstance.dateofincident,programinstance.enrollmentdate FROM programinstance INNER JOIN patient "
                  + " ON programinstance.patientid = patient.patientid "
                  + " WHERE patient.organisationunitid = "
                  + orgUnit.getId()
                  + " AND programinstance.programid = "
                  + selProgram.getId()
                  + " AND enddate IS NULL";
        }

        SqlRowSet sqlResultSet = jdbcTemplate.queryForRowSet(query);

        if (sqlResultSet != null) {
          int count = 1;
          String orgUnitBranch = "";
          sqlResultSet.beforeFirst();
          while (sqlResultSet.next()) {
            colCount = colStart;

            if (orgUnit.getParent() != null) {
              orgUnitBranch = getOrgunitBranch(orgUnit.getParent());
            } else {
              orgUnitBranch = " ";
            }

            sheet0.addCell(new Label(colCount, rowCount, orgUnitBranch, getCellFormat2()));
            colCount++;
            sheet0.addCell(new Label(colCount, rowCount, orgUnit.getName(), getCellFormat2()));
            colCount++;

            int patientId = sqlResultSet.getInt(1);
            int programInstanceId = sqlResultSet.getInt(2);
            Date dateOfIncident = sqlResultSet.getDate(3);
            Date dateOfEnrollment = sqlResultSet.getDate(4);

            Patient patient = patientService.getPatient(patientId);

            // Patient Identifier Details
            for (PatientIdentifierType patientIdentifierType : patientIdentifierTypes) {
              query =
                  "SELECT identifier from patientidentifier WHERE patientidentifiertypeid = "
                      + patientIdentifierType.getId()
                      + " AND patientid = "
                      + patient.getId();

              SqlRowSet sqlResultSet1 = jdbcTemplate.queryForRowSet(query);
              if (sqlResultSet1 != null && sqlResultSet1.next()) {
                String value = sqlResultSet1.getString(1);
                if (value != null && !value.trim().equalsIgnoreCase("")) {
                  sheet0.addCell(new Label(colCount, rowCount, value, getCellFormat2()));
                } else {
                  sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
                }
              } else {
                sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
              }

              colCount++;
            }

            // Patient Properties

            sheet0.addCell(
                new Label(colCount, rowCount, patient.getId().toString(), getCellFormat2()));
            colCount++;
            sheet0.addCell(new Label(colCount, rowCount, patient.getFullName(), getCellFormat2()));
            colCount++;
            sheet0.addCell(
                new Label(colCount, rowCount, patient.getTextGender(), getCellFormat2()));
            colCount++;
            sheet0.addCell(new Label(colCount, rowCount, patient.getAge(), getCellFormat2()));
            colCount++;
            sheet0.addCell(
                new Label(
                    colCount,
                    rowCount,
                    simpleDateFormat.format(patient.getBirthDate()),
                    getCellFormat2()));
            colCount++;
            /**
             * TODO BloodGroup is removed from Patient Object, so need to change this accordingly
             */
            sheet0.addCell(
                new Label(colCount, rowCount, "" /*patient.getBloodGroup()*/, getCellFormat2()));
            colCount++;
            sheet0.addCell(
                new Label(
                    colCount,
                    rowCount,
                    simpleDateFormat.format(patient.getRegistrationDate()),
                    getCellFormat2()));
            colCount++;

            // Patient Attribute Values
            for (PatientAttribute patientAttribute : patientAttributes) {
              query =
                  "SELECT value from patientattributevalue WHERE patientid = "
                      + patient.getId()
                      + " AND patientattributeid = "
                      + patientAttribute.getId();

              SqlRowSet sqlResultSet1 = jdbcTemplate.queryForRowSet(query);
              if (sqlResultSet1 != null && sqlResultSet1.next()) {
                String value = sqlResultSet1.getString(1);
                if (value != null && !value.trim().equalsIgnoreCase("")) {
                  sheet0.addCell(new Label(colCount, rowCount, value, getCellFormat2()));
                } else {
                  sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
                }
              } else {
                sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
              }

              colCount++;
            }

            // Program Enrollment Details
            sheet0.addCell(
                new Label(
                    colCount, rowCount, simpleDateFormat.format(dateOfIncident), getCellFormat2()));
            colCount++;
            sheet0.addCell(
                new Label(
                    colCount,
                    rowCount,
                    simpleDateFormat.format(dateOfEnrollment),
                    getCellFormat2()));
            colCount++;

            // ProgramStage Values
            for (ProgramStage programStage : programStages) {
              query =
                  "SELECT programstageinstanceid,duedate,executiondate from programstageinstance "
                      + " WHERE programinstanceid = "
                      + programInstanceId
                      + " AND programstageid = "
                      + programStage.getId();

              SqlRowSet sqlResultSet2 = jdbcTemplate.queryForRowSet(query);
              Integer programStageInstanceId = 0;
              if (sqlResultSet2 != null && sqlResultSet2.next()) {
                programStageInstanceId = sqlResultSet2.getInt(1);

                // ProgramStage DueDate and Execution Date
                Date dueDate = sqlResultSet2.getDate(2);
                Date exeDate = sqlResultSet2.getDate(3);

                if (dueDate != null) {
                  String dueDateStr = simpleDateFormat.format(dueDate);
                  sheet0.addCell(new Label(colCount, rowCount, dueDateStr, getCellFormat3()));
                } else {
                  sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
                }
                colCount++;

                if (exeDate != null) {
                  String exeDateStr = simpleDateFormat.format(exeDate);
                  sheet0.addCell(new Label(colCount, rowCount, exeDateStr, getCellFormat3()));
                } else {
                  sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
                }

                colCount++;
              } else {
                sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
                colCount++;
                sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
                colCount++;
              }

              for (DataElement dataElement : programStageDataElementMap.get(programStage)) {
                query =
                    "SELECT value from patientdatavalue WHERE programstageinstanceid = "
                        + programStageInstanceId
                        + " AND dataelementid = "
                        + dataElement.getId();
                // " AND organisationunitid = " + orgUnit.getId();

                SqlRowSet sqlResultSet3 = jdbcTemplate.queryForRowSet(query);

                if (sqlResultSet3 != null && sqlResultSet3.next()) {
                  String value = sqlResultSet3.getString(1);

                  if (dataElement.getType().equalsIgnoreCase(DataElement.VALUE_TYPE_BOOL)) {
                    if (value.equalsIgnoreCase("false")) value = "No";
                    else value = "Yes";
                  }

                  if (value != null && !value.trim().equalsIgnoreCase("")) {
                    sheet0.addCell(new Label(colCount, rowCount, value, getCellFormat2()));
                  } else {
                    sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
                  }
                } else {
                  sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
                }

                colCount++;
              }
            }

            rowCount++;
          }
        }
      }
    } catch (Exception e) {
      System.out.println("Exception: " + e.getMessage());
      e.printStackTrace();
    }

    outputReportWorkbook.write();
    outputReportWorkbook.close();
    fileName = selProgram.getName() + ".xls";
    File outputReportFile = new File(outputReportPath);
    inputStream = new BufferedInputStream(new FileInputStream(outputReportFile));
    outputReportFile.deleteOnExit();
  }