Esempio n. 1
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 byte[] writeOutXlsFileToBytes() {
    byte[] bytes = null;

    WritableFont normalFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
    WritableFont boldFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
    WritableCellFormat cellFormat = null;

    try {
      ByteArrayOutputStream output = new ByteArrayOutputStream();
      WritableWorkbook w = Workbook.createWorkbook(output);
      WritableSheet writableSheet = w.createSheet("Sheet", 0);

      for (int row = 0; row < getTemplateCells().length; row++) {
        for (int col = 0; col < sheetMetaData.getCols(); col++) {
          String cellData = getTemplateCells()[row][col];
          jxl.write.Label label = new jxl.write.Label(col, row, cellData);

          if (row == 0) {
            // Header row in bold
            cellFormat = new WritableCellFormat(boldFont);
          } else {
            cellFormat = new WritableCellFormat(normalFont);
          }

          label.setCellFormat(cellFormat);
          writableSheet.addCell(label);
        }
      }

      w.write();
      w.close();
      bytes = output.toByteArray();
      output.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return bytes;
  }
  // -------------------------------------------------------------------------
  // 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;
    }
  }
  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;
  }