/** Writes a date with standard date format into a spreadsheet.
* @param xSheet The XSpreadsheet interface of the spreadsheet.
* @param aCellName The address of the cell (or a named range).
* @param nDay The day of the date.
* @param nMonth The month of the date.
* @param nYear The year of the date. */
public void setDate(
unoidl.com.sun.star.sheet.XSpreadsheet xSheet,
String aCellName,
int nDay, int nMonth, int nYear)
{
// Set the date value.
unoidl.com.sun.star.table.XCell xCell =
xSheet.getCellRangeByName(aCellName).getCellByPosition(0, 0);
String aDateStr = nMonth + "/" + nDay + "/" + nYear;
xCell.setFormula(aDateStr);
// Set standard date format.
unoidl.com.sun.star.util.XNumberFormatsSupplier xFormatsSupplier =
(unoidl.com.sun.star.util.XNumberFormatsSupplier)getDocument();
unoidl.com.sun.star.util.XNumberFormatTypes xFormatTypes =
(unoidl.com.sun.star.util.XNumberFormatTypes)
xFormatsSupplier.getNumberFormats();
int nFormat = xFormatTypes.getStandardFormat(
unoidl.com.sun.star.util.NumberFormat.DATE,
new unoidl.com.sun.star.lang.Locale());
unoidl.com.sun.star.beans.XPropertySet xPropSet =
(unoidl.com.sun.star.beans.XPropertySet)xCell;
xPropSet.setPropertyValue(
"NumberFormat",
new uno.Any((Int32)nFormat));
}
/// <summary>
/// draw border color
/// </summary>
/// <param name="xSheet"></param>
/// <param name="aRange"></param>
/// <param name="width"></param>
/// <param name="color"></param>
public void setBorderColor(unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange, short width, int color)
{
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 = color;
aLine.InnerLineWidth = aLine.LineDistance = 0;
aLine.OuterLineWidth = width;
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));
}
/// <summary>
/// MergeCell
/// </summary>
/// <param name="xSheet"></param>
/// <param name="aRange"></param>
public void MergeCell(unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange)
{
unoidl.com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName(aRange);
unoidl.com.sun.star.util.XMergeable xMerge =
(unoidl.com.sun.star.util.XMergeable)xCellRange;
xMerge.merge(true);
}
/** 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());
}
// Methods to fill values into cells.
/** Writes a double value into a spreadsheet.
* @param xSheet The XSpreadsheet interface of the spreadsheet.
* @param aCellName The address of the cell (or a named range).
* @param fValue The value to write into the cell. */
public void setValue(
unoidl.com.sun.star.sheet.XSpreadsheet xSheet,
String aCellName,
double fValue)
{
xSheet.getCellRangeByName(aCellName).getCellByPosition(
0, 0).setValue(fValue);
}
/** Writes a formula into a spreadsheet.
* @param xSheet The XSpreadsheet interface of the spreadsheet.
* @param aCellName The address of the cell (or a named range).
* @param aFormula The formula to write into the cell. */
public void setFormula(
unoidl.com.sun.star.sheet.XSpreadsheet xSheet,
String aCellName,
String aFormula)
{
xSheet.getCellRangeByName(aCellName).getCellByPosition(
0, 0).setFormula(aFormula);
}
// Methods to create cell addresses and range addresses.
/** Creates a unoidl.com.sun.star.table.CellAddress and initializes it
* with the given range.
* @param xSheet The XSpreadsheet interface of the spreadsheet.
* @param aCell The address of the cell (or a named cell). */
public unoidl.com.sun.star.table.CellAddress createCellAddress(
unoidl.com.sun.star.sheet.XSpreadsheet xSheet,
String aCell)
{
unoidl.com.sun.star.sheet.XCellAddressable xAddr =
(unoidl.com.sun.star.sheet.XCellAddressable)
xSheet.getCellRangeByName(aCell).getCellByPosition(0, 0);
return(xAddr.getCellAddress());
}
/// <summary>
/// Insert a TEXT CELL using the XText interface
/// </summary>
/// <param name="xSheet"></param>
/// <param name="nColumn"></param>
/// <param name="nRow"></param>
/// <param name="strText"></param>
public void InsertTextCell(unoidl.com.sun.star.sheet.XSpreadsheet xSheet, int nColumn, int nRow, string strText)
{
unoidl.com.sun.star.table.XCell xCell = xSheet.getCellByPosition(nColumn, nRow);
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, strText, false);
}
/// <summary>
/// set Cell BackColor
/// </summary>
/// <param name="xSheet"></param>
/// <param name="aRange"></param>
/// <param name="color"></param>
public void setCellBackColor(unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange, int color)
{
unoidl.com.sun.star.beans.XPropertySet xPropSet = null;
unoidl.com.sun.star.table.XCellRange xCellRange = null;
// draw back color
xCellRange = xSheet.getCellRangeByName(aRange);
xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange;
xPropSet.setPropertyValue(
"CellBackColor", new uno.Any((Int32)color));
}
/** 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));
}
/** Inserts a new empty spreadsheet with the specified name.
* @param aName The name of the new sheet.
* @param nIndex The insertion index.
* @return The XSpreadsheet interface of the new sheet. */
public unoidl.com.sun.star.sheet.XSpreadsheet insertSpreadsheet(
String aName, short nIndex)
{
// Collection of sheets
unoidl.com.sun.star.sheet.XSpreadsheets xSheets =
mxDocument.getSheets();
xSheets.insertNewByName(aName, nIndex);
unoidl.com.sun.star.sheet.XSpreadsheet xSheet =
(unoidl.com.sun.star.sheet.XSpreadsheet)
xSheets.getByName(aName).Value;
return(xSheet);
}
/** Returns the spreadsheet with the specified index (0-based).
* @param nIndex The index of the sheet.
* @return XSpreadsheet interface of the sheet. */
public unoidl.com.sun.star.sheet.XSpreadsheet getSpreadsheet(int nIndex)
{
// Collection of sheets
unoidl.com.sun.star.sheet.XSpreadsheets xSheets =
mxDocument.getSheets();
unoidl.com.sun.star.container.XIndexAccess xSheetsIA =
(unoidl.com.sun.star.container.XIndexAccess)xSheets;
unoidl.com.sun.star.sheet.XSpreadsheet xSheet =
(unoidl.com.sun.star.sheet.XSpreadsheet)
xSheetsIA.getByIndex(nIndex).Value;
return(xSheet);
}
/** Activates a scenario.
* @param xSheet The XSpreadsheet interface of the spreadsheet.
* @param aScenarioName The name of the scenario. */
public void showScenario(
unoidl.com.sun.star.sheet.XSpreadsheet xSheet,
String aScenarioName)
{
// get the scenario set
unoidl.com.sun.star.sheet.XScenariosSupplier xScenSupp =
(unoidl.com.sun.star.sheet.XScenariosSupplier)xSheet;
unoidl.com.sun.star.sheet.XScenarios xScenarios =
xScenSupp.getScenarios();
// get the scenario and activate it
uno.Any aScenarioObj = xScenarios.getByName(aScenarioName);
unoidl.com.sun.star.sheet.XScenario xScenario =
(unoidl.com.sun.star.sheet.XScenario)aScenarioObj.Value;
xScenario.apply();
}
/// <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);
}
// ________________________________________________________________
/// 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);
}
/** 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);
}
/** 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 unoidl.com.sun.star.sheet.XCellSeries getCellSeries(
unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange)
{
return((unoidl.com.sun.star.sheet.XCellSeries)
xSheet.getCellRangeByName(aRange));
}
/// <summary>
/// getCellByPosition
/// </summary>
/// <param name="xSheet"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <returns></returns>
public unoidl.com.sun.star.table.XCell getCellByPosition(unoidl.com.sun.star.sheet.XSpreadsheet xSheet, int col, int row)
{
return(xSheet.getCellByPosition(col, row));
}
/** 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));
}
}
private bool ExportOOo()
{
try
{
SpreadSheetHelper helper = new SpreadSheetHelper(true);
//Samples for service sheet.SheetCell
unoidl.com.sun.star.sheet.XSpreadsheet xSheet = helper.getSpreadsheet(0);
unoidl.com.sun.star.table.XCell xCell = null;
//xCell = xSheet.getCellByPosition(1, 1);
//// --- Insert two text paragraphs into the cell. ---
//unoidl.com.sun.star.text.XText tText = (unoidl.com.sun.star.text.XText)xCell;
//unoidl.com.sun.star.text.XTextCursor tTextCursor =tText.createTextCursor();
//tText.insertString(tTextCursor, _title + "\r\n", false);
if (_gridType == 0)
{
for (int j = 0; j < _exportGrid.Columns.Count; j++)
{
xCell = xSheet.getCellByPosition(j + 1, 2);
// --- Insert two text paragraphs into the cell. ---
unoidl.com.sun.star.text.XText xText = (unoidl.com.sun.star.text.XText)xCell;
unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
xText.insertString(xTextCursor, _exportGrid.Columns[j].HeaderText, false);
}
// --- Get cell B3 by position - (column, row) ---
for (int i = 0; i < _exportGrid.Rows.Count; i++)
{
if (_exportGrid.Rows[i].Visible)
{
for (int j = 0; j < _exportGrid.Columns.Count; j++)
{
xCell = xSheet.getCellByPosition(j + 1, i + 3);
unoidl.com.sun.star.text.XText xText = (unoidl.com.sun.star.text.XText)xCell;
unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
xText.insertString(xTextCursor, _exportGrid.Rows[i].Cells[j].Value.ToString(), false);
}
}
}
}
else if (_gridType == 1)
{
for (int i = 0; i < _flexGrid.Rows; i++)
{
for (int j = 0; j < _flexGrid.Cols; j++)
{
xCell = xSheet.getCellByPosition(j + 1, i + 2);
unoidl.com.sun.star.text.XText xText = (unoidl.com.sun.star.text.XText)xCell;
unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
xText.insertString(xTextCursor, _flexGrid.get_TextMatrix(i, j), false);
}
}
}
else
{
for (int i = 0; i < _listview.Items.Count; i++)
{
xCell = xSheet.getCellByPosition(1, i + 2);
unoidl.com.sun.star.text.XText xText = (unoidl.com.sun.star.text.XText)xCell;
unoidl.com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
xText.insertString(xTextCursor, _listview.Items[i].Text, false);
for (int j = 1; j < _flexGrid.Cols; j++)
{
xCell = xSheet.getCellByPosition(j + 1, i + 2);
xText = (unoidl.com.sun.star.text.XText)xCell;
xTextCursor = xText.createTextCursor();
xText.insertString(xTextCursor, _listview.Items[i].SubItems[j].Text, false);
}
}
}
helper.storeDocComponent(_filename);
helper.closeDocCompant();
return(true);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
finally
{
//_exportGrid.Dispose();
}
}