Exemplo n.º 1
0
  /**
   * 生成固定格式的excel,表格都为文本,水平居左,垂直居中
   *
   * @param sheetName sheet名称,默认为sheet1
   * @param content Map,要生成excel的数据来源
   * @param os excel输出流
   */
  public void exportFormatExcel(
      Map<String, String[][]> content,
      String[] salary_name_array,
      String sheetName,
      OutputStream os) {
    if (VerifyUtil.isNullObject(content, os) || content.size() == 0) {
      return;
    }
    // 默认名称
    if (VerifyUtil.isNullObject(sheetName)) {
      sheetName = "sheet1";
    }
    WritableWorkbook workbook = null;
    try {
      workbook = Workbook.createWorkbook(os);
      WritableSheet sheet = workbook.createSheet(sheetName, 0);
      int index = 0;
      for (int k = 0; k < salary_name_array.length; k++) {
        String[][] value = (String[][]) content.get(salary_name_array[k]);
        if (value != null && value.length > 0) {
          if (index != 0) {
            index++;
          }
          WritableCellFormat format1 = new WritableCellFormat();
          format1.setAlignment(jxl.format.Alignment.LEFT);
          format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
          Label label1 = new Label(0, index, salary_name_array[k], format1);
          sheet.addCell(label1);
          for (int i = 0; i < value.length; i++) {
            index++;
            for (int j = 0; j < value[i].length; j++) {
              if (value[i][j] == null) {
                value[i][j] = "";
              }
              WritableCellFormat format = new WritableCellFormat();
              format.setAlignment(jxl.format.Alignment.LEFT);
              format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

              Label label = new Label(j, index, value[i][j], format);
              sheet.addCell(label);
            }
          }
        }
      }
      workbook.write();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        workbook.close();
      } catch (WriteException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
  public static void addRowTop(WritableSheet ws) throws Exception {

    WritableFont Bwf =
        new WritableFont(
            WritableFont.ARIAL, 16, WritableFont.BOLD, false); // 创建大字体:Arial,大小为18号,粗体,非斜体
    Bwf.setColour(jxl.format.Colour.BLACK); // //字体颜色为红色
    // 创建单元格格式:
    jxl.write.WritableCellFormat CwcfF = new jxl.write.WritableCellFormat(Bwf);
    CwcfF.setAlignment(jxl.write.Alignment.CENTRE); // 设置水平对齐为居中对齐
    CwcfF.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置垂直对齐为居中对齐
    CwcfF.setBorder(jxl.format.Border.TOP, BorderLineStyle.MEDIUM); // 设置顶部边框线为实线(默认是黑色--也可以设置其他颜色)
    CwcfF.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.MEDIUM); // 设置右边框线为实线
    CwcfF.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.MEDIUM); // 设置顶部框线为实线

    List<Label> labelList = new ArrayList<Label>();
    labelList.add(new Label(0, 0, "商品名称", CwcfF)); //
    labelList.add(new Label(1, 0, "单位", CwcfF)); //
    labelList.add(new Label(2, 0, "销售数量", CwcfF)); //
    labelList.add(new Label(3, 0, "销售成本", CwcfF));
    labelList.add(new Label(4, 0, "销售金额", CwcfF));
    labelList.add(new Label(5, 0, "利润", CwcfF));
    labelList.add(new Label(6, 0, "利润率", CwcfF));
    for (int j = 0; j < labelList.size(); j++) {
      ws.addCell(labelList.get(j));
    }
    for (int i = 0; i < ws.getColumns(); i++) {
      Cell cell = ws.getCell(i, 0);
      ws.setColumnView(i, cell.getContents().length() * 4);
    }
    //		ws.setRowView(0, 80*4);
  }
Exemplo n.º 3
0
  /**
   * 采用List<List<String>>方式写WritableSheet
   *
   * @param sheet
   * @param xlsList
   * @throws Exception
   */
  private static void CreateXlsSheet(WritableSheet sheet, List<List<String>> xlsList)
      throws Exception {
    WritableCellFormat cf = new WritableCellFormat();
    cf.setWrap(true);
    cf.setAlignment(jxl.format.Alignment.LEFT);
    cf.setVerticalAlignment(VerticalAlignment.TOP);
    /** 填充数据 */
    for (int row = 0; row < xlsList.size(); row++) {
      List<String> rowList = xlsList.get(row);
      // 如果该行为空,则跳过(一般跳过两行)
      if (isEmpty(rowList)) continue;
      // 单元格 Object.field
      for (int col = 0; col < rowList.size(); col++) {
        // 跳过无属性行
        if (isEmpty(rowList.get(col))) continue;
        // 写数据
        String value = C.EMPTY_STRING;
        value = StringValue(rowList.get(col));
        WritableCell c = sheet.getWritableCell(col, row);
        c.setCellFormat(cf);

        if (CellType.EMPTY.equals(c.getType())) {
          Label label = new Label(col, row, value);
          label.setCellFormat(cf);
          sheet.addCell(label);
          if (row == 0) label.setCellFormat(GetCellFormat());
        } else {
          Label label = (Label) c;
          label.setString(value);
        }
      }
    }
  }
  public WritableCellFormat getCellFormat2() throws Exception {
    WritableFont arialBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
    WritableCellFormat wCellformat = new WritableCellFormat(arialBold);

    wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
    wCellformat.setAlignment(Alignment.CENTRE);
    wCellformat.setVerticalAlignment(VerticalAlignment.CENTRE);

    return wCellformat;
  }
Exemplo n.º 5
0
 /**
  * 取报表标题单元格格式
  *
  * @return
  */
 public WritableCellFormat getFt_title() {
   try {
     ft_title = new WritableCellFormat();
     ft_title.setFont(new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD));
     ft_title.setAlignment(Alignment.CENTRE);
     ft_title.setVerticalAlignment(VerticalAlignment.CENTRE);
     ft_title.setBorder(Border.ALL, BorderLineStyle.THIN);
   } catch (Exception e) {
     log.info(e);
   }
   return ft_title;
 }
  public WritableCellFormat getCellFormat1() throws Exception {
    WritableFont arialBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
    WritableCellFormat wCellformat = new WritableCellFormat(arialBold);

    wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
    wCellformat.setAlignment(Alignment.CENTRE);
    wCellformat.setVerticalAlignment(VerticalAlignment.CENTRE);
    wCellformat.setBackground(Colour.GRAY_50);
    wCellformat.setWrap(true);

    return wCellformat;
  }
Exemplo n.º 7
0
 /**
  * 单元格左对齐
  *
  * @return
  */
 public WritableCellFormat getFt_item_left() {
   try {
     ft_item_left = new WritableCellFormat();
     ft_item_left.setFont(new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD));
     ft_item_left.setAlignment(Alignment.LEFT);
     ft_item_left.setVerticalAlignment(VerticalAlignment.CENTRE);
     ft_item_left.setBorder(Border.ALL, BorderLineStyle.THIN);
   } catch (Exception e) {
     log.info(e);
   }
   return ft_item_left;
 }
Exemplo n.º 8
0
 /**
  * 单元格右对齐且黑体
  *
  * @return
  */
 public WritableCellFormat getFt_item_right_bold() {
   try {
     ft_item_right_bold = new WritableCellFormat();
     ft_item_right_bold.setFont(new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD));
     ft_item_right_bold.setAlignment(Alignment.RIGHT);
     ft_item_right_bold.setVerticalAlignment(VerticalAlignment.CENTRE);
     ft_item_right_bold.setBorder(Border.ALL, BorderLineStyle.THIN);
   } catch (Exception e) {
     log.info(e);
   }
   return ft_item_right_bold;
 }
Exemplo n.º 9
0
 /**
  * 单元格右对齐并底色为灰色
  *
  * @return
  */
 public WritableCellFormat getFt_item_rightgray() {
   try {
     ft_item_rightgray = new WritableCellFormat();
     ft_item_rightgray.setFont(new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD));
     ft_item_rightgray.setAlignment(Alignment.RIGHT);
     ft_item_rightgray.setVerticalAlignment(VerticalAlignment.CENTRE);
     ft_item_rightgray.setBorder(Border.ALL, BorderLineStyle.THIN);
     ft_item_rightgray.setBackground(jxl.format.Colour.GRAY_25);
   } catch (Exception e) {
     log.info(e);
   }
   return ft_item_rightgray;
 }
