Bug 46742 - Remaining functionality for Sheet.shiftRows()
Summary: Remaining functionality for Sheet.shiftRows()
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.5-dev
Hardware: All All
: P5 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
Keywords: PatchAvailable
Depends on: 54533 52903 56454 56497 58557
  Show dependency tree
Reported: 2009-02-19 19:36 UTC by Josh Micich
Modified: 2015-10-31 12:25 UTC (History)
2 users (show)

Proposed patch for hyperlink shift (3.65 KB, patch)
2014-05-15 13:44 UTC, Alessandro Guarascio
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Josh Micich 2009-02-19 19:36:12 UTC
The set of adjustments that potentially need to be done by shiftRows() is very large.  Currently, POI's implementation covers the most commonly needed adjustments, but is nonetheless incomplete.

This bugzilla has been created to outline the functionality still missing from shiftRows(). As I understand, here is the full list of potential adjustments:

 - Cell Values
 - Cell Formats (+row heights etc)
 - Merged Cells
 - Cell Comments
 - Print Areas
 - Active/selected cells
 - Page Breaks
 - Row Groupings (needed?)
 - Formulas
    - Cell formulas
    - Defined names
    - Conditional formats
    - Data validations
    - VB/Form object formula properties e.g. EmbeddedObjectRefSubRecord.field_2_refPtg

Does anyone have anything to add/remove to this list?

Next, POI's current behaviour should be determined.  From what I understand, HSSF has slightly better coverage. XSSF does not adjust comments, page breaks or conditional formats. Neither HSSF nor XSSF adjust print areas, active/selected cells, row groupings, defined names, data validations, VB/form object formula properties.
It would probably be a good idea to update the javadoc to reflect our understanding.

The remaining functionality needs to be prioritised.  I would suggest the following (according to perceived usefulness):

 - Defined names
 - Conditional formats
 - Data validations
 - Print Areas
 - Page Breaks
 - Cell Comments
 - Row Groupings
 - VB/Form object formula properties
 - Active/selected cells

Future enhancement requests may bump the priorities.  Some items may get done out of order due to relative ease.

This record may remain open for a long time, hence the severity and priority have been set low.  Work on any one of these items probably should be tracked in separate bugzilla records which can be referenced here.
Comment 1 Yegor Kozlov 2009-02-19 23:44:53 UTC
I would add these (in priority order):

 - Repeating rows and columns (workbook.setRepeatingRowsAndColumns )
 - Hyperlinks attached to cells
 - Anchors of drawings (needed?)
   - pictures and shapes anchored to cells
   - drop-down lists with the data set as cell area reference
 - pivot areas ( we should at least check that shiftRows doesn't break .xls with pivot areas)
 - Excel charts (I'm not sure what this involves)

shiftRows is getting complicated. It includes more functionality than a single method can fit. It may be worth putting the logic in a class and use in HSSF-XSSF, or make this class abstract and have HSSF and XSSF implement a method per each operation. 

All common functionality should be tested in terms of common org.apache.poi.ss.usermodel.* interfaces. This should be true for common HSSF-XSSF tests in general, not only for shiftRows. There is an attempt to do that in org.apache.poi.ss.usermodel.BaseTestSheetShiftRows and I expect that we will follow this approach.

As to priorities, I agree that the highest one is Defined names. Without it shiftRows does not guarantee the consistence of sheet formulas. The other items can be shuffled. 
XSSF does not yet support Conditional formats and Data validations and I doubt it will be included in 3.5-final. Also, XSSF does not yet process VB/Form formulas. 

My adjusted list:

 - Defined names (XSSF and HSSF)
 - Conditional formats (HSSF)
 - Data validations (HSSF)
 - Print Areas (XSSF and HSSF)
 - Repeated Rows / Columns (XSSF and HSSF)
 - Page Breaks (XSSF and HSSF)
 - Cell Comments (XSSF and HSSF)
 - Hyperlinks (XSSF and HSSF)
 - Row Groupings (XSSF and HSSF)
 - VB/Form object formula properties (HSSF)
 - Active/selected cells (XSSF and HSSF)

Comment 2 Yegor Kozlov 2009-02-25 11:15:16 UTC
I added support for shifting named ranges both for HSSF and XSSF. With FormulaShifter it was really easy, just a few lines of code. 

Comment 3 Alessandro Guarascio 2014-05-15 13:44:50 UTC
Created attachment 31626 [details]
Proposed patch for hyperlink shift

Here is a patch proposal to support hyperlink shift.
It is based on POI 3.10 and it works only for XSSF
Comment 4 Javen O'Neal 2015-10-27 08:41:55 UTC
(In reply to Alessandro Guarascio from comment #3)
> Created attachment 31626 [details]
> Proposed patch for hyperlink shift
> Here is a patch proposal to support hyperlink shift.
> It is based on POI 3.10 and it works only for XSSF

To keep this organized, I've moved XSSF hyperlink shifting to bug 58557 and made it a blocker of this bug.
Comment 5 Dominik Stadler 2015-10-29 08:47:15 UTC
Bug 52903 discusses Hyperlinks in HSSF documents.