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

Example 66 with CellReference

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

the class CheckFunctionsSupported method getEvaluationProblems.

public FormulaEvaluationProblems getEvaluationProblems(Sheet sheet) {
    Set<String> unsupportedFunctions = new HashSet<String>();
    Map<CellReference, Exception> unevaluatableCells = new HashMap<CellReference, Exception>();
    for (Row r : sheet) {
        for (Cell c : r) {
            try {
                evaluator.evaluate(c);
            } catch (Exception e) {
                if (e instanceof NotImplementedException && e.getCause() != null) {
                    // Has been wrapped with cell details, but we know those
                    e = (Exception) e.getCause();
                }
                if (e instanceof NotImplementedFunctionException) {
                    NotImplementedFunctionException nie = (NotImplementedFunctionException) e;
                    unsupportedFunctions.add(nie.getFunctionName());
                }
                unevaluatableCells.put(new CellReference(c), e);
            }
        }
    }
    return new FormulaEvaluationProblems(unsupportedFunctions, unevaluatableCells);
}

Example 67 with CellReference

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

the class CheckFunctionsSupported method main.

public static void main(String[] args) throws Exception {
    if (args.length < 1) {
        System.err.println("Use:");
        System.err.println("  CheckFunctionsSupported <filename>");
        return;
    }
    Workbook wb = WorkbookFactory.create(new File(args[0]));
    CheckFunctionsSupported check = new CheckFunctionsSupported(wb);
    // Fetch all the problems
    List<FormulaEvaluationProblems> problems = new ArrayList<CheckFunctionsSupported.FormulaEvaluationProblems>();
    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        problems.add(check.getEvaluationProblems(sn));
    }
    // Produce an overall summary
    Set<String> unsupportedFunctions = new TreeSet<String>();
    for (FormulaEvaluationProblems p : problems) {
        unsupportedFunctions.addAll(p.unsupportedFunctions);
    }
    if (unsupportedFunctions.isEmpty()) {
        System.out.println("There are no unsupported formula functions used");
    } else {
        System.out.println("Unsupported formula functions:");
        for (String function : unsupportedFunctions) {
            System.out.println("  " + function);
        }
        System.out.println("Total unsupported functions = " + unsupportedFunctions.size());
    }
    // Report sheet by sheet
    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        String sheetName = wb.getSheetName(sn);
        FormulaEvaluationProblems probs = problems.get(sn);
        System.out.println();
        System.out.println("Sheet = " + sheetName);
        if (probs.unevaluatableCells.isEmpty()) {
            System.out.println(" All cells evaluated without error");
        } else {
            for (CellReference cr : probs.unevaluatableCells.keySet()) {
                System.out.println(" " + cr.formatAsString() + " - " + probs.unevaluatableCells.get(cr));
            }
        }
    }
}

Example 68 with CellReference

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

the class SSPerformanceTest method populateCell.

private static double populateCell(Map<String, CellStyle> styles, double value, Calendar calendar, int rowIndex, Row row, int colIndex) {
    Cell cell = row.createCell(colIndex);
    String address = new CellReference(cell).formatAsString();
    switch(colIndex) {
        case 0:
            // column A: default number format
            cell.setCellValue(value++);
            break;
        case 1:
            // column B: #,##0
            cell.setCellValue(value++);
            cell.setCellStyle(styles.get("#,##0.00"));
            break;
        case 2:
            // column C: $#,##0.00
            cell.setCellValue(value++);
            cell.setCellStyle(styles.get("$#,##0.00"));
            break;
        case 3:
            // column D: red bold text on yellow background
            cell.setCellValue(address);
            cell.setCellStyle(styles.get("red-bold"));
            break;
        case 4:
            // column E: boolean
            // TODO booleans are shown as 1/0 instead of TRUE/FALSE
            cell.setCellValue(rowIndex % 2 == 0);
            break;
        case 5:
            // column F:  date / time
            cell.setCellValue(calendar);
            cell.setCellStyle(styles.get("m/d/yyyy"));
            calendar.roll(Calendar.DAY_OF_YEAR, -1);
            break;
        case 6:
        //break;
        default:
            cell.setCellValue(value++);
            break;
    }
    return value;
}

Example 69 with CellReference

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

the class CreatePivotTable method main.

public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    //Create some data to build the pivot table on
    setCellData(sheet);
    AreaReference source = new AreaReference("A1:D4", SpreadsheetVersion.EXCEL2007);
    CellReference position = new CellReference("H5");
    // Create a pivot table on this sheet, with H5 as the top-left cell..
    // The pivot table's data source is on the same sheet in A1:D4
    XSSFPivotTable pivotTable = sheet.createPivotTable(source, position);
    //Configure the pivot table
    //Use first column as row label
    pivotTable.addRowLabel(0);
    //Sum up the second column
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
    //Set the third column as filter
    pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
    //Add filter on forth column
    pivotTable.addReportFilter(3);
    FileOutputStream fileOut = new FileOutputStream("ooxml-pivottable.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 70 with CellReference

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

the class AddDimensionedImage method addImageToSheet.

/**
     * Add an image to a worksheet.
     *
     * @param cellNumber A String that contains the location of the cell whose
     *                   top left hand corner should be aligned with the top
     *                   left hand corner of the image; for example "A1", "A2"
     *                   etc. This is to support the familiar Excel syntax.
     *                   Whilst images are are not actually inserted into cells
     *                   this provides a convenient method of indicating where
     *                   the image should be positioned on the sheet.
     * @param sheet A reference to the sheet that contains the cell referenced
     *              above.
     * @param drawing An instance of the DrawingPatriarch class. This is now
     *                passed into the method where it was, previously, recovered
     *                from the sheet in order to allow multiple pictures be
     *                inserted. If the patriarch was not 'cached in this manner
     *                each time it was created any previously positioned images
     *                would be simply over-written.
     * @param imageFile An instance of the URL class that encapsulates the name
     *                  of and path to the image that is to be 'inserted into'
     *                  the sheet.
     * @param reqImageWidthMM A primitive double that contains the required
     *                        width of the image in millimetres.
     * @param reqImageHeightMM A primitive double that contains the required
     *                         height of the image in millimetres.
     * @param resizeBehaviour A primitive int whose value will determine how
     *                        the code should react if the image is larger than
     *                        the cell referenced by the cellNumber parameter.
     *                        Four constants are provided to determine what
     *                        should happen;
     *                          AddDimensionedImage.EXPAND_ROW
     *                          AddDimensionedImage.EXPAND_COLUMN
     *                          AddDimensionedImage.EXPAND_ROW_AND_COLUMN
     *                          AddDimensionedImage.OVERLAY_ROW_AND_COLUMN
     * @throws java.io.FileNotFoundException If the file containing the image
     *                                       cannot be located.
     * @throws java.io.IOException If a problem occurs whilst reading the file
     *                             of image data.
     * @throws java.lang.IllegalArgumentException If an invalid value is passed
     *                                            to the resizeBehaviour
     *                                            parameter.
     */
public void addImageToSheet(String cellNumber, Sheet sheet, Drawing<?> drawing, URL imageFile, double reqImageWidthMM, double reqImageHeightMM, int resizeBehaviour) throws IOException, IllegalArgumentException {
    // Convert the String into column and row indices then chain the
    // call to the overridden addImageToSheet() method.
    CellReference cellRef = new CellReference(cellNumber);
    this.addImageToSheet(cellRef.getCol(), cellRef.getRow(), sheet, drawing, imageFile, reqImageWidthMM, reqImageHeightMM, resizeBehaviour);
}