Exemplo n.º 10
0
  /**
   * 生成固定格式的excel,表格都为文本,水平居左,垂直居中
   *
   * @param sheetName sheet名称,默认为sheet1
   * @param content 二维数组,要生成excel的数据来源
   * @param os excel输出流
   */
  public void exportFormatExcel(String[][] content, String sheetName, OutputStream os) {
    if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
      return;
    }
    // 默认名称
    if (VerifyUtil.isNullObject(sheetName)) {
      sheetName = "sheet1";
    }
    WritableWorkbook workbook = null;
    try {
      workbook = Workbook.createWorkbook(os);
      WritableSheet sheet = workbook.createSheet(sheetName, 0);

      for (int i = 0; i < content.length; i++) {
        for (int j = 0; j < content[i].length; j++) {
          if (content[i][j] == null) {
            content[i][j] = "";
          }
          WritableCellFormat format = new WritableCellFormat();
          format.setAlignment(jxl.format.Alignment.LEFT);
          format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
          Label label = new Label(j, i, content[i][j], format);
          sheet.addCell(label);
        }
      }

      workbook.write();
    } catch (Exception e) {
      e.printStackTrace();

    } finally {
      try {
        workbook.close();
      } catch (WriteException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
  private void exportReport2Excel(
      ExportMaterialReportBean bean,
      ExportMaterialReportDTO results,
      HttpServletRequest request,
      HttpServletResponse response) {
    try {
      String outputFileName =
          "/files/temp/BaoCaoNhapXuatTonNPL" + System.currentTimeMillis() + ".xls";
      String reportTemplate =
          request
              .getSession()
              .getServletContext()
              .getRealPath("/files/export/ExportMaterialReport.xls");
      String export2FileName = request.getSession().getServletContext().getRealPath(outputFileName);

      Workbook templateWorkbook = Workbook.getWorkbook(new File(reportTemplate));
      WritableWorkbook workbook =
          Workbook.createWorkbook(new File(export2FileName), templateWorkbook);

      WritableSheet sheet = workbook.getSheet(0);

      WritableFont normalFont =
          new WritableFont(
              WritableFont.TIMES,
              12,
              WritableFont.NO_BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              jxl.format.Colour.BLACK);
      WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
      normalFormat.setAlignment(Alignment.LEFT);
      normalFormat.setWrap(true);
      normalFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

      WritableFont boldFont =
          new WritableFont(
              WritableFont.TIMES,
              12,
              WritableFont.BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              jxl.format.Colour.BLACK);
      WritableCellFormat headerFormat = new WritableCellFormat(boldFont);
      headerFormat.setAlignment(Alignment.CENTRE);
      headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
      headerFormat.setWrap(true);
      headerFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
      headerFormat.setBackground(jxl.format.Colour.GRAY_25);

      WritableCellFormat boldFormat = new WritableCellFormat(boldFont);
      boldFormat.setAlignment(Alignment.CENTRE);
      boldFormat.setWrap(true);
      boldFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

      DecimalFormat decimalFormat = new DecimalFormat("###,###.##");

      Date from = bean.getFromDate();
      Label fromCell =
          new Label(
              1, 1, from != null ? DateUtils.date2String(from, "dd/MM/yyyy") : "", boldFormat);
      sheet.addCell(fromCell);

      Date to = bean.getToDate();
      Label toCell =
          new Label(1, 2, to != null ? DateUtils.date2String(to, "dd/MM/yyyy") : "", boldFormat);
      sheet.addCell(toCell);

      int startRow = 5;
      List<ExportMaterialReportDetailDTO> initialValue = results.getInitialValue();
      Map<String, Double> mapImportValue = results.getMapImportValue();
      Map<String, Double> mapExportUtilDateValue = results.getMapExportUtilDateValue();
      Map<String, Double> mapExportDuringDateValue = results.getMapExportDuringDateValue();
      int index;
      CellValue[] resValue;
      Double iVal;
      Double exportToVal;
      Double importVal;
      Double exportVal;
      Double remainVal;
      String key, origin;
      for (ExportMaterialReportDetailDTO initVal : initialValue) {
        origin = initVal.getOrigin() != null ? initVal.getOrigin().getOriginID().toString() : "";
        key = initVal.getMaterial().getMaterialID() + "_" + origin;
        exportToVal =
            mapExportUtilDateValue.get(key) != null ? mapExportUtilDateValue.get(key) : 0d;
        iVal =
            initVal.getQuantity() != null ? initVal.getQuantity() - exportToVal : 0 - exportToVal;
        importVal = mapImportValue.get(key) != null ? mapImportValue.get(key) : 0d;
        exportVal =
            mapExportDuringDateValue.get(key) != null ? mapExportDuringDateValue.get(key) : 0d;
        remainVal = iVal + importVal - exportVal;
        index = 0;
        resValue = new CellValue[10];

        resValue[index++] =
            new CellValue(
                CellDataType.STRING,
                initVal.getOrigin() != null ? initVal.getOrigin().getName() : "");
        resValue[index++] =
            new CellValue(
                CellDataType.STRING,
                initVal.getMaterial() != null ? initVal.getMaterial().getName() : "");
        resValue[index++] =
            new CellValue(
                CellDataType.STRING,
                StringUtils.isNotBlank(initVal.getCode()) ? initVal.getCode() : "");
        resValue[index++] =
            new CellValue(
                CellDataType.STRING,
                initVal.getMaterial() != null
                    ? initVal.getMaterial().getUnit() != null
                        ? initVal.getMaterial().getUnit().getName()
                        : ""
                    : "");
        resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(iVal));
        resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(importVal));
        resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(exportVal));
        resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(remainVal));
        resValue[index++] = new CellValue(CellDataType.STRING, "");
        resValue[index++] =
            new CellValue(
                CellDataType.STRING,
                initVal.getImportDate() != null
                    ? DateUtils.date2String(initVal.getImportDate(), "dd/MM/yyyy")
                    : "");

        ExcelUtil.addRow(
            sheet, startRow++, resValue, normalFormat, normalFormat, normalFormat, normalFormat);
      }
      workbook.write();
      workbook.close();
      response.sendRedirect(
          request.getSession().getServletContext().getContextPath() + outputFileName);
    } catch (Exception ex) {
      logger.error(ex.getMessage(), ex);
    }
  }
