org.apache.poi.ss.util.CellReference Java Examples

The following examples show how to use org.apache.poi.ss.util.CellReference. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: FormulaParser.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
/**
 *
 * @param sheetIden may be <code>null</code>
 * @param part1
 * @param part2 may be <code>null</code>
 */
private ParseNode createAreaRefParseNode(SheetIdentifier sheetIden, SimpleRangePart part1,
        SimpleRangePart part2) throws FormulaParseException {
    Ptg ptg;
    if (part2 == null) {
        CellReference cr = part1.getCellReference();
        if (sheetIden == null) {
            ptg = new RefPtg(cr);
        } else {
            ptg = _book.get3DReferencePtg(cr, sheetIden);
        }
    } else {
        AreaReference areaRef = createAreaRef(part1, part2);

        if (sheetIden == null) {
            ptg = new AreaPtg(areaRef);
        } else {
            ptg = _book.get3DReferencePtg(areaRef, sheetIden);
        }
    }
    return new ParseNode(ptg);
}
 
Example #2
Source File: EvaluationConditionalFormatRule.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
/**
 * @param cell needed for offsets from region anchor - may be null!
 * @param region for adjusting relative formulas
 * @return true/false using the same rules as Data Validation evaluations
 */
private boolean checkFormula(CellReference ref, CellRangeAddress region) {
    ValueEval comp = unwrapEval(workbookEvaluator.evaluate(rule.getFormula1(), ref, region));
    
    // Copied for now from DataValidationEvaluator.ValidationEnum.FORMULA#isValidValue()
    if (comp instanceof BlankEval) {
        return true;
    }
    if (comp instanceof ErrorEval) {
        return false;
    }
    if (comp instanceof BoolEval) {
        return ((BoolEval) comp).getBooleanValue();
    }
    // empirically tested in Excel - 0=false, any other number = true/valid
    // see test file DataValidationEvaluations.xlsx
    if (comp instanceof NumberEval) {
        return ((NumberEval) comp).getNumberValue() != 0;
    }
    return false; // anything else is false, such as text
}
 
Example #3
Source File: AbstractHandler.java    From birt with Eclipse Public License 1.0 6 votes vote down vote up
protected void createName(HandlerState state, String bookmark, int row1, int col1, int row2, int col2 ) {
	CellReference crFirst = new CellReference( state.currentSheet.getSheetName(), row1, col1, true, true );
	CellReference crLast = new CellReference( row2, col2, true, true );
	String formula = crFirst.formatAsString() + ":" + crLast.formatAsString();

	Name name = state.currentSheet.getWorkbook().getName(bookmark);
	if( name == null ) {
		name = state.currentSheet.getWorkbook().createName();
		name.setNameName( bookmark ); 
		name.setRefersToFormula( formula );
	} else {
		String existingFormula = name.getRefersToFormula();
		try {
			name.setRefersToFormula(existingFormula + "," + formula);
		} catch( FormulaParseException ex ) {
			log.warn( 0, "Unable to add \"" + formula + "\" to name (\"" + bookmark + "\") with existing formula: " + existingFormula, ex );
		}
	}
}
 
Example #4
Source File: DataValidationEvaluator.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
/**
 * Use the validation returned by {@link #getValidationForCell(CellReference)} if you
 * want the error display details. This is the validation checked by this
 * method, which attempts to replicate Excel's data validation rules.
 * <p>
 * Note that to properly apply some validations, care must be taken to
 * offset the base validation formula by the relative position of the
 * current cell, or the wrong value is checked.
 * 
 * @param cellRef The reference of the cell to evaluate
 * @return true if the cell has no validation or the cell value passes the
 *         defined validation, false if it fails
 */
