public CellStyle buildDimensionCellStyle(Sheet sheet) {
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);

    String headerBGColor = (String) this.getProperty(PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR);
    logger.debug("Header background color : " + headerBGColor);
    short backgroundColorIndex =
        headerBGColor != null
            ? IndexedColors.valueOf(headerBGColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR).getIndex();
    cellStyle.setFillForegroundColor(backgroundColorIndex);

    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);

    String bordeBorderColor = (String) this.getProperty(PROPERTY_HEADER_BORDER_COLOR);
    logger.debug("Header border color : " + bordeBorderColor);
    short borderColorIndex =
        bordeBorderColor != null
            ? IndexedColors.valueOf(bordeBorderColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_HEADER_BORDER_COLOR).getIndex();

    cellStyle.setLeftBorderColor(borderColorIndex);
    cellStyle.setRightBorderColor(borderColorIndex);
    cellStyle.setBottomBorderColor(borderColorIndex);
    cellStyle.setTopBorderColor(borderColorIndex);

    Font font = sheet.getWorkbook().createFont();

    Short headerFontSize = (Short) this.getProperty(PROPERTY_HEADER_FONT_SIZE);
    logger.debug("Header font size : " + headerFontSize);
    short headerFontSizeShort =
        headerFontSize != null ? headerFontSize.shortValue() : DEFAULT_HEADER_FONT_SIZE;
    font.setFontHeightInPoints(headerFontSizeShort);

    String fontName = (String) this.getProperty(PROPERTY_FONT_NAME);
    logger.debug("Font name : " + fontName);
    fontName = fontName != null ? fontName : DEFAULT_FONT_NAME;
    font.setFontName(fontName);

    String color = (String) this.getProperty(PROPERTY_DIMENSION_NAME_COLOR);
    logger.debug("Dimension color : " + color);
    short colorIndex =
        bordeBorderColor != null
            ? IndexedColors.valueOf(color).getIndex()
            : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_COLOR).getIndex();
    font.setColor(colorIndex);

    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setItalic(true);
    cellStyle.setFont(font);
    return cellStyle;
  }
  public CellStyle buildDataCellStyle(Sheet sheet) {
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);

    String cellBGColor = (String) this.getProperty(PROPERTY_CELL_BACKGROUND_COLOR);
    logger.debug("Cell background color : " + cellBGColor);
    short backgroundColorIndex =
        cellBGColor != null
            ? IndexedColors.valueOf(cellBGColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_CELL_BACKGROUND_COLOR).getIndex();
    cellStyle.setFillForegroundColor(backgroundColorIndex);

    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);

    String bordeBorderColor = (String) this.getProperty(PROPERTY_CELL_BORDER_COLOR);
    logger.debug("Cell border color : " + bordeBorderColor);
    short borderColorIndex =
        bordeBorderColor != null
            ? IndexedColors.valueOf(bordeBorderColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_CELL_BORDER_COLOR).getIndex();

    cellStyle.setLeftBorderColor(borderColorIndex);
    cellStyle.setRightBorderColor(borderColorIndex);
    cellStyle.setBottomBorderColor(borderColorIndex);
    cellStyle.setTopBorderColor(borderColorIndex);

    Font font = sheet.getWorkbook().createFont();

    Short cellFontSize = (Short) this.getProperty(PROPERTY_CELL_FONT_SIZE);
    logger.debug("Cell font size : " + cellFontSize);
    short cellFontSizeShort =
        cellFontSize != null ? cellFontSize.shortValue() : DEFAULT_CELL_FONT_SIZE;
    font.setFontHeightInPoints(cellFontSizeShort);

    String fontName = (String) this.getProperty(PROPERTY_FONT_NAME);
    logger.debug("Font name : " + fontName);
    fontName = fontName != null ? fontName : DEFAULT_FONT_NAME;
    font.setFontName(fontName);

    String cellColor = (String) this.getProperty(PROPERTY_CELL_COLOR);
    logger.debug("Cell color : " + cellColor);
    short cellColorIndex =
        cellColor != null
            ? IndexedColors.valueOf(cellColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_CELL_COLOR).getIndex();
    font.setColor(cellColorIndex);

    cellStyle.setFont(font);
    return cellStyle;
  }
 private Result populateLoansTable(Sheet savingsTransactionSheet) {
   Result result = new Result();
   Workbook workbook = savingsTransactionSheet.getWorkbook();
   CellStyle dateCellStyle = workbook.createCellStyle();
   short df = workbook.createDataFormat().getFormat("dd/mm/yy");
   dateCellStyle.setDataFormat(df);
   int rowIndex = 1;
   Row row;
   Collections.sort(savings, CompactSavingsAccount.ClientNameComparator);
   try {
     for (CompactSavingsAccount savingsAccount : savings) {
       row = savingsTransactionSheet.createRow(rowIndex++);
       writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName());
       writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
       writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
       writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
       writeDate(
           LOOKUP_SAVINGS_ACTIVATION_DATE_COL,
           row,
           savingsAccount.getTimeline().getActivatedOnDate().get(2)
               + "/"
               + savingsAccount.getTimeline().getActivatedOnDate().get(1)
               + "/"
               + savingsAccount.getTimeline().getActivatedOnDate().get(0),
           dateCellStyle);
     }
   } catch (Exception e) {
     result.addError(e.getMessage());
     logger.error(e.getMessage());
   }
   return result;
 }
Esempio n. 4
0
 private Color getColor(Font font) {
   if (helper instanceof HSSFHtmlHelper) {
     return ((HSSFWorkbook) sheet.getWorkbook()).getCustomPalette().getColor(font.getColor());
   } else {
     return ((XSSFFont) font).getXSSFColor();
   }
 }
Esempio n. 5
0
  /**
   * Compute width of a column based on a subset of the rows and return the result
   *
   * @param sheet the sheet to calculate
   * @param column 0-based index of the column
   * @param useMergedCells whether to use merged cells
   * @param firstRow 0-based index of the first row to consider (inclusive)
   * @param lastRow 0-based index of the last row to consider (inclusive)
   * @return the width in pixels
   */
  public static double getColumnWidth(
      Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow) {
    AttributedString str;
    TextLayout layout;

    Workbook wb = sheet.getWorkbook();
    DataFormatter formatter = new DataFormatter();
    Font defaultFont = wb.getFontAt((short) 0);

    str = new AttributedString(String.valueOf(defaultChar));
    copyAttributes(defaultFont, str, 0, 1);
    layout = new TextLayout(str.getIterator(), fontRenderContext);
    int defaultCharWidth = (int) layout.getAdvance();

    double width = -1;
    for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) {
      Row row = sheet.getRow(rowIdx);
      if (row != null) {

        Cell cell = row.getCell(column);

        if (cell == null) {
          continue;
        }

        double cellWidth = getCellWidth(cell, defaultCharWidth, formatter, useMergedCells);
        width = Math.max(width, cellWidth);
      }
    }
    return width;
  }
