Other Table Operations

Protecting Spreadsheets
Sheet Outline
Detective
Other Table Operations

Overall Document Features

Spreadsheet Document Controller

Spreadsheet Add-Ins

< Previous Page

Next Page >

Data Validation

Data validation checks if a user entered valid entries.

TableValidation

A cell or cell range object contains the properties Validation and ValidationLocal. They return the interface com.sun.star.beans.XPropertySet of the validation object com.sun.star.sheet.TableValidation. The objects of both properties are equal, except the representation of formulas. The ValidationLocal property uses function names in the current language).

After the validation settings are changed, the validation object is reinserted into the property set of the cell or cell range.

Type (type com.sun.star.sheet.ValidationType): Describes the type of data the cells contain. In text cells, it is possible to check the length of the text.
IgnoreBlankCells: Determines if blank cells are valid.
ShowInputMessage, InputTitle and InputMessage: These properties describe the message that appears if a cell of the validation area is selected.
ShowErrorMessage, ErrorTitle, ErrorMessage and ErrorAlertStyle (type com.sun.star.sheet.ValidationAlertStyle): These properties describe the error message that appear if an invalid value has been entered. If the alert style is STOP, all invalid values are rejected. With the alerts WARNING and INFO, it is possible to keep invalid values. The alert MACRO starts a macro on invalid values. The property ErrorTitle has to contain the name of the macro.

The interface com.sun.star.sheet.XSheetCondition sets the conditions for valid values. The comparison operator, the first and second formula and the base address for relative references in formulas.

The following example enters values between 0.0 and 5.0 in a cell range. The xSheet is the interface com.sun.star.sheet.XSpreadsheet of a spreadsheet.

  // --- Data validation ---
  com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName("A7:C7");
  com.sun.star.beans.XPropertySet xCellPropSet = (com.sun.star.beans.XPropertySet)
      UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xCellRange);
 
  // validation properties
  com.sun.star.beans.XPropertySet xValidPropSet = (com.sun.star.beans.XPropertySet)
      xCellPropSet.getPropertyValue("Validation");
  xValidPropSet.setPropertyValue("Type", com.sun.star.sheet.ValidationType.DECIMAL);
  xValidPropSet.setPropertyValue("ShowErrorMessage", new Boolean(true));
  xValidPropSet.setPropertyValue("ErrorMessage", "This is an invalid value!");
  xValidPropSet.setPropertyValue("ErrorAlertStyle", com.sun.star.sheet.ValidationAlertStyle.STOP);
 
  // condition
  com.sun.star.sheet.XSheetCondition xCondition = (com.sun.star.sheet.XSheetCondition)
      UnoRuntime.queryInterface(com.sun.star.sheet.XSheetCondition.class, xValidPropSet);
  xCondition.setOperator(com.sun.star.sheet.ConditionOperator.BETWEEN);
  xCondition.setFormula1("0.0");
  xCondition.setFormula2("5.0");
 
  // apply on cell range
  xCellPropSet.setPropertyValue("Validation", xValidPropSet);

Validating macro

The macro started on invalid values has two arguments of type String.

The first argument is the exact string typed by the user. It is always a string, even for numbers. It may differ from the value displayed by the cell.

The second argument is a string that represents the cell address, example : Sheet3.B17

The macro should return a Boolean value. If it returns nothing, or if it returns True, the input is accepted and the cell is updated according to the input.

If the macro decides that the input is incorrect, it may set a value in the cell and then it returns False to reject the input.

Data Consolidation

The data consolidation feature calculates results based on several cell ranges.

ConsolidationDescriptor

The com.sun.star.sheet.XConsolidatable's method createConsolidationDescriptor() returns the interface com.sun.star.sheet.XConsolidationDescriptor of a consolidation descriptor (service com.sun.star.sheet.ConsolidationDescriptor). This descriptor contains all data needed for a consolidation. It is possible to get and set all properties:

getFunction() and setFunction(): The function for calculation, type com.sun.star.sheet.GeneralFunction.
getSources() and setSources(): A sequence of com.sun.star.table.CellRangeAddress structs with all cell ranges containing the source data.
getStartOutputPosition() and setStartOutputPosition(): A com.sun.star.table.CellAddress containing the first cell of the result cell range.
getUseColumnHeaders(), setUseColumnHeaders(), getUseRowHeaders() and setUseRowHeaders(): Determine if the first column or row of each cell range is used to find matching data.
getInsertLinks() and setInsertLinks(): Determine if the results are linked to the source data (formulas are inserted) or not (only results are inserted).

The method consolidate() of the interface com.sun.star.sheet.XConsolidatable performs a consolidation with the passed descriptor.

Charts

TableCharts

The service com.sun.star.table.TableChart represents a chart object. The interface com.sun.star.table.XTableChart provides access to the cell range of the source data and controls the existence of column and row headers.

The service com.sun.star.table.TableChart does not represent the chart document, but the object in the table that contains the chart document. The interface com.sun.star.document.XEmbeddedObjectSupplier provides access to that chart document. For further information, see Charts.

The interface com.sun.star.container.XNamed retrieves and changes the name of the chart object.

For further information about charts, see Charts.

The service com.sun.star.table.TableCharts represents the collection of all chart objects contained in the table. It implements the interfaces:

com.sun.star.table.XTableCharts to create new charts and accessing them by their names.
com.sun.star.container.XIndexAccess to access the charts by the insertion index.
com.sun.star.container.XEnumerationAccess to create an enumeration of all charts.