Exemplo n.º 12
0
  /**
   *
   *
   * <DL>
   *   <DT>メソッド記述:
   *   <DD><br>
   * </DL>
   *
   * @param MCtlListVo , String
   * @return int
   * @throws BizException
   */
  public boolean exportEXCEL(String strFilePath) throws BizException {
    try {
      List<MCtlVo> lstMCtlVo = null;

      /** データある場合、データ取得 */
      lstMCtlVo = dao.exportEXCEL();

      if (lstMCtlVo == null || lstMCtlVo.size() == 0) {
        return false;
      }
      WritableWorkbook workbook = Workbook.createWorkbook(new File(strFilePath));
      WritableSheet s1 = workbook.createSheet("M_CTL", 0);

      FontName fontName = WritableFont.createFont("MS ゴシック");
      WritableFont fontFormat = new WritableFont(fontName, 12);

      // header format (color, padding, border)
      WritableCellFormat headFormat = new WritableCellFormat(fontFormat);
      headFormat.setAlignment(Alignment.CENTRE);
      headFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
      headFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      headFormat.setBackground(Colour.VERY_LIGHT_YELLOW);

      // left format
      WritableCellFormat leftCellFormat = new WritableCellFormat(fontFormat);
      leftCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      leftCellFormat.setAlignment(Alignment.LEFT);

      // center format
      WritableCellFormat centerCellFormat = new WritableCellFormat(fontFormat);
      centerCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      centerCellFormat.setAlignment(Alignment.CENTRE);

      // right format
      WritableCellFormat rightCellFormat = new WritableCellFormat(fontFormat);
      rightCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      rightCellFormat.setAlignment(Alignment.RIGHT);

      final String[] excelHeader = {
        "USERID",
        "KEY",
        "NAME",
        "DATA",
        "HELP",
        "入力桁数",
        "入力桁数(小数桁)",
        "入力属性地",
        "メンテフラグ",
        "コントロールフラグ",
        "変更可否フラグ",
        "登録ユーザー名",
        "登録PC名",
        "登録日付",
        "登録時刻",
        "最終更新ユーザー名",
        "最終更新PC名",
        "最終更新日付",
        "最終更新時刻"
      };

      int columnCount = excelHeader.length;
      for (int i = 0; i < columnCount; i++) {
        Label lbHeader = new Label(i, 0, excelHeader[i], headFormat);
        s1.addCell(lbHeader);
        if (i == 2 || i == 3) s1.setColumnView(i, 60);
        else if (i == 4) s1.setColumnView(i, 150);
        else s1.setColumnView(i, 20);
      }
      for (int i = 0; i < lstMCtlVo.size(); i++) {
        int column = 0;
        MCtlVo mCtlVo = (MCtlVo) lstMCtlVo.get(i);

        s1.addCell(new Label(column++, i + 1, mCtlVo.getUserid(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCKey(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCName(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCData(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCHelp(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCBm(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCDecbm(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCAttr(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getMtnFlg(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCntFlg(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getUpdFlg(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getAddUserView(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getAddPc(), leftCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getDateWithSplitYobi(mCtlVo.getAddDate()),
                centerCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getTimeWithSplit(mCtlVo.getAddTime()),
                centerCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getLastupUserView(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getLastupPc(), leftCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getDateWithSplitYobi(mCtlVo.getLastupDate()),
                centerCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getTimeWithSplit(mCtlVo.getLastupTime()),
                centerCellFormat));
      }
      workbook.write();
      workbook.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return true;
  }
  // -------------------------------------------------------------------------
  // Action Implementation
  // -------------------------------------------------------------------------
  public String execute() throws Exception {
    statementManager.initialise();

    // Initialization
    raFolderName = reportService.getRAFolderName();
    String deCodesXMLFileName = "";
    simpleDateFormat = new SimpleDateFormat("MMM-yyyy");
    monthFormat = new SimpleDateFormat("MMMM");
    yearFormat = new SimpleDateFormat("yyyy");
    simpleMonthFormat = new SimpleDateFormat("MMM");
    String parentUnit = "";

    Report_in selReportObj = reportService.getReport(Integer.parseInt(reportList));

    deCodesXMLFileName = selReportObj.getXmlTemplateName();

    reportModelTB = selReportObj.getModel();
    reportFileNameTB = selReportObj.getExcelTemplateName();

    String inputTemplatePath =
        System.getenv("DHIS2_HOME")
            + File.separator
            + raFolderName
            + File.separator
            + "template"
            + File.separator
            + reportFileNameTB;
    // String outputReportFolderPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName
    // + File.separator + "output" + File.separator + UUID.randomUUID().toString();
    String outputReportFolderPath =
        System.getenv("DHIS2_HOME")
            + File.separator
            + Configuration_IN.DEFAULT_TEMPFOLDER
            + File.separator
            + UUID.randomUUID().toString();
    File newdir = new File(outputReportFolderPath);
    if (!newdir.exists()) {
      newdir.mkdirs();
    }

    if (reportModelTB.equalsIgnoreCase("STATIC")
        || reportModelTB.equalsIgnoreCase("STATIC-DATAELEMENTS")
        || reportModelTB.equalsIgnoreCase("STATIC-FINANCIAL")) {
      orgUnitList =
          new ArrayList<OrganisationUnit>(
              organisationUnitService.getOrganisationUnitWithChildren(ouIDTB));
      OrganisationUnitGroup orgUnitGroup = selReportObj.getOrgunitGroup();

      orgUnitList.retainAll(orgUnitGroup.getMembers());
    } else {
      return INPUT;
    }

    // System.out.println(  "---Size of Org Unit List ----: " + orgUnitList.size() + ",Report Group
    // name is :---" + selReportObj.getOrgunitGroup().getName() + ", Size of Group member is ----:"
    // + selReportObj.getOrgunitGroup().getMembers().size()  );

    System.out.println(" ---- Size of OrgUnit List is ---- " + orgUnitList.size());

    OrganisationUnit selOrgUnit = organisationUnitService.getOrganisationUnit(ouIDTB);

    System.out.println(
        selOrgUnit.getName()
            + " : "
            + selReportObj.getName()
            + " : Report Generation Start Time is : "
            + new Date());

    selectedPeriod = periodService.getPeriod(availablePeriods);

    sDate = format.parseDate(String.valueOf(selectedPeriod.getStartDate()));

    eDate = format.parseDate(String.valueOf(selectedPeriod.getEndDate()));

    Workbook templateWorkbook = Workbook.getWorkbook(new File(inputTemplatePath));

    // collect periodId by commaSepareted
    List<Period> tempPeriodList =
        new ArrayList<Period>(periodService.getIntersectingPeriods(sDate, eDate));

    Collection<Integer> tempPeriodIds =
        new ArrayList<Integer>(getIdentifiers(Period.class, tempPeriodList));

    String periodIdsByComma = getCommaDelimitedString(tempPeriodIds);

    // Getting DataValues
    List<Report_inDesign> reportDesignList = reportService.getReportDesign(deCodesXMLFileName);

    // collect dataElementIDs by commaSepareted
    String dataElmentIdsByComma = reportService.getDataelementIds(reportDesignList);

    int orgUnitCount = 0;

    Iterator<OrganisationUnit> it = orgUnitList.iterator();
    while (it.hasNext()) {
      OrganisationUnit currentOrgUnit = (OrganisationUnit) it.next();

      String outPutFileName = reportFileNameTB.replace(".xls", "");
      outPutFileName += "_" + currentOrgUnit.getShortName();
      outPutFileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".xls";

      String outputReportPath = outputReportFolderPath + File.separator + outPutFileName;
      WritableWorkbook outputReportWorkbook =
          Workbook.createWorkbook(new File(outputReportPath), templateWorkbook);

      Map<String, String> aggDeMap = new HashMap<String, String>();
      if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
        aggDeMap.putAll(
            reportService.getResultDataValueFromAggregateTable(
                currentOrgUnit.getId(), dataElmentIdsByComma, periodIdsByComma));
      } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
        List<OrganisationUnit> childOrgUnitTree =
            new ArrayList<OrganisationUnit>(
                organisationUnitService.getOrganisationUnitWithChildren(currentOrgUnit.getId()));
        List<Integer> childOrgUnitTreeIds =
            new ArrayList<Integer>(getIdentifiers(OrganisationUnit.class, childOrgUnitTree));
        String childOrgUnitsByComma = getCommaDelimitedString(childOrgUnitTreeIds);

        aggDeMap.putAll(
            reportService.getAggDataFromDataValueTable(
                childOrgUnitsByComma, dataElmentIdsByComma, periodIdsByComma));
      } else if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
        aggDeMap.putAll(
            reportService.getAggDataFromDataValueTable(
                "" + currentOrgUnit.getId(), dataElmentIdsByComma, periodIdsByComma));
      }

      int count1 = 0;
      Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator();
      while (reportDesignIterator.hasNext()) {
        Report_inDesign report_inDesign = (Report_inDesign) reportDesignIterator.next();

        String deType = report_inDesign.getPtype();
        String sType = report_inDesign.getStype();
        String deCodeString = report_inDesign.getExpression();
        String tempStr = "";

        Calendar tempStartDate = Calendar.getInstance();
        Calendar tempEndDate = Calendar.getInstance();
        List<Calendar> calendarList =
            new ArrayList<Calendar>(reportService.getStartingEndingPeriods(deType, selectedPeriod));
        if (calendarList == null || calendarList.isEmpty()) {
          tempStartDate.setTime(selectedPeriod.getStartDate());
          tempEndDate.setTime(selectedPeriod.getEndDate());
          return SUCCESS;
        } else {
          tempStartDate = calendarList.get(0);
          tempEndDate = calendarList.get(1);
        }

        if (deCodeString.equalsIgnoreCase("FACILITY")) {
          tempStr = currentOrgUnit.getName();
        } else if (deCodeString.equalsIgnoreCase("FACILITY-NOREPEAT")) {
          tempStr = parentUnit;
        } else if (deCodeString.equalsIgnoreCase("FACILITYP")) {
          tempStr = currentOrgUnit.getParent().getName();
        } else if (deCodeString.equalsIgnoreCase("FACILITYPP")) {
          tempStr = currentOrgUnit.getParent().getParent().getName();
        } else if (deCodeString.equalsIgnoreCase("FACILITYPPP")) {
          tempStr = currentOrgUnit.getParent().getParent().getParent().getName();
        } else if (deCodeString.equalsIgnoreCase("FACILITYPPPP")) {
          tempStr = currentOrgUnit.getParent().getParent().getParent().getParent().getName();
        } else if (deCodeString.equalsIgnoreCase("PERIOD")
            || deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT")) {
          tempStr = simpleDateFormat.format(sDate);
        } else if (deCodeString.equalsIgnoreCase("PERIOD-MONTH")) {
          tempStr = monthFormat.format(sDate);
        } else if (deCodeString.equalsIgnoreCase("PERIOD-YEAR")) {
          tempStr = yearFormat.format(sDate);
        } else if (deCodeString.equalsIgnoreCase("MONTH-START-SHORT")) {
          tempStr = simpleMonthFormat.format(sDate);
        } else if (deCodeString.equalsIgnoreCase("MONTH-END-SHORT")) {
          tempStr = simpleMonthFormat.format(eDate);
        } else if (deCodeString.equalsIgnoreCase("MONTH-START")) {
          tempStr = monthFormat.format(sDate);
        } else if (deCodeString.equalsIgnoreCase("MONTH-END")) {
          tempStr = monthFormat.format(eDate);
        } else if (deCodeString.equalsIgnoreCase("SLNO")) {
          tempStr = "" + (orgUnitCount + 1);
        } else if (deCodeString.equalsIgnoreCase("NA")) {
          tempStr = " ";
        } else {
          if (sType.equalsIgnoreCase("dataelement")) {
            if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
              tempStr = getAggVal(deCodeString, aggDeMap);
              // tempStr = reportService.getIndividualResultDataValue(deCodeString,
              // tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB );
            } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
              tempStr = getAggVal(deCodeString, aggDeMap);
              // tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(),
              // tempEndDate.getTime(), currentOrgUnit, reportModelTB );
            } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {

              tempStr = getAggVal(deCodeString, aggDeMap);
              /*
              List<Period> periodList = new ArrayList<Period>( periodService.getPeriodsBetweenDates( tempStartDate.getTime(), tempEndDate.getTime() ) );
              Collection<Integer> periodIds = new ArrayList<Integer>( getIdentifiers(Period.class, periodList ) );
              tempStr = reportService.getResultDataValueFromAggregateTable( deCodeString, periodIds, currentOrgUnit, reportModelTB );
              */
            }
          } else if (sType.equalsIgnoreCase("dataelement-boolean")) {
            if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
              tempStr =
                  reportService.getBooleanDataValue(
                      deCodeString,
                      tempStartDate.getTime(),
                      tempEndDate.getTime(),
                      currentOrgUnit,
                      reportModelTB);
            } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
              tempStr =
                  reportService.getBooleanDataValue(
                      deCodeString,
                      tempStartDate.getTime(),
                      tempEndDate.getTime(),
                      currentOrgUnit,
                      reportModelTB);
            } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
              tempStr =
                  reportService.getBooleanDataValue(
                      deCodeString,
                      tempStartDate.getTime(),
                      tempEndDate.getTime(),
                      currentOrgUnit,
                      reportModelTB);
            }
          } else {
            if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
              tempStr =
                  reportService.getIndividualResultIndicatorValue(
                      deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit);
            } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
              tempStr =
                  reportService.getResultIndicatorValue(
                      deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit);
            } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
              // List<Period> periodList = new ArrayList<Period>(
              // periodService.getPeriodsBetweenDates( tempStartDate.getTime(),
              // tempEndDate.getTime() ) );
              // Collection<Integer> periodIds = new ArrayList<Integer>(
              // getIdentifiers(Period.class, periodList ) );
              tempStr =
                  reportService.getResultIndicatorValue(
                      deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit);
            }
          }
        }

        int tempRowNo = report_inDesign.getRowno();
        int tempColNo = report_inDesign.getColno();
        int sheetNo = report_inDesign.getSheetno();
        WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo);

        if (tempStr == null || tempStr.equals(" ")) {
          tempColNo += orgUnitCount;

          WritableCellFormat wCellformat = new WritableCellFormat();
          wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
          wCellformat.setWrap(true);
          wCellformat.setAlignment(Alignment.CENTRE);

          sheet0.addCell(new Blank(tempColNo, tempRowNo, wCellformat));
        } else {
          if (reportModelTB.equalsIgnoreCase("DYNAMIC-ORGUNIT")) {
            if (deCodeString.equalsIgnoreCase("FACILITYP")
                || deCodeString.equalsIgnoreCase("FACILITYPP")
                || deCodeString.equalsIgnoreCase("FACILITYPPP")
                || deCodeString.equalsIgnoreCase("FACILITYPPPP")) {
            } else if (deCodeString.equalsIgnoreCase("PERIOD")
                || deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT")
                || deCodeString.equalsIgnoreCase("PERIOD-WEEK")
                || deCodeString.equalsIgnoreCase("PERIOD-MONTH")
                || deCodeString.equalsIgnoreCase("PERIOD-QUARTER")
                || deCodeString.equalsIgnoreCase("PERIOD-YEAR")
                || deCodeString.equalsIgnoreCase("MONTH-START")
                || deCodeString.equalsIgnoreCase("MONTH-END")
                || deCodeString.equalsIgnoreCase("MONTH-START-SHORT")
                || deCodeString.equalsIgnoreCase("MONTH-END-SHORT")
                || deCodeString.equalsIgnoreCase("SIMPLE-QUARTER")
                || deCodeString.equalsIgnoreCase("QUARTER-MONTHS-SHORT")
                || deCodeString.equalsIgnoreCase("QUARTER-MONTHS")
                || deCodeString.equalsIgnoreCase("QUARTER-START-SHORT")
                || deCodeString.equalsIgnoreCase("QUARTER-END-SHORT")
                || deCodeString.equalsIgnoreCase("QUARTER-START")
                || deCodeString.equalsIgnoreCase("QUARTER-END")
                || deCodeString.equalsIgnoreCase("SIMPLE-YEAR")
                || deCodeString.equalsIgnoreCase("YEAR-END")
                || deCodeString.equalsIgnoreCase("YEAR-FROMTO")) {
            } else {
              tempColNo += orgUnitCount;
            }
          } else if (reportModelTB.equalsIgnoreCase("dynamicwithrootfacility")) {
            if (deCodeString.equalsIgnoreCase("FACILITYP")
                || deCodeString.equalsIgnoreCase("FACILITY-NOREPEAT")
                || deCodeString.equalsIgnoreCase("FACILITYPP")
                || deCodeString.equalsIgnoreCase("FACILITYPPP")
                || deCodeString.equalsIgnoreCase("FACILITYPPPP")) {
            } else if (deCodeString.equalsIgnoreCase("PERIOD")
                || deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT")
                || deCodeString.equalsIgnoreCase("PERIOD-WEEK")
                || deCodeString.equalsIgnoreCase("PERIOD-MONTH")
                || deCodeString.equalsIgnoreCase("PERIOD-QUARTER")
                || deCodeString.equalsIgnoreCase("PERIOD-YEAR")
                || deCodeString.equalsIgnoreCase("MONTH-START")
                || deCodeString.equalsIgnoreCase("MONTH-END")
                || deCodeString.equalsIgnoreCase("MONTH-START-SHORT")
                || deCodeString.equalsIgnoreCase("MONTH-END-SHORT")
                || deCodeString.equalsIgnoreCase("SIMPLE-QUARTER")
                || deCodeString.equalsIgnoreCase("QUARTER-MONTHS-SHORT")
                || deCodeString.equalsIgnoreCase("QUARTER-MONTHS")
                || deCodeString.equalsIgnoreCase("QUARTER-START-SHORT")
                || deCodeString.equalsIgnoreCase("QUARTER-END-SHORT")
                || deCodeString.equalsIgnoreCase("QUARTER-START")
                || deCodeString.equalsIgnoreCase("QUARTER-END")
                || deCodeString.equalsIgnoreCase("SIMPLE-YEAR")
                || deCodeString.equalsIgnoreCase("YEAR-END")
                || deCodeString.equalsIgnoreCase("YEAR-FROMTO")) {
            } else {
              tempRowNo += orgUnitCount;
            }
          }

          WritableCell cell = sheet0.getWritableCell(tempColNo, tempRowNo);

          CellFormat cellFormat = cell.getCellFormat();
          WritableCellFormat wCellformat = new WritableCellFormat();
          wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
          wCellformat.setWrap(true);
          wCellformat.setAlignment(Alignment.CENTRE);

          if (cell.getType() == CellType.LABEL) {
            Label l = (Label) cell;
            l.setString(tempStr);
            l.setCellFormat(cellFormat);
          } else {
            try {
              sheet0.addCell(
                  new Number(tempColNo, tempRowNo, Double.parseDouble(tempStr), wCellformat));
            } catch (Exception e) {
              sheet0.addCell(new Label(tempColNo, tempRowNo, tempStr, wCellformat));
            }
          }
        }

        count1++;
      } // inner while loop end

      outputReportWorkbook.write();
      outputReportWorkbook.close();

      orgUnitCount++;
    } // outer while loop end

    statementManager.destroy();

    if (zipDirectory(outputReportFolderPath, outputReportFolderPath + ".zip")) {
      System.out.println(
          selOrgUnit.getName()
              + " : "
              + selReportObj.getName()
              + " Report Generation End Time is : "
              + new Date());

      fileName = reportFileNameTB.replace(".xls", "");
      fileName += "_" + selOrgUnit.getShortName();
      fileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".zip";

      File outputReportFile = new File(outputReportFolderPath + ".zip");
      inputStream = new BufferedInputStream(new FileInputStream(outputReportFile));

      return SUCCESS;
    } else {
      return INPUT;
    }
  }
  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();
    }
  }
