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