You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Renjith R <ra...@gmail.com> on 2014/05/15 13:56:05 UTC
Inconsistancy in row numbers
Hi,
I am reading an excel file using SAX+XSSF method. While reading I am
saving the row numbers of each row from 'attributes.getValue("r")' of 'Row'
tag.
After validating data of each row in the application, i want to write error
at the last column of each row.
For writing the error ro excel I am using XSSFWorkbook alone.
*Issue:*
My sheet is having date at second row(first row is not used).On reading the
excel sheet, i will record the row number as 2.while validating i will
check if the date is a past date or not. If the date is a past date, i have
to write 'INVAlID DATE' at the last column of second row.
For that i am using the following code.
XSSFWorkbook workBook = new XSSFWorkbbok(filePath);
Sheet mySheet = workBook.getSheetAt(0);
Row errorRow = mySheet.getRow(errorRowNumber); //errorRowNumber = 2
But row returned is not the second row, but the 3rd row which has some
other data.
My Assumption: XSSFWorkbook will not consider the first empty row. It will
consider the second row(which has data) as the first row. So on getting row
with row number '2', it will return 3rd row.
Please let me know if my assumption is correct or not, if yes, how can i
resolve it?
--
*RENJITH R*
9446011990
Re: Inconsistancy in row numbers
Posted by Nick Burch <ap...@gagravarr.org>.
On Tue, 20 May 2014, Renjith R wrote:
> I am facing one more issue. This time it is in writing the error
> details to excel. As my excel file size can go upto 40Mb, i need a faster
> approach to edit the excel file.
> XSSF approch will end in OutOfMemoryException.
Buy some more memory for your server? These days, memory is cheap,
developers much less so, so it might be worth taking the easy and simple
route with XSSF and just slinging a few more gigs of ram into your box...
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Inconsistancy in row numbers
Posted by Renjith R <ra...@gmail.com>.
Hi All,
I am facing one more issue. This time it is in writing the error
details to excel. As my excel file size can go upto 40Mb, i need a faster
approach to edit the excel file.
XSSF approch will end in OutOfMemoryException.
I am not able to use SAX here, as it can only read excel data.
SXSSF approch also dint helped me as it is for writing new rows to excel
file.
I have a difficuly route in my mind, whcih i dont want to follow as the
excel file is having macros.
(Reading whole content of excel using SAX and Re-Write(by adding errors)
excel using SXSSF).
Is there any other way to edit the excel file?
On Sat, May 17, 2014 at 12:18 PM, Renjith R <ra...@gmail.com> wrote:
> Thanks a lot for the response, David & Scott.
>
> This is how my excel looks like.
>
> [image: Inline image 2]
>
> Following is that data read by SAX parser. I am storing the row numbers 2
> and 3.
>
> <sheetData><row *r="2"* spans="1:2" x14ac:dyDescent="0.25"><c r="A2"
> s="8" t="s"><v>16</v></c><c r="B2" s="19"><v>41760</v></c></row><row
> *r="3"* spans="1:2" x14ac:dyDescent="0.25"><c r="A3" s="8"
> t="s"><v>17</v></c><c r="B3" s="9"><v>80</v></c></row></sheetData>
>
> So can i conclude that, XML file which represents the sheet will start the
> row number from 1(as seen in excel) and apache poi will start at '0' ?
>
>
>
> On Thu, May 15, 2014 at 5:26 PM, Renjith R <ra...@gmail.com> wrote:
>
>> Hi,
>>
>> I am reading an excel file using SAX+XSSF method. While reading I
>> am saving the row numbers of each row from 'attributes.getValue("r")' of
>> 'Row' tag.
>> After validating data of each row in the application, i want to write
>> error at the last column of each row.
>> For writing the error ro excel I am using XSSFWorkbook alone.
>>
>> *Issue:*
>>
>> My sheet is having date at second row(first row is not used).On reading
>> the excel sheet, i will record the row number as 2.while validating i will
>> check if the date is a past date or not. If the date is a past date, i have
>> to write 'INVAlID DATE' at the last column of second row.
>> For that i am using the following code.
>>
>> XSSFWorkbook workBook = new XSSFWorkbbok(filePath);
>> Sheet mySheet = workBook.getSheetAt(0);
>> Row errorRow = mySheet.getRow(errorRowNumber); //errorRowNumber = 2
>>
>> But row returned is not the second row, but the 3rd row which has some
>> other data.
>>
>> My Assumption: XSSFWorkbook will not consider the first empty row. It
>> will consider the second row(which has data) as the first row. So on
>> getting row with row number '2', it will return 3rd row.
>>
>>
>> Please let me know if my assumption is correct or not, if yes, how can i
>> resolve it?
>>
>> --
>> *RENJITH R*
>> 9446011990
>>
>
>
>
> --
> *RENJITH R*
> 9446011990
>
--
*RENJITH R*
9446011990
Re: Inconsistancy in row numbers
Posted by Scott Polk <sc...@pearson.com>.
I can only assume that is correct since I have never actually opened the
XML to see how Excel writes it. It makes sense, though, for the XML file
to start with row 1 since Excel displays it as row 1, plus your example
shows XML row 2 as the displayed row 2 in Excel. So your conclusion is
correct. Excel (displayed and the XML) start with 1, while POI starts with
0.
Scott
On May 17, 2014, at 2:07 AM, Renjith R <ra...@gmail.com> wrote:
Thanks a lot for the response, David & Scott.
This is how my excel looks like.
[image: Inline image 2]
Following is that data read by SAX parser. I am storing the row numbers 2
and 3.
<sheetData><row *r="2"* spans="1:2" x14ac:dyDescent="0.25"><c r="A2" s="8"
t="s"><v>16</v></c><c r="B2" s="19"><v>41760</v></c></row><row
*r="3"*spans="1:2" x14ac:dyDescent="0.25"><c r="A3" s="8"
t="s"><v>17</v></c><c
r="B3" s="9"><v>80</v></c></row></sheetData>
So can i conclude that, XML file which represents the sheet will start the
row number from 1(as seen in excel) and apache poi will start at '0' ?
On Thu, May 15, 2014 at 5:26 PM, Renjith R <ra...@gmail.com> wrote:
> Hi,
>
> I am reading an excel file using SAX+XSSF method. While reading I am
> saving the row numbers of each row from 'attributes.getValue("r")' of 'Row'
> tag.
> After validating data of each row in the application, i want to write
> error at the last column of each row.
> For writing the error ro excel I am using XSSFWorkbook alone.
>
> *Issue:*
>
> My sheet is having date at second row(first row is not used).On reading
> the excel sheet, i will record the row number as 2.while validating i will
> check if the date is a past date or not. If the date is a past date, i have
> to write 'INVAlID DATE' at the last column of second row.
> For that i am using the following code.
>
> XSSFWorkbook workBook = new XSSFWorkbbok(filePath);
> Sheet mySheet = workBook.getSheetAt(0);
> Row errorRow = mySheet.getRow(errorRowNumber); //errorRowNumber = 2
>
> But row returned is not the second row, but the 3rd row which has some
> other data.
>
> My Assumption: XSSFWorkbook will not consider the first empty row. It will
> consider the second row(which has data) as the first row. So on getting row
> with row number '2', it will return 3rd row.
>
>
> Please let me know if my assumption is correct or not, if yes, how can i
> resolve it?
>
> --
> *RENJITH R*
> 9446011990
>
--
*RENJITH R*
9446011990
Re: Inconsistancy in row numbers
Posted by Renjith R <ra...@gmail.com>.
Thanks a lot for the response, David & Scott.
This is how my excel looks like.
[image: Inline image 2]
Following is that data read by SAX parser. I am storing the row numbers 2
and 3.
<sheetData><row *r="2"* spans="1:2" x14ac:dyDescent="0.25"><c r="A2" s="8"
t="s"><v>16</v></c><c r="B2" s="19"><v>41760</v></c></row><row
*r="3"*spans="1:2" x14ac:dyDescent="0.25"><c r="A3" s="8"
t="s"><v>17</v></c><c
r="B3" s="9"><v>80</v></c></row></sheetData>
So can i conclude that, XML file which represents the sheet will start the
row number from 1(as seen in excel) and apache poi will start at '0' ?
On Thu, May 15, 2014 at 5:26 PM, Renjith R <ra...@gmail.com> wrote:
> Hi,
>
> I am reading an excel file using SAX+XSSF method. While reading I am
> saving the row numbers of each row from 'attributes.getValue("r")' of 'Row'
> tag.
> After validating data of each row in the application, i want to write
> error at the last column of each row.
> For writing the error ro excel I am using XSSFWorkbook alone.
>
> *Issue:*
>
> My sheet is having date at second row(first row is not used).On reading
> the excel sheet, i will record the row number as 2.while validating i will
> check if the date is a past date or not. If the date is a past date, i have
> to write 'INVAlID DATE' at the last column of second row.
> For that i am using the following code.
>
> XSSFWorkbook workBook = new XSSFWorkbbok(filePath);
> Sheet mySheet = workBook.getSheetAt(0);
> Row errorRow = mySheet.getRow(errorRowNumber); //errorRowNumber = 2
>
> But row returned is not the second row, but the 3rd row which has some
> other data.
>
> My Assumption: XSSFWorkbook will not consider the first empty row. It will
> consider the second row(which has data) as the first row. So on getting row
> with row number '2', it will return 3rd row.
>
>
> Please let me know if my assumption is correct or not, if yes, how can i
> resolve it?
>
> --
> *RENJITH R*
> 9446011990
>
--
*RENJITH R*
9446011990
RE: Inconsistancy in row numbers
Posted by "Crocker, David" <Da...@nrel.gov>.
No apologies necessary. You gave a full, clear, and complete answer, and it must have taken significant time and effort.
-----Original Message-----
From: Polk, Scott [mailto:scott.polk@pearson.com]
Sent: Friday, May 16, 2014 3:13 PM
To: POI Users List
Subject: Re: Inconsistancy in row numbers
David - I'm not trying to step on your response, but I feel a bit of explanation is in order that may help resolve the issue.
As stated in the Javadocs for getRow()
(https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html):
public XSSFRow getRow(int rownum)
Returns the logical row (0-based). If you ask for a row that is not defined you get a null. This is to say row 4 represents the fifth row on a sheet.
Looking at the worksheet in Excel, the rows start at row 1 (1-based).
POI starts at row 0 (0-based). So:
Excel row 1 = POI row 0
Excel row 2 = POI row 1
Excel row 3 = POI row 2
etc.
If your code is:
Row row = mySheet.getRow(2);
it is going to return the 3rd row of data. If you want the 2nd row, then you need to use mySheet.getRow(1). If you want 1st row, then you need to use mySheet.getRow(0).
I'm not sure how you are keeping track of which row you are on, but you could just subtract 1 from errorRowNumber, which should return the right row:
XSSFWorkbook workBook = new XSSFWorkbbok(filePath); Sheet mySheet = workBook.getSheetAt(0); Row errorRow = mySheet.getRow(errorRowNumber - 1); //errorRowNumber = 2
Depending on how your code is written, this could work perfectly, or it could cause issues somewhere downstream. It all depends on how you are assigning errorRowNumber (as David Crocker asked) and if/how it is being used later in your code. This would also fail if errorRowNumber = 0.
On Fri, May 16, 2014 at 2:43 PM, Crocker, David <Da...@nrel.gov> wrote:
>
> Is it a zero-base array error? When you get the row number, how do you do the comparison?
>
> -----Original Message-----
> From: Renjith R [mailto:ranju4u6@gmail.com]
> Sent: Thursday, May 15, 2014 5:56 AM
> To: user@poi.apache.org
> Subject: Inconsistancy in row numbers
>
> Hi,
>
> I am reading an excel file using SAX+XSSF method. While reading I am saving the row numbers of each row from 'attributes.getValue("r")' of 'Row'
> tag.
> After validating data of each row in the application, i want to write error at the last column of each row.
> For writing the error ro excel I am using XSSFWorkbook alone.
>
> *Issue:*
>
> My sheet is having date at second row(first row is not used).On reading the excel sheet, i will record the row number as 2.while validating i will check if the date is a past date or not. If the date is a past date, i have to write 'INVAlID DATE' at the last column of second row.
> For that i am using the following code.
>
> XSSFWorkbook workBook = new XSSFWorkbbok(filePath); Sheet mySheet =
> workBook.getSheetAt(0); Row errorRow = mySheet.getRow(errorRowNumber);
> //errorRowNumber = 2
>
> But row returned is not the second row, but the 3rd row which has some other data.
>
> My Assumption: XSSFWorkbook will not consider the first empty row. It will consider the second row(which has data) as the first row. So on getting row with row number '2', it will return 3rd row.
>
>
> Please let me know if my assumption is correct or not, if yes, how can i resolve it?
>
> --
> *RENJITH R*
> 9446011990
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional
> commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional commands, e-mail: user-help@poi.apache.org
Re: Inconsistancy in row numbers
Posted by "Polk, Scott" <sc...@pearson.com>.
David - I'm not trying to step on your response, but I feel a bit of
explanation is in order that may help resolve the issue.
As stated in the Javadocs for getRow()
(https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html):
public XSSFRow getRow(int rownum)
Returns the logical row (0-based). If you ask for a row that is not
defined you get a null. This is to say row 4 represents the fifth row
on a sheet.
Looking at the worksheet in Excel, the rows start at row 1 (1-based).
POI starts at row 0 (0-based). So:
Excel row 1 = POI row 0
Excel row 2 = POI row 1
Excel row 3 = POI row 2
etc.
If your code is:
Row row = mySheet.getRow(2);
it is going to return the 3rd row of data. If you want the 2nd row,
then you need to use mySheet.getRow(1). If you want 1st row, then you
need to use mySheet.getRow(0).
I'm not sure how you are keeping track of which row you are on, but
you could just subtract 1 from errorRowNumber, which should return the
right row:
XSSFWorkbook workBook = new XSSFWorkbbok(filePath); Sheet mySheet =
workBook.getSheetAt(0); Row errorRow = mySheet.getRow(errorRowNumber -
1); //errorRowNumber = 2
Depending on how your code is written, this could work perfectly, or
it could cause issues somewhere downstream. It all depends on how you
are assigning errorRowNumber (as David Crocker asked) and if/how it is
being used later in your code. This would also fail if errorRowNumber
= 0.
On Fri, May 16, 2014 at 2:43 PM, Crocker, David <Da...@nrel.gov> wrote:
>
> Is it a zero-base array error? When you get the row number, how do you do the comparison?
>
> -----Original Message-----
> From: Renjith R [mailto:ranju4u6@gmail.com]
> Sent: Thursday, May 15, 2014 5:56 AM
> To: user@poi.apache.org
> Subject: Inconsistancy in row numbers
>
> Hi,
>
> I am reading an excel file using SAX+XSSF method. While reading I am saving the row numbers of each row from 'attributes.getValue("r")' of 'Row'
> tag.
> After validating data of each row in the application, i want to write error at the last column of each row.
> For writing the error ro excel I am using XSSFWorkbook alone.
>
> *Issue:*
>
> My sheet is having date at second row(first row is not used).On reading the excel sheet, i will record the row number as 2.while validating i will check if the date is a past date or not. If the date is a past date, i have to write 'INVAlID DATE' at the last column of second row.
> For that i am using the following code.
>
> XSSFWorkbook workBook = new XSSFWorkbbok(filePath); Sheet mySheet = workBook.getSheetAt(0); Row errorRow = mySheet.getRow(errorRowNumber); //errorRowNumber = 2
>
> But row returned is not the second row, but the 3rd row which has some other data.
>
> My Assumption: XSSFWorkbook will not consider the first empty row. It will consider the second row(which has data) as the first row. So on getting row with row number '2', it will return 3rd row.
>
>
> Please let me know if my assumption is correct or not, if yes, how can i resolve it?
>
> --
> *RENJITH R*
> 9446011990
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Inconsistancy in row numbers
Posted by "Crocker, David" <Da...@nrel.gov>.
Is it a zero-base array error? When you get the row number, how do you do the comparison?
-----Original Message-----
From: Renjith R [mailto:ranju4u6@gmail.com]
Sent: Thursday, May 15, 2014 5:56 AM
To: user@poi.apache.org
Subject: Inconsistancy in row numbers
Hi,
I am reading an excel file using SAX+XSSF method. While reading I am saving the row numbers of each row from 'attributes.getValue("r")' of 'Row'
tag.
After validating data of each row in the application, i want to write error at the last column of each row.
For writing the error ro excel I am using XSSFWorkbook alone.
*Issue:*
My sheet is having date at second row(first row is not used).On reading the excel sheet, i will record the row number as 2.while validating i will check if the date is a past date or not. If the date is a past date, i have to write 'INVAlID DATE' at the last column of second row.
For that i am using the following code.
XSSFWorkbook workBook = new XSSFWorkbbok(filePath); Sheet mySheet = workBook.getSheetAt(0); Row errorRow = mySheet.getRow(errorRowNumber); //errorRowNumber = 2
But row returned is not the second row, but the 3rd row which has some other data.
My Assumption: XSSFWorkbook will not consider the first empty row. It will consider the second row(which has data) as the first row. So on getting row with row number '2', it will return 3rd row.
Please let me know if my assumption is correct or not, if yes, how can i resolve it?
--
*RENJITH R*
9446011990
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org