1*cf279e26SAndrew Rist /************************************************************** 2*cf279e26SAndrew Rist * 3*cf279e26SAndrew Rist * Licensed to the Apache Software Foundation (ASF) under one 4*cf279e26SAndrew Rist * or more contributor license agreements. See the NOTICE file 5*cf279e26SAndrew Rist * distributed with this work for additional information 6*cf279e26SAndrew Rist * regarding copyright ownership. The ASF licenses this file 7*cf279e26SAndrew Rist * to you under the Apache License, Version 2.0 (the 8*cf279e26SAndrew Rist * "License"); you may not use this file except in compliance 9*cf279e26SAndrew Rist * with the License. You may obtain a copy of the License at 10*cf279e26SAndrew Rist * 11*cf279e26SAndrew Rist * http://www.apache.org/licenses/LICENSE-2.0 12*cf279e26SAndrew Rist * 13*cf279e26SAndrew Rist * Unless required by applicable law or agreed to in writing, 14*cf279e26SAndrew Rist * software distributed under the License is distributed on an 15*cf279e26SAndrew Rist * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16*cf279e26SAndrew Rist * KIND, either express or implied. See the License for the 17*cf279e26SAndrew Rist * specific language governing permissions and limitations 18*cf279e26SAndrew Rist * under the License. 19*cf279e26SAndrew Rist * 20*cf279e26SAndrew Rist *************************************************************/ 21*cf279e26SAndrew Rist 22cdf0e10cSrcweir 23cdf0e10cSrcweir using System; 24cdf0e10cSrcweir 25cdf0e10cSrcweir 26cdf0e10cSrcweir // __________ implementation ____________________________________ 27cdf0e10cSrcweir 28cdf0e10cSrcweir /** Create a spreadsheet document and provide access to table contents. 29cdf0e10cSrcweir */ 30cdf0e10cSrcweir public class GeneralTableSample : SpreadsheetDocHelper 31cdf0e10cSrcweir { 32cdf0e10cSrcweir Main( String [] args )33cdf0e10cSrcweir public static void Main( String [] args ) 34cdf0e10cSrcweir { 35cdf0e10cSrcweir try 36cdf0e10cSrcweir { 37cdf0e10cSrcweir using ( GeneralTableSample aSample = 38cdf0e10cSrcweir new GeneralTableSample( args ) ) 39cdf0e10cSrcweir { 40cdf0e10cSrcweir aSample.doSampleFunction(); 41cdf0e10cSrcweir } 42cdf0e10cSrcweir Console.WriteLine( "Sample done." ); 43cdf0e10cSrcweir } 44cdf0e10cSrcweir catch (Exception ex) 45cdf0e10cSrcweir { 46cdf0e10cSrcweir Console.WriteLine( "Sample caught exception! " + ex ); 47cdf0e10cSrcweir } 48cdf0e10cSrcweir } 49cdf0e10cSrcweir 50cdf0e10cSrcweir // ________________________________________________________________ 51cdf0e10cSrcweir GeneralTableSample( String[] args )52cdf0e10cSrcweir public GeneralTableSample( String[] args ) : base( args ) 53cdf0e10cSrcweir { 54cdf0e10cSrcweir } 55cdf0e10cSrcweir 56cdf0e10cSrcweir // ________________________________________________________________ 57cdf0e10cSrcweir 58cdf0e10cSrcweir /// This sample function modifies cells and cell ranges. doSampleFunction()59cdf0e10cSrcweir public void doSampleFunction() 60cdf0e10cSrcweir { 61cdf0e10cSrcweir // for common usage 62cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); 63cdf0e10cSrcweir unoidl.com.sun.star.beans.XPropertySet xPropSet = null; 64cdf0e10cSrcweir unoidl.com.sun.star.table.XCell xCell = null; 65cdf0e10cSrcweir unoidl.com.sun.star.table.XCellRange xCellRange = null; 66cdf0e10cSrcweir 67cdf0e10cSrcweir // *** Access and modify a VALUE CELL *** 68cdf0e10cSrcweir Console.WriteLine( "*** Sample for service table.Cell ***" ); 69cdf0e10cSrcweir 70cdf0e10cSrcweir xCell = xSheet.getCellByPosition( 0, 0 ); 71cdf0e10cSrcweir // Set cell value. 72cdf0e10cSrcweir xCell.setValue( 1234 ); 73cdf0e10cSrcweir 74cdf0e10cSrcweir // Get cell value. 75cdf0e10cSrcweir double nDblValue = xCell.getValue() * 2; 76cdf0e10cSrcweir xSheet.getCellByPosition( 0, 1 ).setValue( nDblValue ); 77cdf0e10cSrcweir 78cdf0e10cSrcweir // *** Create a FORMULA CELL and query error type *** 79cdf0e10cSrcweir xCell = xSheet.getCellByPosition( 0, 2 ); 80cdf0e10cSrcweir // Set formula string. 81cdf0e10cSrcweir xCell.setFormula( "=1/0" ); 82cdf0e10cSrcweir 83cdf0e10cSrcweir // Get error type. 84cdf0e10cSrcweir bool bValid = (xCell.getError() == 0); 85cdf0e10cSrcweir // Get formula string. 86cdf0e10cSrcweir String aText = "The formula " + xCell.getFormula() + " is "; 87cdf0e10cSrcweir aText += bValid ? "valid." : "erroneous."; 88cdf0e10cSrcweir 89cdf0e10cSrcweir // *** Insert a TEXT CELL using the XText interface *** 90cdf0e10cSrcweir xCell = xSheet.getCellByPosition( 0, 3 ); 91cdf0e10cSrcweir unoidl.com.sun.star.text.XText xCellText = 92cdf0e10cSrcweir (unoidl.com.sun.star.text.XText) xCell; 93cdf0e10cSrcweir unoidl.com.sun.star.text.XTextCursor xTextCursor = 94cdf0e10cSrcweir xCellText.createTextCursor(); 95cdf0e10cSrcweir xCellText.insertString( xTextCursor, aText, false ); 96cdf0e10cSrcweir 97cdf0e10cSrcweir // *** Change cell properties *** 98cdf0e10cSrcweir int nValue = bValid ? 0x00FF00 : 0xFF4040; 99cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCell; 100cdf0e10cSrcweir xPropSet.setPropertyValue( 101cdf0e10cSrcweir "CellBackColor", new uno.Any( (Int32) nValue ) ); 102cdf0e10cSrcweir 103cdf0e10cSrcweir 104cdf0e10cSrcweir // *** Accessing a CELL RANGE *** 105cdf0e10cSrcweir Console.WriteLine( "*** Sample for service table.CellRange ***" ); 106cdf0e10cSrcweir 107cdf0e10cSrcweir // Accessing a cell range over its position. 108cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByPosition( 2, 0, 3, 1 ); 109cdf0e10cSrcweir 110cdf0e10cSrcweir // Change properties of the range. 111cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; 112cdf0e10cSrcweir xPropSet.setPropertyValue( 113cdf0e10cSrcweir "CellBackColor", new uno.Any( (Int32) 0x8080FF ) ); 114cdf0e10cSrcweir 115cdf0e10cSrcweir // Accessing a cell range over its name. 116cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "C4:D5" ); 117cdf0e10cSrcweir 118cdf0e10cSrcweir // Change properties of the range. 119cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; 120cdf0e10cSrcweir xPropSet.setPropertyValue( 121cdf0e10cSrcweir "CellBackColor", new uno.Any( (Int32) 0xFFFF80 ) ); 122cdf0e10cSrcweir 123cdf0e10cSrcweir 124cdf0e10cSrcweir // *** Using the CELL CURSOR to add some data below of 125cdf0e10cSrcweir // the filled area *** 126cdf0e10cSrcweir Console.WriteLine( "*** Sample for service table.CellCursor ***" ); 127cdf0e10cSrcweir 128cdf0e10cSrcweir // Create a cursor using the XSpreadsheet method createCursorByRange() 129cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "A1" ); 130cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSheetCellRange xSheetCellRange = 131cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XSheetCellRange) xCellRange; 132cdf0e10cSrcweir 133cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSheetCellCursor xSheetCellCursor = 134cdf0e10cSrcweir xSheet.createCursorByRange( xSheetCellRange ); 135cdf0e10cSrcweir unoidl.com.sun.star.table.XCellCursor xCursor = 136cdf0e10cSrcweir (unoidl.com.sun.star.table.XCellCursor) xSheetCellCursor; 137cdf0e10cSrcweir 138cdf0e10cSrcweir // Move to the last filled cell. 139cdf0e10cSrcweir xCursor.gotoEnd(); 140cdf0e10cSrcweir // Move one row down. 141cdf0e10cSrcweir xCursor.gotoOffset( 0, 1 ); 142cdf0e10cSrcweir xCursor.getCellByPosition( 0, 0 ).setFormula( 143cdf0e10cSrcweir "Beyond of the last filled cell." ); 144cdf0e10cSrcweir 145cdf0e10cSrcweir 146cdf0e10cSrcweir // *** Modifying COLUMNS and ROWS *** 147cdf0e10cSrcweir Console.WriteLine( "*** Sample for services table.TableRows and " + 148cdf0e10cSrcweir "table.TableColumns ***" ); 149cdf0e10cSrcweir 150cdf0e10cSrcweir unoidl.com.sun.star.table.XColumnRowRange xCRRange = 151cdf0e10cSrcweir (unoidl.com.sun.star.table.XColumnRowRange) xSheet; 152cdf0e10cSrcweir unoidl.com.sun.star.table.XTableColumns xColumns = 153cdf0e10cSrcweir xCRRange.getColumns(); 154cdf0e10cSrcweir unoidl.com.sun.star.table.XTableRows xRows = xCRRange.getRows(); 155cdf0e10cSrcweir 156cdf0e10cSrcweir // Get column C by index (interface XIndexAccess). 157cdf0e10cSrcweir uno.Any aColumnObj = xColumns.getByIndex( 2 ); 158cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aColumnObj.Value; 159cdf0e10cSrcweir xPropSet.setPropertyValue( "Width", new uno.Any( (Int32) 5000 ) ); 160cdf0e10cSrcweir 161cdf0e10cSrcweir // Get the name of the column. 162cdf0e10cSrcweir unoidl.com.sun.star.container.XNamed xNamed = 163cdf0e10cSrcweir (unoidl.com.sun.star.container.XNamed) aColumnObj.Value; 164cdf0e10cSrcweir aText = "The name of this column is " + xNamed.getName() + "."; 165cdf0e10cSrcweir xSheet.getCellByPosition( 2, 2 ).setFormula( aText ); 166cdf0e10cSrcweir 167cdf0e10cSrcweir // Get column D by name (interface XNameAccess). 168cdf0e10cSrcweir unoidl.com.sun.star.container.XNameAccess xColumnsName = 169cdf0e10cSrcweir (unoidl.com.sun.star.container.XNameAccess) xColumns; 170cdf0e10cSrcweir 171cdf0e10cSrcweir aColumnObj = xColumnsName.getByName( "D" ); 172cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aColumnObj.Value; 173cdf0e10cSrcweir xPropSet.setPropertyValue( 174cdf0e10cSrcweir "IsVisible", new uno.Any( (Boolean) false ) ); 175cdf0e10cSrcweir 176cdf0e10cSrcweir // Get row 7 by index (interface XIndexAccess) 177cdf0e10cSrcweir uno.Any aRowObj = xRows.getByIndex( 6 ); 178cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aRowObj.Value; 179cdf0e10cSrcweir xPropSet.setPropertyValue( "Height", new uno.Any( (Int32) 5000 ) ); 180cdf0e10cSrcweir 181cdf0e10cSrcweir xSheet.getCellByPosition( 2, 6 ).setFormula( "What a big cell." ); 182cdf0e10cSrcweir 183cdf0e10cSrcweir // Create a cell series with the values 1 ... 7. 184cdf0e10cSrcweir for (int nRow = 8; nRow < 15; ++nRow) 185cdf0e10cSrcweir xSheet.getCellByPosition( 0, nRow ).setValue( nRow - 7 ); 186cdf0e10cSrcweir // Insert a row between 1 and 2 187cdf0e10cSrcweir xRows.insertByIndex( 9, 1 ); 188cdf0e10cSrcweir // Delete the rows with the values 3 and 4. 189cdf0e10cSrcweir xRows.removeByIndex( 11, 2 ); 190cdf0e10cSrcweir 191cdf0e10cSrcweir // *** Inserting CHARTS *** 192cdf0e10cSrcweir Console.WriteLine( "*** Sample for service table.TableCharts ***" ); 193cdf0e10cSrcweir 194cdf0e10cSrcweir unoidl.com.sun.star.table.XTableChartsSupplier xChartsSupp = 195cdf0e10cSrcweir (unoidl.com.sun.star.table.XTableChartsSupplier) xSheet; 196cdf0e10cSrcweir unoidl.com.sun.star.table.XTableCharts xCharts = 197cdf0e10cSrcweir xChartsSupp.getCharts(); 198cdf0e10cSrcweir 199cdf0e10cSrcweir // The chart will base on the last cell series, initializing all values. 200cdf0e10cSrcweir String aName = "newChart"; 201cdf0e10cSrcweir unoidl.com.sun.star.awt.Rectangle aRect = 202cdf0e10cSrcweir new unoidl.com.sun.star.awt.Rectangle(); 203cdf0e10cSrcweir aRect.X = 10000; 204cdf0e10cSrcweir aRect.Y = 3000; 205cdf0e10cSrcweir aRect.Width = aRect.Height = 5000; 206cdf0e10cSrcweir unoidl.com.sun.star.table.CellRangeAddress[] aRanges = 207cdf0e10cSrcweir new unoidl.com.sun.star.table.CellRangeAddress[1]; 208cdf0e10cSrcweir aRanges[0] = createCellRangeAddress( xSheet, "A9:A14" ); 209cdf0e10cSrcweir 210cdf0e10cSrcweir // Create the chart. 211cdf0e10cSrcweir xCharts.addNewByName( aName, aRect, aRanges, false, false ); 212cdf0e10cSrcweir 213cdf0e10cSrcweir // Get the chart by name. 214cdf0e10cSrcweir uno.Any aChartObj = xCharts.getByName( aName ); 215cdf0e10cSrcweir unoidl.com.sun.star.table.XTableChart xChart = 216cdf0e10cSrcweir (unoidl.com.sun.star.table.XTableChart) aChartObj.Value; 217cdf0e10cSrcweir 218cdf0e10cSrcweir // Query the state of row and column headers. 219cdf0e10cSrcweir aText = "Chart has column headers: "; 220cdf0e10cSrcweir aText += xChart.getHasColumnHeaders() ? "yes" : "no"; 221cdf0e10cSrcweir xSheet.getCellByPosition( 2, 8 ).setFormula( aText ); 222cdf0e10cSrcweir aText = "Chart has row headers: "; 223cdf0e10cSrcweir aText += xChart.getHasRowHeaders() ? "yes" : "no"; 224cdf0e10cSrcweir xSheet.getCellByPosition( 2, 9 ).setFormula( aText ); 225cdf0e10cSrcweir } 226cdf0e10cSrcweir 227cdf0e10cSrcweir } 228