public boolean isValidCell(CellReference cellRef) {
    final DataValidationContext context = getValidationContextForCell(cellRef);

    if (context == null) return true;
    
    final Cell cell = SheetUtil.getCell(workbook.getSheet(cellRef.getSheetName()), cellRef.getRow(), cellRef.getCol());
    
    // now we can validate the cell
    
    // if empty, return not allowed flag
    if (   cell == null
        || isType(cell, CellType.BLANK)  
        || (isType(cell,CellType.STRING) 
            && (cell.getStringCellValue() == null || cell.getStringCellValue().isEmpty())
           )
       ) {
        return context.getValidation().getEmptyCellAllowed();
    }
    
    // cell has a value
    
    return ValidationEnum.isValid(cell, context);
}
 
Example #5
Source File: ForkedEvaluationSheet.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
public ForkedEvaluationCell getOrCreateUpdatableCell(int rowIndex, int columnIndex) {
    RowColKey key = new RowColKey(rowIndex, columnIndex);

    ForkedEvaluationCell result = _sharedCellsByRowCol.get(key);
    if (result == null) {
        EvaluationCell mcell = _masterSheet.getCell(rowIndex, columnIndex);
        if (mcell == null) {
            CellReference cr = new CellReference(rowIndex, columnIndex);
            throw new UnsupportedOperationException("Underlying cell '"
                    + cr.formatAsString() + "' is missing in master sheet.");
        }
        result = new ForkedEvaluationCell(this, mcell);
        _sharedCellsByRowCol.put(key, result);
    }
    return result;
}
 
Example #6
Source File: DataValidationEvaluator.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
/**
 * Finds and returns the {@link DataValidationContext} for the cell, if there is
 * one. Lookup is based on the first match from
 * {@link DataValidation#getRegions()} for the cell's sheet. DataValidation
 * regions must be in the same sheet as the DataValidation. Allowed values
 * expressions may reference other sheets, however.
 * 
 * @param cell reference to check
 * @return the DataValidationContext applicable to the given cell, or null if no
 *         validation applies
 */
public DataValidationContext getValidationContextForCell(CellReference cell) {
    final Sheet sheet = workbook.getSheet(cell.getSheetName());
    if (sheet == null) return null;
    final List<? extends DataValidation> dataValidations = getValidations(sheet);
    if (dataValidations == null) return null;
    for (DataValidation dv : dataValidations) {
        final CellRangeAddressList regions = dv.getRegions();
        if (regions == null) return null;
        // current implementation can't return null
        for (CellRangeAddressBase range : regions.getCellRangeAddresses()) {
            if (range.isInRange(cell)) {
                return new DataValidationContext(dv, this, range, cell);
            }
        }
    }
    return null;
}
 
