Adding/Deleting a Column

[AR Williamson]

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: [hidden email]
For additional commands, e-mail: [hidden email]

[Mark Beardsley]

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: [e-mail]
For additional commands, e-mail: [e-mail]

[Mark Beardsley]

.....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: [e-mail]
For additional commands, e-mail: [e-mail]

[AR Williamson]

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: [hidden email]
For additional commands, e-mail: [hidden email]

[Mark Beardsley]

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: [e-mail]
For additional commands, e-mail: [e-mail]

[AR Williamson]

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: [hidden email]
For additional commands, e-mail: [hidden email]

[Mark Beardsley]

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: [e-mail]
For additional commands, e-mail: [e-mail]

[Josh Micich]

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: [hidden email]
For additional commands, e-mail: [hidden email]

[Kishore]

hi All,

Did any body figure out to insert or add column for .xlsx file using POI ?