Example usage for org.apache.poi.hssf.usermodel HSSFSheet 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:com.codecrate.shard.transfer.excel.ExcelObjectImporter.java

License:Apache License

public Collection importObjects(File file, ProgressMonitor progress) {
    Collection results = new ArrayList();

    try {//from w w w  .  j a va 2 s.co  m
        POIFSFileSystem poifs = new POIFSFileSystem(new FileInputStream(file));

        HSSFWorkbook workbook = new HSSFWorkbook(poifs);
        HSSFSheet sheet = workbook.getSheetAt(0);

        int firstRow = sheet.getFirstRowNum() + 1;
        int lastRow = sheet.getLastRowNum();

        progress.startTask("Import objects from " + file.getName(), lastRow - firstRow);
        for (int currentRow = firstRow; currentRow <= lastRow; currentRow++) {
            HSSFRow row = sheet.getRow(currentRow);

            try {
                Object result = rowHandler.handleRow(row);
                results.add(result);
            } catch (Exception e) {
                LOG.error("Error importing row: " + currentRow, e);
            }

            progress.completeUnitOfWork();
        }
    } catch (Exception e) {
        LOG.error("Error importing file: " + file, e);
    }

    progress.finish();

    return results;
}

---

From source file:com.codequicker.quick.templates.source.adapters.ExcelSourceAdapter.java

License:Apache License

private void readOleBasedData(BufferedInputStream bufferedStream, Map<String, List<Map<String, String>>> data) {

    try {/*from ww  w.jav  a 2 s  . c  o  m*/
        HSSFWorkbook workbook = new HSSFWorkbook(bufferedStream);

        int sheetCount = workbook.getNumberOfSheets();

        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
            HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
            Cell cell = null;

            List<Map<String, String>> sheetData = new ArrayList<Map<String, String>>();

            int lastRowNumber = sheet.getLastRowNum();

            for (int rowIndex = 0; rowIndex <= lastRowNumber; rowIndex++) {
                HSSFRow row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue;
                }

                Map<String, String> columnData = new HashMap<String, String>();

                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    cell = row.getCell(cellIndex, Row.CREATE_NULL_AS_BLANK);
                    columnData.put("column" + (cellIndex + 1), cell.toString());
                }

                sheetData.add(columnData);
            }

            data.put("sheet" + (sheetIndex + 1), sheetData);
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

---

From source file:com.codesnippets4all.jthunder.extension.plugins.input.excel.ExcelReaderPlugin.java

License:Apache License

@SuppressWarnings("rawtypes")
private void readOLEbasedExcel(BufferedInputStream bufferedStream, List<Sheet> sheets) throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook(bufferedStream);

    int sheetCount = workbook.getNumberOfSheets();

    for (int index = 0; index < sheetCount; index++) {
        HSSFSheet sheet = workbook.getSheetAt(index);

        Sheet s = new Sheet();

        sheets.add(s);/* w  ww . j a  va 2s  .  c  om*/

        int lastRowNumber = sheet.getLastRowNum();

        for (int rowIndex = 0; rowIndex < lastRowNumber; rowIndex++) {
            HSSFRow row = sheet.getRow(rowIndex);

            Record record = new Record();

            s.addRecord(record);

            Iterator it = row.cellIterator();

            while (it.hasNext()) {
                record.addCellValue(it.next());
            }
        }
    }

}

---

From source file:com.comcast.cats.config.ui.monitoring.reboot.UpTimeAndRebootStatusBean.java

License:Open Source License

