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

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

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

Introduction

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

Prototype

public AreaReference(String reference, SpreadsheetVersion version)

Source Link

Document

Create an area ref from a string representation.

Usage

From source file:CreateTable.java

License:Apache License

public static void main(String[] args) throws FileNotFoundException, IOException {

    Workbook wb = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet) wb.createSheet();

    //Create //  w  w w  .  java2s .c  om
    XSSFTable table = sheet.createTable();
    table.setDisplayName("Test");
    CTTable cttable = table.getCTTable();

    //Style configurations
    CTTableStyleInfo style = cttable.addNewTableStyleInfo();
    style.setName("TableStyleMedium2");
    style.setShowColumnStripes(false);
    style.setShowRowStripes(true);

    //Set which area the table should be placed in
    AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(3, 3));
    cttable.setRef(reference.formatAsString());
    cttable.setId(1);
    cttable.setName("Test");
    cttable.setTotalsRowCount(1);

    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3);
    CTTableColumn column;
    XSSFRow row;
    XSSFCell cell;
    for (int i = 0; i < 3; i++) {
        //Create column
        column = columns.addNewTableColumn();
        column.setName("Column");
        column.setId(i + 1);
        //Create row
        row = sheet.createRow(i);
        for (int j = 0; j < 3; j++) {
            //Create cell
            cell = row.createCell(j);
            if (i == 0) {
                cell.setCellValue("Column" + j);
            } else {
                cell.setCellValue(i + j + 0.0);
            }
        }
    }

    FileOutputStream fileOut = new FileOutputStream("ooxml-table.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:android_connector.ExcelReader.java

/**
 * Gibt den Wert einer Zelle zurck.//from w w  w  .jav a 2s.  c  o m
 * @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.Area.java

License:Apache License

public AreaReference toAreaReference() {
    return new AreaReference(topLeft.toCellReference(), bottomRight.toCellReference());
}

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

License:Apache License

public static AreaReference getAreaForRange(HSSFWorkbook workbook, String rangeName) {
    int rangeNameIdx = workbook.getNameIndex(rangeName);
    if (rangeNameIdx == -1)
        return null;

    HSSFName aNamedRange = workbook.getNameAt(rangeNameIdx);
    return new AreaReference(aNamedRange.getRefersToFormula(), SpreadsheetVersion.EXCEL97);
}

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

License:Apache License

/**
 * Method writes horizontal band/*from  w  ww.ja  v a  2  s  . c om*/
 * 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

/**
 * Method writes vertical band//from   w ww  . j  a v a 2 s.  c om
 * Note: no child support for vertical band ;)
 *
 * @param band          - band to write
 * @param templateSheet - template sheet
 * @param resultSheet   - result sheet
 */
protected void writeVerticalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) {
    String rangeName = band.getName();
    CellReference[] crefs = getRangeContent(templateWorkbook, rangeName);

    Set<Integer> addedRowNumbers = new HashSet<Integer>();

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

        Bounds thisBounds = templateBounds.get(band.getName());
        Bounds parentBounds = templateBounds.get(band.getParentBand().getName());
        Range parentRange = bandsToResultRanges.get(band.getParentBand());

        int localRowNum = parentBounds != null && parentRange != null
                ? parentRange.getFirstRow() - 1 + thisBounds.row0 - parentBounds.row0
                : rownum;

        colnum = colnum == 0 ? getCellFromReference(crefs[0], templateSheet).getColumnIndex() : colnum;
        copyMergeRegions(resultSheet, rangeName, localRowNum, colnum);

        int firstRow = crefs[0].getRow();
        int firstColumn = crefs[0].getCol();

        for (CellReference cref : crefs) {//create necessary rows
            int currentRow = cref.getRow();
            final int rowOffset = currentRow - firstRow;
            if (!rowExists(resultSheet, localRowNum + rowOffset)) {
                HSSFRow resultRow = resultSheet.createRow(localRowNum + rowOffset);
                copyPageBreaks(templateSheet, resultSheet, cref.getRow(), resultRow.getRowNum());
            }
            addedRowNumbers.add(cref.getRow());
        }

        CellReference topLeft = null;
        CellReference bottomRight = null;
        for (CellReference cref : crefs) {
            int currentRow = cref.getRow();
            int currentColumn = cref.getCol();
            final int rowOffset = currentRow - firstRow;
            final int columnOffset = currentColumn - firstColumn;

            HSSFCell templateCell = getCellFromReference(cref, templateSheet);
            resultSheet.setColumnWidth(colnum + columnOffset,
                    templateSheet.getColumnWidth(templateCell.getColumnIndex()));
            HSSFCell resultCell = copyCellFromTemplate(templateCell,
                    resultSheet.getRow(localRowNum + rowOffset), colnum + columnOffset, band);
            if (topLeft == null) {
                topLeft = new CellReference(resultCell);
            }
            bottomRight = new CellReference(resultCell);
        }

        colnum += crefs[crefs.length - 1].getCol() - firstColumn + 1;

        AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName);
        AreaReference resultRange = new AreaReference(topLeft, bottomRight);
        areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.VERTICAL, templateRange),
                new Area(band.getName(), Area.AreaAlign.VERTICAL, 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 first level vertical bands we should increase rownum by number of rows added by vertical band
    //nested vertical bands do not add rows, they use parent space
    if (BandData.ROOT_BAND_NAME.equals(band.getParentBand().getName())) {
        List<BandData> sameBands = band.getParentBand().getChildrenByName(band.getName());
        if (sameBands.size() > 0 && sameBands.get(sameBands.size() - 1) == band) {//check if this vertical band is last vertical band with same name
            rownum += addedRowNumbers.size();
            //      rowsAddedByVerticalBand = 0;
        }
    }
}