Example #7
Source File: WorkbookEvaluator.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
/**
 * 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);
}
 
Example #8
Source File: HSSFSheet.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
@Override
public CellRange<HSSFCell> removeArrayFormula(Cell cell) {
    if (cell.getSheet() != this) {
        throw new IllegalArgumentException("Specified cell does not belong to this sheet.");
    }
    CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord();
    if (!(rec instanceof FormulaRecordAggregate)) {
        String ref = new CellReference(cell).formatAsString();
        throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
    }
    FormulaRecordAggregate fra = (FormulaRecordAggregate) rec;
    CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex());

    CellRange<HSSFCell> result = getCellRange(range);
    // clear all cells in the range
    for (Cell c : result) {
        c.setCellType(CellType.BLANK);
    }
    return result;
}
 
Example #9
Source File: XLSFormatter.java    From yarg with Apache License 2.0 6 votes vote down vote up
protected void updateFormulas(Area templateArea, Area dependentResultArea) {
    HSSFSheet templateSheet = getTemplateSheetForRangeName(templateWorkbook, templateArea.getName());
    HSSFSheet resultSheet = templateToResultSheetsMapping.get(templateSheet);

    AreaReference area = dependentResultArea.toAreaReference();
    for (CellReference cell : area.getAllReferencedCells()) {
        HSSFCell resultCell = getCellFromReference(cell, resultSheet);

        if (resultCell.getCellType() == CellType.FORMULA) {
            Ptg[] ptgs = HSSFFormulaParser.parse(resultCell.getCellFormula(), resultWorkbook);

            for (Ptg ptg : ptgs) {
                if (ptg instanceof AreaPtg) {
                    areaDependencyManager.updateAreaPtg(templateArea, dependentResultArea, (AreaPtg) ptg);
                } else if (ptg instanceof RefPtg) {
                    areaDependencyManager.updateRefPtg(templateArea, dependentResultArea, (RefPtg) ptg);
                }
            }

            String calculatedFormula = HSSFFormulaParser.toFormulaString(templateWorkbook, ptgs);
            resultCell.setCellFormula(calculatedFormula);
        }
    }
}
 
Example #10
Source File: SXSSFUtil.java    From javautils with Apache License 2.0 6 votes vote down vote up
private static void common(Workbook wb, String filename) throws Throwable{
    Sheet sh = wb.createSheet();
    for(int rownum = 0; rownum < 1000; rownum++){
        Row row = sh.createRow(rownum);
        for(int cellnum = 0; cellnum < 10; cellnum++){
            Cell cell = row.createCell(cellnum);
            String address = new CellReference(cell).formatAsString();
            cell.setCellValue(address);
        }

    }
    // Rows with rownum < 900 are flushed and not accessible
    for(int rownum = 0; rownum < 900; rownum++){
        //Assert.assertNull(sh.getRow(rownum));
    }

    // ther last 100 rows are still in memory
    for(int rownum = 900; rownum < 1000; rownum++){
        //Assert.assertNotNull(sh.getRow(rownum));
    }

    FileOutputStream out = new FileOutputStream(filename);
    wb.write(out);
    out.close();
}
 
Example #11
Source File: ExcelWriterStep.java    From pentaho-kettle with Apache License 2.0 6 votes vote down vote up
/**
 * @param reference
 * @return the cell the reference points to
 */
private Cell getCellFromReference( String reference ) {

  CellReference cellRef = new CellReference( reference );
  String sheetName = cellRef.getSheetName();

  Sheet sheet = meta.isStreamingData() ? data.xssfWorkbook.getSheet( data.realSheetname ) : data.sheet;
  if ( !Utils.isEmpty( sheetName ) ) {
    sheet = meta.isStreamingData() ? data.xssfWorkbook.getSheet( sheetName ) : data.wb.getSheet( sheetName );
  }
  if ( sheet == null ) {
    return null;
  }
  // reference is assumed to be absolute
  Row xlsRow = sheet.getRow( cellRef.getRow() );
  if ( xlsRow == null ) {
    return null;
  }
  Cell styleCell = xlsRow.getCell( cellRef.getCol() );
  return styleCell;
}
 
Example #12
Source File: FormulaParser.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
/**
 * @return <code>true</code> if the specified name is a valid cell reference
 */
private boolean isValidCellReference(String str) {
    //check range bounds against grid max
    boolean result = CellReference.classifyCellReference(str, _ssVersion) == NameType.CELL;

    if(result){
        /*
         * Check if the argument is a function. Certain names can be either a cell reference or a function name
         * depending on the contenxt. Compare the following examples in Excel 2007:
         * (a) LOG10(100) + 1
         * (b) LOG10 + 1
         * In (a) LOG10 is a name of a built-in function. In (b) LOG10 is a cell reference
         */
        boolean isFunc = FunctionMetadataRegistry.getFunctionByName(str.toUpperCase(Locale.ROOT)) != null;
        if(isFunc){
            int savePointer = _pointer;
            resetPointer(_pointer + str.length());
            SkipWhite();
            // open bracket indicates that the argument is a function,
            // the returning value should be false, i.e. "not a valid cell reference"
            result = look != '(';
            resetPointer(savePointer);
        }
    }
    return result;
}
 
Example #13
Source File: SharedValueManager.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
/**
 * Removes the {@link ArrayRecord} for the cell group containing the specified cell.
 * The caller should clear (set blank) all cells in the returned range.
 * @return the range of the array formula which was just removed. Never <code>null</code>.
 */
