poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: Read data from excel file in matrix.
Date Sun, 21 Mar 2010 11:40:44 GMT

Chnaged the example now so that it handles empty rows. Also included a method
that shows how to use the MissingCellPolicy.

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Row;

/**
 * This class has been created to demonstarte how to handle worksheets when
the
 * requirement is to recover a square matrix describing the cells and their
 * contents. In order to test the class, a workbook was cxreated which
contained
 * a single sheet. That sheet contained six rows populated with data
 * and this class is designed to assemble a String describing each line;
where
 * a cell is found it's contents will be converted to a String and where a
cell
 * is missing the words 'Empty Cell' will be substituted.
 *
 * @author Mark B
 */
public class DemonstrateMissingCellPolicy {

    /**
     * Demonstrate one way to accomodate ragged rows 'manually'; that is to
say
     * identify the legth of the longest row and ensure that the cell values
     * that are missing from shorter rows are rpleaced with a suitable
     * substitue. Any empty rows are included in the output; simply all of
the
     * cell positions on an empty row are regarded as being empty for the
     * purposes of this method.
     *
     * @param filename An instance of the String class that encapsulates the
     *                 name of and path to an Excel workbook.
     */
    public void demoRowLengthDetection(String filename) {
        File inputFile = null;
        FileInputStream fis = null;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        int lastRowNum = 0;
        int lastCellNum = 0;
        StringBuffer buffer = null;

        try {

            // Open the workbook
            inputFile = new File(filename);
            fis = new FileInputStream(inputFile);
            workbook = new HSSFWorkbook(fis);

            // Get the first sheet and note the index number of the last row
            // on the sheet. 
            sheet = workbook.getSheetAt(0);
            lastRowNum = sheet.getLastRowNum();

            // The first iteration through the rows will identify the length
of
            // the longest row
            for(int i = 0; i < lastRowNum; i++) {

                // Get a row from the sheet and recover the index number of
                // the right most populated cell. Compare this to the value
                // stored in the local lastCellNum variable and, if the
former is
                // greater, replace the value stored in that variable. This
ensures
                // that once all of the rows have been checked, a note is
made of the
                // index number of the right most cell on the entire sheet.
                row = sheet.getRow(i);
                if(row != null) {
                    if(row.getLastCellNum() > lastCellNum) {
                        lastCellNum = row.getLastCellNum();
                    }
                }
            }

            // The second iteration through the rows should get the contents
            // of the cells as a square matrix. Here, the code simply builds
            // up a StringBuffer to demonstarte that the contents of each
            // cell are recovered successfully and that empty cells are
            // included whether they be at the start of the row, the end or
            // somewhere inbetween.
            for(int i = 0; i < lastRowNum; i++) {

                // Initialise the StringBuffer then get a row.
                buffer = new StringBuffer();
                row = sheet.getRow(i);

                // If the row is missing from the worksheet.
                if(row == null) {
                    for(int j = 0; j < lastCellNum; j++) {
                        buffer.append("[Empty Cell.]");
                    }
                }
                else {
                    // If the row existed on the worksheet, step through the
                    // cells it contains from the left most column - in this
case
                    // column zero - to the right most column on the sheet.
                    // Remember that the rightmost column is not that one on
this
                    // row but within the worksheet.
                    for(int j = 0; j < lastCellNum; j++) {
                        // Get the cell from the row and check to make sure
that
                        // and, if the value returned is not equal to null,
get
                        // the contents of the cell as a String in this case
and
                        // append it to the StringBuffer.
                        cell = row.getCell(j);
                        if(cell != null) {
                            buffer.append("[" + cell.getStringCellValue() +
"]");
                        }
                        else {
                            // If a null value was returned by the
getCell(int)
                            // method, then there is no cell on the row at
this
                            // location. In this case, a simple String will
be used
                            // to indicate that is the case; of course this
could
                            // very easilly be modified to support other
requirements
                            buffer.append("[Empty Cell.]");
                        }
                    }
                }

                // Displaying the contents of the StringBuffer to standard
                // output confirms correct processing of the row.
                System.out.println(buffer.toString().trim());
            }
        }
        catch(IOException ioEx) {
            System.out.println("Caught an: " + ioEx.getClass().getName());
            System.out.println("Message: " + ioEx.getMessage());
            System.out.println("Stacktrace follows:.....");
            ioEx.printStackTrace(System.out);
        }
        finally {
            if(fis != null) {
                try {
                    fis.close();
                }
                catch(IOException ioEX) {
                    // Too late to recover from an exception here.
                }
            }
        }
    }

