Bug 32562 - Enable inserting new columns
Summary: Enable inserting new columns
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC Linux
: P2 enhancement with 7 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2004-12-07 13:38 UTC by aspa
Modified: 2009-04-21 20:18 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description aspa 2004-12-07 13:38:43 UTC
there should be a way of inserting a new column in the middle of existing columns with HSSF 
usermodel API in a similar fashion as rows can be inserted using HSSFSheet.shiftRows(). currently, you 
can't just create a new cell in the end of each row and then shift the values of existing cells to the left 
because the shifted cells might contain formulas and HSSFCell.setCellType() not supported for formulas 
according to documentation. also, what about other weird cell types? does HSSF support all types of 
cells that can be created by MS Excel?
Comment 1 Avik Sengupta 2005-05-30 12:02:06 UTC
Unlikely to happen, not a priority.  POI will not let you create a functional
equivalent to Excel. 
Comment 2 Amol Deshmukh 2005-05-31 16:10:14 UTC
I've been thinking about this myself and although I agree 
with the idea that POI is basically a file format translator,
this is also one of the desirable features, especially when 
creating new workbooks. (For instance when you want a 
summary table at the top that has dynamic num of rows - 
hence it is unpredictable how many rows it will occupy - 
and consists of formulas based on cells below, which in turn
have formulas...)

IMHO, POI should allow ways for external classes to hook
into the FormulaParser and add such features. Currently,
since FormulaParser can be only contructed with a Workbook
instance, it isnot possible for HSSF client classes to
parse formulas.

With a clean way to hook into the FormulaParser api, I 
imagine it should not be too much of an effort to emulate 
excel's shift row/columns functionality (AFAIK, All 
CellReferences are shifted by an X,Y offset by excel
when insert col/row is performed). Ofcourse, I'm not
suggesting that such a functionality be a part of 
POI main, but seems like it would be a good addition
to 'contrib' or 'examples'.
Comment 3 Jim Robinson 2009-04-21 20:18:57 UTC
I am working with a "template" file that is kept up to date.  A live query is running against a DB that extracts a new data attribute based on matching fields for each row of data in the sheet.  Essentially I am creating a new column if its header is not already present in the template, and populating my new data attribute in each row for that column.  

Without such an API method, I will have to iterate over every populated row in the sheet, and copy every cell from the desired column target index one cell to the right (right to left of course).