Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getRow.

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.ocs.dynamo.importer.impl.BaseXlsImporterTest.java

License:Apache License

/**
 * Test that an exception occurs if an illegal negative value is found
 * /*from  w  w w.j  ava  2  s  .c  o  m*/
 * @throws IOException
 */
@Test
public void testCreateWorkbook_CannotBeNegative() throws IOException {
    byte[] bytes = readFile("importertest_notnegative.xlsx");
    Workbook wb = importer.createWorkbook(bytes);

    Sheet sheet = wb.getSheetAt(0);

    try {
        importer.processRow(0, sheet.getRow(1), PersonDTO.class);
    } catch (OCSImportException ex) {
        Assert.assertEquals("Negative value -1.2 found for field 'factor'", ex.getMessage());
    }
}

From source file:com.ocs.dynamo.importer.impl.BaseXlsImporterTest.java

License:Apache License

/**
 * Test whether a certain row contains a cell with a certain string value
 * /*from   w  w  w  .ja  v a 2 s.  co  m*/
 * @throws IOException
 */
@Test
public void testContainsStringValue() throws IOException {
    byte[] bytes = readFile("importertest.xlsx");
    Workbook wb = importer.createWorkbook(bytes);

    Sheet sheet = wb.getSheetAt(0);

    Assert.assertTrue(importer.containsStringValue(sheet.getRow(0), "Bas"));
    Assert.assertFalse(importer.containsStringValue(sheet.getRow(0), "Bob"));

    Assert.assertFalse(importer.containsStringValue(null, "Bas"));
}

From source file:com.ocs.dynamo.importer.impl.BaseXlsImporterTest.java

License:Apache License

@Test
public void testIsRowEmpty() throws IOException {
    Assert.assertTrue(importer.isRowEmpty(null));

    byte[] bytes = readFile("importertest.xlsx");
    Workbook wb = importer.createWorkbook(bytes);

    Sheet sheet = wb.getSheetAt(0);
    Assert.assertFalse(importer.isRowEmpty(sheet.getRow(0)));

    Assert.assertTrue(importer.isRowEmpty(sheet.getRow(7)));
}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

/**
 * See http://thinktibits.blogspot.co.uk/2012/12/Java-POI-XLS-XLSX-Change-Cell-Font-Color-Example.html
 * Currently only for xlsx/* w ww.j  a v  a2 s .c om*/
 * @param wb
 * @param sheet
 */
private static void styleHeader(Workbook wb, Sheet sheet) {
    if (XSSFWorkbook.class.isInstance(wb) && XSSFSheet.class.isInstance(sheet)) {
        XSSFWorkbook my_workbook = (XSSFWorkbook) wb;
        XSSFCellStyle my_style = my_workbook.createCellStyle();
        XSSFFont my_font = my_workbook.createFont();
        my_font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        my_style.setFont(my_font);

        Row row = sheet.getRow(0);
        if (row != null && row.getFirstCellNum() >= 0) {
            for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    cell.setCellStyle(my_style);
                }
            }
        }
    }
}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

private static Dimension getBoundingBox(Sheet sheet) {
    Dimension ret = new Dimension(0, sheet.getLastRowNum() + 1);
    for (int i = 0; i < ret.height; i++) {
        Row row = sheet.getRow(i);
        if (row != null) {
            ret.width = Math.max(ret.width, row.getLastCellNum());
        }//from  w w w.  ja  va  2  s . co  m
    }
    return ret;
}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

/**
 * Schema table can contain multiple tables...
 * @param sheet/*from   w  w w . j a v a  2  s .  co  m*/
 */
private static SchemaSheetInformation importSchemaTables(Sheet sheet, ExecutionReport report) {
    List<ODLTableReadOnly> tables = new ArrayList<>();

    // schema tables are separated by empty rows
    int lastRow = sheet.getLastRowNum();
    int firstRow = sheet.getFirstRowNum();

    int firstNonEmptyRow = -1;
    int nbCols = 0;
    for (int x = firstRow; x <= lastRow; x++) {

        // check for completely empty row
        Row row = sheet.getRow(x);
        boolean isEmptyRow = true;
        for (int y = 0; row != null && y <= row.getLastCellNum(); y++) {
            if (isEmptyCell(row, y) == false) {
                isEmptyRow = false;
            }
        }

        if (isEmptyRow || x == lastRow) {

            // dump table if row was empty or on last row, but we previously had a non empty row
            if (firstNonEmptyRow != -1) {
                ODLDatastoreAlterable<ODLTableAlterable> tmpDs = ODLDatastoreImpl.alterableFactory.create();
                ODLTableAlterable table = tmpDs.createTable(sheet.getSheetName(), -1);
                importSheetSubset(table, sheet, null, true, firstNonEmptyRow, isEmptyRow ? x - 1 : x, nbCols);
                tables.add(table);
            }
            firstNonEmptyRow = -1;
        } else if (firstNonEmptyRow == -1) {
            // initialise table if we've just found the first non empty row
            firstNonEmptyRow = x;
            nbCols = 0;
            for (int y = 0; y <= row.getLastCellNum(); y++) {
                if (isEmptyCell(row, y)) {
                    break;
                } else {
                    nbCols = y + 1;
                }
            }
        }
    }

    return readSchemaFromODLTables(tables, report);
}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

