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

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

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

Introduction

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

Prototype

int getFirstRowNum();

Source Link

Document

Gets the first row on the sheet.

Usage

From source file:org.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy sheets./*from   w w w . j a v  a2  s.com*/
 *
 * @param _newSheet the new sheet
 * @param _sheet the sheet
 * @param _copyStyle the copy style
 */
protected void copySheets(final Sheet _newSheet, final Sheet _sheet, final boolean _copyStyle) {
    int maxColumnNum = 0;
    final Map<Integer, CellStyle> styleMap = _copyStyle ? new HashMap<>() : null;
    for (int i = _sheet.getFirstRowNum(); i <= _sheet.getLastRowNum(); i++) {
        final Row srcRow = _sheet.getRow(i);
        final Row destRow = _newSheet.createRow(i);
        if (srcRow != null) {
            copyRow(_sheet, _newSheet, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        _newSheet.setColumnWidth(i, _sheet.getColumnWidth(i));
    }
}

From source file:org.generationcp.middleware.operation.parser.WorkbookParser.java

License:Open Source License

public void removeObsoleteColumnsInExcelWorkbook(final org.apache.poi.ss.usermodel.Workbook excelWorkbook,
        final List<String> obsoleteVariableNames) {

    // Get the Observation sheet
    final Sheet observationSheet = excelWorkbook.getSheetAt(WorkbookParser.OBSERVATION_SHEET);

    // The first row is the header that contains column names
    final Row headerRow = observationSheet.getRow(observationSheet.getFirstRowNum());

    for (int columnIndex = 0; columnIndex <= headerRow.getLastCellNum(); columnIndex++) {
        final Cell cell = headerRow.getCell(columnIndex);
        if (cell != null) {
            final String columnName = cell.getStringCellValue();
            if (obsoleteVariableNames.contains(columnName)) {
                // Delete the column of the obsolete variable.
                PoiUtil.deleteColumn(observationSheet, columnIndex);
                // Decrement the column index since we deleted a column
                columnIndex--;//  ww w.  j  ava  2  s . c  o  m
            }
        }

    }

}

From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5180IT.java

License:Open Source License

private void validateExcelSheet(final ByteArrayOutputStream boutSlow, final TableModel data)
        throws IOException, InvalidFormatException {
    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutSlow.toByteArray()));
    Sheet sheet = workbook.getSheetAt(0);
    Assert.assertEquals(0, sheet.getFirstRowNum());
    Assert.assertEquals(data.getRowCount() - 1, sheet.getLastRowNum());

    for (int r = 0; r < data.getRowCount(); r += 1) {
        Row row = sheet.getRow(r);//from w ww.jav  a  2  s .  c o  m
        for (int c = 0; c < data.getColumnCount(); c += 1) {
            Cell cell = row.getCell(c);

            Object valueAt = data.getValueAt(r, c);
            if (valueAt == null) {
                if (cell != null) {
                    // excel cells never return null
                    Assert.assertEquals("", cell.getStringCellValue());
                }
            } else {
                Assert.assertEquals(valueAt, cell.getStringCellValue());
            }
        }

    }

}

From source file:org.project.utilities.ExcelRead.java

