You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Robert Herold <rh...@xetus.com> on 2005/08/09 22:37:30 UTC
Shrink spreadsheet after removing rows?
Just started using poi.hssf yesterday - very nice!
I'm using it to manipulate an existing spreadsheet. I want to remove a row
in the middle, so I move all rows after it up by one. This leaves a blank
row at the end, which one can see after writing it out and opening it with
Excel. I've tried removing the blank row, to no avail. Is there any way to
shrink the spreadsheet to not include the blank row?
-- bob
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
AW: Shrink spreadsheet after removing rows?
Posted by "KHZ (SAW)" <ka...@sawag.com>.
Hi Bob.
I think it's not restricted to rows. Chunks of memory are allocated and
within such steps you'll surely have no reduction in size. And you could
see it as a question whether unused chunks are removed automatically or
not.
Regards, Karl-Heinz.
-----Ursprüngliche Nachricht-----
Von: Robert Herold [mailto:rherold@xetus.com]
Gesendet: Mittwoch, 17. August 2005 22:26
An: 'POI Users List'
Betreff: RE: Shrink spreadsheet after removing rows?
Zach - Using sheet.shiftRows() to shift the rows up leaves an equal
number
of empty rows at the end. Those empty rows are in fact what I'd like to
get
rid of. I've tried deleting them, to no avail - the blanks rows are
still
there. So unfortunately sheet.shiftRows does not help.
In Excel, the same thing happens. However, you can save the file, and
the
extra rows disappear. Excel seems to trim unused (i.e. empty,
unformatted)
rows and columns beyond the end of the actual data when saving the file.
See http://support.microsoft.com/?id=244435 for Microsoft's take on the
problem.
So one approach I could take is to simply open the spreadsheet in Excel,
make a trivial modification and save it again. But that retains a
manual
step in a process I'm trying to automate.
Karl-Heinz, thanks for the pointer to the "Phantom Cells" thread. That
thread addresses why the width of a row might not be what one would
expect
(rows are in groups of 16, with the width of all rows in the group being
the
width of the longest row in the group), but it does not talk about this
problem of removing entire rows.
Thanks for all the ideas, though!
-- bob
-----Original Message-----
From: Zach Engel [mailto:zak@checkpoint.com]
Sent: Wednesday, August 17, 2005 10:22 AM
To: 'POI Users List'
Subject: RE: Shrink spreadsheet after removing rows?
I think you should use sheet.shiftRows (int startRow, int endRow, int n)
Shift all the rows (under the row you want to remove) up by one
Does this help?
-----Original Message-----
From: Robert Herold [mailto:rherold@xetus.com]
Sent: Wednesday, August 17, 2005 8:16 PM
To: 'POI Users List'
Subject: RE: Shrink spreadsheet after removing rows?
(Bump to top of communal conciousness again)
Is this possible to do? Maybe it's so obvious that no one bothered to
respond to such an idiotic question...
Thanks for any help.
-- bob
-----Original Message-----
From: Robert Herold [mailto:rherold@xetus.com]
Sent: Tuesday, August 09, 2005 1:38 PM
To: poi-user@jakarta.apache.org
Subject: Shrink spreadsheet after removing rows?
Just started using poi.hssf yesterday - very nice!
I'm using it to manipulate an existing spreadsheet. I want to remove a
row
in the middle, so I move all rows after it up by one. This leaves a
blank
row at the end, which one can see after writing it out and opening it
with
Excel. I've tried removing the blank row, to no avail. Is there any
way to
shrink the spreadsheet to not include the blank row?
-- bob
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: Shrink spreadsheet after removing rows?
Posted by Robert Herold <rh...@xetus.com>.
Zach - Using sheet.shiftRows() to shift the rows up leaves an equal number
of empty rows at the end. Those empty rows are in fact what I'd like to get
rid of. I've tried deleting them, to no avail - the blanks rows are still
there. So unfortunately sheet.shiftRows does not help.
In Excel, the same thing happens. However, you can save the file, and the
extra rows disappear. Excel seems to trim unused (i.e. empty, unformatted)
rows and columns beyond the end of the actual data when saving the file.
See http://support.microsoft.com/?id=244435 for Microsoft's take on the
problem.
So one approach I could take is to simply open the spreadsheet in Excel,
make a trivial modification and save it again. But that retains a manual
step in a process I'm trying to automate.
Karl-Heinz, thanks for the pointer to the "Phantom Cells" thread. That
thread addresses why the width of a row might not be what one would expect
(rows are in groups of 16, with the width of all rows in the group being the
width of the longest row in the group), but it does not talk about this
problem of removing entire rows.
Thanks for all the ideas, though!
-- bob
-----Original Message-----
From: Zach Engel [mailto:zak@checkpoint.com]
Sent: Wednesday, August 17, 2005 10:22 AM
To: 'POI Users List'
Subject: RE: Shrink spreadsheet after removing rows?
I think you should use sheet.shiftRows (int startRow, int endRow, int n)
Shift all the rows (under the row you want to remove) up by one
Does this help?
-----Original Message-----
From: Robert Herold [mailto:rherold@xetus.com]
Sent: Wednesday, August 17, 2005 8:16 PM
To: 'POI Users List'
Subject: RE: Shrink spreadsheet after removing rows?
(Bump to top of communal conciousness again)
Is this possible to do? Maybe it's so obvious that no one bothered to
respond to such an idiotic question...
Thanks for any help.
-- bob
-----Original Message-----
From: Robert Herold [mailto:rherold@xetus.com]
Sent: Tuesday, August 09, 2005 1:38 PM
To: poi-user@jakarta.apache.org
Subject: Shrink spreadsheet after removing rows?
Just started using poi.hssf yesterday - very nice!
I'm using it to manipulate an existing spreadsheet. I want to remove a row
in the middle, so I move all rows after it up by one. This leaves a blank
row at the end, which one can see after writing it out and opening it with
Excel. I've tried removing the blank row, to no avail. Is there any way to
shrink the spreadsheet to not include the blank row?
-- bob
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
AW: Shrink spreadsheet after removing rows?
Posted by "KHZ (SAW)" <ka...@sawag.com>.
Hi Bob.
Around 1 1/2 years ago there was the thread "phantom cells". Please read
this. There some things about memory consumption (allocation of chunks)
are mentioned.
I don't know whether POI was improved meanwhile. But I hope that the
answers there let you understand the thing a bit better.
Regards, Karl-Heinz.
-----Ursprüngliche Nachricht-----
Von: Zach Engel [mailto:zak@checkpoint.com]
Gesendet: Mittwoch, 17. August 2005 19:22
An: 'POI Users List'
Betreff: RE: Shrink spreadsheet after removing rows?
I think you should use sheet.shiftRows (int startRow, int endRow, int n)
Shift all the rows (under the row you want to remove) up by one
Does this help?
-----Original Message-----
From: Robert Herold [mailto:rherold@xetus.com]
Sent: Wednesday, August 17, 2005 8:16 PM
To: 'POI Users List'
Subject: RE: Shrink spreadsheet after removing rows?
(Bump to top of communal conciousness again)
Is this possible to do? Maybe it's so obvious that no one bothered to
respond to such an idiotic question...
Thanks for any help.
-- bob
-----Original Message-----
From: Robert Herold [mailto:rherold@xetus.com]
Sent: Tuesday, August 09, 2005 1:38 PM
To: poi-user@jakarta.apache.org
Subject: Shrink spreadsheet after removing rows?
Just started using poi.hssf yesterday - very nice!
I'm using it to manipulate an existing spreadsheet. I want to remove a
row
in the middle, so I move all rows after it up by one. This leaves a
blank
row at the end, which one can see after writing it out and opening it
with
Excel. I've tried removing the blank row, to no avail. Is there any
way to
shrink the spreadsheet to not include the blank row?
-- bob
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: Shrink spreadsheet after removing rows?
Posted by Zach Engel <za...@checkpoint.com>.
I think you should use sheet.shiftRows (int startRow, int endRow, int n)
Shift all the rows (under the row you want to remove) up by one
Does this help?
-----Original Message-----
From: Robert Herold [mailto:rherold@xetus.com]
Sent: Wednesday, August 17, 2005 8:16 PM
To: 'POI Users List'
Subject: RE: Shrink spreadsheet after removing rows?
(Bump to top of communal conciousness again)
Is this possible to do? Maybe it's so obvious that no one bothered to
respond to such an idiotic question...
Thanks for any help.
-- bob
-----Original Message-----
From: Robert Herold [mailto:rherold@xetus.com]
Sent: Tuesday, August 09, 2005 1:38 PM
To: poi-user@jakarta.apache.org
Subject: Shrink spreadsheet after removing rows?
Just started using poi.hssf yesterday - very nice!
I'm using it to manipulate an existing spreadsheet. I want to remove a row
in the middle, so I move all rows after it up by one. This leaves a blank
row at the end, which one can see after writing it out and opening it with
Excel. I've tried removing the blank row, to no avail. Is there any way to
shrink the spreadsheet to not include the blank row?
-- bob
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: Shrink spreadsheet after removing rows?
Posted by Robert Herold <rh...@xetus.com>.
(Bump to top of communal conciousness again)
Is this possible to do? Maybe it's so obvious that no one bothered to
respond to such an idiotic question...
Thanks for any help.
-- bob
-----Original Message-----
From: Robert Herold [mailto:rherold@xetus.com]
Sent: Tuesday, August 09, 2005 1:38 PM
To: poi-user@jakarta.apache.org
Subject: Shrink spreadsheet after removing rows?
Just started using poi.hssf yesterday - very nice!
I'm using it to manipulate an existing spreadsheet. I want to remove a row
in the middle, so I move all rows after it up by one. This leaves a blank
row at the end, which one can see after writing it out and opening it with
Excel. I've tried removing the blank row, to no avail. Is there any way to
shrink the spreadsheet to not include the blank row?
-- bob
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/