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: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  www  .j ava2 s .co  m*/
            final CellRange cellRange = (CellRange) parser.rangeOrCellRefA1();
            _spreadsheet.defineModelRangeName(rangeName, cellRange);
        } catch (ParseException e) {
            // Ignore all non 'named range' names
        }
    }
}

From source file:org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.java

License:Open Source License

private Cell getCellByName(String _name, Workbook _workbook, BaseSpreadsheet _spreadsheet) {
    final Name name = _workbook.getName(_name);
    if (name == null)
        return null;
    if (name.isFunctionName())
        return null;

    final String cellRangeAddress = name.getRefersToFormula();

    final ExpressionParser parser = new SpreadsheetExpressionParserA1OOXML(cellRangeAddress, _spreadsheet);
    try {//from w  ww.  j  ava 2s  .c o m
        final CellRange range = (CellRange) parser.rangeOrCellRefA1();
        if (!(range instanceof CellIndex)) {
            return null;
        }
        final CellIndex cellIndex = (CellIndex) range;
        final int sheetIndex = cellIndex.getSheetIndex();
        final int rowIndex = cellIndex.getRowIndex();
        final int columnIndex = cellIndex.getColumnIndex();
        return _workbook.getSheetAt(sheetIndex).getRow(rowIndex).getCell(columnIndex);
    } catch (ParseException e) {
        // Ignore all non 'named range' names
        return null;
    }
}

From source file:org.jreserve.gui.poi.ExcelUtil.java

License:Open Source License

public static CellReference getCellReference(Workbook wb, String ref) {
    Name name = wb.getName(ref);
    if (name != null)
        ref = name.getRefersToFormula();

    if (ref.indexOf(':') > 0) {
        AreaReference aRef = new AreaReference(ref);
        return aRef.getFirstCell();
    } else {//from   ww w .jav a2 s.  com
        return new CellReference(ref);
    }
}

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

License:Open Source License

private void finishSheet(HSSFSheet sheet, HSSFWorkbook wb, String qcName, String qcType, String sheetName) {
    int i, columnIndex;
    ArrayList<DictionaryDO> tempQcColumns;
    DictionaryDO dict;/*from  ww w. jav a  2 s  . c om*/
    HashSet<Integer> emptyColumns;
    Name rangeName;
    Row row;
    String rangeFormula;

    if (qcColumns != null && !qcColumns.isEmpty())
        row = sheet.getRow(32);
    else
        row = sheet.getRow(3);
    emptyColumns = new HashSet<Integer>();
    for (i = 0; i < row.getLastCellNum(); i++) {
        if (i >= maxChars.size() || maxChars.get(i) == 0)
            emptyColumns.add(i);
    }

    setHeaderCells(sheet, qcName, qcType, sheetName);

    if (qcColumns != null && !qcColumns.isEmpty()) {
        tempQcColumns = new ArrayList<DictionaryDO>();
        tempQcColumns.addAll(qcColumns);
        for (i = tempQcColumns.size() - 1; i > -1; i--) {
            if (emptyColumns.contains(i + 5)) {
                tempQcColumns.remove(i);
                removeColumn(sheet, i + 5);
                maxChars.remove(i + 5);
            }
        }

        rangeName = getName(wb, sheet, "RowNumber");
        if (rangeName == null) {
            rangeName = wb.createName();
            rangeName.setSheetIndex(wb.getSheetIndex(sheet));
            rangeName.setNameName("RowNumber");
        }
        rangeFormula = sheet.getSheetName() + "!$" + CellReference.convertNumToColString(0) + "$33:" + "$"
                + CellReference.convertNumToColString(0) + "$" + (sheet.getLastRowNum() + 1);
        rangeName.setRefersToFormula(rangeFormula);

        /*
         * Create named ranges for the graph to be able to locate the appropriate
         * data
         */
        columnIndex = 5;
        for (i = 0; i < tempQcColumns.size(); i++) {
            dict = tempQcColumns.get(i);
            if (!DataBaseUtil.isEmpty(dict.getCode())) {
                rangeName = getName(wb, sheet, dict.getCode());
                if (rangeName == null) {
                    rangeName = wb.createName();
                    rangeName.setSheetIndex(wb.getSheetIndex(sheet));
                    rangeName.setNameName(dict.getCode());
                }
                rangeFormula = rangeName.getRefersToFormula();
                if (rangeFormula != null && rangeFormula.length() > 0
                        && !"$A$2".equals(rangeFormula.substring(rangeFormula.indexOf("!") + 1)))
                    rangeFormula += ",";
                else
                    rangeFormula = "";
                rangeFormula += sheet.getSheetName() + "!$" + CellReference.convertNumToColString(columnIndex)
                        + "$33:" + "$" + CellReference.convertNumToColString(columnIndex) + "$"
                        + (sheet.getLastRowNum() + 1);
                rangeName.setRefersToFormula(rangeFormula);
            }
            columnIndex++;
        }
        /*
         * make each column wide enough to show the longest string in it; the
         * width for each column is set as the maximum number of characters in
         * that column multiplied by 256; this is because the default width of
         * one character is 1/256 units in Excel
         */
        for (i = 5; i < maxChars.size(); i++)
            sheet.setColumnWidth(i, maxChars.get(i) * 256);
    } else if (worksheetHeaders != null && worksheetHeaders.size() > 0) {
        /*
         * make each column wide enough to show the longest string in it; the
         * width for each column is set as the maximum number of characters in
         * that column multiplied by 256; this is because the default width of
         * one character is 1/256 units in Excel
         */
        for (i = 0; i < maxChars.size(); i++)
            sheet.setColumnWidth(i, maxChars.get(i) * 256);
    }

    wb.setSheetName(wb.getSheetIndex(sheet), sheetName);
    sheet.setForceFormulaRecalculation(true);
    maxChars.clear();
}