From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java

License:MIT License

/**
 * Utility function to update a scatterplot line's data series.
 * @param data the datagraph data.// w w w .j  ava2s.c  o m
 * @param sheet the Excel sheet which contains corresponding data from the scatterplot data series.
 * @param seriesIdx the index of the data in the dategraph data.
 * @param series the XML object representing the series in the chart.
 */
private static void updateCTScatterSer(final DategraphData data, final XSSFSheet sheet, final int seriesIdx,
        final CTScatterSer series) {
    final String sheetName = sheet.getSheetName();

    // the series idx starts from 0
    final DategraphData.Row row = data.getRows().get(seriesIdx);
    final String title = row.getLabel();
    final Color color = Color.decode(row.getColor());

    series.getOrder().setVal(seriesIdx);
    series.getIdx().setVal(seriesIdx);

    final CTSolidColorFillProperties fill = series.getSpPr().getLn().getSolidFill();

    // We have to set any possible colour type, PowerPoint throws an error if there's multiple fills, and we don't
    //   know what colour type the user may have used in their template slide.
    if (fill.getSchemeClr() != null) {
        fill.unsetSchemeClr();
    }
    if (fill.getSrgbClr() != null) {
        fill.unsetSrgbClr();
    }
    if (fill.getHslClr() != null) {
        fill.unsetHslClr();
    }
    if (fill.getPrstClr() != null) {
        fill.unsetPrstClr();
    }
    if (fill.getScrgbClr() != null) {
        fill.unsetScrgbClr();
    }
    if (fill.getSysClr() != null) {
        fill.unsetSysClr();
    }

    final CTSRgbColor fillClr = fill.addNewSrgbClr();
    final byte[] colorBytes = { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() };
    fillClr.setVal(colorBytes);

    final CTMarker marker = series.getMarker();

    if (marker != null) {
        final CTShapeProperties markerSpPr = marker.getSpPr();
        unsetSpPrFills(markerSpPr);
        markerSpPr.addNewSolidFill().addNewSrgbClr().setVal(colorBytes);

        final CTLineProperties markerLn = markerSpPr.getLn();
        if (markerLn != null) {
            unsetLineFills(markerLn);
            markerLn.addNewSolidFill().addNewSrgbClr().setVal(colorBytes);
        }
    }

    final CTStrRef strRef = series.getTx().getStrRef();
    strRef.getStrCache().getPtArray()[0].setV(title);

    strRef.setF(new CellReference(sheetName, 0, seriesIdx + 1, true, true).formatAsString());

    final long[] timestamps = data.getTimestamps();
    {
        final CTNumRef timestampCatNumRef = series.getXVal().getNumRef();
        timestampCatNumRef.setF(new AreaReference(new CellReference(sheetName, 1, 0, true, true),
                new CellReference(sheetName, 1 + timestamps.length, 0, true, true)).formatAsString());

        final CTNumData timeStampCatNumCache = timestampCatNumRef.getNumCache();
        timeStampCatNumCache.getPtCount().setVal(timestamps.length);
        timeStampCatNumCache.setPtArray(null);

        for (int ii = 0; ii < timestamps.length; ++ii) {
            final CTNumVal pt = timeStampCatNumCache.addNewPt();
            pt.setIdx(ii);
            pt.setV(sheet.getRow(1 + ii).getCell(0).getRawValue());
        }
    }

    {
        final double[] seriesData = row.getValues();

        final CTNumRef valuesNumRef = series.getYVal().getNumRef();
        valuesNumRef.setF(new AreaReference(new CellReference(sheetName, 1, seriesIdx + 1, true, true),
                new CellReference(sheetName, 1 + timestamps.length, seriesIdx + 1, true, true))
                        .formatAsString());

        final CTNumData valuesNumCache = valuesNumRef.getNumCache();
        valuesNumCache.getPtCount().setVal(timestamps.length);
        valuesNumCache.setPtArray(null);

        for (int ii = 0; ii < timestamps.length; ++ii) {
            final CTNumVal pt = valuesNumCache.addNewPt();
            pt.setIdx(ii);
            pt.setV(Double.toString(seriesData[ii]));
        }
    }
}

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