public void postProcessXls(Object document) {
    logger.trace("postProcessXls start document " + document);
    if (document != null) {
        HSSFWorkbook workBook = (HSSFWorkbook) document;
        HSSFSheet sheet = workBook.getSheetAt(0);

        HSSFRow headerRow = sheet.getRow(0);

        for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
            sheet.setColumnWidth(i, 30 * 265); // width for 40 characters
        }//from w  ww.  j  av  a  2s .  c o m

        sheet.shiftRows(0, sheet.getLastRowNum(), 5); // shift rows 0 to n
                                                      // by 1 to get space
                                                      // for header
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F3"));

        HSSFFont headerFont = workBook.createFont();
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFCellStyle headerCellStyle = workBook.createCellStyle();
        headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFCell headerCell = headerRow.createCell(0);
        headerCell.setCellStyle(headerCellStyle);
        headerCell.setCellValue("CATS Uptime and Reboot Status : " + (new Date()));

        HSSFCellStyle metaDataCellStyle = workBook.createCellStyle();
        metaDataCellStyle.setFont(headerFont);

        HSSFRow metaDataRow = sheet.getRow(3);
        if (metaDataRow == null) {
            metaDataRow = sheet.createRow(3);
        }
        HSSFCell metaDataKey = metaDataRow.createCell(0);
        metaDataKey.setCellStyle(metaDataCellStyle);
        metaDataKey.setCellValue("CATS Instance");

        HSSFCell metaDataValue = metaDataRow.createCell(1);
        metaDataValue.setCellStyle(metaDataCellStyle);
        metaDataValue.setCellValue(AuthController.getHostAddress());

        HSSFCellStyle datatTableHeaderCellStyle = workBook.createCellStyle();
        datatTableHeaderCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        datatTableHeaderCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        datatTableHeaderCellStyle.setFont(headerFont);

        HSSFRow actualDataTableHeaderRow = sheet.getRow(5);
        for (int i = 0; i < actualDataTableHeaderRow.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = actualDataTableHeaderRow.getCell(i);
            if (cell != null) {
                String cellValue = cell.getStringCellValue();
                cellValue = cellValue.replace("<br/> ", ""); // replace
                                                             // any line
                                                             // breaks
                cell.setCellValue(cellValue);
                cell.setCellStyle(datatTableHeaderCellStyle);
            }
        }

    }
    logger.trace("postProcessXls end");
}

---

From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java

License:Apache License

private String getCellValue(HSSFSheet sheet, int row, int column) {
    if (row > sheet.getLastRowNum())
        return "";
    HSSFRow theRow = sheet.getRow(row);/*from  www.  j a  va  2  s .com*/
    if (theRow == null)
        return "";
    HSSFCell cell = theRow.getCell(column);
    if (cell == null)
        return "";
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return "";
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? "true" : "false";
    case HSSFCell.CELL_TYPE_NUMERIC: {
        Double v = cell.getNumericCellValue();
        if (v.doubleValue() == (v.longValue())) {
            return Long.toString(v.longValue());
        }
        return Double.toString(v);
    }
    case HSSFCell.CELL_TYPE_STRING:
        String v = cell.getRichStringCellValue().getString().trim();
        return v;

    default:
        return "";
    }
}

---

From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java

License:Apache License

