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