public CellRangeAddress8Bit removeArrayFormula(int rowIndex, int columnIndex) {
	for (ArrayRecord ar : _arrayRecords) {
		if (ar.isInRange(rowIndex, columnIndex)) {
			_arrayRecords.remove(ar);
			return ar.getRange();
		}
	}
	String ref = new CellReference(rowIndex, columnIndex, false, false).formatAsString();
	throw new IllegalArgumentException("Specified cell " + ref
			+ " is not part of an array formula.");
}
 
Example #14
Source File: FormulaUsedBlankCellSet.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
@Override
      public String toString() {
	StringBuffer sb = new StringBuffer(64);
	CellReference crA = new CellReference(_firstRowIndex, _firstColumnIndex, false, false);
	CellReference crB = new CellReference(_lastRowIndex, _lastColumnIndex, false, false);
	sb.append(getClass().getName());
	sb.append(" [").append(crA.formatAsString()).append(':').append(crB.formatAsString()).append("]");
	return sb.toString();
}
 
Example #15
Source File: LazyRefEval.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
public String toString() {
	CellReference cr = new CellReference(getRow(), getColumn());
	return getClass().getName() + "[" +
			_evaluator.getSheetNameRange() +
			'!' +
			cr.formatAsString() +
			"]";
}
 
Example #16
Source File: AbstractHandler.java    From birt with Eclipse Public License 1.0 5 votes vote down vote up
protected static String prepareName( String name ) {
	char c = name.charAt(0);
	boolean requirePreparation = (!(c == '_' || Character.isLetter(c)) || name.indexOf(' ') != -1);
	if( !requirePreparation ) {
		for( int i = 1; i < name.length(); ++i ) {
			c = name.charAt(i);
			if(!(Character.isLetter(c) || Character.isDigit(c) || c == '_' )) {
				requirePreparation = true;
				break;
			}
		}
	}
	
	if( requirePreparation ) {
		name = name.trim();
		char chars[] = name.toCharArray();
		for( int i = 0; i < name.length(); ++i ) {
			c = chars[i];
			if(!(Character.isLetter(c) || Character.isDigit(c) || c == '_' )) {
				chars[i] = '_';
			}
		}
		name = new String(chars);
	}
	
	NameType refType = CellReference.classifyCellReference( name, SpreadsheetVersion.EXCEL2007 );
	if( ( NameType.CELL == refType ) || ( NameType.COLUMN == refType ) || ( NameType.ROW == refType ) ) {
		name = "_" + name;
	}
	
	return name;
}
 
Example #17
Source File: SharedValueManager.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
public SharedFormulaGroup(SharedFormulaRecord sfr, CellReference firstCell) {
	if (!sfr.isInRange(firstCell.getRow(), firstCell.getCol())) {
		throw new IllegalArgumentException("First formula cell " + firstCell.formatAsString()
				+ " is not shared formula range " + sfr.getRange() + ".");
	}
	_sfr = sfr;
	_firstCell = firstCell;
	int width = sfr.getLastColumn() - sfr.getFirstColumn() + 1;
	int height = sfr.getLastRow() - sfr.getFirstRow() + 1;
	_frAggs = new FormulaRecordAggregate[width * height];
	_numberOfFormulas = 0;
}
 
Example #18
Source File: FormulaRecordAggregate.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
public CellRangeAddress getArrayFormulaRange() {
	if (_sharedFormulaRecord != null) {
		throw new IllegalStateException("not an array formula cell.");
	}
	CellReference expRef = _formulaRecord.getFormula().getExpReference();
	if (expRef == null) {
		throw new IllegalStateException("not an array formula cell.");
	}
	ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
	if (arec == null) {
		throw new IllegalStateException("ArrayRecord was not found for the locator " + expRef.formatAsString());
	}
	CellRangeAddress8Bit a = arec.getRange();
	return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(),a.getLastColumn());
}
 
