Sheet sheet = wb.getSheetAt(0); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); CellReference cellReference = new CellReference("B3"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BLANK:
@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; }
CellReference.convertNumToColString(_firstCell.getCol()) + ":" + CellReference.convertNumToColString(_lastCell.getCol()); sb.append(_firstCell.formatAsString()); if(!_isSingleCell) { sb.append(CELL_DELIMITER); if(_lastCell.getSheetName() == null) { sb.append(_lastCell.formatAsString()); } else { _lastCell.appendCellReference(sb);
Sheet sheet = workbook.getSheet("MyInterestingSheet"); CellReference ref = new CellReference("B12"); Row r = sheet.getRow(ref.getRow()); if (r != null) { Cell c = r.getCell(ref.getCol()); }
@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(); } }
wb.createSheet( data.realSheetname ); wb.write( out ); out.close(); wb.close(); data.wb.setSheetOrder( data.sheet.getSheetName(), replacingSheetAt ); data.startingCol = 0; if ( !Utils.isEmpty( data.realStartingCell ) ) { CellReference cellRef = new CellReference( data.realStartingCell ); data.startingRow = cellRef.getRow(); data.startingCol = cellRef.getCol(); if ( data.sheet.getPhysicalNumberOfRows() > 0 ) { data.posY = data.sheet.getLastRowNum(); data.posY++;
Workbook wb = ... Sheet sheet = wb.getSheetAt(0); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // suppose your formula is in B3 CellReference cellReference = new CellReference("B3"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); CellValue cellValue = evaluator.evaluate(cell);
CellReference cellRef = new CellReference( stepMeta.getStartingCell() ); stepData.startingRow = cellRef.getRow(); stepData.startingCol = cellRef.getCol(); stepData.posX = stepData.startingCol; stepData.posY = stepData.startingRow; stepData.sheet = stepData.wb.createSheet(); stepData.file = null; stepData.clearStyleCache( numOfFields ); DataFormat format = stepData.wb.createDataFormat(); Row xlsRow = stepData.sheet.createRow( 0 ); Cell cell = xlsRow.createCell( 5 ); CellStyle cellStyle = stepData.wb.createCellStyle(); cellStyle.setBorderRight( BorderStyle.THICK ); cellStyle.setFillPattern( FillPatternType.FINE_DOTS ); cell.setCellStyle( cellStyle ); cellStyle = stepData.wb.createCellStyle(); cellStyle.cloneStyleFrom( cell.getCellStyle() ); cell = xlsRow.createCell( 6 ); cellStyle.setDataFormat( format.getFormat( "##0,000.0" ) ); cell.setCellStyle( cellStyle );
return; String ref = new CellReference(_rownum, columnIndex).formatAsString(); _out.write("<c"); writeAttribute("r", ref); CellStyle cellStyle = cell.getCellStyle(); if (cellStyle.getIndex() != 0) { CellType cellType = cell.getCellType(); switch (cellType) { case BLANK: { outputQuotedString(cell.getCellFormula()); _out.write("</f>"); switch (cell.getCachedFormulaResultType()) {
protected void createCacheFields(Sheet sheet) { AreaReference ar = getPivotArea(sheet.getWorkbook()); CellReference firstCell = ar.getFirstCell(); CellReference lastCell = ar.getLastCell(); int columnStart = firstCell.getCol(); int columnEnd = lastCell.getCol(); Row row = sheet.getRow(firstCell.getRow()); CTCacheFields cFields; if(ctPivotCacheDefinition.getCacheFields() != null) { Cell cell = row.getCell(i); cell.setCellType(CellType.STRING); cf.setName(row.getCell(i).getStringCellValue()); cf.addNewSharedItems();
/** * @param reference * @return the cell the refernce points to */ private Cell getCellFromReference( String reference ) { CellReference cellRef = new CellReference( reference ); String sheetName = cellRef.getSheetName(); Sheet sheet = data.sheet; if ( !Utils.isEmpty( sheetName ) ) { sheet = 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; }
FileInputStream fileInputStream = new FileInputStream("test.xlsx"); Workbook workBook = new XSSFWorkbook(fileInputStream); Sheet sheet = workBook.getSheetAt(0); CellReference cellReference = new CellReference("A1"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue());
Cell cell = getDataSheet().getRow(pivotArea.getFirstCell().getRow()) .getCell(pivotArea.getFirstCell().getCol() + columnIndex); cell.setCellType(CellType.STRING); dataField.setName(valueFieldName); dataField.setFld(columnIndex); if (valueFormat != null && !valueFormat.trim().isEmpty()) { DataFormat df = parentSheet.getWorkbook().createDataFormat(); dataField.setNumFmtId(df.getFormat(valueFormat));
CellReference ref = new CellReference("A1"); Row row = sheet.getRow(ref.getRow()); if (row == null) throw new Exception("Row empty"); Cell cell = row.getCell(ref.getCol()); if (cell == null) throw new Exeeption("Cell empty"); cell.setCellValue(cell.getNumericCellValue() + (1.0/24));
CellReference cr = new CellReference(entry.getKey()); int r = cr.getRow(); int c = cr.getCol(); Row row = sheet.getRow(r); if (row == null) row = sheet.createRow(r); Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK); cell.setCellValue(entry.getValue());
/** * Set specified cell format * * @param excelFieldFormat the specified format * @param cell the cell to set up format */ private void setDataFormat( String excelFieldFormat, Cell cell ) { if ( log.isDebug() ) { logDebug( BaseMessages.getString( PKG, "ExcelWriterStep.Log.SetDataFormat", excelFieldFormat, CellReference.convertNumToColString( cell.getColumnIndex() ), cell.getRowIndex() ) ); } DataFormat format = data.wb.createDataFormat(); short formatIndex = format.getFormat( excelFieldFormat ); CellStyle style = data.wb.createCellStyle(); style.cloneStyleFrom( cell.getCellStyle() ); style.setDataFormat( formatIndex ); cell.setCellStyle( style ); }
public static CellReference getRef(Cell cell) { return new CellReference(cell.getSheet().getSheetName(), cell.getRowIndex(), cell.getColumnIndex(), false, false); }
final Cell cell = SheetUtil.getCell(workbook.getSheet(cellRef.getSheetName()), cellRef.getRow(), cellRef.getCol()); || isType(cell, CellType.BLANK) || (isType(cell,CellType.STRING) && (cell.getStringCellValue() == null || cell.getStringCellValue().isEmpty())
public String toCellName() { CellReference cellRef = new CellReference(getRow().getPoiRow().getRowNum(), getPoiCell().getColumnIndex(), false, false); return cellRef.formatAsString(); }