Example usage for org.apache.poi.ss.util AreaReference getAllReferencedCells

List of usage examples for org.apache.poi.ss.util AreaReference getAllReferencedCells

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. AreaReference
  6. getAllReferencedCells

Introduction

In this page you can find the example usage for org.apache.poi.ss.util AreaReference getAllReferencedCells.

Prototype

public CellReference[] getAllReferencedCells()

Source Link

Document

Returns a reference to every cell covered by this area

Usage

From source file:android_connector.ExcelReader.java

/**
 * Gibt den Wert einer Zelle zurck.//ww  w.ja v  a 2 s  .c  om
 * @param cellName Name der Zelle
 * @return alle Zellen dieses Namens
 */
public String[] getCellValue(String cellName) {
    Name cellsName = wb.getName(cellName);
    AreaReference areaRef = new AreaReference(cellsName.getRefersToFormula(), version);
    CellReference[] cellRef = areaRef.getAllReferencedCells();
    String[] returnValue = new String[cellRef.length];
    for (int i = 0; i < cellRef.length; i++) {
        Row row = this.sheet.getRow(cellRef[i].getRow());
        Cell cell = row.getCell(cellRef[i].getCol());
        returnValue[i] = differCellType(cell);
    }

    return returnValue;
}

From source file:com.haulmont.yarg.formatters.impl.xls.HSSFRangeHelper.java

License:Apache License

