Example usage for org.apache.poi.ss.usermodel Name getRefersToFormula

List of usage examples for org.apache.poi.ss.usermodel Name getRefersToFormula

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Name
  6. getRefersToFormula

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Name getRefersToFormula.

Prototype

String getRefersToFormula();

Source Link

Document

Returns the formula that the name is defined to refer to.

Usage

From source file:edu.vt.owml.saurav.raininterpolation.debug.NewMain.java

License:Open Source License

/**
 * @param args the command line arguments
 *//*from  w ww.ja  va  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:nu.mine.kino.jenkins.plugins.projectmanagement.utils.PMUtils.java

License:Open Source License

public static Date getBaseDateFromExcelWithPoi(File file) {
    InputStream in = null;/*from  ww w  .  j  av a2  s.co m*/
    try {
        in = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(in);
        Sheet sheet = workbook.getSheetAt(0);
        Name name = workbook.getName("??");
        CellReference cellRef = new CellReference(name.getRefersToFormula());
        Row row = sheet.getRow(cellRef.getRow());
        Cell baseDateCell = row.getCell(cellRef.getCol());
        // System.out.println("cellt:"
        // + PoiUtil.isCellDateFormatted(baseDateCell));
        Date baseDate = baseDateCell.getDateCellValue();
        return baseDate;

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (in != null)
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

    }
    return null;
}

From source file:nu.mine.kino.projects.utils.POI2Test.java

License:Open Source License

public void test3() throws InvalidFormatException, IOException {

    // String range = "A1:C5";
    // CellRangeAddress address = CellRangeAddress.valueOf(range);
    // int firstRow = address.getFirstRow();
    // int lastRow = address.getLastRow();
    // int firstColumn = address.getFirstColumn();
    // int lastColumn = address.getLastColumn();
    // System.out.println(firstRow);
    // System.out.println(lastRow);
    // System.out.println(lastColumn);
    // System.out.println(firstColumn);

    // OwZ/*  w ww .  j ava 2s.  co m*/
    Sheet sheet = workbook.getSheetAt(0);
    Name name = workbook.getName("??");
    CellReference cellRef = new CellReference(name.getRefersToFormula());

    Row row = sheet.getRow(cellRef.getRow());
    Cell baseDateCell = row.getCell(cellRef.getCol());
    System.out.println("cellt:" + PoiUtil.isCellDateFormatted(baseDateCell));
    Date baseDate = baseDateCell.getDateCellValue();
    System.out.println(baseDate);
    System.out.println(baseDateCell.getNumericCellValue());

    // OwZ?B
    final Name DATA_AREA = workbook.getName("DATA_AREA");
    final AreaReference areaReference = new AreaReference(DATA_AREA.getRefersToFormula());
    final CellReference firstCell = areaReference.getFirstCell();
    final CellReference lastCell = areaReference.getLastCell();

    Row fRow = sheet.getRow(firstCell.getRow());
    Row lRow = sheet.getRow(lastCell.getRow());

    System.out.printf("??sIndex %s\n", firstCell.getRow());
    System.out.printf("??I?sIndex %s\n", lastCell.getRow());

    Cell fCell = fRow.getCell(firstCell.getCol());
    Cell lCell = lRow.getCell(lastCell.getCol());
    System.out.println(fCell);
    System.out.println(lCell);

    for (int index = firstCell.getRow(); index <= lastCell.getRow(); index++) {
        Row tmpRow = sheet.getRow(index);
        Cell tmpCell = tmpRow.getCell(firstCell.getCol()); // RRwColumnNumber?B
        System.out.println(tmpCell);
    }

}

From source file:nu.mine.kino.projects.utils.POI2Test.java

License:Open Source License

public void test4() throws InvalidFormatException, IOException {

    // OwZ/*ww w. j ava  2s  .c om*/
    Sheet sheet = workbook.getSheetAt(0);
    Name name = workbook.getName("??");
    CellReference cellRef = new CellReference(name.getRefersToFormula());

    Row row = sheet.getRow(cellRef.getRow());
    Cell baseDateCell = row.getCell(cellRef.getCol());
    System.out.println("cellt:" + PoiUtil.isCellDateFormatted(baseDateCell));
    Date baseDate = baseDateCell.getDateCellValue();
    System.out.println(baseDate);
    System.out.println(baseDateCell.getNumericCellValue());

    // OwZ?B
    final Name DATA_AREA = workbook.getName("DATA_AREA");
    final AreaReference areaReference = new AreaReference(DATA_AREA.getRefersToFormula());
    final CellReference firstCell = areaReference.getFirstCell();
    final CellReference lastCell = areaReference.getLastCell();

    Row fRow = sheet.getRow(firstCell.getRow());
    Row lRow = sheet.getRow(lastCell.getRow());

    System.out.printf("??sIndex %s\n", firstCell.getRow());
    System.out.printf("??I?sIndex %s\n", lastCell.getRow());

    Cell fCell = fRow.getCell(firstCell.getCol());
    Cell lCell = lRow.getCell(lastCell.getCol());
    System.out.println(fCell);
    System.out.println(lCell);

    for (int index = firstCell.getRow(); index <= lastCell.getRow(); index++) {
        Row tmpRow = sheet.getRow(index);
        Cell tmpCell = tmpRow.getCell(firstCell.getCol()); // RRwColumnNumber?B
        tmpCell.setCellValue(index * 100);
        System.out.println(tmpCell);
    }

    try {
        out = new FileOutputStream(new java.io.File("testdata4.xls"));
        workbook.write(out);
    } catch (FileNotFoundException e) {
        Assert.fail(e.getMessage());
    } catch (IOException e) {
        Assert.fail(e.getMessage());
    }
}

