List of usage examples for org.apache.poi.ss.util CellReference getSheetName
public String getSheetName()
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void clearRangeFromReference(String reference) { AreaReference ref = new AreaReference(reference); CellReference firstCell = ref.getFirstCell(); CellReference lastCell = ref.getLastCell(); String sheetName = firstCell.getSheetName(); int[] coords = { firstCell.getRow(), firstCell.getCol(), lastCell.getRow(), lastCell.getCol() }; clearRange(sheetName, coords);//from w ww . ja v a 2 s. c o m }
From source file:com.vaadin.addon.spreadsheet.charts.converter.Utils.java
public static Double getNumericValue(CellReference ref, Spreadsheet spreadsheet) { try {/*from w ww. ja v a2s. co m*/ Sheet sheet = spreadsheet.getWorkbook().getSheet(ref.getSheetName()); Cell cell = spreadsheet.getCell(ref, sheet); spreadsheet.getFormulaEvaluator().evaluateFormulaCell(cell); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return cell.getNumericCellValue(); } } catch (NullPointerException e) { } catch (IllegalStateException e) { } catch (NumberFormatException e) { } catch (FormulaParseException e) { logError(); } return null; }
From source file:com.vaadin.addon.spreadsheet.charts.converter.xssfreader.AbstractSeriesReader.java
private int calculateDecimalsForTooltip(List<CellReference> ptList) { if (ptList.size() <= 0) { // No points, so go with the default number of decimals return -1; }//from ww w . ja va 2 s . c o m CellReference ref = ptList.get(0); Sheet sheet = spreadsheet.getWorkbook().getSheet(ref.getSheetName()); Cell cell = spreadsheet.getCell(ref, sheet); if (cell == null) { return -1; } CellStyle style = cell.getCellStyle(); String styleString = style.getDataFormatString(); if (styleString == null || styleString.isEmpty() || styleString.equals("General")) { // No formatting info given, so go with the default number of // decimals return -1; } //In formatting strings "." is always used it seems. char sep = '.'; // Take the last occurrence if the user has the same symbol as thousand // separator (should not be possible) int sepIndex = styleString.trim().lastIndexOf(sep); int decimalCount; if (sepIndex < 0) { decimalCount = 0; } else { decimalCount = styleString.length() - sepIndex - 1; } return decimalCount; }
From source file:com.vaadin.addon.spreadsheet.PopupButton.java
License:Open Source License
void setCellReference(CellReference cellReference) { getState().col = cellReference.getCol() + 1; getState().row = cellReference.getRow() + 1; getState().sheet = cellReference.getSheetName(); }
From source file:com.wantdo.stat.excel.poi_src.formula.UserDefinedFunctionExample.java
License:Apache License
public static void main(String[] args) { if (args.length != 2) { System.out.println("usage: UserDefinedFunctionExample fileName cellId"); return;//from w ww.j ava 2s.com } System.out.println("fileName: " + args[0]); System.out.println("cell: " + args[1]); File workbookFile = new File(args[0]); try { FileInputStream fis = new FileInputStream(workbookFile); Workbook workbook = WorkbookFactory.create(fis); fis.close(); String[] functionNames = { "calculatePayment" }; FreeRefFunction[] functionImpls = { new CalculateMortgage() }; UDFFinder udfToolpack = new DefaultUDFFinder(functionNames, functionImpls); // register the user-defined function in the workbook workbook.addToolPack(udfToolpack); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); CellReference cr = new CellReference(args[1]); String sheetName = cr.getSheetName(); Sheet sheet = workbook.getSheet(sheetName); int rowIdx = cr.getRow(); int colIdx = cr.getCol(); Row row = sheet.getRow(rowIdx); Cell cell = row.getCell(colIdx); CellValue value = evaluator.evaluate(cell); System.out.println("returns value: " + value); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:de.jlo.talendcomp.excel.SpreadsheetFile.java
License:Apache License
protected Cell getReferencedCell(String cellRefStr) throws Exception { if (workbook == null) { throw new IllegalStateException("Workbook is not initialized."); }//from ww w . j ava2 s . co m CellReference cellRef = new CellReference(cellRefStr); String sheetName = cellRef.getSheetName(); Sheet cellsheet = null; if (sheetName != null && sheetName.isEmpty() == false) { if (sheet == null || sheet.getSheetName().equalsIgnoreCase(sheetName) == false) { cellsheet = workbook.getSheet(sheetName); } else { throw new Exception("Sheet with name:" + sheetName + " does not exists."); } } else { if (sheet == null) { throw new Exception("No current sheet selected. The given cell reference:" + cellRefStr + " contains not sheet name."); } else { cellsheet = sheet; } } if (cellsheet != null) { int numRow = cellRef.getRow(); Row row = cellsheet.getRow(numRow); if (row == null) { row = cellsheet.createRow(numRow); } short numCol = cellRef.getCol(); Cell cell = row.getCell(numCol); if (cell == null) { cell = row.createCell(numCol); } return cell; } else { return null; } }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private void setupReferencedSheet(String cellRefStr, Object sheetRef) throws Exception { if (sheetRef instanceof String) { sheet = workbook.getSheet((String) sheetRef); if (sheet == null) { sheet = workbook.createSheet((String) sheetRef); }//from w w w . ja v a2s .c o m } else if (sheetRef instanceof Number) { sheet = workbook.getSheetAt(((Number) sheetRef).intValue()); if (sheet == null) { throw new Exception("Sheet with index: " + ((Number) sheetRef).intValue() + " does not exists and can only be created if a name will be provided"); } } else if (cellRefStr != null && cellRefStr.trim().isEmpty() == false) { CellReference cellRef = new CellReference(cellRefStr.trim()); String sheetNameFromRef = cellRef.getSheetName(); if (sheetNameFromRef != null && sheetNameFromRef.trim().isEmpty() == false) { sheet = workbook.getSheet(sheetNameFromRef); if (sheet == null) { sheet = workbook.createSheet(sheetNameFromRef); } } } }
From source file:de.jlo.talendcomp.excel.SpreadsheetReferencedCellInput.java
License:Apache License
private boolean readNextCell(String cellRefStr) throws Exception { if (cellRefStr == null || cellRefStr.trim().isEmpty()) { throw new IllegalArgumentException("cellRefStr cannot ne null or empty!"); }/*from ww w .ja v a 2 s. c o m*/ CellReference cellRef = new CellReference(cellRefStr.trim()); String sheetNameFromRef = cellRef.getSheetName(); if (sheetNameFromRef != null && sheetNameFromRef.trim().isEmpty() == false) { currentSheetIndex = null; currentSheetName = sheetNameFromRef.trim(); } return readNextCell(cellRef.getRow() + 1, cellRef.getCol()); }
From source file:edu.vt.owml.saurav.raininterpolation.debug.NewMain.java
License:Open Source License
/** * @param args the command line arguments *///w w w . jav a 2s . co m public static void main(String[] args) { try { Workbook wb; wb = WorkbookFactory.create(NewMain.class.getResourceAsStream("/unit_test.xlsx")); // retrieve the named range String cellname = "stations"; int namedCellIdx = wb.getNameIndex(cellname); Name aNamedCell = wb.getNameAt(namedCellIdx); // retrieve the cell at the named range and test its contents AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula()); CellReference[] crefs = (CellReference[]) aref.getAllReferencedCells(); int index = 0; int columns = 2; double[][] stations = new double[(int) crefs.length / columns][2]; for (CellReference cref : crefs) { Sheet s = wb.getSheet(cref.getSheetName()); Row r = s.getRow(cref.getRow()); Cell c = r.getCell(cref.getCol()); System.out.println(c.getNumericCellValue()); //2 col array stations[(int) (index / columns)][index % columns] = c.getNumericCellValue(); index++; } printArray(stations); //rain cellname = "gridpts"; namedCellIdx = wb.getNameIndex(cellname); aNamedCell = wb.getNameAt(namedCellIdx); // retrieve the cell at the named range and test its contents aref = new AreaReference(aNamedCell.getRefersToFormula()); crefs = (CellReference[]) aref.getAllReferencedCells(); index = 0; columns = 2; double[][] locations = new double[(int) crefs.length / columns][2]; for (CellReference cref : crefs) { Sheet s = wb.getSheet(cref.getSheetName()); Row r = s.getRow(cref.getRow()); Cell c = r.getCell(cref.getCol()); System.out.println(c.getNumericCellValue()); //2 col array locations[(int) (index / columns)][index % columns] = c.getNumericCellValue(); index++; } printArray(locations); //rain cellname = "rainVal"; namedCellIdx = wb.getNameIndex(cellname); aNamedCell = wb.getNameAt(namedCellIdx); // retrieve the cell at the named range and test its contents aref = new AreaReference(aNamedCell.getRefersToFormula()); crefs = (CellReference[]) aref.getAllReferencedCells(); index = 0; double[] rainValues = new double[crefs.length]; for (CellReference cref : crefs) { Sheet s = wb.getSheet(cref.getSheetName()); Row r = s.getRow(cref.getRow()); Cell c = r.getCell(cref.getCol()); System.out.println(c.getNumericCellValue()); //2 col array rainValues[index] = c.getNumericCellValue(); index++; } printArray(rainValues); //vals cellname = "estimates"; namedCellIdx = wb.getNameIndex(cellname); aNamedCell = wb.getNameAt(namedCellIdx); // retrieve the cell at the named range and test its contents aref = new AreaReference(aNamedCell.getRefersToFormula()); crefs = (CellReference[]) aref.getAllReferencedCells(); index = 0; double[] vals = new double[crefs.length]; for (CellReference cref : crefs) { Sheet s = wb.getSheet(cref.getSheetName()); Row r = s.getRow(cref.getRow()); Cell c = r.getCell(cref.getCol()); System.out.println(c.getNumericCellValue()); //2 col array vals[index] = c.getNumericCellValue(); index++; } printArray(vals); //distances cellname = "distances"; namedCellIdx = wb.getNameIndex(cellname); aNamedCell = wb.getNameAt(namedCellIdx); // retrieve the cell at the named range and test its contents aref = new AreaReference(aNamedCell.getRefersToFormula()); crefs = (CellReference[]) aref.getAllReferencedCells(); index = 0; columns = stations.length; double[] d = new double[stations.length]; List<double[]> distances = new ArrayList(); for (CellReference cref : crefs) { Sheet s = wb.getSheet(cref.getSheetName()); Row r = s.getRow(cref.getRow()); Cell c = r.getCell(cref.getCol()); System.out.println(c.getNumericCellValue()); d[index % columns] = c.getNumericCellValue(); if (index % columns == columns - 1) { distances.add(d); d = new double[stations.length]; } index++; } printArray(distances); IDWInterpolator idw = new IDWInterpolator(); // printArray(idw.getDistances(stations, locations)); } catch (FileNotFoundException ex) { Logger.getLogger(NewMain.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException | InvalidFormatException ex) { Logger.getLogger(NewMain.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.centralperf.helper.view.ExcelOOXMLView.java
License:Open Source License
/** * Retrieve a cell in workbook by its name * @param cellName The name of the cell * @param workbook The workbook//from w w w .jav a2 s . c om * @return the cell found, null if multiple cells or not found */ private Cell getCellByName(String cellName, Workbook workbook) { int namedCellIdx = workbook.getNameIndex(cellName); Name aNamedCell = workbook.getNameAt(namedCellIdx); // retrieve the cell at the named range and test its contents AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula()); if (aref.isSingleCell()) { CellReference cref = aref.getFirstCell(); Sheet s = workbook.getSheet(cref.getSheetName()); Row r = s.getRow(cref.getRow()); Cell c = r.getCell(cref.getCol()); return c; } return null; }