Examples with CellReference org.apache.poi.ss.util.CellReference used on opensource projects

Example 11 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class CellStyleDetails method main.

public static void main(String[] args) throws Exception {
    if (args.length == 0) {
        throw new IllegalArgumentException("Filename must be given");
    }
    Workbook wb = WorkbookFactory.create(new File(args[0]));
    DataFormatter formatter = new DataFormatter();
    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        Sheet sheet = wb.getSheetAt(sn);
        System.out.println("Sheet #" + sn + " : " + sheet.getSheetName());
        for (Row row : sheet) {
            System.out.println("  Row " + row.getRowNum());
            for (Cell cell : row) {
                CellReference ref = new CellReference(cell);
                System.out.print("    " + ref.formatAsString());
                System.out.print(" (" + cell.getColumnIndex() + ") ");
                CellStyle style = cell.getCellStyle();
                System.out.print("Format=" + style.getDataFormatString() + " ");
                System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " ");
                System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " ");
                Font font = wb.getFontAt(style.getFontIndex());
                System.out.print("Font=" + font.getFontName() + " ");
                System.out.print("FontColor=");
                if (font instanceof HSSFFont) {
                    System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb)));
                }
                if (font instanceof XSSFFont) {
                    System.out.print(renderColor(((XSSFFont) font).getXSSFColor()));
                }
                System.out.println();
                System.out.println("        " + formatter.formatCellValue(cell));
            }
        }
        System.out.println();
    }
    wb.close();
}

Example 12 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class CreateTable method main.

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet) wb.createSheet();
    //Create 
    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(2, 2));
    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("0");
            }
        }
    }
    FileOutputStream fileOut = new FileOutputStream("ooxml-table.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 13 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class ForkedEvaluationSheet method getOrCreateUpdatableCell.

public ForkedEvaluationCell getOrCreateUpdatableCell(int rowIndex, int columnIndex) {
    RowColKey key = new RowColKey(rowIndex, columnIndex);
    ForkedEvaluationCell result = _sharedCellsByRowCol.get(key);
    if (result == null) {
        EvaluationCell mcell = _masterSheet.getCell(rowIndex, columnIndex);
        if (mcell == null) {
            CellReference cr = new CellReference(rowIndex, columnIndex);
            throw new UnsupportedOperationException("Underlying cell '" + cr.formatAsString() + "' is missing in master sheet.");
        }
        result = new ForkedEvaluationCell(this, mcell);
        _sharedCellsByRowCol.put(key, result);
    }
    return result;
}

Example 14 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class Address method evaluate.

public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
    if (args.length < 2 || args.length > 5) {
        return ErrorEval.VALUE_INVALID;
    }
    try {
        boolean pAbsRow, pAbsCol;
        int row = (int) NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
        int col = (int) NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
        int refType;
        if (args.length > 2 && args[2] != MissingArgEval.instance) {
            refType = (int) NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
        } else {
            // this is also the default if parameter is not given
            refType = REF_ABSOLUTE;
        }
        switch(refType) {
            case REF_ABSOLUTE:
                pAbsRow = true;
                pAbsCol = true;
                break;
            case REF_ROW_ABSOLUTE_COLUMN_RELATIVE:
                pAbsRow = true;
                pAbsCol = false;
                break;
            case REF_ROW_RELATIVE_RELATIVE_ABSOLUTE:
                pAbsRow = false;
                pAbsCol = true;
                break;
            case REF_RELATIVE:
                pAbsRow = false;
                pAbsCol = false;
                break;
            default:
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
        }
        //            boolean a1;
        //            if(args.length > 3){
        //                ValueEval ve = OperandResolver.getSingleValue(args[3], srcRowIndex, srcColumnIndex);
        //                // TODO R1C1 style is not yet supported
        //                a1 = ve == MissingArgEval.instance ? true : OperandResolver.coerceValueToBoolean(ve, false);
        //            } else {
        //                a1 = true;
        //            }
        String sheetName;
        if (args.length == 5) {
            ValueEval ve = OperandResolver.getSingleValue(args[4], srcRowIndex, srcColumnIndex);
            sheetName = ve == MissingArgEval.instance ? null : OperandResolver.coerceValueToString(ve);
        } else {
            sheetName = null;
        }
        CellReference ref = new CellReference(row - 1, col - 1, pAbsRow, pAbsCol);
        StringBuffer sb = new StringBuffer(32);
        if (sheetName != null) {
            SheetNameFormatter.appendFormat(sb, sheetName);
            sb.append('!');
        }
        sb.append(ref.formatAsString());
        return new StringEval(sb.toString());
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
}

Example 15 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class XSSFRow method shift.

/**
     * update cell references when shifting rows
     *
     * @param n the number of rows to move
     */
protected void shift(int n) {
    int rownum = getRowNum() + n;
    CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain();
    int sheetId = (int) _sheet.sheet.getSheetId();
    String msg = "Row[rownum=" + getRowNum() + "] contains cell(s) included in a multi-cell array formula. " + "You cannot change part of an array.";
    for (Cell c : this) {
        XSSFCell cell = (XSSFCell) c;
        if (cell.isPartOfArrayFormulaGroup()) {
            cell.notifyArrayFormulaChanging(msg);
        }
        //remove the reference in the calculation chain
        if (calcChain != null)
            calcChain.removeItem(sheetId, cell.getReference());
        CTCell ctCell = cell.getCTCell();
        String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
        ctCell.setR(r);
    }
    setRowNum(rownum);
}