    /**
     * Demonstrate how to use the Missing Cell Policy to determine how the
     * call to the getCell(0 method should behave when it is asked to
recover
     * the record for a cell that is missing from - was not included in -
the
     * row.
     * 
     * @param filename An instance of the String class that encapsulates the
     *                 name of and path to an Excel workbook.
     */
    public void demoMissingCellPolicy(String filename) {
        File inputFile = null;
        FileInputStream fis = null;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        int lastRowNum = 0;
        int lastCellNum = 0;
        StringBuffer buffer = null;

        try {

            // Open the workbook
            inputFile = new File(filename);
            fis = new FileInputStream(inputFile);
            workbook = new HSSFWorkbook(fis);

            // Set the MissingCellPolicy for the whole workbook. It is
            // possible to set the missing cell policy when the cell is
actually
            // recovered from the row as can be seen lower in the code that
            // follows.
            workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);

            // Get the first sheet and note the index number of the last row
            // on the sheet.
            sheet = workbook.getSheetAt(0);
            lastRowNum = sheet.getLastRowNum();

            // Step through the rows on the worksheet and recover the
contents
            // of the cells from each. Note that this code is simpler as it
uses
            // the missing cell policy. Note also that as the length of the
            // 'longest' row on the sheet was not established as it was in
the
            // previous method, the matrix is not truly square; any cells
that
            // are issing frm the start of the row are created and returned
by
            // the missing cell policy but none are if they are missing from
the
            // end of the row. Obviously, this is an easy problem to allow
for
            // simply by reinstating that step that determines the length of
the
            // longest row.
            for(int i = 0; i < lastRowNum; i++) {
                row = sheet.getRow(i);
                buffer = new StringBuffer();

                // Note that it is still necessary to check for the row
being
                // equal to null if the complete matrix is to include such
                // rows/cells in it's result set.
                if(row == null) {
                    // Deal with empty rows here. Obviously, this adds one
                    // additional complication; how long is the empty row?
                }
                else {
                    // Step through all of the cells on the row. Note that
                    // the call to the getCell() method will return an empty
                    // or blank cell if none is there on the row.
                    for(int j = 0; j < row.getLastCellNum(); j++) {
                        cell = row.getCell(j);
                        // It is possible to set the Missing Cell Policy
when
                        // the call is made to get the cell, like this;
                        // cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
                        buffer.append("[" + cell.getStringCellValue() +
"]");
                    }
                }

                System.out.println(buffer.toString().trim());
            }
        }
        catch(IOException ioEx) {
            System.out.println("Caught an: " + ioEx.getClass().getName());
            System.out.println("Message: " + ioEx.getMessage());
            System.out.println("Stacktrace follows:.....");
            ioEx.printStackTrace(System.out);
        }
        finally {
            if(fis != null) {
                try {
                    fis.close();
                }
                catch(IOException ioEX) {
                    // Too late to recover from an exception here.
                }
            }
        }
    }
}


Tyagi wrote:
> 
> Hi Nick,
> 
> Thanks for solution.  
> I read the data from excel file and convert it to List(it represent Sheet
> of excel file) of List(it represent one row). My business logic assume
> that all the List(represent Rows) would have same number of elements. 
> 
> I have a Sheet in excel which contain no of rows and each row have
> different number of column. I read the data from file successfully and
> convert it to List(represent rows) but some list have two elements and
> some have three due to which ArrayIndexOutOfBound exception thrown by
> business logic.
> 
> So I need that appache poi read the data from excel file and return same
> number of cells for each row.
> Please suggest. Is there Any Way?
> 
> I have checked out all the methods provided by HSSFWorkbook, but
> unfortunatly it is not helpful for me. 
> Thanks In Advance.
> 
> 
> Nick Burch-11 wrote:
>> 
>> On Wed, 17 Mar 2010, Tyagi wrote:
>>> Is there any way through which I get 5 cells for each row ?
>> 
>> http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html#setMissingCellPolicy(org.apache.poi.ss.usermodel.Row.MissingCellPolicy)
>> 
>> Nick
>> 
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>> 
>> 
>> 
> 
> 

-- 
View this message in context: http://old.nabble.com/Read-data-from-excel-file-in-matrix.-tp27929789p27975496.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Mime
View raw message