The following example shows how xCharts can be a com.sun.star.table.XTableCharts interface of a collection of charts.

  // *** Inserting CHARTS ***
  String aName = "newChart";
  com.sun.star.awt.Rectangle aRect = new com.sun.star.awt.Rectangle();
  aRect.X = 10000;
  aRect.Y = 3000;
  aRect.Width = aRect.Height = 5000;
 
  com.sun.star.table.CellRangeAddress[] aRanges = new com.sun.star.table.CellRangeAddress[1];
  aRanges[0] = new com.sun.star.table.CellRangeAddress();
  aRanges[0].Sheet = aRanges[0].StartColumn = aRanges[0].EndColumn = 0;
  aRanges[0].StartRow = 0; aRanges[0].EndRow = 9;
 
  // Create the chart.
  xCharts.addNewByName(aName, aRect, aRanges, false, false);
 
  // Get the chart by name.
  Object aChartObj = xCharts.getByName(aName);
  com.sun.star.table.XTableChart xChart = (com.sun.star.table.XTableChart)
      UnoRuntime.queryInterface(com.sun.star.table.XTableChart.class, aChartObj);
 
  // Query the state of row and column headers.
  aText = "Chart has column headers: ";
  aText += xChart.getHasColumnHeaders() ? "yes" : "no";
  System.out.println(aText);
  aText = "Chart has row headers: ";
  aText += xChart.getHasRowHeaders() ? "yes" : "no";
  System.out.println(aText);

Scenarios

A set of scenarios contains different selectable cell contents for one or more cell ranges in a spreadsheet. The data of each scenario in this set is stored in a hidden sheet following the scenario sheet. To change the scenario's data, its hidden sheet has to be modified.

Scenarios

The com.sun.star.sheet.XScenariosSupplier's method getScenarios() returns the interface com.sun.star.sheet.XScenarios of the scenario set of the spreadsheet. This scenario set is represented by the service com.sun.star.sheet.Scenarios containing spreadsheet objects. It is possible to access the scenarios through their names that is equal to the name of the corresponding spreadsheet, their index, or using an enumeration (represented by the service com.sun.star.sheet.ScenariosEnumeration).

The interface com.sun.star.sheet.XScenarios inserts and removes scenarios:

The method addNewByName() adds a scenario with the given name that contains the specified cell ranges.
The method removeByName() removes the scenario (the spreadsheet) with the given name.

The following method shows how to create a scenario:

  /** Inserts a scenario containing one cell range into a sheet and applies the value array.
      @param xSheet The XSpreadsheet interface of the spreadsheet.
      @param aRange The range address for the scenario.
      @param aValueArray The array of cell contents.
      @param aScenarioName The name of the new scenario.
      @param aScenarioComment The user comment for the scenario.
   */
  public void insertScenario(
          com.sun.star.sheet.XSpreadsheet xSheet,
          String aRange,
          Object[][] aValueArray,
          String aScenarioName,
          String aScenarioComment ) throws RuntimeException, Exception {
      // get the cell range with the given address
      com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName(aRange);
 
      // create the range address sequence
      com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable)
          UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeAddressable.class, xCellRange);
      com.sun.star.table.CellRangeAddress[] aRangesSeq = new com.sun.star.table.CellRangeAddress[1];
      aRangesSeq[0] = xAddr.getRangeAddress();
 
      // create the scenario
      com.sun.star.sheet.XScenariosSupplier xScenSupp = (com.sun.star.sheet.XScenariosSupplier)
          UnoRuntime.queryInterface(com.sun.star.sheet.XScenariosSupplier.class, xSheet);
      com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios();
      xScenarios.addNewByName(aScenarioName, aRangesSeq, aScenarioComment);
 
      // insert the values into the range
      com.sun.star.sheet.XCellRangeData xData = (com.sun.star.sheet.XCellRangeData)
          UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeData.class, xCellRange);
      xData.setDataArray(aValueArray);
  }

The service com.sun.star.sheet.Spreadsheet implements the interface com.sun.star.sheet.XScenario to modify an existing scenario:

The method getIsScenario() tests if this spreadsheet is used to store scenario data.
The methods getScenarioComment() and setScenarioComment() retrieves and sets the user comment for this scenario.
The method addRanges() adds new cell ranges to the scenario.
The method apply() copies the data of this scenario to the spreadsheet containing the scenario set, that is, it makes the scenario visible.

The following method shows how to activate a scenario:

  /** Activates a scenario.
      @param xSheet The XSpreadsheet interface of the spreadsheet.
      @param aScenarioName The name of the scenario.
   */
  public void showScenario( com.sun.star.sheet.XSpreadsheet xSheet,
          String aScenarioName) throws RuntimeException, Exception {
      // get the scenario set
      com.sun.star.sheet.XScenariosSupplier xScenSupp = (com.sun.star.sheet.XScenariosSupplier)
          UnoRuntime.queryInterface(com.sun.star.sheet.XScenariosSupplier.class, xSheet);
      com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios();
 
      // get the scenario and activate it
      Object aScenarioObj = xScenarios.getByName(aScenarioName);
      com.sun.star.sheet.XScenario xScenario = (com.sun.star.sheet.XScenario)
          UnoRuntime.queryInterface(com.sun.star.sheet.XScenario.class, aScenarioObj);
      xScenario.apply();
  }

Content on this page is licensed under the Public Documentation License (PDL).

Other Table Operations

Data Validation

Validating macro

Data Consolidation

Charts

Scenarios

Views

Personal tools

Navigation

Search

Tools

In other languages