Fill Series

From Apache OpenOffice Wiki
Jump to: navigation, search



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
1
2
4
3
01/30/2002
4
Text 10
5
Jan
10
6
7
1
2
8
05/28/2002
02/28/2002
9
6
4

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
1
3
5
7
9
160
2
4
6
8
80
3
01/30/2002
02/28/2002
03/30/2002
04/30/2002
05/30/2002
40
4
Text 50
Text 40
Text 30
Text 20
Text 10
20
5
Jan
Feb
Mar
Apr
May
10
6
7
1
2
3
4
5
6
7
8
05/28/2002
02/28/2002
11/28/2001
08/28/2001
05/28/2001
02/28/2001
11/28/2000
9
6
4
2
0
-2
-4
-6


Content on this page is licensed under the Public Documentation License (PDL).
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/DevGuide/Spreadsheets/Fill_Series&oldid=246952"
Personal tools
In other languages