Esempio n. 6
0
  /**
   * Watch list serve for formula changes. Basically all the rows appeared in the formula in the
   * current sheet will be watched. Note if the cell reference is from other sheet or workbooks, it
   * will be ignored.
   *
   * @param wbWrapper XSSFEvaluationWorkbook used for formula parse.
   * @param sheet current sheet.
   * @return List row number for monitoring.
   */
  private List<Integer> buildFormWatchList(
      final XSSFEvaluationWorkbook wbWrapper, final Sheet sheet) {

    List<Integer> watchList = new ArrayList<Integer>();

    ConfigRange cRange = this.getConfigRange();
    List<ConfigCommand> commandList = cRange.getCommandList();
    if (commandList.size() <= 0) {
      // if no command then no dynamic changes. then no need formula shifts.
      return watchList;
    }
    int lastStaticRow = commandList.get(0).getTopRow() - 1;
    if (lastStaticRow < 0) {
      lastStaticRow = this.getTopRow();
    }

    Workbook wb = sheet.getWorkbook();

    for (int i = this.getTopRow(); i <= this.getLastRow(); i++) {
      Row row = sheet.getRow(i);
      for (Cell cell : row) {
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {

          String formula = cell.getCellFormula();

          Ptg[] ptgs =
              FormulaParser.parse(formula, wbWrapper, FormulaType.CELL, wb.getSheetIndex(sheet));

          for (int k = 0; k < ptgs.length; k++) {
            Object ptg = ptgs[k];
            // For area formula, only first row is watched.
            // Reason is the lastRow must shift same rows with
            // firstRow.
            // Otherwise it's difficult to calculate.
            // In case some situation cannot fit, then should make
            // change to the formula.
            int areaInt = ShiftFormula.getFirstSupportedRowNumFromPtg(ptg);
            if (areaInt >= 0) {
              addToWatchList(sheet, areaInt, lastStaticRow, watchList);
            }
          }

          // when insert row, the formula may changed. so here is the workaround.
          // change formula to user formula to preserve the row changes.
          cell.setCellType(Cell.CELL_TYPE_STRING);
          cell.setCellValue(
              ConfigurationHelper.USER_FORMULA_PREFIX
                  + formula
                  + ConfigurationHelper.USER_FORMULA_SUFFIX);
        }
      }
    }

    return watchList;
  }
 public ExcelAttributeSheetReader(
     final Sheet sheet,
     final AttributeMappingParameters mapping,
     final CyServiceRegistrar serviceRegistrar) {
   this.sheet = sheet;
   this.mapping = mapping;
   this.startLineNumber = mapping.getStartLineNumber();
   this.parser = new AttributeLineParser(mapping, serviceRegistrar);
   this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
   this.formatter = new DataFormatter();
 }
  @Override
  public void renderCell(Element element, int rowIndex, int columnIndex) {
    Cell cell = sheet.getRow(rowIndex).createCell(columnIndex);

    Double numericValue;

    if (isDateCell(element)) {
      DateFormat df = new SimpleDateFormat(getDateCellFormat(element));

      try {
        cell.setCellValue(df.parse(getElementText(element)));
      } catch (ParseException pex) {
        System.out.println("Invalid Usage");
      }
    } else if ((numericValue = getNumericValue(element)) != null) {
      cell.setCellValue(numericValue);
    } else {
      cell = sheet.getRow(rowIndex).createCell(columnIndex, Cell.CELL_TYPE_STRING);
      cell.setCellValue(getElementText(element));
    }

    Style style = styleMapper.getStyleForElement(element);
    cell.setCellStyle(styleGenerator.getStyle(cell, style));

    if (isDateCell(element)) {
      CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
      cell.getCellStyle()
          .setDataFormat(createHelper.createDataFormat().getFormat(getDateCellFormat(element)));
    }

    String commentText;

    if ((commentText = getCellCommentText(element)) != null) {
      ExcelCellCommentGenerator.addCellComment(cell, commentText, getCellCommentDimension(element));
    }

    if (definesFreezePane(element)) {
      sheet.createFreezePane(columnIndex, rowIndex);
    }
  }
  private void setNames(Sheet worksheet) {
    Workbook savingsTransactionWorkbook = worksheet.getWorkbook();
    ArrayList<String> officeNames =
        new ArrayList<String>(Arrays.asList(officeSheetPopulator.getOfficeNames()));

    // Office Names
    Name officeGroup = savingsTransactionWorkbook.createName();
    officeGroup.setNameName("Office");
    officeGroup.setRefersToFormula("Offices!$B$2:$B$" + (officeNames.size() + 1));

    // Clients Named after Offices
    for (Integer i = 0; i < officeNames.size(); i++) {
      Integer[] officeNameToBeginEndIndexesOfClients =
          clientSheetPopulator.getOfficeNameToBeginEndIndexesOfClients().get(i);
      Name name = savingsTransactionWorkbook.createName();
      if (officeNameToBeginEndIndexesOfClients != null) {
        name.setNameName("Client_" + officeNames.get(i));
        name.setRefersToFormula(
            "Clients!$B$"
                + officeNameToBeginEndIndexesOfClients[0]
                + ":$B$"
                + officeNameToBeginEndIndexesOfClients[1]);
      }
    }

    // Counting clients with active savings and starting and end addresses of cells for naming
    HashMap<String, Integer[]> clientNameToBeginEndIndexes = new HashMap<String, Integer[]>();
    ArrayList<String> clientsWithActiveSavings = new ArrayList<String>();
    int startIndex = 1, endIndex = 1;
    String clientName = "";

    for (int i = 0; i < savings.size(); i++) {
      if (!clientName.equals(savings.get(i).getClientName())) {
        endIndex = i + 1;
        clientNameToBeginEndIndexes.put(clientName, new Integer[] {startIndex, endIndex});
        startIndex = i + 2;
        clientName = savings.get(i).getClientName();
        clientsWithActiveSavings.add(clientName);
      }
      if (i == savings.size() - 1) {
        endIndex = i + 2;
        clientNameToBeginEndIndexes.put(clientName, new Integer[] {startIndex, endIndex});
      }
    }

    // Account Number Named  after Clients
    for (int j = 0; j < clientsWithActiveSavings.size(); j++) {
      Name name = savingsTransactionWorkbook.createName();
      name.setNameName("Account_" + clientsWithActiveSavings.get(j).replaceAll(" ", "_"));
      name.setRefersToFormula(
          "SavingsTransaction!$Q$"
              + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[0]
              + ":$Q$"
              + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[1]);
    }

    // Payment Type Name
    Name paymentTypeGroup = savingsTransactionWorkbook.createName();
    paymentTypeGroup.setNameName("PaymentTypes");
    paymentTypeGroup.setRefersToFormula(
        "Extras!$D$2:$D$" + (extrasSheetPopulator.getPaymentTypesSize() + 1));
  }