From source file:uk.co.spudsoft.birt.emitters.excel.handlers.AbstractHandler.java

License:Open Source License

protected void createName(HandlerState state, String bookmark, int row1, int col1, int row2, int col2) {
    CellReference crFirst = new CellReference(state.currentSheet.getSheetName(), row1, col1, true, true);
    CellReference crLast = new CellReference(row2, col2, true, true);
    String formula = crFirst.formatAsString() + ":" + crLast.formatAsString();

    Name name = state.currentSheet.getWorkbook().getName(bookmark);
    if (name == null) {
        name = state.currentSheet.getWorkbook().createName();
        name.setNameName(bookmark);//from  w  w w  . jav a  2  s .co m
        name.setRefersToFormula(formula);
    } else {
        String existingFormula = name.getRefersToFormula();
        try {
            name.setRefersToFormula(existingFormula + "," + formula);
        } catch (FormulaParseException ex) {
            log.warn(0, "Unable to add \"" + formula + "\" to name (\"" + bookmark
                    + "\") with existing formula: " + existingFormula, ex);
        }
    }
}

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  w  w  . j a  va 2 s  .c  o 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()));
    }
}

From source file:utilities.DocumentXLSManager.java

License:Open Source License

public void create(Connection sd, String remoteUser, ArrayList<KeyValue> data, OutputStream outputStream,
        String basePath, int oId) throws Exception {

    FileInputStream templateIS = null;
    String templateName = "ewarn_report_template.xlsx";
    File templateFile = GeneralUtilityMethods.getDocumentTemplate(basePath, "ewarn_report_template.xlsx", oId);
    try {//from ww  w  . j  a  v a 2 s.c  om
        lm.writeLog(sd, 0, remoteUser, "error", "Failed to open template: " + templateName);
        templateIS = new FileInputStream(templateFile);
    } catch (Exception e) {
        throw e;
    } finally {
        templateIS.close();
    }
    wb = new XSSFWorkbook(templateIS);
    templateIS.close();

    Sheet sheet = wb.getSheetAt(0);
    for (KeyValue kv : data) {
        int namedCellIdx = wb.getNameIndex(kv.k);
        Name aNamedCell = wb.getNameAt(namedCellIdx);

        AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula(), null);
        CellReference[] crefs = aref.getAllReferencedCells();

        for (int i = 0; i < crefs.length; i++) {
            Sheet s = wb.getSheet(crefs[i].getSheetName());
            Row r = sheet.getRow(crefs[i].getRow());
            Cell cell = r.getCell(crefs[i].getCol());
            // extract the cell contents based on cell type etc.
            cell.setCellValue(new Double(kv.v));
        }

    }
    XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
    wb.write(outputStream);
    wb.write(outputStream);
    outputStream.close();

}