Examples with Row org.apache.poi.ss.usermodel.Row used on opensource projects

Example 11 with Row

use of org.apache.poi.ss.usermodel.Row in project poi by apache.

the class UserDefinedFunctionExample method main.

public static void main(String[] args) throws Exception {
    if (args.length != 2) {
        // e.g. src/examples/src/org/apache/poi/ss/examples/formula/mortgage-calculation.xls Sheet1!B4
        System.out.println("usage: UserDefinedFunctionExample fileName cellId");
        return;
    }
    System.out.println("fileName: " + args[0]);
    System.out.println("cell: " + args[1]);
    File workbookFile = new File(args[0]);
    Workbook workbook = WorkbookFactory.create(workbookFile, null, true);
    try {
        String[] functionNames = { "calculatePayment" };
        FreeRefFunction[] functionImpls = { new CalculateMortgage() };
        UDFFinder udfToolpack = new DefaultUDFFinder(functionNames, functionImpls);
        // register the user-defined function in the workbook
        workbook.addToolPack(udfToolpack);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        CellReference cr = new CellReference(args[1]);
        String sheetName = cr.getSheetName();
        Sheet sheet = workbook.getSheet(sheetName);
        int rowIdx = cr.getRow();
        int colIdx = cr.getCol();
        Row row = sheet.getRow(rowIdx);
        Cell cell = row.getCell(colIdx);
        CellValue value = evaluator.evaluate(cell);
        System.out.println("returns value: " + value);
    } finally {
        workbook.close();
    }
}

Example 12 with Row

use of org.apache.poi.ss.usermodel.Row in project poi by apache.

the class SettingExternalFunction method main.

public static void main(String[] args) throws IOException {
    // or new HSSFWorkbook()
    Workbook wb = new XSSFWorkbook();
    // register the add-in
    wb.addToolPack(new BloombergAddIn());
    Sheet sheet = wb.createSheet();
    Row row = sheet.createRow(0);
    row.createCell(0).setCellFormula("BDP(\"GOOG Equity\",\"CHG_PCT_YTD\")/100");
    row.createCell(1).setCellFormula("BDH(\"goog us equity\",\"EBIT\",\"1/1/2005\",\"12/31/2009\",\"per=cy\",\"curr=USD\") ");
    row.createCell(2).setCellFormula("BDS(\"goog us equity\",\"top_20_holders_public_filings\") ");
    FileOutputStream out = new FileOutputStream("bloomberg-demo.xlsx");
    wb.write(out);
    out.close();
    wb.close();
}

Example 13 with Row

use of org.apache.poi.ss.usermodel.Row in project poi by apache.

the class ToHtml method printSheetContent.

private void printSheetContent(Sheet sheet) {
    printColumnHeads();
    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();
        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                    if (content.equals("")) {
                        content = "&nbsp;";
                    }
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

Example 14 with Row

use of org.apache.poi.ss.usermodel.Row in project poi by apache.

the class ToHtml method ensureColumnBounds.

private void ensureColumnBounds(Sheet sheet) {
    if (gotBounds) {
        return;
    }
    Iterator<Row> iter = sheet.rowIterator();
    firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
    endColumn = 0;
    while (iter.hasNext()) {
        Row row = iter.next();
        short firstCell = row.getFirstCellNum();
        if (firstCell >= 0) {
            firstColumn = Math.min(firstColumn, firstCell);
            endColumn = Math.max(endColumn, row.getLastCellNum());
        }
    }
    gotBounds = true;
}

Example 15 with Row

use of org.apache.poi.ss.usermodel.Row in project poi by apache.

the class HSSFOptimiser method optimiseFonts.

/**
	 * Goes through the Workbook, optimising the fonts by
	 *  removing duplicate ones.
	 * For now, only works on fonts used in {@link HSSFCellStyle}
	 *  and {@link HSSFRichTextString}. Any other font uses
	 *  (eg charts, pictures) may well end up broken!
	 * This can be a slow operation, especially if you have
	 *  lots of cells, cell styles or rich text strings
	 * @param workbook The workbook in which to optimise the fonts
	 */
public static void optimiseFonts(HSSFWorkbook workbook) {
    // Where each font has ended up, and if we need to
    //  delete the record for it. Start off with no change
    short[] newPos = new short[workbook.getWorkbook().getNumberOfFontRecords() + 1];
    boolean[] zapRecords = new boolean[newPos.length];
    for (int i = 0; i < newPos.length; i++) {
        newPos[i] = (short) i;
        zapRecords[i] = false;
    }
    // Get each font record, so we can do deletes
    //  without getting confused
    FontRecord[] frecs = new FontRecord[newPos.length];
    for (int i = 0; i < newPos.length; i++) {
        // There is no 4!
        if (i == 4)
            continue;
        frecs[i] = workbook.getWorkbook().getFontRecordAt(i);
    }
    // Note - don't change built in fonts (those before 5)
    for (int i = 5; i < newPos.length; i++) {
        // Check this one for being a duplicate
        //  of an earlier one
        int earlierDuplicate = -1;
        for (int j = 0; j < i && earlierDuplicate == -1; j++) {
            if (j == 4)
                continue;
            FontRecord frCheck = workbook.getWorkbook().getFontRecordAt(j);
            if (frCheck.sameProperties(frecs[i])) {
                earlierDuplicate = j;
            }
        }
        // If we got a duplicate, mark it as such
        if (earlierDuplicate != -1) {
            newPos[i] = (short) earlierDuplicate;
            zapRecords[i] = true;
        }
    }
    // Only need to worry about user fonts
    for (int i = 5; i < newPos.length; i++) {
        // Find the number deleted to that
        //  point, and adjust
        short preDeletePos = newPos[i];
        short newPosition = preDeletePos;
        for (int j = 0; j < preDeletePos; j++) {
            if (zapRecords[j])
                newPosition--;
        }
        // Update the new position
        newPos[i] = newPosition;
    }
    // Zap the un-needed user font records
    for (int i = 5; i < newPos.length; i++) {
        if (zapRecords[i]) {
            workbook.getWorkbook().removeFontRecord(frecs[i]);
        }
    }
    // Tell HSSFWorkbook that it needs to
    //  re-start its HSSFFontCache
    workbook.resetFontCache();
    //  new locations of the fonts
    for (int i = 0; i < workbook.getWorkbook().getNumExFormats(); i++) {
        ExtendedFormatRecord xfr = workbook.getWorkbook().getExFormatAt(i);
        xfr.setFontIndex(newPos[xfr.getFontIndex()]);
    }
    // Update the rich text strings to point at
    //  the new locations of the fonts
    // Remember that one underlying unicode string
    //  may be shared by multiple RichTextStrings!
    HashSet<UnicodeString> doneUnicodeStrings = new HashSet<UnicodeString>();
    for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
        HSSFSheet s = workbook.getSheetAt(sheetNum);
        for (Row row : s) {
            for (Cell cell : row) {
                if (cell.getCellTypeEnum() == CellType.STRING) {
                    HSSFRichTextString rtr = (HSSFRichTextString) cell.getRichStringCellValue();
                    UnicodeString u = rtr.getRawUnicodeString();
                    // Have we done this string already?
                    if (!doneUnicodeStrings.contains(u)) {
                        // Update for each new position
                        for (short i = 5; i < newPos.length; i++) {
                            if (i != newPos[i]) {
                                u.swapFontUse(i, newPos[i]);
                            }
                        }
                        // Mark as done
                        doneUnicodeStrings.add(u);
                    }
                }
            }
        }
    }
}