/**
 * Import the sheet and return key-values if its a schema
 * @param ds/*ww  w  . j a  va 2s .  c  om*/
 * @param sheet
 * @param schema
 * @param isSchemaSheet
 * @return
 */
private static void importSheetSubset(ODLTableAlterable table, Sheet sheet, SchemaIO schema,
        boolean isSchemaSheet, int firstRow, int lastRow, int nbCols) {

    // get column names
    Row header = sheet.getRow(firstRow);
    for (int col = 0; col < nbCols; col++) {

        // try getting schema definition for the column
        String name = null;
        SchemaColumnDefinition dfn = null;
        if (header != null) {
            name = getFormulaSafeTextValue(header.getCell(col));
            if (name != null && schema != null) {
                dfn = schema.findDefinition(sheet.getSheetName(), name);
            }
        }
        name = getValidNewColumnName(name, table);

        // use the schema column definition if we have one
        if (dfn != null) {
            addColumnFromDfn(dfn, name, col, table);
        } else {

            // analyse the other rows for a 'best guess' type
            ODLColumnType chosenType = ODLColumnType.STRING;
            if (isSchemaSheet == false) {
                ColumnTypeEstimator typeEstimator = new ColumnTypeEstimator();
                for (int rowIndx = firstRow + 1; rowIndx <= lastRow; rowIndx++) {
                    Row row = sheet.getRow(rowIndx);
                    String value = getFormulaSafeTextValue(row.getCell(col));
                    typeEstimator.processValue(value);
                }

                chosenType = typeEstimator.getEstimatedType();
            }
            table.addColumn(col, name, chosenType, 0);
        }
    }

    // load all other rows
    for (int rowIndx = firstRow + 1; rowIndx <= lastRow; rowIndx++) {
        Row row = sheet.getRow(rowIndx);
        int outRowIndx = table.createEmptyRow(rowIndx);
        for (int col = 0; col < nbCols; col++) {
            String value = getFormulaSafeTextValue(row.getCell(col));
            table.setValueAt(value, outRowIndx, col);
        }

    }

}

From source file:com.opendoorlogistics.studio.LoadedDatastore.java

License:Open Source License

private void updateWorkbookWithModifications(Workbook wb, ExecutionReport report) {
    // parse the original tables; these will be held in the datastore with the same index as the sheet
    int nbOriginal = originalLoadedDs.getTableCount();
    if (nbOriginal != wb.getNumberOfSheets()) {
        throw new RuntimeException();
    }//from  ww w  .  jav  a  2s. c o  m

    ArrayList<ODLTableReadOnly> oldOnesToReadd = new ArrayList<>();
    for (int i = nbOriginal - 1; i >= 0; i--) {
        ODLTableReadOnly originalTable = originalLoadedDs.getTableAt(i);
        ODLTableReadOnly newTable = ds.getTableByImmutableId(originalTable.getImmutableId());

        if (newTable == null) {
            // table was deleted
            wb.removeSheetAt(i);
        } else if (DatastoreComparer.isSame(originalTable, newTable, DatastoreComparer.CHECK_ALL) == false) {
            Sheet sheet = wb.getSheetAt(i);

            boolean sameStructure = DatastoreComparer.isSameStructure(originalTable, newTable,
                    DatastoreComparer.CHECK_ALL);
            if (sameStructure) {
                // re-write all values but skip the header row
                int nbOversized = 0;
                for (int iRow = 0; iRow < newTable.getRowCount(); iRow++) {
                    int iTargetRow = iRow + 1;
                    Row row = sheet.getRow(iTargetRow);
                    if (row == null) {
                        row = sheet.createRow(iTargetRow);
                    }

                    int nc = newTable.getColumnCount();
                    for (int col = 0; col < nc; col++) {
                        Cell cell = row.getCell(col);
                        if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                            // don't set the value of formula cells...
                            continue;
                        }
                        if (cell == null) {
                            cell = row.createCell(col);
                        }

                        String sval = TableUtils.getValueAsString(newTable, iRow, col);
                        if (sval != null && sval.length() > PoiIO.MAX_CHAR_COUNT_IN_EXCEL_CELL) {
                            nbOversized++;
                        }
                        cell.setCellValue(sval);
                    }
                }

                // delete any rows after the last row (including 1 for the header)
                int lastOKRow = newTable.getRowCount();
                while (sheet.getLastRowNum() > lastOKRow) {
                    sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
                }

                if (nbOversized > 0 && report != null) {
                    report.log(PoiIO.getOversizedWarningMessage(nbOversized, newTable.getName()));
                    ;
                }

            } else {
                // delete and replace. replace after parsing all original tables as we can get table name conflicts
                wb.removeSheetAt(i);
                oldOnesToReadd.add(newTable);
            }

        }

    }

    // re-add any totally replaced tables
    for (ODLTableReadOnly table : oldOnesToReadd) {
        Sheet sheet = wb.createSheet(table.getName());
        if (sheet != null) {
            PoiIO.exportTable(sheet, table, report);
        }
    }

    // add new tables at the end
    for (int i = 0; i < ds.getTableCount(); i++) {
        ODLTableReadOnly newTable = ds.getTableAt(i);
        if (originalLoadedDs.getTableByImmutableId(newTable.getImmutableId()) == null) {
            // new table...
            Sheet sheet = wb.createSheet(newTable.getName());
            if (sheet != null) {
                PoiIO.exportTable(sheet, newTable, report);
            }
        }

    }
}

