You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2015/09/09 01:05:23 UTC
[Bug 58348] New: Add support for copying rows
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Bug ID: 58348
Summary: Add support for copying rows
Product: POI
Version: unspecified
Hardware: PC
OS: Linux
Status: NEW
Severity: enhancement
Priority: P2
Component: SS Common
Assignee: dev@poi.apache.org
Reporter: javenoneal@gmail.com
I am currently working adding the ability to copy rows within POI. I've seen a
number of partial solutions online[1][2], but none shift formula references,
which is what I need.
I figured this would be a good opportunity to write this into the POI API so
others don't have to reinvent the wheel.
Here's the features I'm looking for:
* copies cell values
* copies cell styles
* copies cell formulas, offsetting relative cell references by the distance of
the row copy
* copies array formulas
* copies merged cell regions
* copies row height
* copies conditional formatting
* copies tables, pivot tables
* copies hyperlinks
* copies cell comments
For all these options, if an option isn't copied, need to determine if the
destination cell maintains its option (for example, cell style), or if the cell
is reset to the default value for that option (no style).
Features that we probably don't want/need from shiftRows:
* update Named Regions
* updates formula references that referred to the source range to refer to the
target range
The API probably needs to allow the user to choose what gets copied (paste as
value only, paste formulas only, paste styles only, paste without styles, etc).
Considerations:
* Needs to have consistent API between HSSFWorkbook, XSSFWorkbook, and
SXSSFWorkbook.
* API should allow copying a continuous or discontinuous list of rows from a
different sheet or different workbook into existing sheet.
* What is the desired behavior for copying discontinuous rows? Would
destination rows be continuous, or would discontinuities be copied over. If we
disallow discontinuous row copies, developer would need to make multiple
copyRows calls. This seems acceptable.
* What is the desired behavior for how to copy non-monotonic discontinuous
rows? Is it okay to not support this?
//shiftRows-like API, doesn't meet the inter-sheet copying criteria
Interface Sheet {
public void copyRows(int startRow, int endRow, int n);
}
//Better API:
Interface Sheet {
public void copyRows(List<Rows> srcRows, int startRow, CellCopyOptions
options);
//helper method needed to make first argument of copyRows less painful.
public List<Row> getRows(int firstRow, int lastRow); //similar to
List.subList
}
// Where should this class live? Does a similar class already exist?
SomeUtility {
class CellCopyOptions {
boolean cellValues;
boolean cellStyles;
boolean cellFormulas;
boolean mergedRegions;
boolean rowHeight;
boolean tables;
...
}
}
How should getRows work for blank rows? If copying all rows on one sheet to
another sheet, this could mean creating a list of 1 million rows if the List
has null entries to represent blank rows. Seems wasteful if the sheet only has
content on the first and last row. If it returns a List with blank rows
removed, the caller would need to rely on row.getRowNum() to determine this. We
could return a SortedMap<Int, Row> or other sparse structure which is
convenient to subdivide, but this could cause problems if a row's row number
changes after the SortedMap is created. I'm leaning towards a List that
excludes nulls, which would make behavior match Sheet.rowIterator, and rely on
row.getRowNum() to determine row number.
Until we decide if copyRows will work on discontinuous or non-monotonic rows,
we could check the input and throw an exception. What is the missing row policy
for copyRows? Consolidate rows in destination, or leave room for blank rows
(related to how getRows is implemented).
[1]
http://stackoverflow.com/questions/5785724/how-to-insert-a-row-between-two-rows-in-an-existing-excel-with-hssf-apache-poi
[2] http://www.zachhunter.com/2010/05/npoi-copy-row-helper/
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Bug 58348 depends on bug 58442, which changed state.
Bug 58442 Summary: [PATCH] Add method to in-place reorganize an AreaPtg's to be defined as upper-left and lower-right
https://bz.apache.org/bugzilla/show_bug.cgi?id=58442
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |FIXED
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Javen ONeal <ja...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Depends on| |58439
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Robert Fleming <fl...@cs.washington.edu> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |fleming@cs.washington.edu
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
--- Comment #2 from Javen ONeal <ja...@gmail.com> ---
Created attachment 33105
--> https://bz.apache.org/bugzilla/attachment.cgi?id=33105&action=edit
Progress made so far on adding copyRows to XSSF
HSSF and SXSSF implementations will follow once I have XSSF row/cell copy done.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
--- Comment #9 from Javen O'Neal <on...@apache.org> ---
Added hyperlink copying and merging (only copies a hyperlink from source if
source has a hyperlink) for XSSFWorkbooks in r1711926.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
--- Comment #1 from Javen ONeal <ja...@gmail.com> ---
Created attachment 33102
--> https://bz.apache.org/bugzilla/attachment.cgi?id=33102&action=edit
Test case workbook
Adding workbook to use for unit tests
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Javen ONeal <ja...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Depends on| |58441
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Depends on| |58572
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
--- Comment #6 from Javen ONeal <ja...@gmail.com> ---
TODO: Test for XmlDisconnectedValue errors (learn from shiftRows implementation
with bug 53798)
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Javen ONeal <ja...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Attachment #33105|0 |1
is obsolete| |
--- Comment #3 from Javen ONeal <ja...@gmail.com> ---
Created attachment 33130
--> https://bz.apache.org/bugzilla/attachment.cgi?id=33130&action=edit
Progress made so far on adding copyRows to XSSF
* Moved several pre-requisites out into their own bugs, marked as blockers for
this bug.
* Rebased to r1704452
* still only implemented for XSSFWorkbooks, with placeholders for
SXSSFWorkbooks and HSSFWorkbooks.
I am waiting on blockers being resolved before continuing to develop this
copyRows feature.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
--- Comment #8 from Javen O'Neal <on...@apache.org> ---
In this first release, the following are implemented and tested:
* copies cell values (all Excel-primitive data types)
* copies cell styles
* copies cell formulas, offsetting relative cell references by the distance of
the row copy
* copies merged cell regions
The following are implemented but not tested:
* copies row height
The following are not implemented or tested:
* copies cell comments
* copies array formulas
* copies conditional formatting
* copies tables, pivot tables
* copies hyperlinks
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Javen ONeal <ja...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Depends on| |58350
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Javen ONeal <ja...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Depends on| |58442
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Bug 58348 depends on bug 58350, which changed state.
Bug 58350 Summary: [PATCH] Make (S)XSSFSheet behavior the same as HSSFSheet when calling getMergedRegions
https://bz.apache.org/bugzilla/show_bug.cgi?id=58350
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |FIXED
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Bug 58348 depends on bug 58439, which changed state.
Bug 58439 Summary: [PATCH] Rename private FormulaShifter.adjustPtgDueToShiftMove to adjustPtgDueToSheetMove
https://bz.apache.org/bugzilla/show_bug.cgi?id=58439
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |FIXED
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Javen ONeal <ja...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Attachment #33130|0 |1
is obsolete| |
--- Comment #4 from Javen ONeal <ja...@gmail.com> ---
Created attachment 33138
--> https://bz.apache.org/bugzilla/attachment.cgi?id=33138&action=edit
[PATCH] copyRows implemented for XSSFWorkbooks
I think this patch is ready to be deployed for testing with XSSFWorkbooks.
copyRows support has been stubbed out for HSSFWorkbooks and SXSSFWorkbooks and
will be implemented in a future patch if someone has motivation to write them.
I have annotated most of the new methods with @Beta to indicate the API may
change based on your feedback.
Limitations:
* does not evaluate copied formulas or cache the result
* does not copy array formulas
* does not copy PivotTables or Tables
* I have not tested copying rows from an external workbook
After patching with the blocking bugs, this patch has a merge conflict with bug
58439 that will need to be resolved manually (or I can rebase this patch once
bug 58439 is fixed).
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Bug 58348 depends on bug 58572, which changed state.
Bug 58572 Summary: Make XSSFSheet.getHyperlinkList() and XSSFSheet.getHyperlink(row, col) available in Sheet interface
https://bz.apache.org/bugzilla/show_bug.cgi?id=58572
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |FIXED
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Bug 58348 depends on bug 58441, which changed state.
Bug 58441 Summary: [PATCH] Define equals method for CellRangeAddressBase
https://bz.apache.org/bugzilla/show_bug.cgi?id=58441
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |FIXED
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
--- Comment #7 from Javen O'Neal <on...@apache.org> ---
Row copy is now ready for testing in XSSFWorkbooks!
Applied changes for row copy support for XSSFWorkbooks in r1711864, r1711866,
r1711879, and r1711885 to trunk.
Site docs updated in r1711888.
Several minor supporting changes from attachment 33138 were made in their own
commits. When the code for HSSFWorkbooks is written, the code and tests will be
moved up to the Sheet/Row/Cell Interface and
BaseTestSheet/BaseTestRow/BaseTestCell level.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
--- Comment #5 from Javen ONeal <ja...@gmail.com> ---
TODO: add unit test for shifting or copying a formula that contains an
unregistered user-defined function (bug 58452).
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58348] Add support for copying rows
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58348
Javen ONeal <ja...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Keywords| |PatchAvailable
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org