List of usage examples for org.apache.poi.ss.usermodel Workbook getNameAt
@Deprecated @Removal(version = "5.0.0") Name getNameAt(int nameIndex);
From source file:com.dataart.spreadsheetanalytics.engine.DataModelConverters.java
License:Apache License
/** * For given {@link Workbook} does convert everything to new {@link DataModel} structure. * Does copy all supported fields (for supported fields see {@link DataModel} class. *///from ww w. j a va 2 s . c om static IDataModel toDataModel(final Workbook workbook) { if (workbook == null) { return null; } //add custom functions information workbook.addToolPack(getUdfFinder()); Sheet s = workbook.getSheetAt(0); //TODO: only one sheet is supported if (s == null) { return null; } IDataModel dm = new DataModel(s.getSheetName()); for (int i = s.getFirstRowNum(); i <= s.getLastRowNum(); i++) { Row r = s.getRow(i); if (r == null) { continue; } DmRow row = new DmRow(i); dm.setRow(i, row); for (int j = r.getFirstCellNum(); j < r.getLastCellNum(); j++) { Cell c = r.getCell(j); if (c == null) { continue; } DmCell cell = new DmCell(); row.setCell(j, cell); cell.setAddress(new CellAddress(dm.getDataModelId(), A1Address.fromRowColumn(i, j))); cell.setContent(ConverterUtils.resolveCellValue(c)); } } EvaluationWorkbook evaluationWbook = ConverterUtils.newEvaluationWorkbook(workbook); for (int nIdx = 0; nIdx < workbook.getNumberOfNames(); nIdx++) { Name name = workbook.getNameAt(nIdx); String reference = name.getRefersToFormula(); if (reference == null) { continue; } if (A1Address.isAddress(removeSheetFromNameRef(reference))) { dm.setNamedAddress(name.getNameName(), A1Address.fromA1Address(removeSheetFromNameRef(reference))); } else if (isFormula(reference, evaluationWbook)) { dm.setNamedValue(name.getNameName(), new CellValue(FORMULA_PREFIX + reference)); } else { dm.setNamedValue(name.getNameName(), CellValue.from(reference)); } } return dm; }
From source file:com.dataart.spreadsheetanalytics.engine.PoiWorkbookConverters.java
License:Apache License
private Map<Integer, PoiProxyName> makeNames(Workbook wb, EvaluationWorkbook ewb) { Map<Integer, PoiProxyName> names = new HashMap<>(); for (int nIdx = 0; nIdx < wb.getNumberOfNames(); nIdx++) { Name wbName = wb.getNameAt(nIdx); Ptg[] ptgs;//ww w. j av a 2 s .c om String refersToFormula; if (!wbName.isFunctionName() && wbName.getRefersToFormula() != null) { //NOPMD refersToFormula = wbName.getRefersToFormula(); ptgs = FormulaParser.parse(refersToFormula, (FormulaParsingWorkbook) ewb, FormulaType.NAMEDRANGE, 0 /*TODO: sheet index*/); } else { ptgs = null; refersToFormula = null; } names.put(nIdx, new PoiProxyName(wbName.getNameName(), wbName.isFunctionName(), refersToFormula != null, ptgs, wbName.isFunctionName(), nIdx)); } return names; }
From source file:com.dataart.spreadsheetanalytics.engine.SpreadsheetAuditor.java
License:Apache License
public static Map<String, String> getWorkbookNames(Workbook workbook) { Map<String, String> result = new HashMap<>(workbook.getNumberOfNames()); for (int i = 0; i < workbook.getNumberOfNames(); i++) { Name name = workbook.getNameAt(i); result.put(name.getNameName(), name.getRefersToFormula()); }//from w w w .j a v a 2 s . c om return result; }
From source file:edu.vt.owml.saurav.raininterpolation.debug.NewMain.java
License:Open Source License
/** * @param args the command line arguments *///w w w . j ava 2 s . c o 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 ww w . j av a2s. 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; }
From source file:org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.java
License:Open Source License
private void loadNames(Workbook _xlsWorkbook, BaseSpreadsheet _spreadsheet) { final int numberOfNames = _xlsWorkbook.getNumberOfNames(); for (int nameIndex = 0; nameIndex < numberOfNames; nameIndex++) { final Name name = _xlsWorkbook.getNameAt(nameIndex); if (name.isFunctionName()) continue; final String cellRangeAddress = name.getRefersToFormula(); final String rangeName = name.getNameName(); final ExpressionParser parser = new SpreadsheetExpressionParserA1OOXML(cellRangeAddress, _spreadsheet); try {//from ww w .j av a2 s . com final CellRange cellRange = (CellRange) parser.rangeOrCellRefA1(); _spreadsheet.defineModelRangeName(rangeName, cellRange); } catch (ParseException e) { // Ignore all non 'named range' names } } }
From source file:org.jreserve.gui.poi.ExcelUtil.java
License:Open Source License
public static List<Name> getReferenceNames(Workbook wb) { int size = wb.getNumberOfNames(); List<Name> result = new ArrayList<Name>(size); for (int i = 0; i < size; i++) { Name name = wb.getNameAt(i); if (isReferenceName(wb, name)) result.add(name);//from www. j a va 2s . c o m } return result; }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.HyperlinksTest.java
License:Open Source License
private void validateNamedRange(Workbook workbook, int index, String name, int sheetIndex, int row1, int col1, int row2, int col2) { Name namedRange = workbook.getNameAt(index); assertEquals(name, namedRange.getNameName()); assertEquals(sheetIndex, namedRange.getSheetIndex()); AreaReference ref = new AreaReference(namedRange.getRefersToFormula()); if ((row1 == row2) && (col1 == col2)) { assertTrue(ref.isSingleCell());/* w ww. j a v a 2s . co m*/ assertEquals(row1, ref.getFirstCell().getRow()); assertEquals(col1, ref.getFirstCell().getCol()); } else { assertTrue(AreaReference.isContiguous(namedRange.getRefersToFormula())); assertEquals(row1, Math.min(ref.getFirstCell().getRow(), ref.getLastCell().getRow())); assertEquals(col1, Math.min(ref.getFirstCell().getCol(), ref.getLastCell().getCol())); assertEquals(row2, Math.max(ref.getFirstCell().getRow(), ref.getLastCell().getRow())); assertEquals(col2, Math.max(ref.getFirstCell().getCol(), ref.getLastCell().getCol())); } }