public void outParameters(out bool aBool, out byte aByte,
out short aShort, out ushort aUShort,
out int aInt, out uint aUInt,
out long aLong, out ulong aULong,
out float aFloat, out double aDouble,
out char aChar, out string aString,
out Type aType, out uno.Any aAny,
out Enum2 aEnum, out Struct1 aStruct,
out object aXInterface,
out unoidl.com.sun.star.lang.XComponent aXComponent,
out bool[] seqBool)
{
aBool = m_Bool;
aByte = m_Byte;
aShort = m_Short;
aUShort = m_UShort;
aInt = m_Int;
aUInt = m_UInt;
aLong = m_Long;
aULong = m_ULong;
aFloat = m_Float;
aDouble = m_Double;
aChar = m_Char;
aString = m_String;
aType = m_Type;
aAny = m_Any;
aEnum = m_Enum2;
aStruct = m_Struct1;
aXInterface = m_XInterface;
aXComponent = m_XComponent;
seqBool = m_seqBool;
}
public void inParameters(bool aBool, byte aByte,
short aShort, ushort aUShort,
int aInt, uint aUInt,
long aLong, ulong aULong,
float aFloat, double aDouble,
char aChar, string aString,
Type aType, uno.Any aAny,
Enum2 aEnum, Struct1 aStruct,
object aXInterface,
unoidl.com.sun.star.lang.XComponent aXComponent,
bool[] seqBool)
{
m_Bool = aBool;
m_Byte = aByte;
m_Short = aShort;
m_UShort = aUShort;
m_Int = aInt;
m_UInt = aUInt;
m_Long = aLong;
m_ULong = aULong;
m_Float = aFloat;
m_Double = aDouble;
m_Char = aChar;
m_String = aString;
m_Type = aType;
m_Any = aAny;
m_Enum2 = aEnum;
m_Struct1 = aStruct;
m_XInterface = aXInterface;
m_XComponent = aXComponent;
m_seqBool = seqBool;
}
/** Returns a list of addresses of all cell ranges contained in the
* collection.
*
* @param xRangesIA The XIndexAccess interface of the collection.
* @return A string containing the cell range address list. */
public String getCellRangeListString(
unoidl.com.sun.star.container.XIndexAccess xRangesIA)
{
String aStr = "";
int nCount = xRangesIA.getCount();
for (int nIndex = 0; nIndex < nCount; ++nIndex)
{
if (nIndex > 0)
{
aStr += " ";
}
uno.Any aRangeObj = xRangesIA.getByIndex(nIndex);
unoidl.com.sun.star.sheet.XSheetCellRange xCellRange =
(unoidl.com.sun.star.sheet.XSheetCellRange)aRangeObj.Value;
aStr += getCellRangeAddressString(xCellRange, false);
}
return(aStr);
}
private void RunMacro(XComponent document, string macroName, object[] macroParam)
{
var providerSupplier = (XScriptProviderSupplier)document;
if (document != null)
{
var provider = providerSupplier.getScriptProvider();
var script = provider.getScript(
String.Format("vnd.sun.star.script:{0}?language=Basic&location=document", macroName));
// Convert macroParam to Any array
var macroParamAny = Array.ConvertAll(macroParam, item => new Any(item.GetType(), item));
var param = new Any[] {
new Any(macroParamAny.GetType(), macroParamAny)};
var outParamIndex = new short[0];
var outParam = new Any[0];
script.invoke(param, out outParamIndex, out outParam);
}
}
// ________________________________________________________________
/** All samples regarding the formatting of cells and ranges. */
private void doFormattingSamples()
{
Console.WriteLine( "\n*** Formatting samples ***\n" );
unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 1 );
unoidl.com.sun.star.table.XCellRange xCellRange;
unoidl.com.sun.star.beans.XPropertySet xPropSet = null;
unoidl.com.sun.star.container.XIndexAccess xRangeIA = null;
unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager;
// --- Cell styles ---
// get the cell style container
unoidl.com.sun.star.style.XStyleFamiliesSupplier xFamiliesSupplier =
(unoidl.com.sun.star.style.XStyleFamiliesSupplier) getDocument();
unoidl.com.sun.star.container.XNameAccess xFamiliesNA =
xFamiliesSupplier.getStyleFamilies();
uno.Any aCellStylesObj = xFamiliesNA.getByName( "CellStyles" );
unoidl.com.sun.star.container.XNameContainer xCellStylesNA =
(unoidl.com.sun.star.container.XNameContainer) aCellStylesObj.Value;
// create a new cell style
xServiceManager =
(unoidl.com.sun.star.lang.XMultiServiceFactory) getDocument();
Object aCellStyle = xServiceManager.createInstance(
"com.sun.star.style.CellStyle" );
String aStyleName = "MyNewCellStyle";
xCellStylesNA.insertByName(
aStyleName, new uno.Any( typeof (Object), aCellStyle ) );
// modify properties of the new style
xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aCellStyle;
xPropSet.setPropertyValue(
"CellBackColor", new uno.Any( (Int32) 0x888888 ) );
xPropSet.setPropertyValue(
"IsCellBackgroundTransparent", new uno.Any( false ) );
// --- Query equal-formatted cell ranges ---
// prepare example, use the new cell style
xCellRange = xSheet.getCellRangeByName( "D2:F2" );
xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange;
xPropSet.setPropertyValue( "CellStyle", new uno.Any( aStyleName ) );
xCellRange = xSheet.getCellRangeByName( "A3:G3" );
xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange;
xPropSet.setPropertyValue( "CellStyle", new uno.Any( aStyleName ) );
// All ranges in one container
xCellRange = xSheet.getCellRangeByName( "A1:G3" );
Console.WriteLine( "Service CellFormatRanges:" );
unoidl.com.sun.star.sheet.XCellFormatRangesSupplier xFormatSupp =
(unoidl.com.sun.star.sheet.XCellFormatRangesSupplier) xCellRange;
xRangeIA = xFormatSupp.getCellFormatRanges();
Console.WriteLine( getCellRangeListString( xRangeIA ) );
// Ranges sorted in SheetCellRanges containers
Console.WriteLine( "\nService UniqueCellFormatRanges:" );
unoidl.com.sun.star.sheet.XUniqueCellFormatRangesSupplier
xUniqueFormatSupp =
(unoidl.com.sun.star.sheet.XUniqueCellFormatRangesSupplier)
xCellRange;
unoidl.com.sun.star.container.XIndexAccess xRangesIA =
xUniqueFormatSupp.getUniqueCellFormatRanges();
int nCount = xRangesIA.getCount();
for (int nIndex = 0; nIndex < nCount; ++nIndex)
{
uno.Any aRangesObj = xRangesIA.getByIndex( nIndex );
xRangeIA =
(unoidl.com.sun.star.container.XIndexAccess) aRangesObj.Value;
Console.WriteLine(
"Container " + (nIndex + 1) + ": " +
getCellRangeListString( xRangeIA ) );
}
// --- Table auto formats ---
// get the global collection of table auto formats,
// use global service manager
xServiceManager = getServiceManager();
Object aAutoFormatsObj = xServiceManager.createInstance(
"com.sun.star.sheet.TableAutoFormats" );
unoidl.com.sun.star.container.XNameContainer xAutoFormatsNA =
(unoidl.com.sun.star.container.XNameContainer) aAutoFormatsObj;
// create a new table auto format and insert into the container
String aAutoFormatName = "Temp_Example";
bool bExistsAlready = xAutoFormatsNA.hasByName( aAutoFormatName );
uno.Any aAutoFormatObj;
if (bExistsAlready)
// auto format already exists -> use it
aAutoFormatObj = xAutoFormatsNA.getByName( aAutoFormatName );
else
{
// create a new auto format (with document service manager!)
xServiceManager =
(unoidl.com.sun.star.lang.XMultiServiceFactory) getDocument();
aAutoFormatObj = new uno.Any(
typeof (Object),
xServiceManager.createInstance(
"com.sun.star.sheet.TableAutoFormat" ) );
xAutoFormatsNA.insertByName( aAutoFormatName, aAutoFormatObj );
}
// index access to the auto format fields
unoidl.com.sun.star.container.XIndexAccess xAutoFormatIA =
(unoidl.com.sun.star.container.XIndexAccess) aAutoFormatObj.Value;
// set properties of all auto format fields
for (int nRow = 0; nRow < 4; ++nRow)
{
int nRowColor = 0;
switch (nRow)
{
case 0: nRowColor = 0x999999; break;
case 1: nRowColor = 0xFFFFCC; break;
case 2: nRowColor = 0xEEEEEE; break;
case 3: nRowColor = 0x999999; break;
}
for (int nColumn = 0; nColumn < 4; ++nColumn)
{
int nColor = nRowColor;
if ((nColumn == 0) || (nColumn == 3))
nColor -= 0x333300;
// get the auto format field and apply properties
uno.Any aFieldObj = xAutoFormatIA.getByIndex(
4 * nRow + nColumn );
xPropSet =
(unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value;
xPropSet.setPropertyValue(
"CellBackColor", new uno.Any( (Int32) nColor ) );
}
}
// set the auto format to the spreadsheet
xCellRange = xSheet.getCellRangeByName( "A5:H25" );
unoidl.com.sun.star.table.XAutoFormattable xAutoForm =
(unoidl.com.sun.star.table.XAutoFormattable) xCellRange;
xAutoForm.autoFormat( aAutoFormatName );
// remove the auto format
if (!bExistsAlready)
xAutoFormatsNA.removeByName( aAutoFormatName );
// --- Conditional formats ---
xSheet = getSpreadsheet( 0 );
prepareRange( xSheet, "K20:K23", "Cond. Format" );
setValue( xSheet, "K21", 1 );
setValue( xSheet, "K22", 2 );
setValue( xSheet, "K23", 3 );
// get the conditional format object of the cell range
xCellRange = xSheet.getCellRangeByName( "K21:K23" );
xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange;
unoidl.com.sun.star.sheet.XSheetConditionalEntries xEntries =
(unoidl.com.sun.star.sheet.XSheetConditionalEntries)
xPropSet.getPropertyValue( "ConditionalFormat" ).Value;
// create a condition and apply it to the range
unoidl.com.sun.star.beans.PropertyValue[] aCondition =
new unoidl.com.sun.star.beans.PropertyValue[3];
aCondition[0] = new unoidl.com.sun.star.beans.PropertyValue();
aCondition[0].Name = "Operator";
aCondition[0].Value =
new uno.Any(
typeof (unoidl.com.sun.star.sheet.ConditionOperator),
unoidl.com.sun.star.sheet.ConditionOperator.GREATER );
aCondition[1] = new unoidl.com.sun.star.beans.PropertyValue();
aCondition[1].Name = "Formula1";
aCondition[1].Value = new uno.Any( "1" );
aCondition[2] = new unoidl.com.sun.star.beans.PropertyValue();
aCondition[2].Name = "StyleName";
aCondition[2].Value = new uno.Any( aStyleName );
xEntries.addNew( aCondition );
xPropSet.setPropertyValue(
"ConditionalFormat",
new uno.Any(
typeof (unoidl.com.sun.star.sheet.XSheetConditionalEntries),
xEntries ) );
}
// ________________________________________________________________
/** All samples regarding the spreadsheet document. */
private void doDocumentSamples()
{
Console.WriteLine( "\n*** Samples for spreadsheet document ***\n" );
// --- Insert a new spreadsheet ---
unoidl.com.sun.star.sheet.XSpreadsheet xSheet =
insertSpreadsheet( "A new sheet", (short) 0x7FFF );
// --- Copy a cell range ---
prepareRange( xSheet, "A1:B3", "Copy from" );
prepareRange( xSheet, "D1:E3", "To" );
setValue( xSheet, "A2", 123 );
setValue( xSheet, "B2", 345 );
setFormula( xSheet, "A3", "=SUM(A2:B2)" );
setFormula( xSheet, "B3", "=FORMULA(A3)" );
unoidl.com.sun.star.sheet.XCellRangeMovement xMovement =
(unoidl.com.sun.star.sheet.XCellRangeMovement) xSheet;
unoidl.com.sun.star.table.CellRangeAddress aSourceRange =
createCellRangeAddress( xSheet, "A2:B3" );
unoidl.com.sun.star.table.CellAddress aDestCell =
createCellAddress( xSheet, "D2" );
xMovement.copyRange( aDestCell, aSourceRange );
// --- Print automatic column page breaks ---
unoidl.com.sun.star.sheet.XSheetPageBreak xPageBreak =
(unoidl.com.sun.star.sheet.XSheetPageBreak) xSheet;
unoidl.com.sun.star.sheet.TablePageBreakData[] aPageBreakArray =
xPageBreak.getColumnPageBreaks();
Console.Write( "Automatic column page breaks:" );
for (int nIndex = 0; nIndex < aPageBreakArray.Length; ++nIndex)
if (!aPageBreakArray[nIndex].ManualBreak)
Console.Write( " " + aPageBreakArray[nIndex].Position );
Console.WriteLine();
// --- Document properties ---
unoidl.com.sun.star.beans.XPropertySet xPropSet =
(unoidl.com.sun.star.beans.XPropertySet) getDocument();
String aText = "Value of property IsIterationEnabled: ";
aText +=
(Boolean) xPropSet.getPropertyValue( "IsIterationEnabled" ).Value;
Console.WriteLine( aText );
aText = "Value of property IterationCount: ";
aText += (Int32) xPropSet.getPropertyValue( "IterationCount" ).Value;
Console.WriteLine( aText );
aText = "Value of property NullDate: ";
unoidl.com.sun.star.util.Date aDate = (unoidl.com.sun.star.util.Date)
xPropSet.getPropertyValue( "NullDate" ).Value;
aText += aDate.Year + "-" + aDate.Month + "-" + aDate.Day;
Console.WriteLine( aText );
// --- Data validation ---
prepareRange( xSheet, "A5:C7", "Validation" );
setFormula( xSheet, "A6", "Insert values between 0.0 and 5.0 below:" );
unoidl.com.sun.star.table.XCellRange xCellRange =
xSheet.getCellRangeByName( "A7:C7" );
unoidl.com.sun.star.beans.XPropertySet xCellPropSet =
(unoidl.com.sun.star.beans.XPropertySet) xCellRange;
// validation properties
unoidl.com.sun.star.beans.XPropertySet xValidPropSet =
(unoidl.com.sun.star.beans.XPropertySet)
xCellPropSet.getPropertyValue( "Validation" ).Value;
xValidPropSet.setPropertyValue(
"Type",
new uno.Any(
typeof (unoidl.com.sun.star.sheet.ValidationType),
unoidl.com.sun.star.sheet.ValidationType.DECIMAL ) );
xValidPropSet.setPropertyValue(
"ShowErrorMessage", new uno.Any( true ) );
xValidPropSet.setPropertyValue(
"ErrorMessage", new uno.Any( "This is an invalid value!" ) );
xValidPropSet.setPropertyValue(
"ErrorAlertStyle",
new uno.Any(
typeof (unoidl.com.sun.star.sheet.ValidationAlertStyle),
unoidl.com.sun.star.sheet.ValidationAlertStyle.STOP ) );
// condition
unoidl.com.sun.star.sheet.XSheetCondition xCondition =
(unoidl.com.sun.star.sheet.XSheetCondition) xValidPropSet;
xCondition.setOperator(
unoidl.com.sun.star.sheet.ConditionOperator.BETWEEN );
xCondition.setFormula1( "0.0" );
xCondition.setFormula2( "5.0" );
// apply on cell range
xCellPropSet.setPropertyValue(
"Validation",
new uno.Any(
typeof (unoidl.com.sun.star.beans.XPropertySet),
xValidPropSet ) );
// --- Scenarios ---
uno.Any [][] aValues = {
new uno.Any [] { uno.Any.VOID, uno.Any.VOID },
new uno.Any [] { uno.Any.VOID, uno.Any.VOID }
};
aValues[ 0 ][ 0 ] = new uno.Any( (Double) 11 );
aValues[ 0 ][ 1 ] = new uno.Any( (Double) 12 );
aValues[ 1 ][ 0 ] = new uno.Any( "Test13" );
aValues[ 1 ][ 1 ] = new uno.Any( "Test14" );
insertScenario(
xSheet, "B10:C11", aValues,
"First Scenario", "The first scenario." );
aValues[ 0 ][ 0 ] = new uno.Any( "Test21" );
aValues[ 0 ][ 1 ] = new uno.Any( "Test22" );
aValues[ 1 ][ 0 ] = new uno.Any( (Double) 23 );
aValues[ 1 ][ 1 ] = new uno.Any( (Double) 24 );
insertScenario(
xSheet, "B10:C11", aValues,
"Second Scenario", "The visible scenario." );
aValues[ 0 ][ 0 ] = new uno.Any( (Double) 31 );
aValues[ 0 ][ 1 ] = new uno.Any( (Double) 32 );
aValues[ 1 ][ 0 ] = new uno.Any( "Test33" );
aValues[ 1 ][ 1 ] = new uno.Any( "Test34" );
insertScenario(
xSheet, "B10:C11", aValues,
"Third Scenario", "The last scenario." );
// show second scenario
showScenario( xSheet, "Second Scenario" );
}
// ________________________________________________________________
private void doFunctionAccessSamples()
{
Console.WriteLine( "\n*** Samples for function handling ***\n" );
unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager =
getServiceManager();
// --- Calculate a function ---
Object aFuncInst = xServiceManager.createInstance(
"com.sun.star.sheet.FunctionAccess" );
unoidl.com.sun.star.sheet.XFunctionAccess xFuncAcc =
(unoidl.com.sun.star.sheet.XFunctionAccess) aFuncInst;
// put the data in a two-dimensional array
Double [][] aData = { new Double [] { 1.0, 2.0, 3.0 } };
// construct the array of function arguments
uno.Any [] aArgs = new uno.Any [2];
aArgs[0] = new uno.Any( typeof (Double [][]), aData );
aArgs[1] = new uno.Any( (Double) 2.0 );
uno.Any aResult = xFuncAcc.callFunction( "ZTEST", aArgs );
Console.WriteLine(
"ZTEST result for data {1,2,3} and value 2 is " + aResult.Value );
// --- Get the list of recently used functions ---
Object aRecInst = xServiceManager.createInstance(
"com.sun.star.sheet.RecentFunctions" );
unoidl.com.sun.star.sheet.XRecentFunctions xRecFunc =
(unoidl.com.sun.star.sheet.XRecentFunctions) aRecInst;
int[] nRecentIds = xRecFunc.getRecentFunctionIds();
// --- Get the names for these functions ---
Object aDescInst = xServiceManager.createInstance(
"com.sun.star.sheet.FunctionDescriptions" );
unoidl.com.sun.star.sheet.XFunctionDescriptions xFuncDesc =
(unoidl.com.sun.star.sheet.XFunctionDescriptions) aDescInst;
Console.Write("Recently used functions: ");
for (int nFunction=0; nFunction<nRecentIds.Length; nFunction++)
{
unoidl.com.sun.star.beans.PropertyValue[] aProperties =
xFuncDesc.getById( nRecentIds[nFunction] );
for (int nProp=0; nProp<aProperties.Length; nProp++)
if ( aProperties[nProp].Name.Equals( "Name" ) )
Console.Write( aProperties[nProp].Value + " " );
}
Console.WriteLine();
}
public Any transportAny(Any value)
{
return value;
}
public TestDataElements setValues2(
/*INOUT*/ref bool io_bool,
/*INOUT*/ref char io_char,
/*INOUT*/ref byte io_byte,
/*INOUT*/ref short io_short,
/*INOUT*/ref ushort io_ushort,
/*INOUT*/ref int io_long,
/*INOUT*/ref uint io_ulong,
/*INOUT*/ref long io_hyper,
/*INOUT*/ref ulong io_uhyper,
/*INOUT*/ref float io_float,
/*INOUT*/ref double io_double,
/*INOUT*/ref TestEnum io_testEnum,
/*INOUT*/ref String io_string,
/*INOUT*/ref Object io_xInterface,
/*INOUT*/ref Any io_any,
/*INOUT*/ref TestElement[] io_testElements,
/*INOUT*/ref TestDataElements io_testDataElements )
{
Debug.WriteLine( "##### " + GetType().FullName + ".setValues2:" + io_any );
_bool = io_bool;
_char = io_char;
_byte = io_byte;
_short = io_short;
_ushort = io_ushort;
_long = io_long;
_ulong = io_ulong;
_hyper = io_hyper;
_uhyper = io_uhyper;
_float = io_float;
_double = io_double;
_testEnum = io_testEnum;
_string = io_string;
_xInterface = io_xInterface;
_any = io_any;
_testElements = (TestElement[]) io_testElements.Clone();
_testDataElements = io_testDataElements;
TestElement temp = io_testElements[ 0 ];
io_testElements[ 0 ] = io_testElements[ 1 ];
io_testElements[ 1 ] = temp;
return _testDataElements;
}
public void setAny_attr( ref Any _any_attr )
{
_any = _any_attr;
}
public void setSequencesInOut(ref bool[] aSeqBoolean,
ref char[] aSeqChar,
ref byte[] aSeqByte,
ref short[] aSeqShort,
ref UInt16[] aSeqUShort,
ref int[] aSeqLong,
ref UInt32[] aSeqULong,
ref long[] aSeqHyper,
ref UInt64[] aSeqUHyper,
ref float[] aSeqFloat,
ref double[] aSeqDouble,
ref TestEnum[] aSeqTestEnum,
ref string[] aSeqString,
ref object[] aSeqXInterface,
ref Any[] aSeqAny,
ref int[][] aSeqDim2,
ref int[][][] aSeqDim3)
{
_arBool = aSeqBoolean;
_arChar = aSeqChar;
_arByte = aSeqByte;
_arShort = aSeqShort;
_arUShort = aSeqUShort;
_arLong = aSeqLong;
_arULong = aSeqULong;
_arHyper = aSeqHyper;
_arUHyper = aSeqUHyper;
_arFloat = aSeqFloat;
_arDouble = aSeqDouble;
_arEnum = aSeqTestEnum;
_arString = aSeqString;
_arObject = aSeqXInterface;
_arAny = aSeqAny;
_arLong2 = aSeqDim2;
_arLong3 = aSeqDim3;
}
public Any[] setSequenceAny(Any[] val)
{
_arAny = val;
return val;
}
public TestDataElements getValues(
/*OUT*/out bool o_bool,
/*OUT*/out char o_char,
/*OUT*/out byte o_byte,
/*OUT*/out short o_short,
/*OUT*/out ushort o_ushort,
/*OUT*/out int o_long,
/*OUT*/out uint o_ulong,
/*OUT*/out long o_hyper,
/*OUT*/out ulong o_uhyper,
/*OUT*/out float o_float,
/*OUT*/out double o_double,
/*OUT*/out TestEnum o_testEnum,
/*OUT*/out String o_string,
/*OUT*/out Object o_xInterface,
/*OUT*/out Any o_any,
/*OUT*/out TestElement[] o_testElements,
/*OUT*/out TestDataElements o_testDataElements )
{
Debug.WriteLine( "##### " + GetType().FullName + ".getValues" );
o_bool = _bool;
o_char = _char;
o_byte = _byte;
o_short = _short;
o_ushort = _ushort;
o_long = _long;
o_ulong = _ulong;
o_hyper = _hyper;
o_uhyper = _uhyper;
o_float = _float;
o_double = _double;
o_testEnum = _testEnum;
o_string = _string;
o_xInterface = _xInterface;
o_any = _any;
o_testElements = _testElements;
o_testDataElements = _testDataElements;
return _testDataElements;
}
/** This sample function performs all changes on the view. */
public void doSampleFunction()
{
unoidl.com.sun.star.sheet.XSpreadsheetDocument xDoc = getDocument();
unoidl.com.sun.star.frame.XModel xModel =
(unoidl.com.sun.star.frame.XModel)xDoc;
unoidl.com.sun.star.frame.XController xController =
xModel.getCurrentController();
// --- Spreadsheet view ---
// freeze the first column and first two rows
unoidl.com.sun.star.sheet.XViewFreezable xFreeze =
(unoidl.com.sun.star.sheet.XViewFreezable)xController;
if (null != xFreeze)
{
Console.WriteLine("got xFreeze");
}
xFreeze.freezeAtPosition(1, 2);
// --- View pane ---
// get the cell range shown in the second pane and assign
// a cell background to them
unoidl.com.sun.star.container.XIndexAccess xIndex =
(unoidl.com.sun.star.container.XIndexAccess)xController;
uno.Any aPane = xIndex.getByIndex(1);
unoidl.com.sun.star.sheet.XCellRangeReferrer xRefer =
(unoidl.com.sun.star.sheet.XCellRangeReferrer)aPane.Value;
unoidl.com.sun.star.table.XCellRange xRange = xRefer.getReferredCells();
unoidl.com.sun.star.beans.XPropertySet xRangeProp =
(unoidl.com.sun.star.beans.XPropertySet)xRange;
xRangeProp.setPropertyValue(
"IsCellBackgroundTransparent", new uno.Any(false));
xRangeProp.setPropertyValue(
"CellBackColor", new uno.Any((Int32)0xFFFFCC));
// --- View settings ---
// change the view to display green grid lines
unoidl.com.sun.star.beans.XPropertySet xProp =
(unoidl.com.sun.star.beans.XPropertySet)xController;
xProp.setPropertyValue(
"ShowGrid", new uno.Any(true));
xProp.setPropertyValue(
"GridColor", new uno.Any((Int32)0x00CC00));
// --- Range selection ---
// let the user select a range and use it as the view's selection
unoidl.com.sun.star.sheet.XRangeSelection xRngSel =
(unoidl.com.sun.star.sheet.XRangeSelection)xController;
ExampleRangeListener aListener = new ExampleRangeListener();
xRngSel.addRangeSelectionListener(aListener);
unoidl.com.sun.star.beans.PropertyValue[] aArguments =
new unoidl.com.sun.star.beans.PropertyValue[2];
aArguments[0] = new unoidl.com.sun.star.beans.PropertyValue();
aArguments[0].Name = "Title";
aArguments[0].Value = new uno.Any("Please select a range");
aArguments[1] = new unoidl.com.sun.star.beans.PropertyValue();
aArguments[1].Name = "CloseOnMouseRelease";
aArguments[1].Value = new uno.Any(true);
xRngSel.startRangeSelection(aArguments);
Monitor.Enter(aListener);
try
{
Monitor.Wait(aListener); // wait until the selection is done
}
finally
{
Monitor.Exit(aListener);
}
xRngSel.removeRangeSelectionListener(aListener);
if (aListener.aResult != null && aListener.aResult.Length != 0)
{
unoidl.com.sun.star.view.XSelectionSupplier xSel =
(unoidl.com.sun.star.view.XSelectionSupplier)xController;
unoidl.com.sun.star.sheet.XSpreadsheetView xView =
(unoidl.com.sun.star.sheet.XSpreadsheetView)xController;
unoidl.com.sun.star.sheet.XSpreadsheet xSheet =
xView.getActiveSheet();
unoidl.com.sun.star.table.XCellRange xResultRange =
xSheet.getCellRangeByName(aListener.aResult);
xSel.select(
new uno.Any(
typeof(unoidl.com.sun.star.table.XCellRange),
xResultRange));
}
}
//returns the values which have been set in a previous call
//to this function or inParameters.
public void inoutParameters(ref bool aBool, ref byte aByte,
ref short aShort, ref ushort aUShort,
ref int aInt, ref uint aUInt,
ref long aLong, ref ulong aULong,
ref float aFloat, ref double aDouble,
ref char aChar, ref string aString,
ref Type aType, ref uno.Any aAny,
ref Enum2 aEnum, ref Struct1 aStruct,
ref object aXInterface,
ref unoidl.com.sun.star.lang.XComponent aXComponent,
ref bool[] seqBool)
{
bool _bool = aBool;
aBool = m_Bool;
m_Bool = _bool;
byte _byte = aByte;
aByte = m_Byte;
m_Byte = _byte;
short _short = aShort;
aShort = m_Short;
m_Short = _short;
ushort _ushort = aUShort;
aUShort = m_UShort;
m_UShort = _ushort;
int _int = aInt;
aInt = m_Int;
m_Int = _int;
uint _uint = aUInt;
aUInt = m_UInt;
m_UInt = _uint;
long _long = aLong;
aLong = m_Long;
m_Long = _long;
ulong _ulong = aULong;
aULong = m_ULong;
m_ULong = _ulong;
float _f = aFloat;
aFloat = m_Float;
m_Float = _f;
double _d = aDouble;
aDouble = m_Double;
m_Double = _d;
char _char = aChar;
aChar = m_Char;
m_Char = _char;
string _string = aString;
aString = m_String;
m_String = _string;
Type _type = aType;
aType = m_Type;
m_Type = _type;
Any _any = aAny;
aAny = m_Any;
m_Any = _any;
Enum2 _enum2 = aEnum;
aEnum = m_Enum2;
m_Enum2 = _enum2;
Struct1 _struct1 = aStruct;
aStruct = m_Struct1;
m_Struct1 = _struct1;
object _obj = aXInterface;
aXInterface = m_XInterface;
m_XInterface = _obj;
ucss.lang.XComponent _xcomp = aXComponent;
aXComponent = m_XComponent;
m_XComponent = _xcomp;
bool[] _seq = seqBool;
seqBool = m_seqBool;
m_seqBool = _seq;
}
// ________________________________________________________________
/// This sample function modifies cells and cell ranges.
public void doSampleFunction()
{
// for common usage
unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet(0);
unoidl.com.sun.star.beans.XPropertySet xPropSet = null;
unoidl.com.sun.star.table.XCell xCell = null;
unoidl.com.sun.star.table.XCellRange xCellRange = null;
// *** Access and modify a VALUE CELL ***
Console.WriteLine("*** Sample for service table.Cell ***");
xCell = xSheet.getCellByPosition(0, 0);
// Set cell value.
xCell.setValue(1234);
// Get cell value.
double nDblValue = xCell.getValue() * 2;
xSheet.getCellByPosition(0, 1).setValue(nDblValue);
// *** Create a FORMULA CELL and query error type ***
xCell = xSheet.getCellByPosition(0, 2);
// Set formula string.
xCell.setFormula("=1/0");
// Get error type.
bool bValid = (xCell.getError() == 0);
// Get formula string.
String aText = "The formula " + xCell.getFormula() + " is ";
aText += bValid ? "valid." : "erroneous.";
// *** Insert a TEXT CELL using the XText interface ***
xCell = xSheet.getCellByPosition(0, 3);
unoidl.com.sun.star.text.XText xCellText =
(unoidl.com.sun.star.text.XText)xCell;
unoidl.com.sun.star.text.XTextCursor xTextCursor =
xCellText.createTextCursor();
xCellText.insertString(xTextCursor, aText, false);
// *** Change cell properties ***
int nValue = bValid ? 0x00FF00 : 0xFF4040;
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCell;
xPropSet.setPropertyValue(
"CellBackColor", new uno.Any((Int32)nValue));
// *** Accessing a CELL RANGE ***
Console.WriteLine("*** Sample for service table.CellRange ***");
// Accessing a cell range over its position.
xCellRange = xSheet.getCellRangeByPosition(2, 0, 3, 1);
// Change properties of the range.
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange;
xPropSet.setPropertyValue(
"CellBackColor", new uno.Any((Int32)0x8080FF));
// Accessing a cell range over its name.
xCellRange = xSheet.getCellRangeByName("C4:D5");
// Change properties of the range.
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange;
xPropSet.setPropertyValue(
"CellBackColor", new uno.Any((Int32)0xFFFF80));
// *** Using the CELL CURSOR to add some data below of
// the filled area ***
Console.WriteLine("*** Sample for service table.CellCursor ***");
// Create a cursor using the XSpreadsheet method createCursorByRange()
xCellRange = xSheet.getCellRangeByName("A1");
unoidl.com.sun.star.sheet.XSheetCellRange xSheetCellRange =
(unoidl.com.sun.star.sheet.XSheetCellRange)xCellRange;
unoidl.com.sun.star.sheet.XSheetCellCursor xSheetCellCursor =
xSheet.createCursorByRange(xSheetCellRange);
unoidl.com.sun.star.table.XCellCursor xCursor =
(unoidl.com.sun.star.table.XCellCursor)xSheetCellCursor;
// Move to the last filled cell.
xCursor.gotoEnd();
// Move one row down.
xCursor.gotoOffset(0, 1);
xCursor.getCellByPosition(0, 0).setFormula(
"Beyond of the last filled cell.");
// *** Modifying COLUMNS and ROWS ***
Console.WriteLine("*** Sample for services table.TableRows and " +
"table.TableColumns ***");
unoidl.com.sun.star.table.XColumnRowRange xCRRange =
(unoidl.com.sun.star.table.XColumnRowRange)xSheet;
unoidl.com.sun.star.table.XTableColumns xColumns =
xCRRange.getColumns();
unoidl.com.sun.star.table.XTableRows xRows = xCRRange.getRows();
// Get column C by index (interface XIndexAccess).
uno.Any aColumnObj = xColumns.getByIndex(2);
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)aColumnObj.Value;
xPropSet.setPropertyValue("Width", new uno.Any((Int32)5000));
// Get the name of the column.
unoidl.com.sun.star.container.XNamed xNamed =
(unoidl.com.sun.star.container.XNamed)aColumnObj.Value;
aText = "The name of this column is " + xNamed.getName() + ".";
xSheet.getCellByPosition(2, 2).setFormula(aText);
// Get column D by name (interface XNameAccess).
unoidl.com.sun.star.container.XNameAccess xColumnsName =
(unoidl.com.sun.star.container.XNameAccess)xColumns;
aColumnObj = xColumnsName.getByName("D");
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)aColumnObj.Value;
xPropSet.setPropertyValue(
"IsVisible", new uno.Any((Boolean)false));
// Get row 7 by index (interface XIndexAccess)
uno.Any aRowObj = xRows.getByIndex(6);
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)aRowObj.Value;
xPropSet.setPropertyValue("Height", new uno.Any((Int32)5000));
xSheet.getCellByPosition(2, 6).setFormula("What a big cell.");
// Create a cell series with the values 1 ... 7.
for (int nRow = 8; nRow < 15; ++nRow)
{
xSheet.getCellByPosition(0, nRow).setValue(nRow - 7);
}
// Insert a row between 1 and 2
xRows.insertByIndex(9, 1);
// Delete the rows with the values 3 and 4.
xRows.removeByIndex(11, 2);
// *** Inserting CHARTS ***
Console.WriteLine("*** Sample for service table.TableCharts ***");
unoidl.com.sun.star.table.XTableChartsSupplier xChartsSupp =
(unoidl.com.sun.star.table.XTableChartsSupplier)xSheet;
unoidl.com.sun.star.table.XTableCharts xCharts =
xChartsSupp.getCharts();
// The chart will base on the last cell series, initializing all values.
String aName = "newChart";
unoidl.com.sun.star.awt.Rectangle aRect =
new unoidl.com.sun.star.awt.Rectangle();
aRect.X = 10000;
aRect.Y = 3000;
aRect.Width = aRect.Height = 5000;
unoidl.com.sun.star.table.CellRangeAddress[] aRanges =
new unoidl.com.sun.star.table.CellRangeAddress[1];
aRanges[0] = createCellRangeAddress(xSheet, "A9:A14");
// Create the chart.
xCharts.addNewByName(aName, aRect, aRanges, false, false);
// Get the chart by name.
uno.Any aChartObj = xCharts.getByName(aName);
unoidl.com.sun.star.table.XTableChart xChart =
(unoidl.com.sun.star.table.XTableChart)aChartObj.Value;
// Query the state of row and column headers.
aText = "Chart has column headers: ";
aText += xChart.getHasColumnHeaders() ? "yes" : "no";
xSheet.getCellByPosition(2, 8).setFormula(aText);
aText = "Chart has row headers: ";
aText += xChart.getHasRowHeaders() ? "yes" : "no";
xSheet.getCellByPosition(2, 9).setFormula(aText);
}
public void setAny( /*IN*/ref Any a )
{
_any = a;
}
public void setSequencesOut(out bool[] aSeqBoolean,
out char[] aSeqChar,
out byte[] aSeqByte,
out short[] aSeqShort,
out UInt16[] aSeqUShort,
out int[] aSeqLong,
out UInt32[] aSeqULong,
out long[] aSeqHyper,
out UInt64[] aSeqUHyper,
out float[] aSeqFloat,
out double[] aSeqDouble,
out TestEnum[] aSeqTestEnum,
out string[] aSeqString,
out object[] aSeqXInterface,
out Any[] aSeqAny,
out int[][] aSeqDim2,
out int[][][] aSeqDim3)
{
aSeqBoolean = _arBool;
aSeqChar = _arChar;
aSeqByte = _arByte;
aSeqShort = _arShort;
aSeqUShort = _arUShort;
aSeqLong = _arLong;
aSeqULong = _arULong;
aSeqHyper = _arHyper;
aSeqUHyper = _arUHyper;
aSeqFloat = _arFloat;
aSeqDouble = _arDouble;
aSeqTestEnum = _arEnum;
aSeqString = _arString;
aSeqXInterface = _arObject;
aSeqAny = _arAny;
aSeqDim2 = _arLong2;
aSeqDim3 = _arLong3;
}
// private int _raiseAttr1;
public void setValues(
bool bBool,
char cChar,
byte nByte,
short nShort,
ushort nUShort,
int nLong,
uint nULong,
long nHyper,
ulong nUHyper,
float fFloat,
double fDouble,
TestEnum testEnum,
String str,
Object xInterface,
Any any,
TestElement [] testElements,
TestDataElements testDataElements )
{
Debug.WriteLine( "##### " + GetType().FullName + ".setValues:" + any );
_bool = bBool;
_char = cChar;
_byte = nByte;
_short = nShort;
_ushort = nUShort;
_long = nLong;
_ulong = nULong;
_hyper = nHyper;
_uhyper = nUHyper;
_float = fFloat;
_double = fDouble;
_testEnum = testEnum;
_string = str;
_xInterface = xInterface;
_any = any;
_testElements = testElements;
_testDataElements = testDataElements;
}
public bool Equals(Any obj)
{
return Type.Equals(obj.Type)
&& (Value == null ?
obj.Value == null :
Value.Equals(obj.Value));
}
