You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "@lan Williamson" <al...@alanwilliamson.org> on 2009/10/20 23:24:05 UTC
Adding/Deleting a Column
Good Day all.
I am loving Apache POI at the moment, but i have some simple questions
that I am sure someone can help with.
With respect to adding a new column; is it true I don't need to do this
explicitly, merely set a cell value in the given row/col position.
On that logic, how does one remove a column? What is the recommended
approach here?
thanks
alan
http://alan.blog-city.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Adding/Deleting a Column
Posted by Kishore <ki...@gmail.com>.
hi All,
Did any body figure out to insert or add column for .xlsx file using POI ?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Adding-Deleting-a-Column-tp2309908p5711927.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: Adding/Deleting a Column
Posted by Josh Micich <jo...@gmail.com>.
Hello Alan,
The list of things to consider when moving cells is quite extensive.
There is already a bugzilla discussing this for the case of shifting
rows: https://issues.apache.org/bugzilla/show_bug.cgi?id=46742 (Note
- POI has not implemented everything yet, but we should try to have
consistent behaviour when shifting columns).
Some steps have already been taken to generalise treatment of these
issues for shifting rows / columns or any arbitrary region. The class
"FormulaShifter" is an abstraction that deals with all of the formula
related issues. Any method which currently takes FormulaShifter as a
parameter will be perfectly reusable for column moves as well.
However, we need a new method "FormulaShifter.createForColumnShift(int
externSheetIndex, int firstMovedColIndex, int lastMovedColIndex, int
numberOfColsToMove)"
Take a look at the existing method "HSSFSheet.shiftRows(int startRow,
int endRow, int n)". You could make a new method
"HSSFSheet.shiftColumns(int startColumn, int endColumn, int n)" using
your code in pastebin.com. With this new general method, the two use
cases you describe are trivial to implement:
void insertColumn(HSSFSheet s, int columnIndex) {
s.shiftColumns(columnIndex, 254, 1);
}
void deleteColumn(HSSFSheet s, int columnIndex) {
s.shiftColumns(columnIndex+1, 255, -1);
}
hope this helps,
-josh
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Adding/Deleting a Column
Posted by MSB <ma...@tiscali.co.uk>.
Thanks very much for that Alan, and I am delighted you made such rapid
progress. I will try to take a look tonight but failing that I will
definately look through the code over the weekend as I am not working - dry
stone walling at Belvoir Castle, what am I missing? Would much rather be
paddling across in a dodgy plastic canoe to grub out Dock plants from a
shingle topped island, oh the glamour!
Yours
Mark B
AR Williamson wrote:
>
> Mark (and all),
>
> Here is my first pass at the deleting of a column in a utility class:
>
> http://pastebin.com/ff806298
>
> I didn't want to go using functions that were not part of the Sheet/Row
> interfaces, hence allowing me to straddle both formats.
>
> I have ran it against a number of XLS files and it's doing what it
> should without too much fuss.
>
> A number of issues need to resolve; which isn't so much a programmatic
> problem, more of a business-rule:
>
> + What to do with the formula in the moved columns?
> + Column breaks
> + Merged regions
>
> I think by'n'large this will satisfy the vast majority of use cases,
> were by you simply want to drop a column. After all, POI isn't meant to
> replace the functionality of Excel, merely support the file format.
>
> Any input etc always appreciated.
>
> thanks
>
> alan
> http://alan.blog-city.com/
>
>
> ---------------------------------------------------------------------
> 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://www.nabble.com/Adding-Deleting-a-Column-tp25982828p26012233.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: Adding/Deleting a Column
Posted by "@lan Williamson" <al...@alanwilliamson.org>.
Mark (and all),
Here is my first pass at the deleting of a column in a utility class:
http://pastebin.com/ff806298
I didn't want to go using functions that were not part of the Sheet/Row
interfaces, hence allowing me to straddle both formats.
I have ran it against a number of XLS files and it's doing what it
should without too much fuss.
A number of issues need to resolve; which isn't so much a programmatic
problem, more of a business-rule:
+ What to do with the formula in the moved columns?
+ Column breaks
+ Merged regions
I think by'n'large this will satisfy the vast majority of use cases,
were by you simply want to drop a column. After all, POI isn't meant to
replace the functionality of Excel, merely support the file format.
Any input etc always appreciated.
thanks
alan
http://alan.blog-city.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Adding/Deleting a Column
Posted by MSB <ma...@tiscali.co.uk>.
Ah, now I was going to suggest a utility class but thought that a little bit
presumptious; if you will do this, I am confident that lots of others will
appreciate the contribution and that it might be possible to prevail on
Yegor to add it to the utility package. Must admit that I had not thought
overly long about perfromance and it will be fascinating to see just how
long it takes to work on larger sheets, I am hoping that we will both be
surprised.
All the best and I look forward to seeing the results.
Yours
Mark B
AR Williamson wrote:
>
> Mark,
>
> Let me thank you for you a well thought out reply, appreciate it.
>
> Yes, I can see there is potential for a lot of "fun" in terms of column
> management. I will write up a Utility class to help with this and
> throw it to the list.
>
> My only fear is that, while technically feasible, its going to be
> horrendously slow with large sheets; particularly if you are
> inserting/deleting columns near the left as oppose to the right.
>
> thanks and i will let you know how i get on,
>
> alan
> http://alan.blog-city.com/
>
> MSB wrote:
>> .....and, I should have made it clear that removing a cell will not cause
>> those cells to it's right top automatically re-number themselves and so
>> effectively remove the column for you. You will still need to move these
>> cells to the left manually so to speak; well at least I think that you
>> will
>> have to.
>
> ---------------------------------------------------------------------
> 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://www.nabble.com/Adding-Deleting-a-Column-tp25982828p26005004.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: Adding/Deleting a Column
Posted by "@lan Williamson" <al...@alanwilliamson.org>.
Mark,
Let me thank you for you a well thought out reply, appreciate it.
Yes, I can see there is potential for a lot of "fun" in terms of column
management. I will write up a Utility class to help with this and
throw it to the list.
My only fear is that, while technically feasible, its going to be
horrendously slow with large sheets; particularly if you are
inserting/deleting columns near the left as oppose to the right.
thanks and i will let you know how i get on,
alan
http://alan.blog-city.com/
MSB wrote:
> .....and, I should have made it clear that removing a cell will not cause
> those cells to it's right top automatically re-number themselves and so
> effectively remove the column for you. You will still need to move these
> cells to the left manually so to speak; well at least I think that you will
> have to.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Adding/Deleting a Column
Posted by MSB <ma...@tiscali.co.uk>.
.....and, I should have made it clear that removing a cell will not cause
those cells to it's right top automatically re-number themselves and so
effectively remove the column for you. You will still need to move these
cells to the left manually so to speak; well at least I think that you will
have to.
Yours
Mark B
MSB wrote:
>
> Bear in ind that I have never needed to perfrom either operation when I
> say that I do not think thata there is an 'easy' soution to either
> problem.
>
> Removing a column will be by far the easier operation to perform as all
> you will need to do is to iterate through all of the rows on the sheet and
> call the removeCell() method. This method is defined on the
> org.apache.poi.ss.usermodel.Row interface which HSSFRow and XSSFRow both
> implement.
>
> Adding a column will, I think, be a little more tricky. On the HSSFRow
> class there is a method called moveCell() which, as it's name suggestes,
> allows you to move an existing cell from one column to a new one. The only
> snag with tha method is that the javadoc says the following "Moves the
> supplied cell to a new column, which must not already have a cell there!".
> To insert a new column, I am guessing that you will again need to process
> each row iteratively. Staring with the final cell in the column, you will
> need to move this to the next column, possibly next to remove it from the
> column it once occupied and then repeat this process; however, I have
> never tried this so cannot promise any sort of success. If you are
> targeting the OpenXML based file format through XSSFRow, then you are
> likely to be in more trouble beacuse I cannot see any similarly named
> methods defined on that class. Further, even though XSSFCell allows you to
> get at the column index of the cell, the setCellNum() method - which you
> allow you to set the cells column index - is protected. Having said this,
> I will take the time to have a better dig around and post again if I
> manage to make any progress.
>
> Yours
>
> Mark B
>
>
> AR Williamson wrote:
>>
>> Good Day all.
>>
>> I am loving Apache POI at the moment, but i have some simple questions
>> that I am sure someone can help with.
>>
>> With respect to adding a new column; is it true I don't need to do this
>> explicitly, merely set a cell value in the given row/col position.
>>
>> On that logic, how does one remove a column? What is the recommended
>> approach here?
>>
>> thanks
>>
>> alan
>> http://alan.blog-city.com/
>>
>> ---------------------------------------------------------------------
>> 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://www.nabble.com/Adding-Deleting-a-Column-tp25982828p26004545.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: Adding/Deleting a Column
Posted by MSB <ma...@tiscali.co.uk>.
Bear in ind that I have never needed to perfrom either operation when I say
that I do not think thata there is an 'easy' soution to either problem.
Removing a column will be by far the easier operation to perform as all you
will need to do is to iterate through all of the rows on the sheet and call
the removeCell() method. This method is defined on the
org.apache.poi.ss.usermodel.Row interface which HSSFRow and XSSFRow both
implement.
Adding a column will, I think, be a little more tricky. On the HSSFRow class
there is a method called moveCell() which, as it's name suggestes, allows
you to move an existing cell from one column to a new one. The only snag
with tha method is that the javadoc says the following "Moves the supplied
cell to a new column, which must not already have a cell there!". To insert
a new column, I am guessing that you will again need to process each row
iteratively. Staring with the final cell in the column, you will need to
move this to the next column, possibly next to remove it from the column it
once occupied and then repeat this process; however, I have never tried this
so cannot promise any sort of success. If you are targeting the OpenXML
based file format through XSSFRow, then you are likely to be in more trouble
beacuse I cannot see any similarly named methods defined on that class.
Further, even though XSSFCell allows you to get at the column index of the
cell, the setCellNum() method - which you allow you to set the cells column
index - is protected. Having said this, I will take the time to have a
better dig around and post again if I manage to make any progress.
Yours
Mark B
AR Williamson wrote:
>
> Good Day all.
>
> I am loving Apache POI at the moment, but i have some simple questions
> that I am sure someone can help with.
>
> With respect to adding a new column; is it true I don't need to do this
> explicitly, merely set a cell value in the given row/col position.
>
> On that logic, how does one remove a column? What is the recommended
> approach here?
>
> thanks
>
> alan
> http://alan.blog-city.com/
>
> ---------------------------------------------------------------------
> 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://www.nabble.com/Adding-Deleting-a-Column-tp25982828p25996500.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