Example usage for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getLastRowNum

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum.

Prototype

@Override
public int getLastRowNum()

Source Link

Document

Gets the number last row on the sheet.

Usage

From source file:org.projectforge.excel.ExcelImport.java

License:Open Source License

/**
 * convert the contents of the table into an array.
 * //  ww  w  . j av a2 s . c  om
 * @param clazz the target class
 * @return an array with the object values.
 */
@SuppressWarnings("unchecked")
public T[] convertToRows(final Class<T> clazz) {
    if (clazzFactory == null) {
        setRowClass(clazz);
    }
    final HSSFSheet sheet = work.getSheetAt(activeSheet);
    final int numberOfRows = sheet.getLastRowNum();
    final List<T> list = new ArrayList<T>(numberOfRows);
    final HSSFRow columnNames = sheet.getRow(columnNameRow);
    for (int i = startAtRow; i <= numberOfRows; i++) {
        try {
            T line;
            line = convertToBean(sheet.getRow(i), columnNames, i + 1);
            if (line == null) {
                continue;
            }
            if (clazz.isInstance(line) == false) {
                throw new IllegalStateException("returned type " + line.getClass() + " is not assignable to "
                        + clazz + " in sheet='" + sheet.getSheetName() + "', row=" + i);
            }
            list.add(line);
        } catch (final InstantiationException ex) {
            throw new IllegalArgumentException("Can't create bean " + ex.toString() + " in sheet='"
                    + sheet.getSheetName() + "', row=" + i);
        } catch (final IllegalAccessException ex) {
            throw new IllegalArgumentException("Getter is not visible " + ex.toString() + " in sheet='"
                    + sheet.getSheetName() + "', row=" + i);
        } catch (final InvocationTargetException ex) {
            log.error(ex.getMessage(), ex);
            throw new IllegalArgumentException("Getter threw an exception " + ex.toString() + " in sheet='"
                    + sheet.getSheetName() + "', row=" + i);
        } catch (final NoSuchMethodException ex) {
            throw new IllegalArgumentException("Getter is not existant " + ex.toString() + " in sheet='"
                    + sheet.getSheetName() + "', row=" + i);
        }
    }
    return list.toArray((T[]) Array.newInstance(clazz, 0));
}

From source file:org.ramadda.data.tools.ProcessXls.java

License:Apache License

/**
 * _more_//from   w w w  .  j ava 2 s .co  m
 *
 * @param filename _more_
 *
 * @return _more_
 *
 * @throws Exception _more_
 */
public String makeEntries(String filename) throws Exception {
    sb.append("<entries>");
    /*
            
    sb.append(XmlUtil.tag("entry", XmlUtil.attrs(new String[]{
                "type",
                "group",
                "name",
                "Projects",
                "id","project",
            }),""));
    */

    InputStream myxls = IOUtil.getInputStream(filename, ProcessXls.class);
    HSSFWorkbook wb = new HSSFWorkbook(myxls);
    HSSFSheet sheet = wb.getSheetAt(0);
    int skipRows = 2;
    for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) {
        if (skipRows-- > 0) {
            //                System.err.println("skipping");
            continue;
        }
        HSSFRow row = sheet.getRow(rowIdx);
        if ((row == null) || (rowIdx == 0)) {
            continue;
        }
        processRow(row);
    }

    sb.append(end);
    sb.append("</entries>");

    return sb.toString();
}

From source file:org.ramadda.util.XlsUtil.java

License:Apache License

/**
 * Convert excel to csv//from  w w w .j  ava  2  s.c o m
 *
 * @param filename excel file
 * @param skipToFirstNumeric _more_
 * @param sdf If non null then use this to format any date cells
 *
 * @return csv
 *
 * @throws Exception On badness
 */
