| /************************************************************** |
| * |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| * |
| *************************************************************/ |
| |
| |
| using System; |
| using unoidl.com.sun.star.lang; |
| using unoidl.com.sun.star.uno; |
| using unoidl.com.sun.star.bridge; |
| using unoidl.com.sun.star.frame; |
| |
| // __________ implementation ____________________________________ |
| |
| /** This is a helper class for the spreadsheet and table samples. |
| It connects to a running office and creates a spreadsheet document. |
| Additionally it contains various helper functions. |
| */ |
| public class SpreadsheetDocHelper : System.IDisposable |
| { |
| |
| // __ private members ___________________________________________ |
| |
| private const String msDataSheetName = "Data"; |
| |
| private unoidl.com.sun.star.uno.XComponentContext m_xContext; |
| private unoidl.com.sun.star.lang.XMultiServiceFactory mxMSFactory; |
| private unoidl.com.sun.star.sheet.XSpreadsheetDocument mxDocument; |
| |
| // ________________________________________________________________ |
| |
| public SpreadsheetDocHelper( String[] args ) |
| { |
| // Connect to a running office and get the service manager |
| mxMSFactory = connect( args ); |
| // Create a new spreadsheet document |
| mxDocument = initDocument(); |
| } |
| |
| // __ helper methods ____________________________________________ |
| |
| /** Returns the service manager. |
| @return XMultiServiceFactory interface of the service manager. */ |
| public unoidl.com.sun.star.lang.XMultiServiceFactory getServiceManager() |
| { |
| return mxMSFactory; |
| } |
| |
| /** Returns the whole spreadsheet document. |
| @return XSpreadsheetDocument interface of the document. */ |
| public unoidl.com.sun.star.sheet.XSpreadsheetDocument getDocument() |
| { |
| return mxDocument; |
| } |
| |
| /** 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; |
| } |
| |
| /** 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; |
| } |
| |
| // ________________________________________________________________ |
| // 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 ); |
| } |
| |
| /** 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 ) ); |
| } |
| |
| /** 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 ) ); |
| } |
| |
| // ________________________________________________________________ |
| // 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(); |
| } |
| |
| /** 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 convert cell addresses and range addresses to strings. |
| |
| /** Returns the text address of the cell. |
| @param nColumn The column index. |
| @param nRow The row index. |
| @return A string containing the cell address. */ |
| public String getCellAddressString( int nColumn, int nRow ) |
| { |
| String aStr = ""; |
| if (nColumn > 25) |
| aStr += (char) ('A' + nColumn / 26 - 1); |
| aStr += (char) ('A' + nColumn % 26); |
| aStr += (nRow + 1); |
| return aStr; |
| } |
| |
| /** Returns the text address of the cell range. |
| @param aCellRange The cell range address. |
| @return A string containing the cell range address. */ |
| public String getCellRangeAddressString( |
| unoidl.com.sun.star.table.CellRangeAddress aCellRange ) |
| { |
| return |
| getCellAddressString( aCellRange.StartColumn, aCellRange.StartRow ) |
| + ":" |
| + getCellAddressString( aCellRange.EndColumn, aCellRange.EndRow ); |
| } |
| |
| /** Returns the text address of the cell range. |
| @param xCellRange The XSheetCellRange interface of the cell range. |
| @param bWithSheet true = Include sheet name. |
| @return A string containing the cell range address. */ |
| 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; |
| } |
| |
| /** 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; |
| } |
| |
| // ________________________________________________________________ |
| |
| /** Connect to a running office that is accepting connections. |
| @return The ServiceManager to instantiate office components. */ |
| private XMultiServiceFactory connect( String [] args ) |
| { |
| |
| m_xContext = uno.util.Bootstrap.bootstrap(); |
| |
| return (XMultiServiceFactory) m_xContext.getServiceManager(); |
| } |
| |
| public void Dispose() |
| { |
| |
| } |
| |
| /** Creates an empty spreadsheet document. |
| @return The XSpreadsheetDocument interface of the document. */ |
| private unoidl.com.sun.star.sheet.XSpreadsheetDocument initDocument() |
| { |
| XComponentLoader aLoader = (XComponentLoader) |
| mxMSFactory.createInstance( "com.sun.star.frame.Desktop" ); |
| |
| XComponent xComponent = aLoader.loadComponentFromURL( |
| "private:factory/scalc", "_blank", 0, |
| new unoidl.com.sun.star.beans.PropertyValue[0] ); |
| |
| return (unoidl.com.sun.star.sheet.XSpreadsheetDocument) xComponent; |
| } |
| |
| // ________________________________________________________________ |
| } |
