1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 22 23 using System; 24 using unoidl.com.sun.star.lang; 25 using unoidl.com.sun.star.uno; 26 using unoidl.com.sun.star.bridge; 27 using unoidl.com.sun.star.frame; 28 29 // __________ implementation ____________________________________ 30 31 /** This is a helper class for the spreadsheet and table samples. 32 It connects to a running office and creates a spreadsheet document. 33 Additionally it contains various helper functions. 34 */ 35 public class SpreadsheetDocHelper : System.IDisposable 36 { 37 38 // __ private members ___________________________________________ 39 40 private const String msDataSheetName = "Data"; 41 42 private unoidl.com.sun.star.uno.XComponentContext m_xContext; 43 private unoidl.com.sun.star.lang.XMultiServiceFactory mxMSFactory; 44 private unoidl.com.sun.star.sheet.XSpreadsheetDocument mxDocument; 45 46 // ________________________________________________________________ 47 SpreadsheetDocHelper( String[] args )48 public SpreadsheetDocHelper( String[] args ) 49 { 50 // Connect to a running office and get the service manager 51 mxMSFactory = connect( args ); 52 // Create a new spreadsheet document 53 mxDocument = initDocument(); 54 } 55 56 // __ helper methods ____________________________________________ 57 58 /** Returns the service manager. 59 @return XMultiServiceFactory interface of the service manager. */ getServiceManager()60 public unoidl.com.sun.star.lang.XMultiServiceFactory getServiceManager() 61 { 62 return mxMSFactory; 63 } 64 65 /** Returns the whole spreadsheet document. 66 @return XSpreadsheetDocument interface of the document. */ getDocument()67 public unoidl.com.sun.star.sheet.XSpreadsheetDocument getDocument() 68 { 69 return mxDocument; 70 } 71 72 /** Returns the spreadsheet with the specified index (0-based). 73 @param nIndex The index of the sheet. 74 @return XSpreadsheet interface of the sheet. */ getSpreadsheet( int nIndex )75 public unoidl.com.sun.star.sheet.XSpreadsheet getSpreadsheet( int nIndex ) 76 { 77 // Collection of sheets 78 unoidl.com.sun.star.sheet.XSpreadsheets xSheets = 79 mxDocument.getSheets(); 80 81 unoidl.com.sun.star.container.XIndexAccess xSheetsIA = 82 (unoidl.com.sun.star.container.XIndexAccess) xSheets; 83 84 unoidl.com.sun.star.sheet.XSpreadsheet xSheet = 85 (unoidl.com.sun.star.sheet.XSpreadsheet) 86 xSheetsIA.getByIndex( nIndex ).Value; 87 88 return xSheet; 89 } 90 91 /** Inserts a new empty spreadsheet with the specified name. 92 @param aName The name of the new sheet. 93 @param nIndex The insertion index. 94 @return The XSpreadsheet interface of the new sheet. */ insertSpreadsheet( String aName, short nIndex )95 public unoidl.com.sun.star.sheet.XSpreadsheet insertSpreadsheet( 96 String aName, short nIndex ) 97 { 98 // Collection of sheets 99 unoidl.com.sun.star.sheet.XSpreadsheets xSheets = 100 mxDocument.getSheets(); 101 102 xSheets.insertNewByName( aName, nIndex ); 103 unoidl.com.sun.star.sheet.XSpreadsheet xSheet = 104 (unoidl.com.sun.star.sheet.XSpreadsheet) 105 xSheets.getByName( aName ).Value; 106 107 return xSheet; 108 } 109 110 // ________________________________________________________________ 111 // Methods to fill values into cells. 112 113 /** Writes a double value into a spreadsheet. 114 @param xSheet The XSpreadsheet interface of the spreadsheet. 115 @param aCellName The address of the cell (or a named range). 116 @param fValue The value to write into the cell. */ setValue( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aCellName, double fValue )117 public void setValue( 118 unoidl.com.sun.star.sheet.XSpreadsheet xSheet, 119 String aCellName, 120 double fValue ) 121 { 122 xSheet.getCellRangeByName( aCellName ).getCellByPosition( 123 0, 0 ).setValue( fValue ); 124 } 125 126 /** Writes a formula into a spreadsheet. 127 @param xSheet The XSpreadsheet interface of the spreadsheet. 128 @param aCellName The address of the cell (or a named range). 129 @param aFormula The formula to write into the cell. */ setFormula( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aCellName, String aFormula )130 public void setFormula( 131 unoidl.com.sun.star.sheet.XSpreadsheet xSheet, 132 String aCellName, 133 String aFormula ) 134 { 135 xSheet.getCellRangeByName( aCellName ).getCellByPosition( 136 0, 0 ).setFormula( aFormula ); 137 } 138 139 /** Writes a date with standard date format into a spreadsheet. 140 @param xSheet The XSpreadsheet interface of the spreadsheet. 141 @param aCellName The address of the cell (or a named range). 142 @param nDay The day of the date. 143 @param nMonth The month of the date. 144 @param nYear The year of the date. */ setDate( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aCellName, int nDay, int nMonth, int nYear )145 public void setDate( 146 unoidl.com.sun.star.sheet.XSpreadsheet xSheet, 147 String aCellName, 148 int nDay, int nMonth, int nYear ) 149 { 150 // Set the date value. 151 unoidl.com.sun.star.table.XCell xCell = 152 xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ); 153 String aDateStr = nMonth + "/" + nDay + "/" + nYear; 154 xCell.setFormula( aDateStr ); 155 156 // Set standard date format. 157 unoidl.com.sun.star.util.XNumberFormatsSupplier xFormatsSupplier = 158 (unoidl.com.sun.star.util.XNumberFormatsSupplier) getDocument(); 159 unoidl.com.sun.star.util.XNumberFormatTypes xFormatTypes = 160 (unoidl.com.sun.star.util.XNumberFormatTypes) 161 xFormatsSupplier.getNumberFormats(); 162 int nFormat = xFormatTypes.getStandardFormat( 163 unoidl.com.sun.star.util.NumberFormat.DATE, 164 new unoidl.com.sun.star.lang.Locale() ); 165 166 unoidl.com.sun.star.beans.XPropertySet xPropSet = 167 (unoidl.com.sun.star.beans.XPropertySet) xCell; 168 xPropSet.setPropertyValue( 169 "NumberFormat", 170 new uno.Any( (Int32) nFormat ) ); 171 } 172 173 /** Draws a colored border around the range and writes the headline 174 in the first cell. 175 176 @param xSheet The XSpreadsheet interface of the spreadsheet. 177 @param aRange The address of the cell range (or a named range). 178 @param aHeadline The headline text. */ prepareRange( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange, String aHeadline )179 public void prepareRange( 180 unoidl.com.sun.star.sheet.XSpreadsheet xSheet, 181 String aRange, String aHeadline ) 182 { 183 unoidl.com.sun.star.beans.XPropertySet xPropSet = null; 184 unoidl.com.sun.star.table.XCellRange xCellRange = null; 185 186 // draw border 187 xCellRange = xSheet.getCellRangeByName( aRange ); 188 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; 189 unoidl.com.sun.star.table.BorderLine aLine = 190 new unoidl.com.sun.star.table.BorderLine(); 191 aLine.Color = 0x99CCFF; 192 aLine.InnerLineWidth = aLine.LineDistance = 0; 193 aLine.OuterLineWidth = 100; 194 unoidl.com.sun.star.table.TableBorder aBorder = 195 new unoidl.com.sun.star.table.TableBorder(); 196 aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine = 197 aBorder.RightLine = aLine; 198 aBorder.IsTopLineValid = aBorder.IsBottomLineValid = true; 199 aBorder.IsLeftLineValid = aBorder.IsRightLineValid = true; 200 xPropSet.setPropertyValue( 201 "TableBorder", 202 new uno.Any( 203 typeof (unoidl.com.sun.star.table.TableBorder), aBorder ) ); 204 205 // draw headline 206 unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = 207 (unoidl.com.sun.star.sheet.XCellRangeAddressable) xCellRange; 208 unoidl.com.sun.star.table.CellRangeAddress aAddr = 209 xAddr.getRangeAddress(); 210 211 xCellRange = xSheet.getCellRangeByPosition( 212 aAddr.StartColumn, 213 aAddr.StartRow, aAddr.EndColumn, aAddr.StartRow ); 214 215 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; 216 xPropSet.setPropertyValue( 217 "CellBackColor", new uno.Any( (Int32) 0x99CCFF ) ); 218 // write headline 219 unoidl.com.sun.star.table.XCell xCell = 220 xCellRange.getCellByPosition( 0, 0 ); 221 xCell.setFormula( aHeadline ); 222 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCell; 223 xPropSet.setPropertyValue( 224 "CharColor", new uno.Any( (Int32) 0x003399 ) ); 225 xPropSet.setPropertyValue( 226 "CharWeight", 227 new uno.Any( (Single) unoidl.com.sun.star.awt.FontWeight.BOLD ) ); 228 } 229 230 // ________________________________________________________________ 231 // Methods to create cell addresses and range addresses. 232 233 /** Creates a unoidl.com.sun.star.table.CellAddress and initializes it 234 with the given range. 235 @param xSheet The XSpreadsheet interface of the spreadsheet. 236 @param aCell The address of the cell (or a named cell). */ createCellAddress( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aCell )237 public unoidl.com.sun.star.table.CellAddress createCellAddress( 238 unoidl.com.sun.star.sheet.XSpreadsheet xSheet, 239 String aCell ) 240 { 241 unoidl.com.sun.star.sheet.XCellAddressable xAddr = 242 (unoidl.com.sun.star.sheet.XCellAddressable) 243 xSheet.getCellRangeByName( aCell ).getCellByPosition( 0, 0 ); 244 return xAddr.getCellAddress(); 245 } 246 247 /** Creates a unoidl.com.sun.star.table.CellRangeAddress and initializes 248 it with the given range. 249 @param xSheet The XSpreadsheet interface of the spreadsheet. 250 @param aRange The address of the cell range (or a named range). */ createCellRangeAddress( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange )251 public unoidl.com.sun.star.table.CellRangeAddress createCellRangeAddress( 252 unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange ) 253 { 254 unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = 255 (unoidl.com.sun.star.sheet.XCellRangeAddressable) 256 xSheet.getCellRangeByName( aRange ); 257 return xAddr.getRangeAddress(); 258 } 259 260 // ________________________________________________________________ 261 // Methods to convert cell addresses and range addresses to strings. 262 263 /** Returns the text address of the cell. 264 @param nColumn The column index. 265 @param nRow The row index. 266 @return A string containing the cell address. */ getCellAddressString( int nColumn, int nRow )267 public String getCellAddressString( int nColumn, int nRow ) 268 { 269 String aStr = ""; 270 if (nColumn > 25) 271 aStr += (char) ('A' + nColumn / 26 - 1); 272 aStr += (char) ('A' + nColumn % 26); 273 aStr += (nRow + 1); 274 return aStr; 275 } 276 277 /** Returns the text address of the cell range. 278 @param aCellRange The cell range address. 279 @return A string containing the cell range address. */ getCellRangeAddressString( unoidl.com.sun.star.table.CellRangeAddress aCellRange )280 public String getCellRangeAddressString( 281 unoidl.com.sun.star.table.CellRangeAddress aCellRange ) 282 { 283 return 284 getCellAddressString( aCellRange.StartColumn, aCellRange.StartRow ) 285 + ":" 286 + getCellAddressString( aCellRange.EndColumn, aCellRange.EndRow ); 287 } 288 289 /** Returns the text address of the cell range. 290 @param xCellRange The XSheetCellRange interface of the cell range. 291 @param bWithSheet true = Include sheet name. 292 @return A string containing the cell range address. */ getCellRangeAddressString( unoidl.com.sun.star.sheet.XSheetCellRange xCellRange, bool bWithSheet )293 public String getCellRangeAddressString( 294 unoidl.com.sun.star.sheet.XSheetCellRange xCellRange, bool bWithSheet ) 295 { 296 String aStr = ""; 297 if (bWithSheet) 298 { 299 unoidl.com.sun.star.sheet.XSpreadsheet xSheet = 300 xCellRange.getSpreadsheet(); 301 unoidl.com.sun.star.container.XNamed xNamed = 302 (unoidl.com.sun.star.container.XNamed) xSheet; 303 aStr += xNamed.getName() + "."; 304 } 305 unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = 306 (unoidl.com.sun.star.sheet.XCellRangeAddressable) xCellRange; 307 aStr += getCellRangeAddressString( xAddr.getRangeAddress() ); 308 return aStr; 309 } 310 311 /** Returns a list of addresses of all cell ranges contained in the 312 collection. 313 314 @param xRangesIA The XIndexAccess interface of the collection. 315 @return A string containing the cell range address list. */ getCellRangeListString( unoidl.com.sun.star.container.XIndexAccess xRangesIA )316 public String getCellRangeListString( 317 unoidl.com.sun.star.container.XIndexAccess xRangesIA ) 318 { 319 String aStr = ""; 320 int nCount = xRangesIA.getCount(); 321 for (int nIndex = 0; nIndex < nCount; ++nIndex) 322 { 323 if (nIndex > 0) 324 aStr += " "; 325 uno.Any aRangeObj = xRangesIA.getByIndex( nIndex ); 326 unoidl.com.sun.star.sheet.XSheetCellRange xCellRange = 327 (unoidl.com.sun.star.sheet.XSheetCellRange) aRangeObj.Value; 328 aStr += getCellRangeAddressString( xCellRange, false ); 329 } 330 return aStr; 331 } 332 333 // ________________________________________________________________ 334 335 /** Connect to a running office that is accepting connections. 336 @return The ServiceManager to instantiate office components. */ connect( String [] args )337 private XMultiServiceFactory connect( String [] args ) 338 { 339 340 m_xContext = uno.util.Bootstrap.bootstrap(); 341 342 return (XMultiServiceFactory) m_xContext.getServiceManager(); 343 } 344 Dispose()345 public void Dispose() 346 { 347 348 } 349 350 /** Creates an empty spreadsheet document. 351 @return The XSpreadsheetDocument interface of the document. */ initDocument()352 private unoidl.com.sun.star.sheet.XSpreadsheetDocument initDocument() 353 { 354 XComponentLoader aLoader = (XComponentLoader) 355 mxMSFactory.createInstance( "com.sun.star.frame.Desktop" ); 356 357 XComponent xComponent = aLoader.loadComponentFromURL( 358 "private:factory/scalc", "_blank", 0, 359 new unoidl.com.sun.star.beans.PropertyValue[0] ); 360 361 return (unoidl.com.sun.star.sheet.XSpreadsheetDocument) xComponent; 362 } 363 364 // ________________________________________________________________ 365 } 366