Esempio n. 10
0
  /**
   * Compute width of a single cell
   *
   * @param cell the cell whose width is to be calculated
   * @param defaultCharWidth the width of a single character
   * @param formatter formatter used to prepare the text to be measured
   * @param useMergedCells whether to use merged cells
   * @return the width in pixels
   */
  public static double getCellWidth(
      Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) {

    Sheet sheet = cell.getSheet();
    Workbook wb = sheet.getWorkbook();
    Row row = cell.getRow();
    int column = cell.getColumnIndex();

    int colspan = 1;
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
      CellRangeAddress region = sheet.getMergedRegion(i);
      if (containsCell(region, row.getRowNum(), column)) {
        if (!useMergedCells) {
          // If we're not using merged cells, skip this one and move on to the next.
          return -1;
        }
        cell = row.getCell(region.getFirstColumn());
        colspan = 1 + region.getLastColumn() - region.getFirstColumn();
      }
    }

    CellStyle style = cell.getCellStyle();
    int cellType = cell.getCellType();

    // for formula cells we compute the cell width for the cached formula result
    if (cellType == Cell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType();

    Font font = wb.getFontAt(style.getFontIndex());

    AttributedString str;
    TextLayout layout;

    double width = -1;
    if (cellType == Cell.CELL_TYPE_STRING) {
      RichTextString rt = cell.getRichStringCellValue();
      String[] lines = rt.getString().split("\\n");
      for (int i = 0; i < lines.length; i++) {
        String txt = lines[i] + defaultChar;

        str = new AttributedString(txt);
        copyAttributes(font, str, 0, txt.length());

        if (rt.numFormattingRuns() > 0) {
          // TODO: support rich text fragments
        }

        layout = new TextLayout(str.getIterator(), fontRenderContext);
        if (style.getRotation() != 0) {
          /*
           * Transform the text using a scale so that it's height is increased by a multiple of the leading,
           * and then rotate the text before computing the bounds. The scale results in some whitespace around
           * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
           * is added by the standard Excel autosize.
           */
          AffineTransform trans = new AffineTransform();
          trans.concatenate(
              AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
          trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
          width =
              Math.max(
                  width,
                  ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        } else {
          width =
              Math.max(
                  width,
                  ((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        }
      }
    } else {
      String sval = null;
      if (cellType == Cell.CELL_TYPE_NUMERIC) {
        // Try to get it formatted to look the same as excel
        try {
          sval = formatter.formatCellValue(cell, dummyEvaluator);
        } catch (Exception e) {
          sval = String.valueOf(cell.getNumericCellValue());
        }
      } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
        sval = String.valueOf(cell.getBooleanCellValue()).toUpperCase();
      }
      if (sval != null) {
        String txt = sval + defaultChar;
        str = new AttributedString(txt);
        copyAttributes(font, str, 0, txt.length());

        layout = new TextLayout(str.getIterator(), fontRenderContext);
        if (style.getRotation() != 0) {
          /*
           * Transform the text using a scale so that it's height is increased by a multiple of the leading,
           * and then rotate the text before computing the bounds. The scale results in some whitespace around
           * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
           * is added by the standard Excel autosize.
           */
          AffineTransform trans = new AffineTransform();
          trans.concatenate(
              AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
          trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
          width =
              Math.max(
                  width,
                  ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        } else {
          width =
              Math.max(
                  width,
                  ((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        }
      }
    }
    return width;
  }