/** 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(
unoidl.com.sun.star.sheet.XSpreadsheet xSheet,
String aRange,
uno.Any[][] aValueArray,
String aScenarioName,
String aScenarioComment)
{
// get the cell range with the given address
unoidl.com.sun.star.table.XCellRange xCellRange =
xSheet.getCellRangeByName(aRange);
// create the range address sequence
unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr =
(unoidl.com.sun.star.sheet.XCellRangeAddressable)xCellRange;
unoidl.com.sun.star.table.CellRangeAddress[] aRangesSeq =
new unoidl.com.sun.star.table.CellRangeAddress[1];
aRangesSeq[0] = xAddr.getRangeAddress();
// create the scenario
unoidl.com.sun.star.sheet.XScenariosSupplier xScenSupp =
(unoidl.com.sun.star.sheet.XScenariosSupplier)xSheet;
unoidl.com.sun.star.sheet.XScenarios xScenarios =
xScenSupp.getScenarios();
xScenarios.addNewByName(aScenarioName, aRangesSeq, aScenarioComment);
// insert the values into the range
unoidl.com.sun.star.sheet.XCellRangeData xData =
(unoidl.com.sun.star.sheet.XCellRangeData)xCellRange;
xData.setDataArray(aValueArray);
}
/** Creates a unoidl.com.sun.star.table.CellRangeAddress and initializes
* it with the given range.
* @param xSheet The XSpreadsheet interface of the spreadsheet.
* @param aRange The address of the cell range (or a named range). */
public unoidl.com.sun.star.table.CellRangeAddress createCellRangeAddress(
unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange)
{
unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr =
(unoidl.com.sun.star.sheet.XCellRangeAddressable)
xSheet.getCellRangeByName(aRange);
return(xAddr.getRangeAddress());
}
/** Draws a colored border around the range and writes the headline
* in the first cell.
*
* @param xSheet The XSpreadsheet interface of the spreadsheet.
* @param aRange The address of the cell range (or a named range).
* @param aHeadline The headline text. */
public void prepareRange(
unoidl.com.sun.star.sheet.XSpreadsheet xSheet,
String aRange, String aHeadline)
{
unoidl.com.sun.star.beans.XPropertySet xPropSet = null;
unoidl.com.sun.star.table.XCellRange xCellRange = null;
// draw border
xCellRange = xSheet.getCellRangeByName(aRange);
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange;
unoidl.com.sun.star.table.BorderLine aLine =
new unoidl.com.sun.star.table.BorderLine();
aLine.Color = 0x99CCFF;
aLine.InnerLineWidth = aLine.LineDistance = 0;
aLine.OuterLineWidth = 100;
unoidl.com.sun.star.table.TableBorder aBorder =
new unoidl.com.sun.star.table.TableBorder();
aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine =
aBorder.RightLine = aLine;
aBorder.IsTopLineValid = aBorder.IsBottomLineValid = true;
aBorder.IsLeftLineValid = aBorder.IsRightLineValid = true;
xPropSet.setPropertyValue(
"TableBorder",
new uno.Any(
typeof(unoidl.com.sun.star.table.TableBorder), aBorder));
// draw headline
unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr =
(unoidl.com.sun.star.sheet.XCellRangeAddressable)xCellRange;
unoidl.com.sun.star.table.CellRangeAddress aAddr =
xAddr.getRangeAddress();
xCellRange = xSheet.getCellRangeByPosition(
aAddr.StartColumn,
aAddr.StartRow, aAddr.EndColumn, aAddr.StartRow);
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange;
xPropSet.setPropertyValue(
"CellBackColor", new uno.Any((Int32)0x99CCFF));
// write headline
unoidl.com.sun.star.table.XCell xCell =
xCellRange.getCellByPosition(0, 0);
xCell.setFormula(aHeadline);
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCell;
xPropSet.setPropertyValue(
"CharColor", new uno.Any((Int32)0x003399));
xPropSet.setPropertyValue(
"CharWeight",
new uno.Any((Single)unoidl.com.sun.star.awt.FontWeight.BOLD));
}
/// <summary>
/// Returns the text address of the cell range
/// </summary>
/// <param name="xCellRange">The XSheetCellRange interface of the cell range.</param>
/// <param name="bWithSheet">true = Include sheet name.</param>
/// <returns>A string containing the cell range address list.</returns>
public String getCellRangeAddressString(unoidl.com.sun.star.sheet.XSheetCellRange xCellRange, bool bWithSheet)
{
String aStr = "";
if (bWithSheet)
{
unoidl.com.sun.star.sheet.XSpreadsheet xSheet =
xCellRange.getSpreadsheet();
unoidl.com.sun.star.container.XNamed xNamed =
(unoidl.com.sun.star.container.XNamed)xSheet;
aStr += xNamed.getName() + ".";
}
unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr =
(unoidl.com.sun.star.sheet.XCellRangeAddressable)xCellRange;
aStr += getCellRangeAddressString(xAddr.getRangeAddress());
return(aStr);
}
/** All samples regarding the service com.sun.star.sheet.SheetCellRange. */
public void doCellRangeSamples()
{
unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet(0);
unoidl.com.sun.star.table.XCellRange xCellRange = null;
unoidl.com.sun.star.beans.XPropertySet xPropSet = null;
unoidl.com.sun.star.table.CellRangeAddress aRangeAddress = null;
// Preparation
setFormula(xSheet, "B5", "First cell");
setFormula(xSheet, "B6", "Second cell");
// Get cell range B5:B6 by position - (column, row, column, row)
xCellRange = xSheet.getCellRangeByPosition(1, 4, 1, 5);
// --- Change cell range properties. ---
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange;
// from com.sun.star.styles.CharacterProperties
xPropSet.setPropertyValue(
"CharColor", new uno.Any((Int32)0x003399));
xPropSet.setPropertyValue(
"CharHeight", new uno.Any((Single)20.0));
// from com.sun.star.styles.ParagraphProperties
xPropSet.setPropertyValue(
"ParaLeftMargin", new uno.Any((Int32)500));
// from com.sun.star.table.CellProperties
xPropSet.setPropertyValue(
"IsCellBackgroundTransparent", new uno.Any(false));
xPropSet.setPropertyValue(
"CellBackColor", new uno.Any((Int32)0x99CCFF));
// --- Replace text in all cells. ---
unoidl.com.sun.star.util.XReplaceable xReplace =
(unoidl.com.sun.star.util.XReplaceable)xCellRange;
unoidl.com.sun.star.util.XReplaceDescriptor xReplaceDesc =
xReplace.createReplaceDescriptor();
xReplaceDesc.setSearchString("cell");
xReplaceDesc.setReplaceString("text");
// property SearchWords searches for whole cells!
xReplaceDesc.setPropertyValue("SearchWords", new uno.Any(false));
int nCount = xReplace.replaceAll(xReplaceDesc);
// --- Merge cells. ---
xCellRange = xSheet.getCellRangeByName("F3:G6");
prepareRange(xSheet, "E1:H7", "XMergeable");
unoidl.com.sun.star.util.XMergeable xMerge =
(unoidl.com.sun.star.util.XMergeable)xCellRange;
xMerge.merge(true);
// --- Column properties. ---
xCellRange = xSheet.getCellRangeByName("B1");
unoidl.com.sun.star.table.XColumnRowRange xColRowRange =
(unoidl.com.sun.star.table.XColumnRowRange)xCellRange;
unoidl.com.sun.star.table.XTableColumns xColumns =
xColRowRange.getColumns();
uno.Any aColumnObj = xColumns.getByIndex(0);
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)aColumnObj.Value;
xPropSet.setPropertyValue("Width", new uno.Any((Int32)6000));
unoidl.com.sun.star.container.XNamed xNamed =
(unoidl.com.sun.star.container.XNamed)aColumnObj.Value;
// --- Cell range data ---
prepareRange(xSheet, "A9:C30", "XCellRangeData");
xCellRange = xSheet.getCellRangeByName("A10:C30");
unoidl.com.sun.star.sheet.XCellRangeData xData =
(unoidl.com.sun.star.sheet.XCellRangeData)xCellRange;
uno.Any [][] aValues =
{
new uno.Any [] { new uno.Any("Name"),
new uno.Any("Fruit"),
new uno.Any("Quantity") },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Apples"),
new uno.Any((Double)3.0) },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Oranges"),
new uno.Any((Double)7.0) },
new uno.Any [] { new uno.Any("Bob"),
new uno.Any("Apples"),
new uno.Any((Double)3.0) },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Apples"),
new uno.Any((Double)9.0) },
new uno.Any [] { new uno.Any("Bob"),
new uno.Any("Apples"),
new uno.Any((Double)5.0) },
new uno.Any [] { new uno.Any("Bob"),
new uno.Any("Oranges"),
new uno.Any((Double)6.0) },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Oranges"),
new uno.Any((Double)3.0) },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Apples"),
new uno.Any((Double)8.0) },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Oranges"),
new uno.Any((Double)1.0) },
new uno.Any [] { new uno.Any("Bob"),
new uno.Any("Oranges"),
new uno.Any((Double)2.0) },
new uno.Any [] { new uno.Any("Bob"),
new uno.Any("Oranges"),
new uno.Any((Double)7.0) },
new uno.Any [] { new uno.Any("Bob"),
new uno.Any("Apples"),
new uno.Any((Double)1.0) },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Apples"),
new uno.Any((Double)8.0) },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Oranges"),
new uno.Any((Double)8.0) },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Apples"),
new uno.Any((Double)7.0) },
new uno.Any [] { new uno.Any("Bob"),
new uno.Any("Apples"),
new uno.Any((Double)1.0) },
new uno.Any [] { new uno.Any("Bob"),
new uno.Any("Oranges"),
new uno.Any((Double)9.0) },
new uno.Any [] { new uno.Any("Bob"),
new uno.Any("Oranges"),
new uno.Any((Double)3.0) },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Oranges"),
new uno.Any((Double)4.0) },
new uno.Any [] { new uno.Any("Alice"),
new uno.Any("Apples"),
new uno.Any((Double)9.0) }
};
xData.setDataArray(aValues);
// --- Get cell range address. ---
unoidl.com.sun.star.sheet.XCellRangeAddressable xRangeAddr =
(unoidl.com.sun.star.sheet.XCellRangeAddressable)xCellRange;
aRangeAddress = xRangeAddr.getRangeAddress();
// --- Sheet operation. ---
// uses the range filled with XCellRangeData
unoidl.com.sun.star.sheet.XSheetOperation xSheetOp =
(unoidl.com.sun.star.sheet.XSheetOperation)xData;
double fResult = xSheetOp.computeFunction(
unoidl.com.sun.star.sheet.GeneralFunction.AVERAGE);
// --- Fill series ---
// Prepare the example
setValue(xSheet, "E10", 1);
setValue(xSheet, "E11", 4);
setDate(xSheet, "E12", 30, 1, 2002);
setFormula(xSheet, "I13", "Text 10");
setFormula(xSheet, "E14", "Jan");
setValue(xSheet, "K14", 10);
setValue(xSheet, "E16", 1);
setValue(xSheet, "F16", 2);
setDate(xSheet, "E17", 28, 2, 2002);
setDate(xSheet, "F17", 28, 1, 2002);
setValue(xSheet, "E18", 6);
setValue(xSheet, "F18", 4);
}