Java Code Examples for org.apache.poi.ss.util.CellReference#getRow()

/**
 * Evaluate a formula outside a cell value, e.g. conditional format rules or data validation expressions
 * 
 * @param formula to evaluate
 * @param ref defines the optional sheet and row/column base for the formula, if it is relative
 * @return value
 */
public ValueEval evaluate(String formula, CellReference ref) {
    final String sheetName = ref == null ? null : ref.getSheetName();
    int sheetIndex;
    if (sheetName == null) {
        sheetIndex = -1; // workbook scope only
    } else {
        sheetIndex = getWorkbook().getSheetIndex(sheetName);
    }
    int rowIndex = ref == null ? -1 : ref.getRow();
    short colIndex = ref == null ? -1 : ref.getCol();
    final OperationEvaluationContext ec = new OperationEvaluationContext(
            this, 
            getWorkbook(), 
            sheetIndex, 
            rowIndex, 
            colIndex, 
            new EvaluationTracker(_cache)
        );
    Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook) getWorkbook(), FormulaType.CELL, sheetIndex, rowIndex);
    return evaluateNameFormula(ptgs, ec);
}
 

private ValueEval evaluate(String formula, CellReference target, CellRangeAddressBase region, FormulaType formulaType) {
    final String sheetName = target == null ? null : target.getSheetName();
    if (sheetName == null) throw new IllegalArgumentException("Sheet name is required");
    
    final int sheetIndex = getWorkbook().getSheetIndex(sheetName);
    Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook) getWorkbook(), formulaType, sheetIndex, target.getRow());

    adjustRegionRelativeReference(ptgs, target, region);
    
    final OperationEvaluationContext ec = new OperationEvaluationContext(this, getWorkbook(), sheetIndex, target.getRow(), target.getCol(), new EvaluationTracker(_cache), formulaType.isSingleValue());
    return evaluateNameFormula(ptgs, ec);
}
 

/**
 * 文字列の形式のセルのアドレスを、Point形式に変換する。
 * @param address
 * @return
 */
public static Point toPointAddress(final String address) {
    ArgUtils.notEmpty(address, "address");

    CellReference ref = new CellReference(address);
    return new Point(ref.getCol(), ref.getRow());
}
 

/**
 * Setup the data necessary for Excel Writer transform
 *
 * @param fileType
 * @throws HopException
 */
private void createTransformData( String fileType ) throws HopException {
  transformData = new ExcelWriterData();
  transformData.inputRowMeta = transform.getInputRowMeta().clone();
  transformData.outputRowMeta = transform.getInputRowMeta().clone();

  // we don't run pipeline so ExcelWriter.processRow() doesn't get executed
  // we populate the ExcelWriterData with bare minimum required values
  //
  CellReference cellRef = new CellReference( transformMeta.getStartingCell() );
  transformData.startingRow = cellRef.getRow();
  transformData.startingCol = cellRef.getCol();
  transformData.posX = transformData.startingCol;
  transformData.posY = transformData.startingRow;

  int numOfFields = transformData.inputRowMeta.size();
  transformData.fieldnrs = new int[ numOfFields ];
  transformData.linkfieldnrs = new int[ numOfFields ];
  transformData.commentfieldnrs = new int[ numOfFields ];
  for ( int i = 0; i < numOfFields; i++ ) {
    transformData.fieldnrs[ i ] = i;
    transformData.linkfieldnrs[ i ] = -1;
    transformData.commentfieldnrs[ i ] = -1;
  }

  // we avoid reading/writing Excel files, so ExcelWriter.prepareNextOutputFile() doesn't get executed
  // create Excel workbook object
  //
  transformData.wb = transformMeta.getExtension().equalsIgnoreCase( "xlsx" ) ? new XSSFWorkbook() : new HSSFWorkbook();
  transformData.sheet = transformData.wb.createSheet();
  transformData.file = null;
  transformData.clearStyleCache( numOfFields );

  // we avoid reading template file from disk
  // so set beforehand cells with custom style and formatting
  DataFormat format = transformData.wb.createDataFormat();
  Row xlsRow = transformData.sheet.createRow( 0 );

  // Cell F1 has custom style applied, used as template
  Cell cell = xlsRow.createCell( 5 );
  CellStyle cellStyle = transformData.wb.createCellStyle();
  cellStyle.setBorderRight( BorderStyle.THICK );
  cellStyle.setFillPattern( FillPatternType.FINE_DOTS );
  cell.setCellStyle( cellStyle );

  // Cell G1 has same style, but also a custom data format
  cellStyle = transformData.wb.createCellStyle();
  cellStyle.cloneStyleFrom( cell.getCellStyle() );
  cell = xlsRow.createCell( 6 );
  cellStyle.setDataFormat( format.getFormat( "##0,000.0" ) );
  cell.setCellStyle( cellStyle );
}
 