public static String xlsToCsv(String filename) {
    try {

        StringBuffer sb = new StringBuffer();
        InputStream myxls = IOUtil.getInputStream(filename, XlsUtil.class);
        HSSFWorkbook wb = new HSSFWorkbook(myxls);
        HSSFSheet sheet = wb.getSheetAt(0);
        boolean seenNumber = false;
        for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) {
            HSSFRow row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }

            short firstCol = row.getFirstCellNum();
            for (short col = firstCol; col < row.getLastCellNum(); col++) {
                HSSFCell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                String value = cell.toString();
                if (col > firstCol) {
                    sb.append(",");
                }
                sb.append(clean(value));
            }
            sb.append("\n");
        }

        return sb.toString();
    } catch (Exception exc) {
        throw new RuntimeException(exc);

    }
}

From source file:org.sakaiproject.sitestats.impl.report.ReportManagerImpl.java

License:Educational Community License

public byte[] getReportAsExcel(Report report, String sheetName) {
    List<Stat> statsObjects = report.getReportData();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(sheetName));
    HSSFRow headerRow = sheet.createRow(0);

    // Add the column headers
    int ix = 0;//www .  java  2 s  . c  o  m
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_SITE)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_site"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_USER)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_id"));
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_user"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_TOOL)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_tool"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_EVENT)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_event"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_RESOURCE)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_resource"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(),
            StatsManager.T_RESOURCE_ACTION)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_action"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATE)
            || isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATEMONTH)
            || isReportColumnAvailable(report.getReportDefinition().getReportParams(),
                    StatsManager.T_DATEYEAR)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_date"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_LASTDATE)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_lastdate"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_TOTAL)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_total"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_VISITS)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_visits"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_UNIQUEVISITS)) {
        headerRow.createCell(ix++).setCellValue(msgs.getString("th_uniquevisitors"));
    }
    if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DURATION)) {
        headerRow.createCell(ix++)
                .setCellValue(msgs.getString("th_duration") + " (" + msgs.getString("minutes_abbr") + ")");
    }

    // Fill the spreadsheet cells
    Iterator<Stat> i = statsObjects.iterator();
    while (i.hasNext()) {
        HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
        Stat se = i.next();
        ix = 0;
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_SITE)) {
            try {
                Site site = M_ss.getSite(se.getSiteId());
                row.createCell(ix++).setCellValue(site.getTitle());
            } catch (IdUnusedException e) {
                logger.debug("can't find site with id: " + se.getSiteId());
                row.createCell(ix++).setCellValue(se.getSiteId().toString());
            }
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_USER)) {
            String userId = se.getUserId();
            String userEid = null;
            String userName = null;
            if (userId != null) {
                if (("-").equals(userId)) {
                    userEid = "-";
                    userName = msgs.getString("user_anonymous");
                } else if (("?").equals(userId)) {
                    userEid = "-";
                    userName = msgs.getString("user_anonymous_access");
                } else {
                    try {
                        User user = M_uds.getUser(userId);
                        userEid = user.getDisplayId();
                        userName = M_sm.getUserNameForDisplay(user);
                    } catch (UserNotDefinedException e1) {
                        userEid = userId;
                        userName = msgs.getString("user_unknown");
                    }
                }
            } else {
                userName = msgs.getString("user_unknown");
            }
            row.createCell(ix++).setCellValue(userEid);
            row.createCell(ix++).setCellValue(userName);
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_TOOL)) {
            EventStat es = (EventStat) se;
            row.createCell(ix++).setCellValue(M_ers.getToolName(es.getToolId()));
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_EVENT)) {
            EventStat es = (EventStat) se;
            row.createCell(ix++).setCellValue(M_ers.getEventName(es.getEventId()));
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_RESOURCE)) {
            ResourceStat rs = (ResourceStat) se;
            row.createCell(ix++).setCellValue(rs.getResourceRef());
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(),
                StatsManager.T_RESOURCE_ACTION)) {
            ResourceStat rs = (ResourceStat) se;
            row.createCell(ix++).setCellValue(rs.getResourceAction());
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATE)) {
            row.createCell(ix++).setCellValue(se.getDate().toString());
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATEMONTH)) {
            row.createCell(ix++).setCellValue(dateMonthFrmt.format(se.getDate()));
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DATEYEAR)) {
            row.createCell(ix++).setCellValue(dateYearFrmt.format(se.getDate()));
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_LASTDATE)) {
            row.createCell(ix++).setCellValue(se.getDate().toString());
        }
        if (report.getReportDefinition().getReportParams().getSiteId() != null
                && !"".equals(report.getReportDefinition().getReportParams().getSiteId())) {
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_TOTAL)) {
            row.createCell(ix++).setCellValue(se.getCount());
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_VISITS)) {
            SiteVisits sv = (SiteVisits) se;
            row.createCell(ix++).setCellValue(sv.getTotalVisits());
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(),
                StatsManager.T_UNIQUEVISITS)) {
            SiteVisits sv = (SiteVisits) se;
            row.createCell(ix++).setCellValue(sv.getTotalUnique());
        }
        if (isReportColumnAvailable(report.getReportDefinition().getReportParams(), StatsManager.T_DURATION)) {
            SitePresence ss = (SitePresence) se;
            double durationInMin = ss.getDuration() == 0 ? 0
                    : Util.round((double) ss.getDuration() / 1000 / 60, 1); // in minutes
            row.createCell(ix++).setCellValue(durationInMin);
        }
    }

    ByteArrayOutputStream baos = null;
    try {
        baos = new ByteArrayOutputStream();
        wb.write(baos);
    } catch (IOException e) {
        LOG.error("Error writing Excel bytes from SiteStats report", e);
    } finally {
        if (baos != null) {
            try {
                baos.close();
            } catch (IOException e) {
                /* ignore */ }
        }
    }
    if (baos != null) {
        return baos.toByteArray();
    } else {
        return new byte[0];
    }
}

