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