License:Apache License

private boolean extendTable(XSSFTable table, int firstRow, int firstCol, int lastRow) throws Exception {
    try {/*from  w w w .  j  a v a  2 s  .  co  m*/
        AreaReference currentRef = null;
        if (currentType == SpreadsheetTyp.XLS) {
            currentRef = new AreaReference(table.getCTTable().getRef(), SpreadsheetVersion.EXCEL97);
        } else {
            currentRef = new AreaReference(table.getCTTable().getRef(), SpreadsheetVersion.EXCEL2007);
        }
        CellReference topLeft = currentRef.getFirstCell();
        CellReference buttomRight = currentRef.getLastCell();
        if (topLeft.getRow() <= firstRow && buttomRight.getRow() >= firstRow && topLeft.getCol() <= firstCol
                && buttomRight.getCol() >= firstCol) {
            // this table is within our write area, we have to expand it
            AreaReference newRef = new AreaReference(topLeft, // left top including the header line
                    new CellReference(lastRow, buttomRight.getCol())); // bottom right
            table.getCTTable().setRef(newRef.formatAsString());
            return true;
        } else {
            return false;
        }
    } catch (Exception t) {
        if (workbook instanceof SXSSFWorkbook) {
            throw new Exception(
                    "Extending table ranges cannot work in a workbook which is not fully loaded because of the memory saving mode. Uncheck Memory saving mode in tFileExcelWorkbookOpen!",
                    t);
        } else {
            throw t;
        }
    }
}

From source file:edu.cmu.emfta.actions.CutSet.java

License:Open Source License

public XSSFWorkbook toSingleSheetWorkbook() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    int cutSetIdentifier = 0;
    double cutsetProbability;

    XSSFSheet sheet = workbook.createSheet();

    XSSFTable table = sheet.createTable();
    table.setDisplayName("Cutsets");
    CTTable cttable = table.getCTTable();

    // Set which area the table should be placed in
    AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(2, 2));
    cttable.setRef(reference.formatAsString());
    cttable.setId((long) 1);
    cttable.setName("Cutsets");
    cttable.setTotalsRowCount((long) 1);

    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount((long) 3);
    CTTableColumn column;/*  www .ja v a  2s.  co  m*/
    XSSFRow row;
    XSSFCell cell;

    column = columns.addNewTableColumn();

    // Create row
    row = sheet.createRow(0);
    CellStyle headingCellStyle = workbook.createCellStyle();
    XSSFFont headingFont = workbook.createFont();
    headingFont.setBold(true);
    headingCellStyle.setFont(headingFont);
    row.setRowStyle(headingCellStyle);

    CellStyle normalCellStyle = workbook.createCellStyle();
    XSSFFont normalFont = workbook.createFont();
    normalFont.setBold(false);
    normalCellStyle.setFont(normalFont);

    for (int j = 0; j < 3; j++) {
        // Create cell
        cell = row.createCell(j);

        switch (j) {
        case 0: {
            cell.setCellValue("Identifier");
            break;
        }
        case 1: {
            cell.setCellValue("Description");
            break;
        }
        case 2: {
            cell.setCellValue("Probability");
            break;
        }

        }
    }

    int rowId = 1;

    for (List<Event> events : cutset) {
        row = sheet.createRow(rowId++);
        row = sheet.createRow(rowId++);
        row.setRowStyle(normalCellStyle);

        cell = row.createCell(0);
        cell.setCellValue("Cutset #" + cutSetIdentifier);

        cutsetProbability = 1;
        for (int i = 0; i < events.size(); i++) {
            cutsetProbability = cutsetProbability * events.get(i).getProbability();
        }

        cell = row.createCell(2);
        if (cutsetProbability != 1) {
            cell.setCellValue("" + cutsetProbability);
        } else {
            cell.setCellValue("" + cutsetProbability);
        }
        //         System.out.println("[CutSet] cutset id=" + cutSetIdentifier);

        for (int i = 0; i < events.size(); i++) {
            Event e = events.get(i);

            //            System.out.println("[CutSet] event name=" + e.getName());

            // Create row
            row = sheet.createRow(rowId++);
            row.setRowStyle(normalCellStyle);
            for (int j = 0; j < 3; j++) {
                // Create cell
                cell = row.createCell(j);

                switch (j) {
                case 0: {
                    cell.setCellValue(e.getName());
                    break;
                }
                case 1: {
                    cell.setCellValue(e.getDescription());
                    break;
                }
                case 2: {
                    cell.setCellValue(e.getProbability());
                    break;
                }

                }
            }
        }
        cutSetIdentifier = cutSetIdentifier + 1;
    }

    return workbook;

}

