You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by nayal_apache <na...@gmail.com> on 2012/02/24 12:02:47 UTC
Problem in reading data from merged cells in excel using XSSF
I am reading data from Excel file.The columns in my worksheet contains merged
cells.
When I read data from merged cells which contains a value, I am getting the
value only for first row of the merged region whereas for remaining rows I
am getting 0 value.
For example:If my worksheet has a merge region :(A1:A2) with value 54575,
Then when I retrieve both rows cells value value using XSSF
row.getCell(0).getNumericCellValue(),
for 1st row cell i get value as=54575
for 2nd row value=0;
wheras what I require is that both 1st and 2nd row cell value should be
54575.
Please suggest me some solution?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-in-reading-data-from-merged-cells-in-excel-using-XSSF-tp5512364p5512364.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Problem in reading data from merged cells in excel using XSSF
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Not sure what you are asking here. Are you saying that you want the worksheet
to ensure that users are entering six digits into the cells in a specific
column or are you saying that you want to write code using POI that will
allow you to check that the users did enter six digit codes into a specific
column.
If the former then you need to look at data validations (and there is a
section in the Quick Guide covering this), if the latter then you simply
need to write code that will;
Open the workbook.
Get the sheet.
Iterate through the rows on the sheet and for each row get the cell that
would be in the specific column.
Get the contents of the cell and, if they are numeric, convert them into a
String value using Java's native language support - hint String.valueOf().
Check the length of the String - all Strings have the length() function
defined and available for this.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-in-reading-data-from-merged-cells-in-excel-using-XSSF-tp5512364p5525498.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Problem in reading data from merged cells in excel using XSSF
Posted by nayal_apache <na...@gmail.com>.
Hi Mark,
Can you suggest me some apache poi functionlity or some other library to
validate columns in a excel sheet.
Suppose in column A I have all Customer Id values,I want to put a validation
when reading this column that all values should be of 6 digits before
converting this file to xml.
Regards,
Ashish
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-in-reading-data-from-merged-cells-in-excel-using-XSSF-tp5512364p5524714.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Problem in reading data from merged cells in excel using XSSF
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Good news and thanks for letting us know. All the best with the project and
if you have any other questions, just post to the list.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-in-reading-data-from-merged-cells-in-excel-using-XSSF-tp5512364p5518161.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Problem in reading data from merged cells in excel using XSSF
Posted by nayal_apache <na...@gmail.com>.
Thnx Mark for your valuable suggestion.I was able to solve my problem
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-in-reading-data-from-merged-cells-in-excel-using-XSSF-tp5512364p5516658.html
Sent from the POI - Dev mailing list archive at Nabble.com.
Re: Problem in reading data from merged cells in excel using XSSF
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
OK. I cannot test this code as I no longer have access to Excel - we use
OpenOffice exclusivly and there are a few differences between the two - so
you will have to do that.
1. Open the workbook.
2. Get a sheet from the workbook.
3. On the sheet object, call the getNumberMergedRegions() method to discover
how many merged regions
there are on the sheet.
4. Enter a for loop conditioned on the number of merged regions and recover
a CellRangeAddress object
for each merged region something like this
List<CellRangeAddress> regionsList = new ArrayList<CellRangeAddress>();
for(int i = 0; i < sheet.getNumberMergedRegions(); i++) {
regionsList.add(sheet.getMergedRegion(i));
}
5. Now, when you get a cell from the sheet check to see if it is in a merged
region. If it is, from what I
understood about your original request, you want to return the contents
of the merged area
every time you encounter a cell that is contained within it. So, all you
need to do is the following.
for(CellRangeAddress region : regionsList) {
// If the region does contain the cell you have just read from the row
if(region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
// Now, you need to get the cell from the top left hand corner of this
int rowNum = region.getFirstRow();
int colIndex = region.getFirstColumn();
cell = sheet.getRow(rowNum).getCell(colIndex);
System.out.println("Cel is in merged region. The value stored in that
region is " +
cell.getStringCellValue());
}
}
Obviously, each time you get a new sheet from the workbook, you will need to
rebuild the list of CellRangeAddress objects describing any merged areas.
You can optimise the code so that the checks are never performed if there
are no merged areas, you can exit the for loop above once the cell is found
to be in a merged area, and so on.
That ought to work. I have not tested it and have just written what I think
should meet your criteria but the overall flow seems correct. Also, some of
the method names may be slightly off - written from memory - so do not rely
on just copying and pasting the code; check with the javadoc and make sure
the sisnatures for the methods are correct please.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-in-reading-data-from-merged-cells-in-excel-using-XSSF-tp5512364p5516388.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Problem in reading data from merged cells in excel using XSSF
Posted by nayal_apache <na...@gmail.com>.
Please let me know how can I check whether a cell is part of a merged region
and, if it is, to get the addresses of all the other cells in that region.
As when I iterate through cell using code as follows:
Iterator cells=row.cellIterator();
XSSFCell cell=(XSSFCell)cells.next();
So,now when I try to find a method for *cell* to check whether it is part
of merged region,there is none.
Please suggest how to achieve the solution you suggested
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-in-reading-data-from-merged-cells-in-excel-using-XSSF-tp5512364p5515167.html
Sent from the POI - Dev mailing list archive at Nabble.com.
Re: Problem in reading data from merged cells in excel using XSSF
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
What you describe is perfectly normal behaviour. For a merged region of
cells, the entire contents of that merged region will be contained within
the cell at the top left hand corner of the merged region and reading any of
the other cells in that region will, as you found out, return an odd looking
value. The way to get around this is to ask if the cell is part of a merged
region and, if it is, to get the addresses of all the other cells in that
region. Then, when you read another cell, check if it is in the merged
region and, if it is, simply return the value that you read from that first
cell.
If you look through the javadocs for the API, all of the support you need is
already there. If memory serves, it is possible to discover how many merged
regions there are on a sheet and to get each individual merged region by
index from the sheet. Also, I think that the CellRangeAddress class has a
method that allows you to test if a cell is within a range using it's row
and column indices.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Problem-in-reading-data-from-merged-cells-in-excel-using-XSSF-tp5512364p5513442.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org