Could not resist putting together some code to demonstrate what I was talking
about in my earlier message;
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;
/**
* This class has been created to demonstrate one way 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 created which
contained
* a single sheet. That sheet contained four rows populated with data such
that
* some of the cells in each row contained value other did not. Further,
some rows
* were longer and other shorter 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.
Obviously, you
* will need to modify the code to suit your requirement.
*
* @author Mark B
*/
public class DemonstrateMissingCellPolicy {
/**
* Demonstarte 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
*
* @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 firstRowNum = 0;
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 numbers of the first
and
// last rows on the sheet. Currently, this code does not provide
// for the situation where there may be an empty row on the
sheet
// between two other populated rows and neither does it allow
for the
// requirement where any rows that are missing from the start of
the
// sheet should be included in the output. Both of these will be
// trivial to correct however.
sheet = workbook.getSheetAt(0);
firstRowNum = sheet.getFirstRowNum();
lastRowNum = sheet.getLastRowNum();
// The first iteration through the rows will identify the length
of
// the longest row
for(int i = firstRowNum; 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 havebeen checked, a note is
made of the
// index number of the right most cell on the entire sheet.
row = sheet.getRow(i);
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);
// Now, step through from the left most column - in this
case
// column zero - to the right most column on the sheet.
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.
}
}
}
}
}
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.-tp27929789p27954866.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]
