Fill Series
From Apache OpenOffice Wiki
< Documentation | DevGuide
The interface com.sun.star.sheet.XCellSeries fills out each cell of a cell range with values based on a start value, step count and fill mode. It is possible to fill a series in each direction, specified by a com.sun.star.sheet.FillDirection constant. If the fill direction is horizontal, each row of the cell range forms a separate series. Similarly each column forms a series on a vertical fill.
- The method
fillSeries()uses the first cell of each series as start value. For example, if the fill direction is "To top", the bottom-most cell of each column is used as the start value. It expects a fill mode to be used to continue the start value, a com.sun.star.sheet.FillMode constant. If the values are dates, com.sun.star.sheet.FillDateMode constants describes the mode how the dates are calculated. If the series reaches the specified end value, the calculation is stopped. - The method
fillAuto()determines the fill mode and step count automatically. It takes a parameter containing the number of cells to be examined. For example, if the fill direction is "To top" and the specified number of cells is three, the three bottom-most cells of each column are used to continue the series.
The following example may operate on the following spreadsheet:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | |||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 |
Inserting filled series in Java:
public void doSeriesSample(com.sun.star.sheet.XSpreadsheet xSheet) {
com.sun.star.sheet.XCellSeries xSeries = null;
// Fill 2 rows linear with end value -> 2nd series is not filled completely
xSeries = getCellSeries(xSheet, "A1:E2");
xSeries.fillSeries(
com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.LINEAR,
com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 9);
// Add months to a date
xSeries = getCellSeries(xSheet, "A3:E3");
xSeries.fillSeries(
com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.DATE,
com.sun.star.sheet.FillDateMode.FILL_DATE_MONTH, 1, 0x7FFFFFFF);
// Fill right to left with a text containing a value
xSeries = getCellSeries(xSheet, "A4:E4");
xSeries.fillSeries(
com.sun.star.sheet.FillDirection.TO_LEFT, com.sun.star.sheet.FillMode.LINEAR,
com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 10, 0x7FFFFFFF);
// Fill with an user defined list
xSeries = getCellSeries(xSheet, "A5:E5");
xSeries.fillSeries(
com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.AUTO,
com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 1, 0x7FFFFFFF);
// Fill bottom to top with a geometric series
xSeries = getCellSeries(xSheet, "G1:G5");
xSeries.fillSeries(
com.sun.star.sheet.FillDirection.TO_TOP, com.sun.star.sheet.FillMode.GROWTH,
com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 0x7FFFFFFF);
// Auto fill
xSeries = getCellSeries(xSheet, "A7:G9");
xSeries.fillAuto(com.sun.star.sheet.FillDirection.TO_RIGHT, 2);
}
/** Returns the XCellSeries interface of a cell range.
@param xSheet The spreadsheet containing the cell range.
@param aRange The address of the cell range.
@return The XCellSeries interface. */
private com.sun.star.sheet.XCellSeries getCellSeries(
com.sun.star.sheet.XSpreadsheet xSheet, String aRange) {
return (com.sun.star.sheet.XCellSeries) UnoRuntime.queryInterface(
com.sun.star.sheet.XCellSeries.class, xSheet.getCellRangeByName(aRange));
}
This example produces the following result:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | |||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 |
| Content on this page is licensed under the Public Documentation License (PDL). |