/**
 * Setup the data necessary for Excel Writer transform
 *
 * @param fileType
 * @throws HopException
 */
private void createTransformData( String fileType ) throws HopException {
  data = new ExcelWriterTransformData();
  data.inputRowMeta = inputRowMeta.clone();
  data.outputRowMeta = inputRowMeta.clone();

  // we don't run pipeline so ExcelWriterTransform.processRow() doesn't get executed
  // we populate the ExcelWriterTransformData with bare minimum required values
  CellReference cellRef = new CellReference( meta.getStartingCell() );
  data.startingRow = cellRef.getRow();
  data.startingCol = cellRef.getCol();
  data.posX = data.startingCol;
  data.posY = data.startingRow;

  int numOfFields = data.inputRowMeta.size();
  data.fieldnrs = new int[ numOfFields ];
  data.linkfieldnrs = new int[ numOfFields ];
  data.commentfieldnrs = new int[ numOfFields ];
  for ( int i = 0; i < numOfFields; i++ ) {
    data.fieldnrs[ i ] = i;
    data.linkfieldnrs[ i ] = -1;
    data.commentfieldnrs[ i ] = -1;
  }

  // we avoid reading/writing Excel files, so ExcelWriterTransform.prepareNextOutputFile() doesn't get executed
  // create Excel workbook object
  data.wb = meta.getExtension().equalsIgnoreCase( "xlsx" ) ? new XSSFWorkbook() : new HSSFWorkbook();
  data.sheet = data.wb.createSheet();
  data.file = null;
  data.clearStyleCache( numOfFields );

  // we avoid reading template file from disk
  // so set beforehand cells with custom style and formatting
  DataFormat format = data.wb.createDataFormat();
  Row xlsRow = data.sheet.createRow( 0 );

  // Cell F1 has custom style applied, used as template
  Cell cell = xlsRow.createCell( 5 );
  CellStyle cellStyle = data.wb.createCellStyle();
  cellStyle.setBorderRight( BorderStyle.THICK );
  cellStyle.setFillPattern( FillPatternType.FINE_DOTS );
  cell.setCellStyle( cellStyle );

  // Cell G1 has same style, but also a custom data format
  cellStyle = data.wb.createCellStyle();
  cellStyle.cloneStyleFrom( cell.getCellStyle() );
  cell = xlsRow.createCell( 6 );
  cellStyle.setDataFormat( format.getFormat( "##0,000.0" ) );
  cell.setCellStyle( cellStyle );
}
 

private Integer getKeyForCache(final CellReference cellRef) {
    // The HSSF has a max of 2^16 rows and 2^8 cols
    return ((cellRef.getCol()+1)<<16 | cellRef.getRow());
}
 

/**
 * Gets the {@link SharedValueRecordBase} record if it should be encoded immediately after the
 * formula record contained in the specified {@link FormulaRecordAggregate} agg.  Note - the
 * shared value record always appears after the first formula record in the group.  For arrays
 * and tables the first formula is always the in the top left cell.  However, since shared
 * formula groups can be sparse and/or overlap, the first formula may not actually be in the
 * top left cell.
 *
 * @return the SHRFMLA, TABLE or ARRAY record for the formula cell, if it is the first cell of
 * a table or array region. <code>null</code> if the formula cell is not shared/array/table,
 * or if the specified formula is not the the first in the group.
 */
