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