From source file:com.opengamma.financial.security.equity.GICSCodeDescription.java

License:Open Source License

/**
 *  Load S&P GICS code mappings from an Apace POI HSSFWorkbook 
 * @param workbook HSSFWorkbook to parse S&P GCIS Excel
 * @param gicsMap Map to add mappings to
 *///from  ww  w  .j  a va2s.  c  o  m
static void processGICSExcelWorkbook(Workbook workbook, Map<String, String> gicsMap) {

    //Assume 1 sheet
    Sheet sheet = workbook.getSheetAt(0);
    if (sheet == null) {
        return;
    }
    for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            continue;
        }
        for (int cellNum = 0; cellNum < row.getPhysicalNumberOfCells(); cellNum++) {
            Cell cell = row.getCell(cellNum, Row.CREATE_NULL_AS_BLANK);
            if (isNumeric(cell)) {
                //worst case if the Excel file is in an  incorrect (or updated) format
                // is that number -> random or empty string mappings will be created
                gicsMap.put(getGICSCellValue(cell), getGICSCellValue(row, cellNum + 1));
            }
        }
    }
}

From source file:com.openitech.db.model.ExcelDataSource.java

License:Apache License

@Override
public boolean loadData(boolean reload, int oldRow) {
    boolean result = false;

    if (isDataLoaded && !reload) {
        return false;
    }/*from  w ww.ja v a2s  .c  o  m*/
    if (sourceFile != null) {
        try {
            Workbook workBook = WorkbookFactory.create(new FileInputStream(sourceFile));
            //        HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(sourceFile));
            Sheet sheet = workBook.getSheetAt(0);
            DataFormatter dataFormatter = new DataFormatter(Locale.GERMANY);
            FormulaEvaluator formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator();

            int lastRowNum = sheet.getLastRowNum();

            boolean isFirstLineHeader = true;

            //count = sheet. - (isFirstLineHeader ? 1 : 0);
            int tempCount = 0;
            for (int j = 0; j <= lastRowNum; j++) {
                //zane se z 0
                Row row = row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }

                // display row number in the console.
                System.out.println("Row No.: " + row.getRowNum());
                if (isFirstLineHeader && row.getRowNum() == 0) {
                    populateHeaders(row);
                    continue;
                }
                tempCount++;

                Map<String, DataColumn> values;
                if (rowValues.containsKey(row.getRowNum())) {
                    values = rowValues.get(row.getRowNum());
                } else {
                    values = new HashMap<String, DataColumn>();
                    rowValues.put(row.getRowNum(), values);
                }

                // once get a row its time to iterate through cells.
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i <= lastCellNum; i++) {
                    DataColumn dataColumn = new DataColumn();
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        continue;
                    }
                    System.out.println("Cell No.: " + cell.getColumnIndex());
                    System.out.println("Value: " + dataFormatter.formatCellValue(cell));
                    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator));
                    } else {
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                    }

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        // cell type numeric.
                        System.out.println("Numeric value: " + cell.getNumericCellValue());
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                        break;
                    }
                    case Cell.CELL_TYPE_STRING:
                        // cell type string.
                        System.out.println("String value: " + cell.getStringCellValue());
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        // cell type string.
                        System.out.println("String value: " + cell.getBooleanCellValue());
                        dataColumn.setValue(cell.getBooleanCellValue(), Boolean.class);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        // cell type string.
                        System.out.println(
                                "Formula value: " + dataFormatter.formatCellValue(cell, formulaEvaluator));
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator));
                        break;
                    default:
                        dataColumn.setValue(cell.getStringCellValue(), String.class);
                        break;
                    }

                    values.put(getColumnName(cell.getColumnIndex()).toUpperCase(), dataColumn);

                }
            }

            count = tempCount;

            isDataLoaded = true;
            //se postavim na staro vrstico ali 1
            if (oldRow > 0) {
                absolute(oldRow);
            } else {
                first();
            }

            result = true;
        } catch (Exception ex) {
            Logger.getLogger(ExcelDataSource.class.getName()).log(Level.SEVERE, null, ex);
            result = false;
        }
    }

    return result;
}