public SharedValueRecordBase getRecordForFirstCell(FormulaRecordAggregate agg) {
	CellReference firstCell = agg.getFormulaRecord().getFormula().getExpReference();
	// perhaps this could be optimised by consulting the (somewhat unreliable) isShared flag
	// and/or distinguishing between tExp and tTbl.
	if (firstCell == null) {
		// not a shared/array/table formula
		return null;
	}


	int row = firstCell.getRow();
	int column = firstCell.getCol();
	if (agg.getRow() != row || agg.getColumn() != column) {
		// not the first formula cell in the group
		return null;
	}

       if(!_groupsBySharedFormulaRecord.isEmpty()) {
           SharedFormulaGroup sfg = findFormulaGroupForCell(firstCell);
           if(null != sfg) {
               return sfg.getSFR();
           }
       }

	// Since arrays and tables cannot be sparse (all cells in range participate)
	// The first cell will be the top left in the range.  So we can match the
	// ARRAY/TABLE record directly.

	for (TableRecord tr : _tableRecords) {
		if (tr.isFirstCell(row, column)) {
			return tr;
		}
	}
	for (ArrayRecord ar : _arrayRecords) {
		if (ar.isFirstCell(row, column)) {
			return ar;
		}
	}
	return null;
}
 

public Cell(CellReference originalCell) {
    this(originalCell.getCol(), originalCell.getRow());
}
 

/**
 * Setup the data necessary for Excel Writer step
 *
 * @param fileType
 * @throws KettleException
 */
private void createStepData( String fileType ) throws KettleException {
  stepData = new ExcelWriterStepData();
  stepData.inputRowMeta = step.getInputRowMeta().clone();
  stepData.outputRowMeta = step.getInputRowMeta().clone();

  // we don't run transformation so ExcelWriterStep.processRow() doesn't get executed
  // we populate the ExcelWriterStepData with bare minimum required values
  CellReference cellRef = new CellReference( stepMeta.getStartingCell() );
  stepData.startingRow = cellRef.getRow();
  stepData.startingCol = cellRef.getCol();
  stepData.posX = stepData.startingCol;
  stepData.posY = stepData.startingRow;

  int numOfFields = stepData.inputRowMeta.size();
  stepData.fieldnrs = new int[numOfFields];
  stepData.linkfieldnrs = new int[numOfFields];
  stepData.commentfieldnrs = new int[numOfFields];
  for ( int i = 0; i < numOfFields; i++ ) {
    stepData.fieldnrs[i] = i;
    stepData.linkfieldnrs[i] = -1;
    stepData.commentfieldnrs[i] = -1;
  }

  // we avoid reading/writing Excel files, so ExcelWriterStep.prepareNextOutputFile() doesn't get executed
  // create Excel workbook object
  stepData.wb = stepMeta.getExtension().equalsIgnoreCase( "xlsx" ) ? new XSSFWorkbook() : new HSSFWorkbook();
  stepData.sheet = stepData.wb.createSheet();
  stepData.file = null;
  stepData.clearStyleCache( numOfFields );

  // we avoid reading template file from disk
  // so set beforehand cells with custom style and formatting
  DataFormat format = stepData.wb.createDataFormat();
  Row xlsRow = stepData.sheet.createRow( 0 );

  // Cell F1 has custom style applied, used as template
  Cell cell = xlsRow.createCell( 5 );
  CellStyle cellStyle = stepData.wb.createCellStyle();
  cellStyle.setBorderRight( BorderStyle.THICK );
  cellStyle.setFillPattern( FillPatternType.FINE_DOTS );
  cell.setCellStyle( cellStyle );

  // Cell G1 has same style, but also a custom data format
  cellStyle = stepData.wb.createCellStyle();
  cellStyle.cloneStyleFrom( cell.getCellStyle() );
  cell = xlsRow.createCell( 6 );
  cellStyle.setDataFormat( format.getFormat( "##0,000.0" ) );
  cell.setCellStyle( cellStyle );
}