public void convertEDD(EntityFactory ef, RuleSet rs, String excelFileName) throws Exception {
    InputStream input = new FileInputStream(new File(excelFileName));

    // If the EDD is an XML file, We assume no conversion is necessary.
    if (excelFileName.endsWith(".xml")) {
        ef.loadedd(rs.newSession(), excelFileName, input);
        // Transfer bytes from in to out
        return;/*from   w w  w  .ja  va  2s .co m*/

    } else if (!(excelFileName.endsWith(".xls")))
        throw new Exception("EDD Excel File name is invalid");
    // If the EDD is an Excel file, we need to convert the thing.        

    HSSFWorkbook wb = new HSSFWorkbook(input);
    HSSFSheet sheet = wb.getSheetAt(0);

    // Open the EDD.xml output file
    String tmpEDDfilename = rs.getWorkingdirectory() + tmpEDD;
    XMLPrinter xout = new XMLPrinter(new FileOutputStream(tmpEDDfilename));

    // Write out a header in the EDD xml file.
    xout.opentag("edd_header");
    xout.printdata("edd_create_stamp", new SimpleDateFormat("EEE, d MMM yyyy HH:mm:ss Z").format(new Date()));
    xout.printdata("Excel_File_Name", excelFileName);
    xout.closetag();
    xout.opentag("edd");

    // Get the indexes of the columns we need to write out the XML for this EDD.
    int rows = sheet.getLastRowNum();
    int entityIndex = findvalue("entity", sheet, 0);
    int attributeIndex = findvalue("attribute", sheet, 0);
    int typeIndex = findvalue("type", sheet, 0);
    int subtypeIndex = findvalue("subtype", sheet, 0);
    int defaultIndex = findvalue("defaultvalue", sheet, 0);
    int inputIndex = findvalue("input", sheet, 0);
    int accessIndex = findvalue("access", sheet, 0);
    int commentIndex = findvalue("comment", sheet, 0); // optional
    int sourceIndex = findvalue("source", sheet, 0); // optional

    // Some columns we just have to have.  Make sure we have them here.
    if (entityIndex < 0 || attributeIndex < 0 || typeIndex < 0 || defaultIndex < 0 || accessIndex < 0
            || inputIndex < 0) {
        String err = " Couldn't find the following column header(s): " + (entityIndex < 0 ? " entity" : "")
                + (attributeIndex < 0 ? " attribute" : "") + (typeIndex < 0 ? " type" : "")
                + (defaultIndex < 0 ? " default value" : "") + (accessIndex < 0 ? " access" : "")
                + (inputIndex < 0 ? " input" : "");
        throw new Exception("This EDD may not be valid, as we didn't find the proper column headers\n" + err);
    }

    // Go through each row, writing out each entry to the XML.
    for (int row = 1; row <= rows; row++) {
        String entityname = getCellValue(sheet, row, entityIndex); // Skip all the rows that have no Entity
        if (entityname.length() > 0) {

            String src = sourceIndex >= 0 ? getCellValue(sheet, row, sourceIndex) : "";
            String comment = commentIndex >= 0 ? getCellValue(sheet, row, commentIndex) : "";
            xout.opentag("entry");
            xout.opentag("entity", "entityname", entityname, "attribute",
                    getCellValue(sheet, row, attributeIndex), "type", getCellValue(sheet, row, typeIndex),
                    "subtype", getCellValue(sheet, row, subtypeIndex), "default",
                    getCellValue(sheet, row, defaultIndex), "access", getCellValue(sheet, row, accessIndex),
                    "input", getCellValue(sheet, row, inputIndex), "comment",
                    getCellValue(sheet, row, commentIndex));
            xout.closetag();
            if (comment.length() > 0)
                xout.printdata("comment", getCellValue(sheet, row, commentIndex));
            if (src.length() > 0)
                xout.printdata("source", getCellValue(sheet, row, sourceIndex));
            xout.closetag();
        }
    }
    xout.closetag();
    xout.close();
    convertEDD(ef, rs, tmpEDDfilename);
}

---

From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java

License:Apache License

/**
 * Returns the index of the heading of the next block.
 * @param sheet//from w  w  w  .j a  v  a  2  s .co  m
 * @param row
 * @return
 */
int nextBlock(HSSFSheet sheet, int row) {
    String attrib = getNextAttrib(sheet, row);
    if (sheet.getRow(row) == null) {
        return row;
    }
    Cell c = sheet.getRow(row).getCell(0);
    while (attrib.equals("") && c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
        row++;
        attrib = getNextAttrib(sheet, row);
        if (row > sheet.getLastRowNum())
            return row - 1;
        c = sheet.getRow(row).getCell(0);
    }
    return row;
}

---

From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java

License:Apache License

/**
 * We used to do something really smart.  Now we just return false at the end of the spread
 * sheet or if we encounter another block.
 * @param sheet/*ww  w.ja v  a  2 s  . co  m*/
 * @param rowIndex
 * @return
 */
private boolean isAction(HSSFSheet sheet, int rowIndex) {
    String attrib = getNextAttrib(sheet, rowIndex);
    if (attrib.length() > 0)
        return false;
    if (rowIndex > sheet.getLastRowNum())
        return false;
    return true;
}

---

From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java

License:Apache License

/**
  * We used to do something really smart.  Now we just return false at the end of the spread
  * sheet or if we encounter another block.
  * @param sheet//  ww w.j  a  v  a  2s.  c o m
  * @param rowIndex
  * @return
  */
private boolean isPolicy(HSSFSheet sheet, int rowIndex) {
    String attrib = getNextAttrib(sheet, rowIndex);
    if (attrib.length() > 0)
        return false;
    if (rowIndex > sheet.getLastRowNum())
        return false;
    return true;
}

---

From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java

License:Apache License

/**
 * We used to do something really smart.  Now we just return false at the end of the spread
 * sheet or if we encounter another block.
 * @param sheet//from w w  w.  j a  v  a  2  s .  c o  m
 * @param rowIndex
 * @return
 */
private boolean isCondition(HSSFSheet sheet, int rowIndex) {
    String attrib = getNextAttrib(sheet, rowIndex);
    if (attrib.length() > 0)
        return false;
    if (rowIndex > sheet.getLastRowNum())
        return false;
    return true;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• »