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?
Unlikely to happen, not a priority. POI will not let you create a functional equivalent to Excel.
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'.
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).