From source file:edu.cmu.emfta.actions.CutSet.java

License:Open Source License

public XSSFWorkbook toMultiSheetsWorkbook() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    int cutSetIdentifier = 0;
    double cutsetProbability;

    for (List<Event> events : cutset) {

        cutsetProbability = 1;//  w  w w . j a v a2  s  .  c o  m
        for (int i = 0; i < events.size(); i++) {
            cutsetProbability = cutsetProbability * events.get(i).getProbability();
        }

        //         System.out.println("[CutSet] cutset id=" + cutSetIdentifier);
        XSSFSheet sheet = workbook.createSheet();

        XSSFTable table = sheet.createTable();
        table.setDisplayName("Cutset");
        CTTable cttable = table.getCTTable();

        // Set which area the table should be placed in
        AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(2, 2));
        cttable.setRef(reference.formatAsString());
        cttable.setId((long) 1);
        cttable.setName("Cutset " + cutSetIdentifier);
        cttable.setTotalsRowCount((long) 1);

        CTTableColumns columns = cttable.addNewTableColumns();
        columns.setCount((long) 3);
        CTTableColumn column;
        XSSFRow row;
        XSSFCell cell;

        column = columns.addNewTableColumn();

        // Create row
        row = sheet.createRow(0);
        CellStyle headingCellStyle = workbook.createCellStyle();
        XSSFFont headingFont = workbook.createFont();
        headingFont.setBold(true);
        headingCellStyle.setFont(headingFont);
        row.setRowStyle(headingCellStyle);

        CellStyle normalCellStyle = workbook.createCellStyle();
        XSSFFont normalFont = workbook.createFont();
        normalFont.setBold(false);
        normalCellStyle.setFont(normalFont);

        for (int j = 0; j < 3; j++) {
            // Create cell
            cell = row.createCell(j);

            switch (j) {
            case 0: {
                cell.setCellValue("Identifier");
                break;
            }
            case 1: {
                cell.setCellValue("Description");
                break;
            }
            case 2: {
                if (cutsetProbability == 1) {
                    cell.setCellValue("Probability");
                } else {
                    cell.setCellValue("Probability (" + cutsetProbability + ")");
                }
                break;
            }

            }
        }

        for (int i = 0; i < events.size(); i++) {
            Event e = events.get(i);

            System.out.println("[CutSet] event name=" + e.getName());
            // Create column
            column = columns.addNewTableColumn();
            column.setName("Column");
            column.setId((long) i + 1);
            // Create row
            row = sheet.createRow(i + 1);
            row.setRowStyle(normalCellStyle);
            for (int j = 0; j < 3; j++) {
                // Create cell
                cell = row.createCell(j);

                switch (j) {
                case 0: {
                    cell.setCellValue(e.getName());
                    break;
                }
                case 1: {
                    cell.setCellValue(e.getDescription());
                    break;
                }
                case 2: {
                    cell.setCellValue(e.getProbability());
                    break;
                }

                }
            }
        }
        cutSetIdentifier = cutSetIdentifier + 1;
    }

    return workbook;

}