public static CellReference[] getRangeContent(HSSFWorkbook workbook, String rangeName) {
    AreaReference areaForRange = getAreaForRange(workbook, rangeName);
    if (areaForRange == null) {
        return null;
    }/* ww w  .  j  a  v a  2s . c o  m*/

    return areaForRange.getAllReferencedCells();
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * Method writes horizontal band//from w  w w . j a v a  2s.co  m
 * Note: Only one band for row is supported. Now we think that many bands for row aren't usable.
 *
 * @param band          - band to write
 * @param templateSheet - template sheet
 * @param resultSheet   - result sheet
 */
protected void writeHorizontalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) {
    String rangeName = band.getName();
    AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName);
    if (templateRange == null) {
        throw wrapWithReportingException(String.format("No such named range in xls file: %s", rangeName));
    }
    CellReference[] crefs = templateRange.getAllReferencedCells();

    CellReference topLeft, bottomRight;
    AreaReference resultRange;

    int rowsAddedByHorizontalBandBackup = rowsAddedByHorizontalBand;
    int rownumBackup = rownum;

    if (crefs != null) {
        addRangeBounds(band, crefs);

        ArrayList<HSSFRow> resultRows = new ArrayList<HSSFRow>();

        int currentRowNum = -1;
        int currentRowCount = -1;
        int currentColumnCount = 0;
        int offset = 0;

        topLeft = new CellReference(rownum + rowsAddedByHorizontalBand, 0);
        // no child bands - merge regions now
        if (band.getChildrenList().isEmpty()) {
            copyMergeRegions(resultSheet, rangeName, rownum + rowsAddedByHorizontalBand,
                    getCellFromReference(crefs[0], templateSheet).getColumnIndex());
        }

        for (CellReference cellRef : crefs) {
            HSSFCell templateCell = getCellFromReference(cellRef, templateSheet);
            HSSFRow resultRow;
            if (templateCell.getRowIndex() != currentRowNum) { //create new row
                resultRow = resultSheet.createRow(rownum + rowsAddedByHorizontalBand);
                copyPageBreaks(templateSheet, resultSheet, templateCell.getRowIndex(), resultRow.getRowNum());
                rowsAddedByHorizontalBand += 1;

                //todo move to options
                if (templateCell.getCellStyle().getParentStyle() != null
                        && templateCell.getCellStyle().getParentStyle().getUserStyleName() != null
                        && templateCell.getCellStyle().getParentStyle().getUserStyleName()
                                .equals(DYNAMIC_HEIGHT_STYLE)) {
                    //resultRow.setHeight(templateCell.getRow().getHeight());
                } else {
                    resultRow.setHeight(templateCell.getRow().getHeight());
                }
                resultRows.add(resultRow);

                currentRowNum = templateCell.getRowIndex();
                currentRowCount++;
                currentColumnCount = 0;
                offset = templateCell.getColumnIndex();
            } else { // or write cell to current row
                resultRow = resultRows.get(currentRowCount);
                currentColumnCount++;
            }

            copyCellFromTemplate(templateCell, resultRow, offset + currentColumnCount, band);
        }

        bottomRight = new CellReference(rownum + rowsAddedByHorizontalBand - 1, offset + currentColumnCount);
        resultRange = new AreaReference(topLeft, bottomRight);

        areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.HORIZONTAL, templateRange),
                new Area(band.getName(), Area.AreaAlign.HORIZONTAL, resultRange));
        bandsToResultRanges.put(band,
                new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1,
                        resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1,
                        resultRange.getLastCell().getRow() + 1));
    }

    for (BandData child : band.getChildrenList()) {
        writeBand(child);
    }

    // scheduled merge regions
    if (!band.getChildrenList().isEmpty() && crefs != null) {
        copyMergeRegions(resultSheet, rangeName, rownumBackup + rowsAddedByHorizontalBandBackup,
                getCellFromReference(crefs[0], templateSheet).getColumnIndex());
    }

    rownum += rowsAddedByHorizontalBand;
    rowsAddedByHorizontalBand = 0;
    rownum += rowsAddedByVerticalBand;
    rowsAddedByVerticalBand = 0;
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

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() == HSSFCell.CELL_TYPE_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);
                }/*from ww  w . j a  va2 s .  c  om*/
            }

            String calculatedFormula = HSSFFormulaParser.toFormulaString(templateWorkbook, ptgs);
            resultCell.setCellFormula(calculatedFormula);
        }
    }
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public void setCellStyle(String formula, CellStyle cs) {
    AreaReference aref = new AreaReference(formula);
    String sheetName = aref.getFirstCell().getSheetName();
    if (sheetName == null) {
        throw new IllegalArgumentException("Invalid formula reference - should be of the form Sheet!A1:B10");
    }/*  w  w  w .j a  v  a2  s  .c om*/
    Sheet sheet = getSheet(sheetName);

    CellReference[] crefs = aref.getAllReferencedCells();
    for (CellReference cref : crefs) {
        Cell c = getCell(sheet, cref.getRow(), cref.getCol());
        setCellStyle(c, cs);
    }
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public void setCellFormula(String formulaDest, String formulaString) {
    AreaReference aref = new AreaReference(formulaDest);
    String sheetName = aref.getFirstCell().getSheetName();
    Sheet sheet = getSheet(sheetName);//w ww.j a va  2  s  . c o  m

    CellReference[] crefs = aref.getAllReferencedCells();
    for (CellReference cref : crefs) {
        Cell c = getCell(sheet, cref.getRow(), cref.getCol());
        setCellFormula(c, formulaString);
    }
}

From source file:com.vaadin.addon.spreadsheet.charts.converter.Utils.java

public static List<CellReference> getAllReferencedCells(String formula) {

    // generateContiguous cannot parse a forumula in parentheses.
    if (formula.startsWith("(") && formula.endsWith("")) {
        formula = formula.substring(1, formula.length() - 1);
    }/*from   w  ww. ja va2 s . c o  m*/

    ArrayList<CellReference> cellRefs = new ArrayList<CellReference>();
    for (AreaReference area : AreaReference.generateContiguous(formula)) {
        cellRefs.addAll(Arrays.asList(area.getAllReferencedCells()));
    }
    return cellRefs;
}

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

License:Open Source License

/**
 * @param args the command line arguments
 *///www.  j a v a 2s.  com
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.nuclos.server.common.ooxml.ExcelReader.java

License:Open Source License

private Object[][] getAreaValueArray(AreaReference ar) {
    int cols = Math.abs(ar.getFirstCell().getCol() - ar.getLastCell().getCol()) + 1;
    int rows = Math.abs(ar.getFirstCell().getRow() - ar.getLastCell().getRow()) + 1;
    CellReference[] crs = ar.getAllReferencedCells();
    if (crs.length != rows * cols) {
        throw new IllegalArgumentException("Invalid area reference " + ar);
    }/*w w  w . ja  v  a 2s  .  co m*/
    Object[][] rect = new Object[rows][cols];
    for (int r = 0; r < rows; r++) {
        for (int c = 0; c < cols; c++) {
            rect[r][c] = getSingleCellValue(crs[r * cols + c]);
        }
    }
    return rect;
}

From source file:org.openelis.bean.QcChartReport1Bean.java

License:Open Source License

private Cell getCellForName(HSSFSheet sheet, HSSFName name) {
    AreaReference aref;
    Cell cell;//from  w w  w  . j  av  a2  s. co  m
    CellReference cref[];

    cell = null;
    if (name != null && !name.isDeleted()) {
        aref = new AreaReference(name.getRefersToFormula());
        cref = aref.getAllReferencedCells();
        cell = sheet.getRow(cref[0].getRow()).getCell((int) cref[0].getCol());
    }

    return cell;
}