Exemplo n.º 15
0
  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();
  }
Exemplo n.º 16
0
  public String execute() throws Exception {
    int tempCol1 = 0;
    int tempRow1 = 1;

    System.out.println("Export to Excel");

    ActionContext ctx = ActionContext.getContext();
    HttpServletRequest req = (HttpServletRequest) ctx.get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = req.getSession();
    BufferedImage chartImage = (BufferedImage) session.getAttribute("chartImage");
    PngEncoder encoder = new PngEncoder(chartImage, false, 0, 9);

    byte[] encoderBytes = encoder.pngEncode();
    Double[][] objData1 = (Double[][]) session.getAttribute("data1");
    Double[][] objData2 = (Double[][]) session.getAttribute("data2");

    String[] series1S = (String[]) session.getAttribute("series1");
    String[] series2S = (String[]) session.getAttribute("series2");
    String[] categories1S = (String[]) session.getAttribute("categories1");
    String[] categories2S = (String[]) session.getAttribute("categories2");

    initialzeAllLists(series1S, series2S, categories1S, categories2S);

    data1 = convertDoubleTodouble(objData1);
    data2 = convertDoubleTodouble(objData2);

    if (chartDisplayOption == null || chartDisplayOption.equalsIgnoreCase("none")) {
    } else if (chartDisplayOption.equalsIgnoreCase("ascend")) {
      sortByAscending();
    } else if (chartDisplayOption.equalsIgnoreCase("desend")) {
      sortByDesscending();
    } else if (chartDisplayOption.equalsIgnoreCase("alphabet")) {
      sortByAlphabet();
    }

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

    WritableWorkbook outputReportWorkbook = Workbook.createWorkbook(new File(outputReportFile));
    WritableSheet sheet0 = outputReportWorkbook.createSheet("ChartOutput", 0);

    if (viewSummary.equals("no")) {
      WritableImage writableImage = new WritableImage(0, 1, 10, 23, encoderBytes);
      sheet0.addImage(writableImage);
      tempRow1 = 24;
    } else {
      tempRow1 -= objData1.length;
    }

    int count1 = 0;
    int count2 = 0;
    int flag1 = 0;
    while (count1 <= categories1.length) {
      for (int j = 0; j < data1.length; j++) {
        tempCol1 = 1;
        tempRow1++;
        WritableCellFormat wCellformat1 = new WritableCellFormat();
        wCellformat1.setBorder(Border.ALL, BorderLineStyle.THIN);
        wCellformat1.setWrap(true);

        WritableCellFormat wCellformat2 = new WritableCellFormat();
        wCellformat2.setBorder(Border.ALL, BorderLineStyle.THIN);
        wCellformat2.setAlignment(Alignment.CENTRE);
        wCellformat2.setBackground(Colour.GRAY_25);
        wCellformat2.setWrap(true);

        WritableCell cell1;
        CellFormat cellFormat1;

        for (int k = count2; k < count1; k++) {
          if (k == count2 && j == 0) {
            tempCol1 = 0;
            tempRow1++;
            cell1 = sheet0.getWritableCell(tempCol1, tempRow1);
            cellFormat1 = cell1.getCellFormat();

            if (cell1.getType() == CellType.LABEL) {
              Label l = (Label) cell1;
              l.setString("Service");
              l.setCellFormat(cellFormat1);
            } else {
              sheet0.addCell(new Label(tempCol1, tempRow1, "Service", wCellformat2));
            }
            tempCol1++;

            for (int i = count2; i < count1; i++) {
              cell1 = sheet0.getWritableCell(tempCol1, tempRow1);
              cellFormat1 = cell1.getCellFormat();
              if (cell1.getType() == CellType.LABEL) {
                Label l = (Label) cell1;
                l.setString(categories1[i]);
                l.setCellFormat(cellFormat1);
              } else {
                sheet0.addCell(new Label(tempCol1, tempRow1, categories1[i], wCellformat2));
              }
              tempCol1++;
            }
            tempRow1++;
            tempCol1 = 1;
          }

          if (k == count2) {
            tempCol1 = 0;
            cell1 = sheet0.getWritableCell(tempCol1, tempRow1);
            cellFormat1 = cell1.getCellFormat();

            if (cell1.getType() == CellType.LABEL) {
              Label l = (Label) cell1;
              l.setString(series1[j]);
              l.setCellFormat(cellFormat1);
            } else {
              sheet0.addCell(new Label(tempCol1, tempRow1, series1[j], wCellformat2));
            }
            tempCol1++;
          }
          cell1 = sheet0.getWritableCell(tempCol1, tempRow1);
          cellFormat1 = cell1.getCellFormat();

          if (cell1.getType() == CellType.LABEL) {
            Label l = (Label) cell1;
            l.setString("" + data1[j][k]);
            l.setCellFormat(cellFormat1);
          } else {
            sheet0.addCell(new Number(tempCol1, tempRow1, data1[j][k], wCellformat1));
          }
          tempCol1++;
        }
      }
      if (flag1 == 1) break;
      count2 = count1;
      if ((count1 + 10 > categories1.length) && (categories1.length - count1 <= 10)) {
        count1 += categories1.length - count1;
        flag1 = 1;
      } else count1 += 10;
    }
    outputReportWorkbook.write();
    outputReportWorkbook.close();

    fileName = "chartOutput.xls";

    inputStream = new BufferedInputStream(new FileInputStream(outputReportFile));

    return SUCCESS;
  }
