Difference between revisions of "Documentation/DevGuide/Spreadsheets/Fill Series"

Revision as of 10:25, 5 June 2008

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:

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: