You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Tyagi <su...@gmail.com> on 2010/03/17 11:02:31 UTC
Read data from excel file in matrix.
Hi ,
I want to read the data from excel file in a matrix. i.e if my file contain
data like
1 2 3
4 5 6 7
8
9 10 11 12
Then I want to get 5 cells for each row, currently I am getting 4 cells for
first row, 5 cells for second row, 2 cells for third row and 5 cells for
fourth row.
Is there any way through which I get 5 cells for each row ?
Please suggest .....
--
View this message in context: http://old.nabble.com/Read-data-from-excel-file-in-matrix.-tp27929789p27929789.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
Re: Read data from excel file in matrix.
Posted by Prenom Nom <ut...@yahoo.fr>.
Le 17/03/2010 11:02, Tyagi a écrit :
> Hi ,
> I want to read the data from excel file in a matrix. i.e if my file contain
> data like
> 1 2 3
> 4 5 6 7
> 8
> 9 10 11 12
> Then I want to get 5 cells for each row, currently I am getting 4 cells for
> first row, 5 cells for second row, 2 cells for third row and 5 cells for
> fourth row.
> Is there any way through which I get 5 cells for each row ?
> Please suggest .....
>
You should have default value for Excel file whire are not complete.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Read data from excel file in matrix.
Posted by MSB <ma...@tiscali.co.uk>.
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
Re: Read data from excel file in matrix.
Posted by MSB <ma...@tiscali.co.uk>.
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: 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.-tp27929789p27954866.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
Re: Read data from excel file in matrix.
Posted by MSB <ma...@tiscali.co.uk>.
Well to my mind you have roughly three options as I am not at all certain
there is an easy, in-built way to accomplish this task; you will need to do
some additional coding IMO.
The first option would be to test out the MissingCellPolicy technique that
Nick suggested in his earlier reply to your question.
Secondly, I would process the rows twice. The first pass would be undertaken
simply to establish the length of the longest row - and to establish the
maximum number of cells on a row therefore. Next, I would use this
nformation to initialise each List so that it contained the correct amount
of padding to ensure the matrix was 'square'. The second pass would actually
populate the Lists with the 'real' data, replacing the placeholders where
necessary.
Finally, you could prcess each row and accept that the resulting lists would
hold different numbers of cells. At the same time as processing the rows,
you would keep track of what the largest number of cells on a row is and use
this value when reading the contents of the List. If there were fewer cells
in the List then simply return the relevant number of 'padding' cells to
satisfy the requirements of your business rules.
Yours
Mark B
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.-tp27929789p27950913.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
Re: Read data from excel file in matrix.
Posted by Tyagi <su...@gmail.com>.
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.-tp27929789p27949111.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
Re: Read data from excel file in matrix.
Posted by Nick Burch <ni...@alfresco.com>.
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