Example usage for org.apache.poi.ss.util CellRangeAddress formatAsString

Introduction

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

Prototype

public String formatAsString(String sheetName, boolean useAbsoluteAddress) 

Source Link

Usage

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

License:Open Source License

public void appendNamedRegion(DataFrame data, String name, boolean header) {
    Sheet sheet = workbook.getSheet(getName(name).getSheetName());
    // top, left, bottom, right
    int[] coord = getReferenceCoordinates(name);
    writeData(data, sheet, coord[2] + 1, coord[1], header);
    int bottom = coord[2] + data.rows();
    int right = Math.max(coord[1] + data.columns() - 1, coord[3]);
    CellRangeAddress cra = new CellRangeAddress(coord[0], bottom, coord[1], right);
    String formula = cra.formatAsString(sheet.getSheetName(), true);
    createName(name, formula, true);/*from  w  w w. j  av  a 2  s .  c om*/
}

---

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.TypeSheetGenerator.java

License:Open Source License

private void createNameListFormula() {
    ColumnContext nameColumn = sheetContext.getColumnByPersistentName(SheetContext.NAME_COLUMN);
    if (nameColumn != null) {
        Name rangeName = wbContext.getWb().createName();
        rangeName.setNameName(ExcelGeneratorUtils.createNameForFormulaAllNames(getTypeExpression()));
        CellRangeAddress nameRange = CellRangeAddress.valueOf(nameColumn.getHeaderCellReference());
        nameRange.setFirstRow(sheetContext.getFirstDataRowNumber());
        nameRange.setLastRow(60000); // high constant last row number, so that manually added row entries will be considered as well, not only generated rows

        String nameRangeFormulaBase = "OFFSET({0},0,0,SUMPRODUCT(--({0}<>\"\")),1)"; // shows only non-empty entries in the name list
        String nameRangeFormula = MessageFormat.format(nameRangeFormulaBase,
                nameRange.formatAsString(sheetContext.getSheetName(), true));
        rangeName.setRefersToFormula(nameRangeFormula);
    }/*from w ww . ja v a  2 s.  com*/
}

---

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelGeneratorUtils.java

License:Open Source License

@SuppressWarnings("boxing")
private static String createLookupFormula(String parentColumn, String nameColumn, int currentRow,
        CellRangeAddress matrix, String sheetName) {

    // jme Alte Version ... so nicht sinnvoll
    // jme: prfen!

    int columnCount = matrix.getLastColumn() - matrix.getFirstColumn() + 1;
    return String.format("IF(%1$s%2$s=\"\",\"\",VLOOKUP(%1$s%2$s,%4$s,%5$s,FALSE) & \" : \") & %3$s%2$s",
            parentColumn, currentRow, nameColumn, matrix.formatAsString(sheetName, true), columnCount);

    // return String.format("IF(%1$s%2$s=\"\",\"\",%1$s%2$s & \" : \" & %3$s%2$s)", parentColumn, currentRow, nameColumn); NEUE VERSION schreiben
}

---