From source file:org.seasar.dbflute.helper.io.xls.DfTableXlsReader.java

License:Apache License

protected DfDataTable setupTable(HSSFSheet sheet, String tableName, final DfDataTable table) {
    final int rowCount = sheet.getLastRowNum();
    final HSSFRow nameRow = sheet.getRow(0);
    if (nameRow == null) {
        throwXlsReaderFirstRowNotColumnDefinitionException(tableName);
    }// w  w w . j a v a  2 s  .  c  o  m
    if (rowCount > 0) {
        setupColumns(table, nameRow, sheet.getRow(1));
        setupRows(table, sheet);
    } else if (rowCount == 0) {
        setupColumns(table, nameRow, null);
    }
    return table;
}

From source file:org.seasar.dbflute.helper.io.xls.DfXlsReader.java

License:Apache License

protected DfDataTable createTable(String sheetName, HSSFSheet sheet) {
    String tableName = sheetName;
    if (_tableNameMap != null && !_tableNameMap.isEmpty() && sheetName.startsWith("$")) {
        String realTableName = _tableNameMap.get(sheetName);
        if (realTableName == null) {
            realTableName = _tableNameMap.get(sheetName.substring("$".length()));
            if (realTableName == null) {
                String msg = "The sheetName[" + sheetName + "] was not found in the tableNameMap: "
                        + _tableNameMap;
                throw new IllegalStateException(msg);
            }/*  w w w.  j a  v  a  2  s  . c  o m*/
        }
        tableName = realTableName;
    }
    final DfDataTable table = _dataSet.addTable(tableName);
    final int rowCount = sheet.getLastRowNum();
    final HSSFRow nameRow = sheet.getRow(0);
    if (nameRow == null) {
        String msg = "The first row of the sheet should be column definition but it is null:";
        msg = msg + " sheet=" + tableName;
        throw new IllegalStateException(msg);
    }
    if (rowCount > 0) {
        setupColumns(table, nameRow, sheet.getRow(1));
        setupRows(table, sheet);
    } else if (rowCount == 0) {
        setupColumns(table, nameRow, null);
    }
    return table;
}

