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 
24 package util;
25 
26 import com.sun.star.container.XIndexAccess;
27 import com.sun.star.lang.IllegalArgumentException;
28 import com.sun.star.lang.IndexOutOfBoundsException;
29 import com.sun.star.lang.WrappedTargetException;
30 import com.sun.star.lang.XComponent;
31 import com.sun.star.sheet.XCellRangeData;
32 import com.sun.star.sheet.XSpreadsheet;
33 import com.sun.star.sheet.XSpreadsheetDocument;
34 import com.sun.star.sheet.XSpreadsheets;
35 import com.sun.star.table.XCellRange;
36 import com.sun.star.uno.AnyConverter;
37 import com.sun.star.uno.Exception;
38 import com.sun.star.uno.Type;
39 import com.sun.star.uno.UnoRuntime;
40 
41 /**
42  * This class contains some usefull mathods to handle Calc documents
43  * and its sheets.
44  */
45 public class CalcTools {
46 
47     /**
48      * fills a range of a calc sheet with computed data of type
49      * <CODE>Double</CODE>.
50      * @param xSheetDoc the Clac documents wich should be filled
51      * @param sheetNumber the number of the sheet of <CODE>xSheetDoc</CODE>
52      * @param startCellX the cell number of the X start point (row) of the range to fill
53      * @param startCellY the cell number of the Y start point (column) of the range to fill
54      * @param rangeLengthX the size of the range expansion in X-direction
55      * @param rangeLengthY the size of the range expansion in Y-direction
56      * @throws java.lang.Exception on any error an <CODE>java.lang.Exception</CODE> was thrown
57      */
fillCalcSheetWithContent(XComponent xSheetDoc, int sheetNumber, int startCellX, int startCellY, int rangeLengthX, int rangeLengthY)58     public static void fillCalcSheetWithContent(XComponent xSheetDoc, int sheetNumber,
59                         int startCellX, int startCellY, int rangeLengthX, int rangeLengthY)
60                   throws java.lang.Exception {
61         try{
62             XSpreadsheet xSheet = getSpreadSheetFromSheetDoc(xSheetDoc, sheetNumber);
63 
64             fillCalcSheetWithContent(xSheet, startCellX, startCellY, rangeLengthX, rangeLengthY);
65 
66         } catch (Exception e){
67                 throw new Exception(
68                         "Couldn't fill CalcSheet with content: " + e.toString());
69         }
70     }
71 
72     /**
73      * fills a range of a calc sheet with computed data of type
74      * <CODE>Double</CODE>.
75      * @param xSheet the sheet to fill with content
76      * @param startCellX the cell number of the X start point (row) of the range to fill
77      * @param startCellY the cell number of the Y start point (column) of the range to fill
78      * @param rangeLengthX the size of the range expansion in X-direction
79      * @param rangeLengthY the size of the range expansion in Y-direction
80      * @throws java.lang.Exception on any error an <CODE>java.lang.Exception</CODE> was thrown
81      */
fillCalcSheetWithContent(XSpreadsheet xSheet, int startCellX, int startCellY, int rangeLengthX, int rangeLengthY)82     public static void fillCalcSheetWithContent(XSpreadsheet xSheet,
83                         int startCellX, int startCellY, int rangeLengthX, int rangeLengthY)
84                   throws java.lang.Exception {
85 
86         try{
87             // create a range with content
88             Object[][] newData = new Object[rangeLengthY][rangeLengthX];
89             for (int i=0; i<rangeLengthY; i++) {
90                 for (int j=0; j<rangeLengthX; j++) {
91                     newData[i][j] = new Double(10*i +j);
92                 }
93             }
94             XCellRange xRange = null;
95             try {
96                 xRange = xSheet.getCellRangeByPosition(startCellX, startCellY,
97                                     startCellX+rangeLengthX-1, startCellY+rangeLengthY-1);
98             } catch ( IndexOutOfBoundsException e){
99                     throw new Exception(
100                             "Couldn't get CellRange from sheett: " + e.toString());
101             }
102 
103             XCellRangeData xRangeData = (XCellRangeData) UnoRuntime.queryInterface(XCellRangeData.class, xRange);
104 
105             xRangeData.setDataArray(newData);
106         } catch (Exception e){
107                 throw new Exception(
108                         "Couldn't fill CalcSheet with content: " + e.toString());
109         }
110     }
111 
112     /**
113      *
114      * returns an <CODE>XSpreadsheet</CODE> from a Calc document.
115      * @param xSheetDoc the Calc docuent which containes the sheet
116      * @param sheetNumber the number of the sheet to return
117      * @throws java.lang.Exception on any error an <CODE>java.lang.Exception</CODE> was thrown
118      * @return calc sheet
119      * @see com.sun.star.sheet.XSpreadsheet
120      */
getSpreadSheetFromSheetDoc(XComponent xSheetDoc, int sheetNumber)121     public static XSpreadsheet getSpreadSheetFromSheetDoc(XComponent xSheetDoc, int sheetNumber)
122                   throws java.lang.Exception {
123 
124         XSpreadsheet xSheet = null;
125 
126         try{
127             XSpreadsheetDocument xSpreadsheetDoc = (XSpreadsheetDocument)
128                     UnoRuntime.queryInterface(XSpreadsheetDocument.class, xSheetDoc);
129 
130             XSpreadsheets xSpreadsheets = xSpreadsheetDoc.getSheets();
131 
132             XIndexAccess xSheetsIndexArray = (XIndexAccess)
133                         UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets);
134 
135             try{
136                 xSheet = (XSpreadsheet) AnyConverter.toObject(
137                         new Type(XSpreadsheet.class),xSheetsIndexArray.getByIndex(sheetNumber));
138 
139             } catch (IllegalArgumentException e){
140                 throw new Exception(
141                         "Couldn't get sheet '" +sheetNumber + "' : " + e.toString());
142             } catch (IndexOutOfBoundsException e){
143                 throw new Exception(
144                         "Couldn't get sheet '" +sheetNumber + "' : " + e.toString());
145             } catch (WrappedTargetException e){
146                 throw new Exception(
147                         "Couldn't get sheet '" +sheetNumber + "' : " + e.toString());
148             }
149         } catch (Exception e){
150             throw new Exception(
151                 "Couldn't get sheet '" +sheetNumber + "' : " + e.toString());
152         }
153          return xSheet;
154     }
155 }
156