From source file:nu.mine.kino.projects.utils.POI2Test.java

License:Open Source License

public void test5() throws InvalidFormatException, IOException {

    // OwZ//from ww  w . j a  va2  s  .c o m
    Sheet sheet = workbook.getSheetAt(1);
    final Name DATA_AREA = workbook.getName("DATA_AREA2");
    final AreaReference areaReference = new AreaReference(DATA_AREA.getRefersToFormula());
    final CellReference firstCell = areaReference.getFirstCell();
    final CellReference lastCell = areaReference.getLastCell();

    Row fRow = sheet.getRow(firstCell.getRow());
    Row lRow = sheet.getRow(lastCell.getRow());

    System.out.printf("??sIndex %s\n", firstCell.getRow());
    System.out.printf("??I?sIndex %s\n", lastCell.getRow());

    Cell fCell = fRow.getCell(firstCell.getCol());
    Cell lCell = lRow.getCell(lastCell.getCol());
    System.out.println(fCell);
    System.out.println(lCell);

    for (int index = firstCell.getRow(); index <= lastCell.getRow(); index++) {
        Row tmpRow = sheet.getRow(index);
        Cell tmpCell = tmpRow.getCell(firstCell.getCol()); // RRwColumnNumber?B
        tmpCell.setCellValue(index * 100);
        System.out.println(tmpCell);
    }
    sheet.shiftRows(lastCell.getRow(), lastCell.getRow(), 3);

    try {
        out = new FileOutputStream(new java.io.File("testdata4.xls"));
        workbook.write(out);
    } catch (FileNotFoundException e) {
        Assert.fail(e.getMessage());
    } catch (IOException e) {
        Assert.fail(e.getMessage());
    }
}

From source file:nu.mine.kino.projects.utils.POI2Test.java

License:Open Source License

private void expandRange(Workbook workbook, String sheetName, String dataName, int dataCount) {

    // OwZ//from   w w  w . j ava  2 s.  c o  m
    Sheet sheet = workbook.getSheet(sheetName);
    final Name DATA_AREA = workbook.getName(dataName);
    final AreaReference areaReference = new AreaReference(DATA_AREA.getRefersToFormula());
    // final CellReference firstCell = areaReference.getFirstCell();
    final CellReference lastCell = areaReference.getLastCell();

    // System.out.printf("??sIndex %s\n", firstCell.getRow());
    System.out.printf("??I?sIndex %s\n", lastCell.getRow());

    sheet.shiftRows(lastCell.getRow(), lastCell.getRow(), dataCount);
}

From source file:nu.mine.kino.projects.utils.POITest.java

License:Open Source License

@Test
public void test3() throws InvalidFormatException, IOException {
    Sheet sheet = workbook.getSheetAt(0);
    Name name = workbook.getName("??");
    CellReference cellRef = new CellReference(name.getRefersToFormula());
    Row row = sheet.getRow(cellRef.getRow());
    Cell baseDateCell = row.getCell(cellRef.getCol());
    System.out.println("cellt:" + PoiUtil.isCellDateFormatted(baseDateCell));
    Date baseDate = baseDateCell.getDateCellValue();
    System.out.println(baseDate);
}

From source file:nu.mine.kino.projects.utils.ProjectUtils.java

License:Open Source License

public static Date createBaseDate(Workbook workbook, Sheet sheet) {
    Date baseDate;/*from   w ww.j a  v  a  2  s  .  c om*/
    Name name = workbook.getName("??");
    CellReference cellReference = new CellReference(name.getRefersToFormula());
    Cell baseDateCell = sheet.getRow(cellReference.getRow()).getCell(cellReference.getCol());
    baseDate = PoiUtils.getDate(baseDateCell);
    return baseDate;
}

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/*  w w  w . j  a v  a 2  s. c o  m*/
 * @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.eclipse.lyo.samples.excel.adapter.dao.internal.ExcelDaoImpl.java

License:Open Source License

private Cell getNamedCell(Sheet sheet, String cellRowString, int defaultRowIndex) {
    Name name = sheet.getWorkbook().getName(cellRowString);
    if (name != null) {
        AreaReference areaRef = new AreaReference(name.getRefersToFormula());
        CellReference firstCell = areaRef.getFirstCell();
        CellReference lastCell = areaRef.getLastCell();
        int rowIndex = defaultRowIndex;
        if (rowIndex < firstCell.getRow() || lastCell.getRow() < rowIndex) {
            rowIndex = firstCell.getRow();
        }// w  w  w . j a  v  a  2s . c  o  m
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            return row.getCell(firstCell.getCol());
        }
    }
    return null;
}