From source file:org.seasar.extension.dataset.impl.XlsReader.java

License:Apache License

/**
 * ????/*from w  w  w. j av  a  2s .com*/
 * 
 * @param sheetName
 *            ??
 * @param sheet
 *            
 * @return 
 */
protected DataTable createTable(String sheetName, HSSFSheet sheet) {
    DataTable table = dataSet.addTable(sheetName);
    int rowCount = sheet.getLastRowNum();
    if (rowCount > 0) {
        setupColumns(table, sheet);
        setupRows(table, sheet);
    } else if (rowCount == 0) {
        setupColumns(table, sheet);
    }
    return table;
}

From source file:org.seasar.extension.dataset.impl.XlsReader.java

License:Apache License

/**
 * ????/*from  w w  w. j a v a 2  s.  co  m*/
 * 
 * @param table
 *            
 * @param sheet
 *            
 */
protected void setupColumns(DataTable table, HSSFSheet sheet) {
    HSSFRow nameRow = sheet.getRow(0);
    HSSFRow valueRow = sheet.getRow(1);
    for (int i = 0; i <= Short.MAX_VALUE; ++i) {
        HSSFCell nameCell = nameRow.getCell((short) i);
        if (nameCell == null) {
            break;
        }
        String columnName = nameCell.getRichStringCellValue().getString();
        if (columnName.length() == 0) {
            break;
        }
        HSSFCell valueCell = null;
        if (valueRow != null) {
            for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                valueCell = sheet.getRow(j).getCell((short) i);
                if (valueCell != null && !StringUtil.isEmpty(valueCell.toString())) {
                    break;
                }
            }
        }
        if (valueCell != null) {
            table.addColumn(columnName, getColumnType(valueCell));
        } else {
            table.addColumn(columnName);
        }
    }
}

From source file:org.symphonyoss.simplebot.LunchBoxBot.java

License:Apache License

private void writeToSpreadsheet(HashMap feedbackMap) throws IOException {
    File file = new File("LunchFeedback" + todayDateString.replace(",", "-") + ".xls");
    HSSFSheet spreadsheet = null;
    HSSFWorkbook workbook = null;/*from  w w  w.  j a v a  2 s  .  c o  m*/
    if (!(file.isFile() && file.exists())) {
        file.createNewFile();
    } else {

        FileInputStream fis = new FileInputStream(file);
        workbook = new HSSFWorkbook(fis);
        spreadsheet = workbook.getSheetAt(0);
    }

    FileOutputStream fos = new FileOutputStream(file);

    if (spreadsheet == null) {
        // create a new spreadsheet with feedbackdata
        workbook = new HSSFWorkbook();
        spreadsheet = workbook.createSheet("Feedback");
        HSSFRow row0 = spreadsheet.createRow(0);
        HSSFRow row = spreadsheet.createRow(1);
        row0.createCell(0).setCellValue("User's ID");
        for (int i = 0; i < todayFoods.size(); i++) {
            row0.createCell(i + 1).setCellValue((String) todayFoods.get(i));
        }

        row0.createCell(todayFoods.size() + 1).setCellValue("Overall");
        row0.createCell(todayFoods.size() + 2).setCellValue("Comments");
    }

    HSSFRow existingRow = null;
    for (int j = 0; j < spreadsheet.getLastRowNum() + 1; j++) {
        HSSFRow row = spreadsheet.getRow(j);
        HSSFCell cell = row.getCell(0); //get first cell
        if (cell != null && cell.getStringCellValue().equals(username)) {
            // user has already submitted feedback, replace it with new feedback
            existingRow = row;
            break;
        }
    }

    if (existingRow == null) {
        existingRow = spreadsheet.createRow(spreadsheet.getLastRowNum() + 1);
    }

    // transfer feedback to spreadsheet
    Set<Integer> keySet = feedbackMap.keySet();
    for (Integer key : keySet) {
        if (key == 0 || key == todayFoods.size() + 1 || key == todayFoods.size() + 2) {
            existingRow.createCell(key).setCellValue((String) feedbackMap.get(key));
        } else {
            existingRow.createCell(key).setCellValue((Integer) feedbackMap.get(key));
        }
    }

    workbook.write(fos);
    fos.close();
}

