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:android_connector.ExcelReader.java

/**
 * Gibt den Wert einer Zelle zurck.//from  ww w.j  av  a 2s.  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.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   w ww  . j  a  va 2s . c  o m*/
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;/* w ww .  j a v  a2  s  .  c o  m*/
        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 va2  s. c  om*/
    return result;
}

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

License:Open Source License

public int[] getReferenceCoordinatesForName(String name) {
    Name cname = getName(name);
    AreaReference aref = new AreaReference(cname.getRefersToFormula());
    // Get upper left corner
    CellReference first = aref.getFirstCell();
    // Get lower right corner
    CellReference last = aref.getLastCell();
    int top = first.getRow();
    int bottom = last.getRow();
    int left = first.getCol();
    int right = last.getCol();
    return new int[] { top, left, bottom, right };
}

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

License:Open Source License

public void writeNamedRegion(DataFrame data, String name, boolean header) {
    Name cname = getName(name);
    checkName(cname);//from  ww  w.ja v a2  s.c  om

    // Get sheet where name is defined in
    Sheet sheet = workbook.getSheet(cname.getSheetName());

    AreaReference aref = new AreaReference(cname.getRefersToFormula());
    // Get upper left corner
    CellReference topLeft = aref.getFirstCell();

    // Compute bottom right cell coordinates
    int bottomRightRow = Math.max(topLeft.getRow() + data.rows() - 1, topLeft.getRow());
    if (header)
        ++bottomRightRow;
    int bottomRightCol = Math.max(topLeft.getCol() + data.columns() - 1, topLeft.getCol());
    // Create bottom right cell reference
    CellReference bottomRight = new CellReference(sheet.getSheetName(), bottomRightRow, bottomRightCol, true,
            true);

    // Define named range area
    aref = new AreaReference(topLeft, bottomRight);
    // Redefine named range
    cname.setRefersToFormula(aref.formatAsString());

    writeData(data, sheet, topLeft.getRow(), topLeft.getCol(), header);
}

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

License:Open Source License

public DataFrame readNamedRegion(String name, boolean header, ReadStrategy readStrategy, DataType[] colTypes,
        boolean forceConversion, String dateTimeFormat, boolean takeCached, int[] subset) {
    Name cname = getName(name);
    checkName(cname);//  w ww.ja  v  a  2 s  . co m

    // Get sheet where name is defined in
    Sheet sheet = workbook.getSheet(cname.getSheetName());

    AreaReference aref = new AreaReference(cname.getRefersToFormula());
    // Get name corners (top left, bottom right)
    CellReference topLeft = aref.getFirstCell();
    CellReference bottomRight = aref.getLastCell();

    // Determine number of rows and columns
    int nrows = bottomRight.getRow() - topLeft.getRow() + 1;
    int ncols = bottomRight.getCol() - topLeft.getCol() + 1;

    return readData(sheet, topLeft.getRow(), topLeft.getCol(), nrows, ncols, header, readStrategy, colTypes,
            forceConversion, dateTimeFormat, takeCached, subset);
}

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

License:Open Source License

public void addImage(File imageFile, String name, boolean originalSize)
        throws FileNotFoundException, IOException {
    Name cname = getName(name);

    // Get sheet where name is defined in
    Sheet sheet = workbook.getSheet(cname.getSheetName());

    AreaReference aref = new AreaReference(cname.getRefersToFormula());
    // Get name corners (top left, bottom right)
    CellReference topLeft = aref.getFirstCell();
    CellReference bottomRight = aref.getLastCell();

    // Determine image type
    int imageType;
    String filename = imageFile.getName().toLowerCase();
    if (filename.endsWith("jpg") || filename.endsWith("jpeg")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_JPEG;
    } else if (filename.endsWith("png")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_PNG;
    } else if (filename.endsWith("wmf")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_WMF;
    } else if (filename.endsWith("emf")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_EMF;
    } else if (filename.endsWith("bmp") || filename.endsWith("dib")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_DIB;
    } else if (filename.endsWith("pict") || filename.endsWith("pct") || filename.endsWith("pic")) {
        imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_PICT;
    } else//from  w  ww  .j av a2  s. com
        throw new IllegalArgumentException(
                "Image type \"" + filename.substring(filename.lastIndexOf('.') + 1) + "\" not supported!");

    InputStream is = new FileInputStream(imageFile);
    byte[] bytes = IOUtils.toByteArray(is);
    int imageIndex = workbook.addPicture(bytes, imageType);
    is.close();

    Drawing drawing;
    if (isHSSF()) {
        drawing = ((HSSFSheet) sheet).getDrawingPatriarch();
        if (drawing == null) {
            drawing = sheet.createDrawingPatriarch();
        }
    } else if (isXSSF()) {
        drawing = ((XSSFSheet) sheet).createDrawingPatriarch();
    } else {
        drawing = sheet.createDrawingPatriarch();
    }

    CreationHelper helper = workbook.getCreationHelper();
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setRow1(topLeft.getRow());
    anchor.setCol1(topLeft.getCol());
    // +1 since we want to include the
    anchor.setRow2(bottomRight.getRow() + 1);
    anchor.setCol2(bottomRight.getCol() + 1);
    anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);

    Picture picture = drawing.createPicture(anchor, imageIndex);
    if (originalSize)
        picture.resize();
}

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

License:Open Source License

private void checkName(Name name) {
    if (!isValidReference(name.getRefersToFormula()))
        throw new IllegalArgumentException("Name '" + name.getNameName() + "' has invalid reference!");
    else if (!existsSheet(name.getSheetName())) {
        // The reference as such is valid but it doesn't point to a (existing) sheet ...
        throw new IllegalArgumentException(
                "Name '" + name.getNameName() + "' does not refer to a valid sheet!");
    }//ww w.j  a v a  2s. co  m
}

From source file:de.jlo.talendcomp.excel.SpreadsheetFile.java

License:Apache License

protected Cell getNamedCell(Name namedCellRef) {
    if (namedCellRef != null) {
        String cellFormula = namedCellRef.getRefersToFormula();
        return getCellByFormula(cellFormula);
    } else {//from  w  ww.ja va2  s .c  om
        return null;
    }
}