Example #19
Source File: ExcelSource.java    From morpheus-core with Apache License 2.0 5 votes vote down vote up
/**
 * POI calls this with the value parsed from the cell.
 * @param cellReference The cell reference
 * @param formattedValue The value of the cell
 * @param comment a comment
 */
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
    this.output.valueParsed(formattedValue);
    int thisCol = (new CellReference(cellReference)).getCol();

    //Fill missing columns
    int missedCols = thisCol - currentCol - 1;
    for (int i=0; i<missedCols; i++) {
        this.output.valueParsed("");
    }
    currentCol = thisCol;
}
 
Example #20
Source File: XLSX2CSV.java    From azeroth with Apache License 2.0 5 votes vote down vote up
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
    if (firstCellOfRow) {
        firstCellOfRow = false;
    } else {
        _resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
    }

    // gracefully handle missing CellRef here in a similar way as
    // XSSFCell does
    if (cellReference == null) {
        cellReference = new CellAddress(currentRow, currentCol).formatAsString();
    }

    // Did we miss any cells?
    int thisCol = (new CellReference(cellReference)).getCol();
    int missedCols = thisCol - currentCol - 1;
    for (int i = 0; i < missedCols; i++) {
        _resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
    }
    currentCol = thisCol;

    // Number or string?
    try {
        Double.parseDouble(formattedValue);
        _resultRowTmp.append(formattedValue);
    } catch (NumberFormatException e) {
        _resultRowTmp.append(formattedValue);
    }
}
 
Example #21
Source File: FormulaRecordAggregate.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
public boolean isPartOfArrayFormula() {
	if (_sharedFormulaRecord != null) {
		return false;
	}
       CellReference expRef = _formulaRecord.getFormula().getExpReference();
       ArrayRecord arec = expRef == null ? null : _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
	return arec != null;
}
 
Example #22
Source File: FormulaRecordAggregate.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
public Ptg[] getFormulaTokens() {
	if (_sharedFormulaRecord != null) {
		return _sharedFormulaRecord.getFormulaTokens(_formulaRecord);
	}
	CellReference expRef = _formulaRecord.getFormula().getExpReference();
	if (expRef != null) {
		ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
		return arec.getFormulaTokens();
	}
	return _formulaRecord.getParsedExpression();
}
 
Example #23
Source File: XlsUtils.java    From data-prep with Apache License 2.0 5 votes vote down vote up
/**
 * return the column number from a cell reference (AA242)
 *
 * @param lastCell
 * @return
 */
public static int getColumnNumberFromCellRef(String lastCell) {

    StringBuilder letters = new StringBuilder();
    // get all letters to remove row number
    StringCharacterIterator iter = new StringCharacterIterator(lastCell);
    for (char c = iter.first(); c != StringCharacterIterator.DONE; c = iter.next()) {
        if (!NumberUtils.isNumber(String.valueOf(c))) {
            letters.append(c);
        }
    }
    // use poi api to calculate column number from an excell column format
    return CellReference.convertColStringToIndex(letters.toString());

}
 
Example #24
Source File: WorkbookEvaluator.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
/**
 * Adds the current cell reference to the exception for easier debugging.
 * Would be nice to get the formula text as well, but that seems to require
 * too much digging around and casting to get the FormulaRenderingWorkbook.
 */
private NotImplementedException addExceptionInfo(NotImplementedException inner, int sheetIndex, int rowIndex, int columnIndex) {

    try {
        String sheetName = _workbook.getSheetName(sheetIndex);
        CellReference cr = new CellReference(sheetName, rowIndex, columnIndex, false, false);
        String msg =  "Error evaluating cell " + cr.formatAsString();
        return new NotImplementedException(msg, inner);
    } catch (Exception e) {
        // avoid bombing out during exception handling
        LOG.log(POILogger.ERROR, "Can't add exception info", e);
        return inner; // preserve original exception
    }
}
 
Example #25
Source File: FormulaRecordAggregate.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
/**
 * @param stringRec may be <code>null</code> if this formula does not have a cached text
 * value.
 * @param svm the {@link SharedValueManager} for the current sheet
 */