Exemplo n.º 17
0
  /**
   * 生成具有一定格式excel
   *
   * @param sheetName sheet名称,默认为sheet1
   * @param nf 数字类型的格式 如:jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");默认无格式
   * @param content 二维数组,要生成excel的数据来源
   * @param 合并项 每一项的数据格式为0,1,0,2 即:把(0,1)和(0,2)合并--->第1列的第一、二个元素合并
   * @param os excel输出流
   * @param row 需要水平居中的行,默认居左。以逗号分隔的字符串
   * @param col 需要水平居中的列,默认居左。以逗号分隔的字符串
   */
  public void export(
      String sheetName,
      NumberFormat nf,
      String[][] content,
      String[] mergeInfo,
      OutputStream os,
      String row,
      String col) {

    if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
      return;
    }
    // 默认名称
    if (VerifyUtil.isNullObject(sheetName)) {
      sheetName = "sheet1";
    }
    Set<Integer> rows = this.getInfo(row);
    Set<Integer> cols = this.getInfo(col);
    WritableWorkbook workbook = null;
    try {
      workbook = Workbook.createWorkbook(os);
      WritableSheet sheet = workbook.createSheet(sheetName, 0);
      for (int i = 0; i < content.length; i++) {
        for (int j = 0; j < content[i].length; j++) {
          if (content[i][j] == null) {
            content[i][j] = "";
          }
          if (isNumber(content[i][j]) && !rows.contains(i) && !cols.contains(j)) { // 处理数字
            Number number = null;
            if (VerifyUtil.isNullObject(nf)) { // 数字无格式
              number = new Number(j, i, Double.valueOf(content[i][j]));
            } else { // 如果有格式,按格式生成
              jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat(nf);
              number = new Number(j, i, Double.valueOf(content[i][j]), wcfn);
            }
            sheet.addCell(number);
          } else { // 处理非数字
            WritableCellFormat format = new WritableCellFormat();
            if (rows.contains(i) || cols.contains(j)) {
              format.setAlignment(jxl.format.Alignment.CENTRE);
            } else {
              format.setAlignment(jxl.format.Alignment.LEFT);
            }
            format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            Label label = new Label(j, i, content[i][j], format);
            sheet.addCell(label);
          }
        }
      }
      this.merge(sheet, mergeInfo);
      workbook.write();
    } catch (Exception e) {
      e.printStackTrace();

    } finally {
      try {
        workbook.close();
        os.close();
      } catch (WriteException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
Exemplo n.º 18
0
  public void doFinish(IProgressMonitor monitor) {
    monitor.beginTask("Exporting ", rowCount);

    try {
      switch (ComboFile) {
        case 0:
          WritableWorkbook myWorkbook = null;
          String str = null;
          try {
            str = command.Command_Int("SELECT COUNT(*) FROM " + sCollection);
          } catch (Exception e) {
            e.printStackTrace();
          }
          if (Integer.parseInt(str) > 50000) {
            int j = 0;
            int k = 0;
            int m = 0;
            while (k * 50000 < Integer.parseInt(str)) {
              if (Integer.parseInt(str) > 100000)
                myWorkbook = Workbook.createWorkbook(new File(sFullPath + "(" + m + ")" + ".xls"));
              else {
                myWorkbook = Workbook.createWorkbook(new File(sFullPath + ".xls"));
              }
              int l = 0;
              for (; k * 50000 < Integer.parseInt(str); k++) {
                myWorkbook.createSheet("Sheet" + k, k);
                WritableSheet mySheet = myWorkbook.getSheet(l);
                String sImsi = "";
                for (int i = 0; i < oColumns.size(); i++) {
                  sImsi = oColumns.get(i);
                  WritableCellFormat ColumnFormat = new WritableCellFormat();
                  ColumnFormat.setAlignment(Alignment.CENTRE);
                  ColumnFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
                  ColumnFormat.setBackground(Colour.GRAY_25);
                  mySheet.setColumnView(i, (sImsi.trim().length() * 2));
                  jxl.write.Label oColumnLabel = null;
                  oColumnLabel = new jxl.write.Label(i, 0, sImsi, ColumnFormat);
                  mySheet.addCell(oColumnLabel);
                }
                sQuery = "selectattr " + sAttr + "'limit " + 50000 + "offset " + (j) * 50000 + "'";
                ArrayList<String> oExcel = command.Command_Excel(sQuery);
                int nTotal = oExcel.size() / oColumns.size();
                int nColumn = oColumns.size();
                int nCount = 0;
                for (int nRow = 1; nRow <= nTotal; nRow++) {
                  for (int nCol = 0; nCol < nColumn; nCol++) {
                    jxl.write.Label numberLabels = null;
                    numberLabels = new jxl.write.Label(nCol, nRow, oExcel.get(nCount++));
                    mySheet.addCell(numberLabels);
                  }
                  monitor.worked(1);
                }
                j++;
                l++;
                if (l == 2) {
                  k++;
                  break;
                }
              }
              m++;
              myWorkbook.write();
              myWorkbook.close();
            }
          } else if (Integer.parseInt(str) < 50000) {
            myWorkbook = Workbook.createWorkbook(new File(sFullPath + ".xls"));
            WritableSheet mySheet = myWorkbook.createSheet(sCollection.replaceAll("/", "_"), 0);

            String sImsi = "";
            for (int i = 0; i < oColumns.size(); i++) {
              sImsi = oColumns.get(i);

              WritableCellFormat ColumnFormat = new WritableCellFormat();
              ColumnFormat.setAlignment(Alignment.CENTRE);
              ColumnFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
              ColumnFormat.setBackground(Colour.GRAY_25);
              mySheet.setColumnView(i, (sImsi.trim().length() * 2));
              jxl.write.Label oColumnLabel = null;

              oColumnLabel = new jxl.write.Label(i, 0, sImsi, ColumnFormat);
              mySheet.addCell(oColumnLabel);
            }

            ArrayList<String> oExcel = command.Command_Excel(sQuery);

            int nTotal = oExcel.size() / oColumns.size();
            int nColumn = oColumns.size();

            int nCount = 0;
            for (int nRow = 1; nRow <= nTotal; nRow++) {
              for (int nCol = 0; nCol < nColumn; nCol++) {
                jxl.write.Label numberLabels = null;
                numberLabels = new jxl.write.Label(nCol, nRow, oExcel.get(nCount++));
                mySheet.addCell(numberLabels);
              }
              monitor.worked(1);
            }
            myWorkbook.write();
            myWorkbook.close();
          }
          final int sheet = lblsheet;
          final int File;
          if ((lblsheet / 2) == 0) {
            File = 1;
          } else {
            File = (lblsheet / 2);
          }
          if (istrue == true) {
            getShell()
                .getDisplay()
                .syncExec(
                    new Runnable() {
                      public void run() {
                        MessageDialog.openInformation(
                            getShell(),
                            "AMGA_Mangaer",
                            "Success : Files have been saved["
                                + sFullPath
                                + (page2.getComboFile() == 0 ? ".xls" : ".txt")
                                + "]"
                                + "\r\n"
                                + "File : "
                                + File
                                + " Sheet : "
                                + sheet);
                      }
                    });
          }

          break;
        case 1:
          // Txt
          String sDelimited = "";
          switch (ComboString) {
            case 0:
              sDelimited = "\t";
              break;
            case 1:
              sDelimited = " ";
              break;
            case 2:
              sDelimited = ",";
              break;
            default:
              break;
          }

          String sTitle = "";

          if (p1btnselection == true) {

            sQuery = "selectattr " + sAttr + " ' '";
            for (int i = 0; i < oColumns.size(); i++) {
              sTitle = sTitle + oColumns.get(i) + sDelimited;
            }
          } else {
            sQuery = p1_1text;
            String Query = sQuery;
            ArrayList<String> ooColumn = new ArrayList<String>();
            int nQuery = 0;
            // selectattr
            for (int i = 0; i < Query.length(); i++) {
              if (Query.charAt(i) == '\'') {
                nQuery = i;
                break;
              }
            }
            StringTokenizer st1 = new StringTokenizer(Query.substring(0, nQuery), " ");
            while (st1.hasMoreElements()) {
              String sTemp = (String) st1.nextElement();
              if (sTemp.trim().charAt(0) == '/') {
                ooColumn.add(sTemp);
              }
            }

            for (int i = 0; i < ooColumn.size(); i++) {
              sTitle = sTitle + ooColumn.get(i).substring(page1.str.length() + 1) + sDelimited;
            }

            nColumnCount = ooColumn.size();
          }
          ArrayList<String> oTxt = null;
          try {
            oTxt = command.Command_Txt(sQuery, sDelimited, nColumnCount);
          } catch (Exception e) {
            final String str1 = e.getLocalizedMessage();
            getShell()
                .getDisplay()
                .syncExec(
                    new Runnable() {
                      public void run() {
                        MessageDialog.openError(getShell(), "AMGA_Mangaer", str1);
                      }
                    });
            istrue = false;
          }
          oTxt.add(0, sTitle);
          BufferedWriter out = new BufferedWriter(new FileWriter(sFullPath + ".txt"));
          for (int i = 0; i < oTxt.size(); i++) {
            out.write(oTxt.get(i));
            out.newLine();
            monitor.worked(1);
          }
          out.close();
          if (istrue == true) {
            getShell()
                .getDisplay()
                .syncExec(
                    new Runnable() {
                      public void run() {
                        MessageDialog.openInformation(
                            getShell(),
                            "AMGA_Mangaer",
                            "Success : Files have been saved["
                                + sFullPath
                                + (page2.getComboFile() == 0 ? ".xls" : ".txt")
                                + "]");
                      }
                    });
          }
          break;
        default:
          break;
      }
    } catch (Exception e) {
      final String str = e.getLocalizedMessage();
      getShell()
          .getDisplay()
          .syncExec(
              new Runnable() {
                public void run() {
                  MessageDialog.openError(getShell(), "AMGA_Mangaer", str);
                }
              });
    }
  }
  private void exportUsedMaterial2Excel(
      SearchUsedMaterialBean bean,
      SummaryUsedMaterialDTO result,
      HttpServletRequest request,
      HttpServletResponse response) {
    try {
      String outputFileName = "/files/temp/VatTuSuDung_" + System.currentTimeMillis() + ".xls";
      String reportTemplate =
          request.getSession().getServletContext().getRealPath("/files/export/UsedMaterial.xls");
      String export2FileName = request.getSession().getServletContext().getRealPath(outputFileName);

      Workbook templateWorkbook = Workbook.getWorkbook(new File(reportTemplate));
      WritableWorkbook workbook =
          Workbook.createWorkbook(new File(export2FileName), templateWorkbook);

      WritableSheet sheet = workbook.getSheet(0);

      WritableFont normalFont =
          new WritableFont(
              WritableFont.TIMES,
              12,
              WritableFont.NO_BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              Colour.BLACK);
      WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
      normalFormat.setAlignment(Alignment.CENTRE);
      normalFormat.setWrap(true);
      normalFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
      DecimalFormat decimalFormat = new DecimalFormat("###,###.##");
      DecimalFormat decimalFormat2 = new DecimalFormat("###,###.####");
      int startRow = 4;
      int stt = 1;

      List<UsedMaterialDTO> shareUsedMaterials = result.getShareUsedMaterials();
      List<UsedMaterialDTO> usedProducts = result.getUsedProducts();
      List<UsedMaterialDTO> usedMeasurementMaterials = result.getUsedMeasurementMaterials();
      Double totalMainProductKg = 0d;
      Double totalMainProductMet2 = 0d;
      Integer counter = 0;
      Double totalMet2 = 0d;
      Double totalKg = 0d;
      Double kgMet;
      Double kgTan;
      Double materialUsed;

      if (usedProducts != null && usedProducts.size() > 0) {
        for (UsedMaterialDTO usedProduct : usedProducts) {
          totalKg = usedProduct.getTotalKgUsed();
          totalMet2 = usedProduct.getTotalMUsed() * Integer.valueOf(usedProduct.getWidth()) / 1000;
          counter++;
          totalMainProductKg += totalKg;
          totalMainProductMet2 += totalMet2;

          addUsedMaterialRow(
              sheet,
              normalFormat,
              decimalFormat,
              decimalFormat2,
              startRow++,
              counter,
              usedProduct.getProductName().getName(),
              "Kg",
              totalKg,
              null,
              null);

          if (usedProduct.getUsedMaterialDTOs() != null
              && usedProduct.getUsedMaterialDTOs().size() > 0) {
            for (UsedMaterialDTO usedMaterial : usedProduct.getUsedMaterialDTOs()) {
              counter++;
              materialUsed = usedMaterial.getTotalUsed();
              kgMet = totalMet2 > 0 ? materialUsed / totalMet2 : null;
              kgTan = totalKg > 0 ? materialUsed * 1000 / totalKg : null;
              addUsedMaterialRow(
                  sheet,
                  normalFormat,
                  decimalFormat,
                  decimalFormat2,
                  startRow++,
                  counter,
                  usedMaterial.getMaterial().getName(),
                  usedMaterial.getMaterial().getUnit().getName(),
                  materialUsed,
                  kgMet,
                  kgTan);
            }
          }
        }
      }

      if (usedMeasurementMaterials != null && usedMeasurementMaterials.size() > 0) {
        for (UsedMaterialDTO usedMaterial : usedMeasurementMaterials) {
          counter++;
          materialUsed = usedMaterial.getTotalUsed();
          kgMet = totalMet2 > 0 ? materialUsed / totalMainProductMet2 : null;
          kgTan = totalKg > 0 ? materialUsed * 1000 / totalMainProductKg : null;
          addUsedMaterialRow(
              sheet,
              normalFormat,
              decimalFormat,
              decimalFormat2,
              startRow++,
              counter,
              usedMaterial.getMaterial().getName(),
              usedMaterial.getMaterial().getUnit().getName(),
              materialUsed,
              kgMet,
              kgTan);
        }
      }

      if (shareUsedMaterials != null && shareUsedMaterials.size() > 0) {
        for (UsedMaterialDTO usedMaterial : shareUsedMaterials) {
          counter++;
          materialUsed = usedMaterial.getTotalUsed();
          kgMet = totalMet2 > 0 ? materialUsed / totalMainProductMet2 : null;
          kgTan = totalKg > 0 ? materialUsed * 1000 / totalMainProductKg : null;
          addUsedMaterialRow(
              sheet,
              normalFormat,
              decimalFormat,
              decimalFormat2,
              startRow++,
              counter,
              usedMaterial.getMaterial().getName(),
              usedMaterial.getMaterial().getUnit().getName(),
              materialUsed,
              kgMet,
              kgTan);
        }
      }
      workbook.write();
      workbook.close();
      response.sendRedirect(
          request.getSession().getServletContext().getContextPath() + outputFileName);
    } catch (Exception ex) {
      logger.error(ex.getMessage(), ex);
    }
  }
Exemplo n.º 20
0
  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);
    }
  }
  // -------------------------------------------------------------------------
  // Action implementation
  // -------------------------------------------------------------------------
  public String execute() throws Exception {
    statementManager.initialise();

    // Initialization
    raFolderName = reportService.getRAFolderName();

    String colArray[] = {
      "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S",
      "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ",
      "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY",
      "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN",
      "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ"
    };

    Report_in selReportObj = reportService.getReport(Integer.parseInt(reportList));

    // OrgUnit Info
    OrganisationUnit currentOrgUnit = organisationUnitService.getOrganisationUnit(ouIDTB);

    System.out.println(
        currentOrgUnit.getName()
            + " : "
            + selReportObj.getName()
            + " : Report Generation Start Time is : "
            + new Date());

    List<OrganisationUnit> childOrgUnitTree =
        new ArrayList<OrganisationUnit>(
            organisationUnitService.getOrganisationUnitWithChildren(ouIDTB));
    List<Integer> childOrgUnitTreeIds =
        new ArrayList<Integer>(getIdentifiers(OrganisationUnit.class, childOrgUnitTree));
    String childOrgUnitsByComma = getCommaDelimitedString(childOrgUnitTreeIds);

    // Report Info
    String deCodesXMLFileName = selReportObj.getXmlTemplateName();
    String reportModelTB = selReportObj.getModel();
    String reportFileNameTB = selReportObj.getExcelTemplateName();

    String inputTemplatePath =
        System.getenv("DHIS2_HOME")
            + File.separator
            + raFolderName
            + File.separator
            + "template"
            + File.separator
            + reportFileNameTB;
    // 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";

    Workbook templateWorkbook = Workbook.getWorkbook(new File(inputTemplatePath));

    WritableWorkbook outputReportWorkbook =
        Workbook.createWorkbook(new File(outputReportPath), templateWorkbook);
    WritableCellFormat wCellformat = new WritableCellFormat();
    wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
    wCellformat.setAlignment(Alignment.CENTRE);
    wCellformat.setVerticalAlignment(VerticalAlignment.CENTRE);
    wCellformat.setWrap(true);

    // Period Info
    selectedPeriod = periodService.getPeriod(availablePeriods);
    selectedEndPeriod = periodService.getPeriod(availablePeriodsto);

    sDate = format.parseDate(String.valueOf(selectedPeriod.getStartDate()));
    eDate = format.parseDate(String.valueOf(selectedEndPeriod.getEndDate()));

    PeriodType periodType = periodService.getPeriodTypeByName(periodTypeId);
    List<Period> periodList =
        new ArrayList<Period>(periodService.getPeriodsBetweenDates(periodType, sDate, eDate));
    // List<Period> periodList = new ArrayList<Period>( periodService.getIntersectingPeriods( sDate,
    // eDate ) );
    Collections.sort(periodList, new PeriodStartDateComparator());

    if (periodTypeId.equalsIgnoreCase("monthly")) {
      simpleDateFormat = new SimpleDateFormat("MMM-yyyy");
    } else if (periodTypeId.equalsIgnoreCase("yearly")) {
      simpleDateFormat = new SimpleDateFormat("yyyy");
    } else {
      simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    }

    // To get Aggregation Data

    List<Report_inDesign> reportDesignList = reportService.getReportDesign(deCodesXMLFileName);

    // String dataElmentIdsByComma = reportService.getDataelementIds( reportDesignList );
    String dataElmentIdsByComma =
        reportService.getDataelementIdsByStype(reportDesignList, Report_inDesign.ST_DATAELEMENT);
    String nonNumberDataElementIdsByComma =
        reportService.getDataelementIdsByStype(
            reportDesignList, Report_inDesign.ST_NON_NUMBER_DATAELEMENT);

    // Collection<Integer> periodIds1 = new ArrayList<Integer>( getIdentifiers(Period.class,
    // periodList ) );
    String periodsByComma = "";
    // getCommaDelimitedString( periodIds1 );

    int colCount = 0;
    for (Period period : periodList) {
      if (periodTypeId.equalsIgnoreCase("daily")) {
        periodsByComma = "" + period.getId();
      } else {
        Collection<Integer> periodIds =
            new ArrayList<Integer>(
                getIdentifiers(
                    Period.class,
                    periodService.getIntersectingPeriods(
                        period.getStartDate(), period.getEndDate())));
        periodsByComma = getCommaDelimitedString(periodIds);
      }

      Map<String, String> aggDeMap = new HashMap<String, String>();
      if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
        aggDeMap.putAll(
            reportService.getResultDataValueFromAggregateTable(
                currentOrgUnit.getId(), dataElmentIdsByComma, periodsByComma));
      } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
        aggDeMap.putAll(
            reportService.getAggDataFromDataValueTable(
                childOrgUnitsByComma, dataElmentIdsByComma, periodsByComma));
        aggDeMap.putAll(
            reportService.getAggNonNumberDataFromDataValueTable(
                childOrgUnitsByComma, nonNumberDataElementIdsByComma, periodsByComma));
        System.out.println(
            childOrgUnitsByComma + " \n " + dataElmentIdsByComma + " \n " + periodsByComma);
      } else if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
        aggDeMap.putAll(
            reportService.getAggDataFromDataValueTable(
                "" + currentOrgUnit.getId(), dataElmentIdsByComma, periodsByComma));
        aggDeMap.putAll(
            reportService.getAggNonNumberDataFromDataValueTable(
                "" + currentOrgUnit.getId(), nonNumberDataElementIdsByComma, periodsByComma));
      }
      System.out.println("aggDeMap size : " + aggDeMap.size());

      Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator();
      while (reportDesignIterator.hasNext()) {
        Report_inDesign reportDesign = reportDesignIterator.next();
        String deCodeString = reportDesign.getExpression();

        String sType = reportDesign.getStype();
        String tempStr = "";

        tempRowNo = reportDesign.getRowno();
        tempColNo = reportDesign.getColno();
        sheetNo = reportDesign.getSheetno();

        if (deCodeString.equalsIgnoreCase("FACILITY")) {
          tempStr = currentOrgUnit.getName();
        } else if (deCodeString.equalsIgnoreCase("PERIOD-RANGE")) {
          tempStr =
              simpleDateFormat.format(selectedPeriod.getStartDate())
                  + " To "
                  + simpleDateFormat.format(selectedEndPeriod.getEndDate());
        } else if (deCodeString.equalsIgnoreCase("PROGRESSIVE-PERIOD")) {
          tempStr = simpleDateFormat.format(period.getStartDate());
        } else if (deCodeString.equalsIgnoreCase("NA")) {
          tempStr = " ";
        } else {
          if (sType.equalsIgnoreCase("dataelement")) {
            if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
              // tempStr = reportService.getIndividualResultDataValue( deCodeString,
              // period.getStartDate(), period.getEndDate(), currentOrgUnit, reportModelTB );
              tempStr = getAggVal(deCodeString, aggDeMap);
            } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
              // tempStr = reportService.getResultDataValue( deCodeString, period.getStartDate(),
              // period.getEndDate(), currentOrgUnit, reportModelTB );
              tempStr = getAggVal(deCodeString, aggDeMap);
            } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
              tempStr = getAggVal(deCodeString, aggDeMap);
            }
          } else if (sType.equalsIgnoreCase(Report_inDesign.ST_DATAELEMENT_NO_REPEAT)) {
            deCodeString = deCodeString.replaceAll(":", "\\.");
            deCodeString = deCodeString.replaceAll("[", "");
            deCodeString = deCodeString.replaceAll("]", "");
            System.out.println("deCodeString : " + deCodeString);
            tempStr = aggDeMap.get(deCodeString);
          }
        }

        if (tempStr == null || tempStr.equals(" ")) {
          tempColNo += colCount;

          WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo);

          sheet0.addCell(new Blank(tempColNo, tempRowNo, wCellformat));
        } else {
          if (reportModelTB.equalsIgnoreCase("PROGRESSIVE-PERIOD")) {
            if (deCodeString.equalsIgnoreCase("FACILITY")
                || deCodeString.equalsIgnoreCase("PERIOD-RANGE")) {
            } else {
              tempColNo += colCount;
            }

            WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo);

            try {
              try {
                sheet0.addCell(
                    new Number(tempColNo, tempRowNo, Double.parseDouble(tempStr), wCellformat));
              } catch (Exception e) {
                sheet0.addCell(new Label(tempColNo, tempRowNo, tempStr, wCellformat));
              }
            } catch (Exception e) {
              System.out.println("Cannot write to Excel");
            }
          }
        }
      } // inner while loop end

      colCount++;
    } // outer while loop end

    // ---------------------------------------------------------------------
    // Writing Total Values
    // ---------------------------------------------------------------------

    Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator();
    while (reportDesignIterator.hasNext()) {
      Report_inDesign reportDesign = reportDesignIterator.next();

      String deCodeString = reportDesign.getExpression();

      if (deCodeString.equalsIgnoreCase("FACILITY")
          || deCodeString.equalsIgnoreCase("PERIOD-RANGE")) {
        continue;
      }

      tempRowNo = reportDesign.getRowno();
      tempColNo = reportDesign.getColno();
      sheetNo = reportDesign.getSheetno();

      String colStart = "" + colArray[tempColNo];
      String colEnd = "" + colArray[tempColNo + colCount - 1];

      String tempFormula =
          "SUM(" + colStart + (tempRowNo + 1) + ":" + colEnd + (tempRowNo + 1) + ")";

      WritableSheet totalSheet = outputReportWorkbook.getSheet(sheetNo);
      WritableFont arialBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
      WritableCellFormat totalCellformat = new WritableCellFormat(arialBold);
      totalCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
      totalCellformat.setAlignment(Alignment.CENTRE);
      totalCellformat.setVerticalAlignment(VerticalAlignment.CENTRE);
      totalCellformat.setWrap(true);

      if (deCodeString.equalsIgnoreCase("PROGRESSIVE-PERIOD")) {
        totalSheet.addCell(new Label(tempColNo + colCount, tempRowNo, "Total", totalCellformat));
      } else if (deCodeString.equalsIgnoreCase("NA")) {
        totalSheet.addCell(new Label(tempColNo + colCount, tempRowNo, " ", totalCellformat));
      } else {
        totalSheet.addCell(
            new Formula(tempColNo + colCount, tempRowNo, tempFormula, totalCellformat));
      }
    }

    outputReportWorkbook.write();
    outputReportWorkbook.close();

    fileName = reportFileNameTB.replace(".xls", "");
    fileName += "_" + currentOrgUnit.getShortName();
    fileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".xls";
    File outputReportFile = new File(outputReportPath);
    inputStream = new BufferedInputStream(new FileInputStream(outputReportFile));

    System.out.println(
        currentOrgUnit.getName()
            + " : "
            + selReportObj.getName()
            + " : Report Generation End Time is : "
            + new Date());

    outputReportFile.deleteOnExit();

    statementManager.destroy();

    return SUCCESS;
  }