From source file:org.yccheok.jstock.file.Statements.java

License:Open Source License

/**
 * Construct Statements based on given Excel File.
 *
 * @param file Given Excel File/*from   ww w . java  2  s.co m*/
 * @return the List of constructed Statements. Empty list if fail.
 */
public static List<Statements> newInstanceFromExcelFile(File file) {
    FileInputStream fileInputStream = null;
    final List<Statements> statementsList = new ArrayList<Statements>();
    try {
        fileInputStream = new FileInputStream(file);
        final POIFSFileSystem fs = new POIFSFileSystem(fileInputStream);
        final HSSFWorkbook wb = new HSSFWorkbook(fs);
        final int numberOfSheets = wb.getNumberOfSheets();
        for (int k = 0; k < numberOfSheets; k++) {
            final HSSFSheet sheet = wb.getSheetAt(k);
            final int startRow = sheet.getFirstRowNum();
            final int endRow = sheet.getLastRowNum();
            // If there are 3 rows, endRow will be 2.
            // We must have at least 2 rows. (endRow = 1)
            if (startRow != 0 || endRow <= startRow) {
                continue;
            }

            final HSSFRow row = sheet.getRow(startRow);
            if (row == null) {
                continue;
            }

            final int startCell = row.getFirstCellNum();
            final int endCell = row.getLastCellNum();
            // If there are 2 cols, endCell will be 2.
            // We must have at least 1 col. (endCell = 1)
            if (startCell != 0 || endCell <= startCell) {
                continue;
            }

            final List<String> types = new ArrayList<String>();
            for (int i = startCell; i < endCell; i++) {
                final HSSFCell cell = row.getCell(i);
                if (cell == null) {
                    continue;
                }

                // Exception may be thrown here, as cell may be numerical value.
                final String type = cell.getRichStringCellValue().getString();
                if (type != null) {
                    types.add(type);
                }
            }

            if (types.isEmpty()) {
                continue;
            }

            if (types.size() != (endCell - startCell)) {
                continue;
            }

            final Statement.What what = Statement.what(types);
            Statements s = new Statements(what.type, what.guiBundleWrapper);
            for (int i = startRow + 1; i <= endRow; i++) {
                final HSSFRow r = sheet.getRow(i);
                if (r == null) {
                    continue;
                }
                final List<Atom> atoms = new ArrayList<Atom>();
                for (int j = startCell; j < endCell; j++) {
                    final HSSFCell cell = r.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    Object value = null;
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        final HSSFRichTextString richString = cell.getRichStringCellValue();
                        if (richString != null) {
                            value = richString.getString();
                        } else {
                            value = "";
                        }
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        try {
                            value = new Double(cell.getNumericCellValue());
                        } catch (NumberFormatException ex) {
                            log.error(null, ex);
                            value = new Double(0.0);
                        }
                    } else {
                    }

                    if (null == value) {
                        continue;
                    }
                    atoms.add(new Atom(value, types.get(j - startCell)));
                }
                final Statement statement = new Statement(atoms);

                if (s.getType() != statement.getType()) {
                    // Give up.
                    s = null;
                    break;
                }
                s.statements.add(statement);
            } // for (int i = startRow + 1; i <= endRow; i++)

            if (s != null) {
                statementsList.add(s);
            }

        } /* for(int k = 0; k < numberOfSheets; k++) */
    } catch (Exception ex) {
        log.error(null, ex);
    } finally {
        org.yccheok.jstock.gui.Utils.close(fileInputStream);
    }
    return statementsList;
}