Ejemplo n.º 1
0
  // returns true if data is set successfully else false
  public boolean setCellData(
      String sheetName, String colName, int rowNum, String data, String url) {
    // System.out.println("setCellData setCellData******************");
    try {
      fis = new FileInputStream(path);
      workBook = new XSSFWorkbook(fis);

      if (rowNum <= 0) return false;

      int index = workBook.getSheetIndex(sheetName);
      int colNum = -1;
      if (index == -1) return false;

      sheet = workBook.getSheetAt(index);
      // System.out.println("A");
      row = sheet.getRow(0);
      for (int i = 0; i < row.getLastCellNum(); i++) {
        // System.out.println(row.getCell(i).getStringCellValue().trim());
        if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i;
      }

      if (colNum == -1) return false;
      sheet.autoSizeColumn(colNum); // ashish
      row = sheet.getRow(rowNum - 1);
      if (row == null) row = sheet.createRow(rowNum - 1);

      cell = row.getCell(colNum);
      if (cell == null) cell = row.createCell(colNum);

      cell.setCellValue(data);
      XSSFCreationHelper createHelper = workBook.getCreationHelper();

      // cell style for hyperlinks
      // by default hypelrinks are blue and underlined
      CellStyle hlink_style = workBook.createCellStyle();
      XSSFFont hlink_font = workBook.createFont();
      hlink_font.setUnderline(XSSFFont.U_SINGLE);
      hlink_font.setColor(IndexedColors.BLUE.getIndex());
      hlink_style.setFont(hlink_font);
      // hlink_style.setWrapText(true);

      XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
      link.setAddress(url);
      cell.setHyperlink(link);
      cell.setCellStyle(hlink_style);

      fos = new FileOutputStream(path);
      workBook.write(fos);

      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }
  private void renderCell(
      BandElement bandElement,
      String bandName,
      Object value,
      int gridRow,
      int sheetRow,
      int sheetColumn,
      int rowSpan,
      int colSpan,
      boolean image) {

    if (bandElement instanceof ReportBandElement) {
      colSpan = 1;
    }
    XSSFCellStyle cellStyle =
        buildBandElementStyle(bandElement, value, gridRow, sheetColumn, colSpan);

    // if we have a subreport on the current grid row we have to take care of the sheetColumn
    if (ReportLayout.HEADER_BAND_NAME.equals(bandName)
        && (gridRow == prevSubreportFirstRow)
        && (prevSubreportLastColumn != -1)) {
      sheetColumn = prevSubreportLastColumn - prevSubreportFirstColumn - 1 + sheetColumn;
    }
    XSSFCell c = xlsRow.createCell(sheetColumn);

    if (image) {
      if ((value == null) || "".equals(value)) {
        c.setCellType(XSSFCell.CELL_TYPE_STRING);
        c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_FOUND));
      } else {
        try {
          ImageBandElement ibe = (ImageBandElement) bandElement;
          byte[] imageBytes = getImage((String) value, ibe.getWidth(), ibe.getHeight());
          XSSFClientAnchor anchor =
              new XSSFClientAnchor(
                  0,
                  0,
                  0,
                  0,
                  (short) sheetColumn,
                  sheetRow,
                  (short) (sheetColumn + colSpan),
                  (sheetRow + rowSpan));
          int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG);

          // image is created over the cells, so if it's height is bigger we set the row height
          short height = xlsRow.getHeight();
          int realImageHeight = getRealImageSize((String) value)[1];
          if (ibe.isScaled()) {
            realImageHeight = ibe.getHeight();
          }
          short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
          boolean doResize = false;
          if (imageHeight > height) {
            xlsRow.setHeight(imageHeight);
          } else {
            doResize = true;
          }

          Picture picture = patriarch.createPicture(anchor, index);
          if (doResize) {
            picture.resize();
          }
          anchor.setAnchorType(2);
        } catch (Exception ex) {
          c.setCellType(XSSFCell.CELL_TYPE_STRING);
          c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED));
        }
      }

      if (cellStyle != null) {
        c.setCellStyle(cellStyle);
      }

    } else {
      if (bandElement instanceof HyperlinkBandElement) {
        Hyperlink hyp = ((HyperlinkBandElement) bandElement).getHyperlink();
        XSSFHyperlink link = wb.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_URL);
        link.setAddress(hyp.getUrl());
        c.setHyperlink(link);
        c.setCellValue(wb.getCreationHelper().createRichTextString(hyp.getText()));
        c.setCellType(XSSFCell.CELL_TYPE_STRING);
      } else if (bandElement instanceof ReportBandElement) {
        Report report = ((ReportBandElement) bandElement).getReport();
        ExporterBean eb = null;
        try {
          eb = getSubreportExporterBean(report, true);
          XlsxExporter subExporter = new XlsxExporter(eb, cellStyle);
          subExporter.export();
          XSSFSheet subreportSheet = subExporter.getSubreportSheet();

          if (ReportLayout.HEADER_BAND_NAME.equals(bandName)
              && (gridRow == prevSubreportFirstRow)) {
            // other subreports on the same header line after the first
            sheetColumn = prevSubreportLastColumn;
            sheetRow -= addedPageRows;
            pageRow -= addedPageRows;
            addedPageRows = 0;
          } else {
            addedPageRows = subreportSheet.getLastRowNum();
            pageRow += addedPageRows;
            // if subreport is not on the first column we merge all cells in the columns before,
            // between the rows subreport occupies
            if (sheetColumn > 0) {
              for (int i = 0; i <= sheetColumn - 1; i++) {
                CellRangeAddress cra = new CellRangeAddress(sheetRow, pageRow, i, i);
                regions.add(new XlsxRegion(cra, null));
              }
            }
          }
          int cols = XlsxUtil.copyToSheet(xlsSheet, sheetRow, sheetColumn, subreportSheet);
          addRegions(xlsSheet, subExporter.getSubreportRegions(), wb);
          if (ReportLayout.HEADER_BAND_NAME.equals(bandName)) {
            prevSubreportFirstRow = gridRow;
            prevSubreportFirstColumn = sheetColumn;
            prevSubreportLastColumn = sheetColumn + cols;
          }
        } catch (Exception e) {
          e.printStackTrace();
        } finally {
          if ((eb != null) && (eb.getResult() != null)) {
            eb.getResult().close();
          }
        }
      } else if (bandElement instanceof ImageColumnBandElement) {
        try {
          ImageColumnBandElement icbe = (ImageColumnBandElement) bandElement;
          String v = StringUtil.getValueAsString(value, null);
          if (StringUtil.BLOB.equals(v)) {
            c.setCellType(XSSFCell.CELL_TYPE_STRING);
            c.setCellValue(wb.getCreationHelper().createRichTextString(StringUtil.BLOB));
          } else {
            byte[] imageD = StringUtil.decodeImage(v);
            byte[] imageBytes = getImage(imageD, icbe.getWidth(), icbe.getHeight());
            XSSFClientAnchor anchor =
                new XSSFClientAnchor(
                    0,
                    0,
                    0,
                    0,
                    (short) sheetColumn,
                    sheetRow,
                    (short) (sheetColumn + colSpan),
                    (sheetRow + rowSpan));
            int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG);

            // image is created over the cells, so if it's height is bigger we set the row height
            short height = xlsRow.getHeight();
            int realImageHeight = getRealImageSize(imageBytes)[1];
            if (icbe.isScaled()) {
              realImageHeight = icbe.getHeight();
            }
            short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
            if (imageHeight > height) {
              xlsRow.setHeight(imageHeight);
            }

            Picture picture = patriarch.createPicture(anchor, index);
            picture.resize();
            anchor.setAnchorType(2);
          }
        } catch (Exception e) {
          e.printStackTrace();
          c.setCellType(XSSFCell.CELL_TYPE_STRING);
          c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED));
        }

      } else {

        if (value == null) {
          c.setCellType(XSSFCell.CELL_TYPE_STRING);
          c.setCellValue(wb.getCreationHelper().createRichTextString(""));
        } else if (value instanceof Number) {
          c.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
          c.setCellValue(((Number) value).doubleValue());
        } else {
          String pattern = null;
          if (bandElement instanceof FieldBandElement) {
            FieldBandElement fbe = (FieldBandElement) bandElement;
            pattern = fbe.getPattern();
          }
          if ((value instanceof java.sql.Date) || (value instanceof java.sql.Timestamp)) {
            Date date;
            if (value instanceof java.sql.Date) {
              date = new Date(((java.sql.Date) value).getTime());
            } else {
              date = (java.sql.Timestamp) value;
            }
            if (cellStyle != null) {
              if (pattern == null) {
                // use default pattern if none selected
                Locale locale = Locale.getDefault();
                pattern =
                    ((SimpleDateFormat) DateFormat.getDateInstance(SimpleDateFormat.MEDIUM, locale))
                        .toPattern();
              } else {
                pattern = StringUtil.getI18nString(pattern, getReportLanguage());
              }
              cellStyle.setDataFormat(wb.createDataFormat().getFormat(pattern));
            }
            c.setCellValue(date);
          } else {
            c.setCellType(XSSFCell.CELL_TYPE_STRING);
            String text = StringUtil.getValueAsString(value, pattern);
            if ((bandElement != null) && bandElement.isWrapText()) {
              // try to interpret new line characters
              // \\n is used here to be possible to add in designer grid cell with \n
              if (text.contains("\\n")
                  || text.contains("\n")
                  || text.contains("\r")
                  || text.contains("\r\n")) {
                String crLf = Character.toString((char) 13) + Character.toString((char) 10);
                int lines = countLines(text);
                if (text.contains("\r\n")) {
                  text = text.replaceAll("\r\n", crLf);
                } else {
                  text = text.replaceAll("(\n)|(\r)|(\\\\n)", crLf);
                }
                c.setCellValue(text);
                cellStyle.setWrapText(true);
                xlsRow.setHeightInPoints(lines * (cellStyle.getFont().getFontHeightInPoints() + 3));
              } else {
                c.setCellValue(wb.getCreationHelper().createRichTextString(text));
              }
            } else {
              c.setCellValue(wb.getCreationHelper().createRichTextString(text));
            }
          }
        }
      }

      if (cellStyle != null) {
        if (bandElement != null) {
          cellStyle.setRotation(bandElement.getTextRotation());
        }
        if (!(bandElement instanceof ReportBandElement)) {
          c.setCellStyle(cellStyle);
        }
      }

      if ((rowSpan > 1) || (colSpan > 1)) {
        CellRangeAddress cra =
            new CellRangeAddress(
                sheetRow, sheetRow + rowSpan - 1, sheetColumn, sheetColumn + colSpan - 1);
        Border beBorder = bandElement.getBorder();
        if (hasRowRenderConditions(bandElement, gridRow, value)) {
          // for row render conditions we must keep the row border
          beBorder = border;
        }
        regions.add(new XlsxRegion(cra, beBorder));
      }
    }
  }