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: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>>
>>
>
>
--
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: [email protected]
For additional commands, e-mail: [email protected]