public FormulaRecordAggregate(FormulaRecord formulaRec, StringRecord stringRec, SharedValueManager svm) {
	if (svm == null) {
		throw new IllegalArgumentException("sfm must not be null");
	}
	if (formulaRec.hasCachedResultString()) {
		if (stringRec == null) {
			throw new RecordFormatException("Formula record flag is set but String record was not found");
		}
		_stringRecord = stringRec;
	} else {
		// Usually stringRec is null here (in agreement with what the formula rec says).
		// In the case where an extra StringRecord is erroneously present, Excel (2007)
		// ignores it (see bug 46213).
		_stringRecord = null;
	}

	_formulaRecord = formulaRec;
	_sharedValueManager = svm;
	if (formulaRec.isSharedFormula()) {
		CellReference firstCell = formulaRec.getFormula().getExpReference();
		if (firstCell == null) {
			handleMissingSharedFormulaRecord(formulaRec);
		} else {
			_sharedFormulaRecord = svm.linkSharedFormulaRecord(firstCell, this);
		}
	}
}
 
Example #26
Source File: HSSFCell.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
public CellRangeAddress getArrayFormulaRange() {
    if (_cellType != CellType.FORMULA) {
        String ref = new CellReference(this).formatAsString();
        throw new IllegalStateException("Cell " + ref
                + " is not part of an array formula.");
    }
    return ((FormulaRecordAggregate)_record).getArrayFormulaRange();
}
 
Example #27
Source File: XLSX2CSV.java    From jeesuite-libs with Apache License 2.0 5 votes vote down vote up
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
	if (firstCellOfRow) {
		firstCellOfRow = false;
	} else {
		_resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
	}

	// gracefully handle missing CellRef here in a similar way as
	// XSSFCell does
	if (cellReference == null) {
		cellReference = new CellAddress(currentRow, currentCol).formatAsString();
	}

	// Did we miss any cells?
	int thisCol = (new CellReference(cellReference)).getCol();
	int missedCols = thisCol - currentCol - 1;
	for (int i = 0; i < missedCols; i++) {
		_resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
	}
	currentCol = thisCol;

	// Number or string?
	try {
		Double.parseDouble(formattedValue);
		_resultRowTmp.append(formattedValue);
	} catch (NumberFormatException e) {
		_resultRowTmp.append(formattedValue);
	}
}
 
Example #28
Source File: AreaPtgBase.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
protected final String formatReferenceAsString() {
    CellReference topLeft = new CellReference(getFirstRow(),getFirstColumn(),!isFirstRowRelative(),!isFirstColRelative());
    CellReference botRight = new CellReference(getLastRow(),getLastColumn(),!isLastRowRelative(),!isLastColRelative());

    if(AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL97, topLeft, botRight)) {
        return (new AreaReference(topLeft, botRight, SpreadsheetVersion.EXCEL97)).formatAsString();
    }
    return topLeft.formatAsString() + ":" + botRight.formatAsString();
}
 
Example #29
Source File: ExcelRepository.java    From molgenis with GNU Lesser General Public License v3.0 5 votes vote down vote up
private Map<String, Integer> toColNamesMap(Row headerRow) {
  if (headerRow == null) return null;

  Map<String, Integer> columnIdx = new LinkedHashMap<>();
  int i = 0;
  for (Iterator<Cell> it = headerRow.cellIterator(); it.hasNext(); ) {
    try {
      String header =
          AbstractCellProcessor.processCell(ExcelUtils.toValue(it.next()), true, cellProcessors);
      if (header != null) {
        if (columnIdx.containsKey(header)) {
          throw new MolgenisDataException(
              format(
                  "Duplicate column header '%s' in sheet '%s' not allowed",
                  header, headerRow.getSheet().getSheetName()));
        }
        columnIdx.put(header, i++);
      }
    } catch (final IllegalStateException ex) {
      final int row = headerRow.getRowNum();
      final String column = CellReference.convertNumToColString(i);
      throw new IllegalStateException(
          "Invalid value at [" + sheet.getSheetName() + "] " + column + row + 1, ex);
    }
  }
  return columnIdx;
}
 
Example #30
Source File: TestUtils.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * 文字列の形式のセルのアドレスを、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());
}