public ArrayList readexl(File exfile, String exfilename) {
    ArrayList storvalues = new ArrayList();
    try {//from  w  w  w. ja v  a2s. c  om
        // File file2 = new File("/home/asl/Desktop/html work/ProgramFile/test_template.xls");
        FileInputStream file = new FileInputStream(exfile);
        //Workbook workbook = null;
        // String name = file2.getName();
        String name = exfilename;
        Workbook workbook = null;
        if (name.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(file);

        } else if (name.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(file);
        }

        //  workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
        DataFormatter fmt = new DataFormatter();

        for (int sn = 0; sn < workbook.getNumberOfSheets(); sn++) {
            Sheet sheet = workbook.getSheetAt(sn);
            for (int rn = sheet.getFirstRowNum() + 1; rn <= sheet.getLastRowNum(); rn++) {
                Row row = sheet.getRow(rn);
                if (row == null) {
                    // There is no data in this row, handle as needed
                } else {
                    // Row "rn" has data
                    ArrayList storeval = new ArrayList();
                    // System.out.println("size " + row.getLastCellNum());
                    for (int cn = 0; cn < 17; cn++) {
                        // for (int cn = 0; cn < row.getLastCellNum(); cn++) {
                        Cell cell = row.getCell(cn);
                        //  cell.setCellType(Cell.CELL_TYPE_STRING);
                        String val = "";
                        // String strCellValue = "";

                        if (cell == null) {
                            // This cell is empty/blank/un-used, handle as needed
                        } else {
                            String cellStr = fmt.formatCellValue(cell);
                            val = cellStr;
                            // Do something with the value
                        }
                        storeval.add(val);
                    }
                    storvalues.add(storeval);
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return storvalues;
}

From source file:org.projectforge.export.ExportSheet.java

License:Open Source License

public ExportSheet(final ContentProvider contentProvider, final String name, final Sheet poiSheet) {
    this.contentProvider = contentProvider;
    this.name = name;
    this.poiSheet = poiSheet;
    this.rows = new ArrayList<ExportRow>();
    final int lastRowNum = poiSheet.getLastRowNum();
    if (lastRowNum > 0) {
        // poiSheet does already exists.
        for (int i = poiSheet.getFirstRowNum(); i < poiSheet.getLastRowNum(); i++) {
            final Row poiRow = poiSheet.getRow(i);
            final ExportRow row = new ExportRow(contentProvider, this, poiRow, i);
            rows.add(row);//w w  w .  j  a va  2  s. co  m
        }
    }
    final PrintSetup printSetup = getPrintSetup();
    printSetup.setPaperSize(ConfigXml.getInstance().getDefaultPaperSize());
}

From source file:org.ramadda.data.docs.TabularOutputHandler.java

License:Apache License

/**
 * _more_/*from w w  w.  j  a va  2s  . c o  m*/
 *
 * @param request _more_
 * @param entry _more_
 * @param suffix _more_
 * @param inputStream _more_
 * @param visitInfo _more_
 * @param visitor _more_
 *
 * @throws Exception _more_
 */
private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream,
        TextReader visitInfo, TabularVisitor visitor) throws Exception {
    //        System.err.println("visitXls: making workbook");
    Workbook wb = makeWorkbook(suffix, inputStream);
    //        System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets());
    int maxRows = visitInfo.getMaxRows();
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        if (!visitInfo.okToShowSheet(sheetIdx)) {
            continue;
        }
        Sheet sheet = wb.getSheetAt(sheetIdx);
        //            System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum());
        List<List<Object>> rows = new ArrayList<List<Object>>();
        int sheetSkip = visitInfo.getSkip();
        for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows)
                && (rowIdx <= sheet.getLastRowNum()); rowIdx++) {
            if (sheetSkip-- > 0) {
                continue;
            }

            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            List<Object> cols = new ArrayList<Object>();
            short firstCol = row.getFirstCellNum();
            for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                cols.add(value);
            }

            /**
             * ** TODO
             * org.ramadda.util.text.Row row = new Row(cols);
             *
             * if ( !visitInfo.rowOk(row)) {
             *   if (rows.size() == 0) {
             *       //todo: check for the header line
             *   } else {
             *       continue;
             *   }
             * }
             */
            rows.add(cols);
        }
        if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) {
            break;
        }
    }
}

From source file:org.ramadda.data.docs.TabularOutputHandler.java

License:Apache License

/**
 * _more_//  ww  w .jav  a 2  s .c o m
 *
 * @param args _more_
 *
 * @throws Exception _more_
 */
public static void main(String[] args) throws Exception {
    Workbook wb = makeWorkbook(IOUtil.getFileExtension(args[0]), new FileInputStream(args[0]));
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        Sheet sheet = wb.getSheetAt(sheetIdx);
        System.err.println(sheet.getSheetName());
        for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) {
            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            short firstCol = row.getFirstCellNum();
            int colCnt = 0;
            for (short col = firstCol; col < row.getLastCellNum(); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                if (colCnt++ > 0)
                    System.out.print(",");
                System.out.print(value);
            }
            System.out.println("");
        }
    }
}

From source file:org.ramadda.plugins.media.TabularOutputHandler.java

License:Open Source License

/**
 * _more_//from   ww w . j a v a2  s  .c  o  m
 *
 * @param request _more_
 * @param entry _more_
 * @param suffix _more_
 * @param inputStream _more_
 * @param visitInfo _more_
 * @param visitor _more_
 *
 * @throws Exception _more_
 */
private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream, Visitor visitInfo,
        TabularVisitor visitor) throws Exception {
    //        System.err.println("visitXls: making workbook");
    Workbook wb = makeWorkbook(suffix, inputStream);
    //        System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets());
    int maxRows = visitInfo.getMaxRows();
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        if (!visitInfo.okToShowSheet(sheetIdx)) {
            continue;
        }
        Sheet sheet = wb.getSheetAt(sheetIdx);
        //            System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum());
        List<List<Object>> rows = new ArrayList<List<Object>>();
        int sheetSkip = visitInfo.getSkip();
        for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows)
                && (rowIdx <= sheet.getLastRowNum()); rowIdx++) {
            if (sheetSkip-- > 0) {
                continue;
            }

            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            List<Object> cols = new ArrayList<Object>();
            short firstCol = row.getFirstCellNum();
            for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                cols.add(value);
            }

            /**
             * ** TODO
             * org.ramadda.util.text.Row row = new Row(cols);
             *
             * if ( !visitInfo.rowOk(row)) {
             *   if (rows.size() == 0) {
             *       //todo: check for the header line
             *   } else {
             *       continue;
             *   }
             * }
             */
            rows.add(cols);
        }
        if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) {
            break;
        }
    }
}

From source file:org.smart.migrate.dao.impl.ExcelImportDao.java

@Override
public List<String> findAllSourcePrimaryKeys(TableSetting tableSetting) {

    sourceData.clear();/* w  ww .  j a  v  a 2 s.  c o  m*/

    List<String> pks = new ArrayList<String>();
    Sheet sheet = sourceWorkbook.getSheet(tableSetting.getSourceTable());
    if (sheet == null) {
        throw new RuntimeException("can not get sheet from " + tableSetting.getSourceTable());
    }
    Row headRow = sheet.getRow(sheet.getFirstRowNum());
    if (headRow != null) {
        int idColumn = ExcelUtils.cellIndexInRow("id", headRow);
        if (idColumn == -1) {
            throw new RuntimeException("sheet must have id column!");
        }

        //initialize header map (key: column index,value: fieldname)
        Map<Integer, String> header = new HashMap<Integer, String>();
        for (Cell cell : headRow) {
            header.put(cell.getColumnIndex(), cell.getStringCellValue());
        }

        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            Row row = sheet.getRow(i);
            String pk;
            if (row.getCell(idColumn).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                Double did = row.getCell(idColumn).getNumericCellValue();
                pk = String.valueOf(did.intValue());
            } else {
                pk = row.getCell(idColumn).getStringCellValue();
            }
            pks.add(pk);
            Map<String, Object> data = new HashMap<String, Object>();
            for (Cell cell : row) {
                String fieldname = header.get(cell.getColumnIndex());

                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    Double dvalue = cell.getNumericCellValue();
                    String s = String.valueOf(dvalue);
                    if (s.endsWith(".0")) {
                        data.put(fieldname, dvalue.intValue());
                    } else {
                        data.put(fieldname, dvalue);
                    }
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                    data.put(fieldname, cell.getBooleanCellValue());
                } else {
                    data.put(fieldname, cell.getStringCellValue());
                }

            }
            sourceData.put(pk, data);
        }
    }
    return pks;
}

From source file:org.smart.migrate.dao.impl.MetaExcelDao.java

@Override
public List<Field> getFieldsOfTable(Connection connection, String table) {
    List<Field> fields = new ArrayList<Field>();
    try {//w  w w.  jav a2s .c  om
        InputStream inp = new FileInputStream(dbfile);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheet(table);
        if (sheet == null) {
            inp.close();
            return fields;
        }
        Row row = sheet.getRow(sheet.getFirstRowNum());
        if (row != null) {
            for (Cell cell : row) {
                Field field = new Field();
                field.setDefaultValue(null);
                field.setName(cell.getStringCellValue());
                field.setNullable(true);
                field.setType("varchar");
                fields.add(field);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InvalidFormatException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    }
    return fields;
}