/************************************************************** * * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, * software distributed under the License is distributed on an * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * KIND, either express or implied. See the License for the * specific language governing permissions and limitations * under the License. * *************************************************************/ import com.sun.star.comp.servicemanager.ServiceManager; import com.sun.star.bridge.XUnoUrlResolver; import com.sun.star.uno.XNamingService; import com.sun.star.frame.XDesktop; import com.sun.star.frame.XComponentLoader; import com.sun.star.lang.XMultiServiceFactory; import com.sun.star.lang.XComponent; import com.sun.star.uno.UnoRuntime; import com.sun.star.uno.RuntimeException; // __________ implementation ____________________________________ /** This is a helper class for the spreadsheet and table samples. It connects to a running office and creates a spreadsheet document. Additionally it contains various helper functions. */ public class SpreadsheetDocHelper { // __ private members ___________________________________________ private final String msDataSheetName = "Data"; private com.sun.star.uno.XComponentContext mxRemoteContext; private com.sun.star.lang.XMultiComponentFactory mxRemoteServiceManager; // private com.sun.star.lang.XMultiServiceFactory mxMSFactory; private com.sun.star.sheet.XSpreadsheetDocument mxDocument; // ________________________________________________________________ public SpreadsheetDocHelper( String[] args ) { // Connect to a running office and get the service manager connect(); // Create a new spreadsheet document try { mxDocument = initDocument(); } catch (Exception ex) { System.err.println( "Couldn't create document: " + ex ); System.err.println( "Error: Couldn't create Document\nException Message = " + ex.getMessage()); ex.printStackTrace(); System.exit( 1 ); } } // __ helper methods ____________________________________________ /** Returns the service manager of the connected office. @return XMultiComponentFactory interface of the service manager. */ public com.sun.star.lang.XMultiComponentFactory getServiceManager() { return mxRemoteServiceManager; } /** Returns the component context of the connected office @return XComponentContext interface of the context. */ public com.sun.star.uno.XComponentContext getContext() { return mxRemoteContext; } /** Returns the whole spreadsheet document. @return XSpreadsheetDocument interface of the document. */ public com.sun.star.sheet.XSpreadsheetDocument getDocument() { return mxDocument; } /** Returns the spreadsheet with the specified index (0-based). @param nIndex The index of the sheet. @return XSpreadsheet interface of the sheet. */ public com.sun.star.sheet.XSpreadsheet getSpreadsheet( int nIndex ) { // Collection of sheets com.sun.star.sheet.XSpreadsheets xSheets = mxDocument.getSheets(); com.sun.star.sheet.XSpreadsheet xSheet = null; try { com.sun.star.container.XIndexAccess xSheetsIA = (com.sun.star.container.XIndexAccess)UnoRuntime.queryInterface( com.sun.star.container.XIndexAccess.class, xSheets ); xSheet = (com.sun.star.sheet.XSpreadsheet) UnoRuntime.queryInterface( com.sun.star.sheet.XSpreadsheet.class, xSheetsIA.getByIndex(nIndex)); } catch (Exception ex) { System.err.println( "Error: caught exception in getSpreadsheet()!\nException Message = " + ex.getMessage()); ex.printStackTrace(); } return xSheet; } /** Inserts a new empty spreadsheet with the specified name. @param aName The name of the new sheet. @param nIndex The insertion index. @return The XSpreadsheet interface of the new sheet. */ public com.sun.star.sheet.XSpreadsheet insertSpreadsheet( String aName, short nIndex ) { // Collection of sheets com.sun.star.sheet.XSpreadsheets xSheets = mxDocument.getSheets(); com.sun.star.sheet.XSpreadsheet xSheet = null; try { xSheets.insertNewByName( aName, nIndex ); xSheet = (com.sun.star.sheet.XSpreadsheet) UnoRuntime.queryInterface(com.sun.star.sheet.XSpreadsheet.class, xSheets.getByName( aName )); } catch (Exception ex) { System.err.println( "Error: caught exception in insertSpreadsheet()!\nException Message = " + ex.getMessage()); ex.printStackTrace(); } return xSheet; } // ________________________________________________________________ // Methods to fill values into cells. /** Writes a double value into a spreadsheet. @param xSheet The XSpreadsheet interface of the spreadsheet. @param aCellName The address of the cell (or a named range). @param fValue The value to write into the cell. */ public void setValue( com.sun.star.sheet.XSpreadsheet xSheet, String aCellName, double fValue ) throws RuntimeException, Exception { xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ).setValue( fValue ); } /** Writes a formula into a spreadsheet. @param xSheet The XSpreadsheet interface of the spreadsheet. @param aCellName The address of the cell (or a named range). @param aFormula The formula to write into the cell. */ public void setFormula( com.sun.star.sheet.XSpreadsheet xSheet, String aCellName, String aFormula ) throws RuntimeException, Exception { xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ).setFormula( aFormula ); } /** Writes a date with standard date format into a spreadsheet. @param xSheet The XSpreadsheet interface of the spreadsheet. @param aCellName The address of the cell (or a named range). @param nDay The day of the date. @param nMonth The month of the date. @param nYear The year of the date. */ public void setDate( com.sun.star.sheet.XSpreadsheet xSheet, String aCellName, int nDay, int nMonth, int nYear ) throws RuntimeException, Exception { // Set the date value. com.sun.star.table.XCell xCell = xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ); String aDateStr = nMonth + "/" + nDay + "/" + nYear; xCell.setFormula( aDateStr ); // Set standard date format. com.sun.star.util.XNumberFormatsSupplier xFormatsSupplier = (com.sun.star.util.XNumberFormatsSupplier) UnoRuntime.queryInterface( com.sun.star.util.XNumberFormatsSupplier.class, getDocument() ); com.sun.star.util.XNumberFormatTypes xFormatTypes = (com.sun.star.util.XNumberFormatTypes) UnoRuntime.queryInterface( com.sun.star.util.XNumberFormatTypes.class, xFormatsSupplier.getNumberFormats() ); int nFormat = xFormatTypes.getStandardFormat( com.sun.star.util.NumberFormat.DATE, new com.sun.star.lang.Locale() ); com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); xPropSet.setPropertyValue( "NumberFormat", new Integer( nFormat ) ); } /** Draws a colored border around the range and writes the headline in the first cell. @param xSheet The XSpreadsheet interface of the spreadsheet. @param aRange The address of the cell range (or a named range). @param aHeadline The headline text. */ public void prepareRange( com.sun.star.sheet.XSpreadsheet xSheet, String aRange, String aHeadline ) throws RuntimeException, Exception { com.sun.star.beans.XPropertySet xPropSet = null; com.sun.star.table.XCellRange xCellRange = null; // draw border xCellRange = xSheet.getCellRangeByName( aRange ); xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); com.sun.star.table.BorderLine aLine = new com.sun.star.table.BorderLine(); aLine.Color = 0x99CCFF; aLine.InnerLineWidth = aLine.LineDistance = 0; aLine.OuterLineWidth = 100; com.sun.star.table.TableBorder aBorder = new com.sun.star.table.TableBorder(); aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine = aBorder.RightLine = aLine; aBorder.IsTopLineValid = aBorder.IsBottomLineValid = true; aBorder.IsLeftLineValid = aBorder.IsRightLineValid = true; xPropSet.setPropertyValue( "TableBorder", aBorder ); // draw headline com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable) UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange ); com.sun.star.table.CellRangeAddress aAddr = xAddr.getRangeAddress(); xCellRange = xSheet.getCellRangeByPosition( aAddr.StartColumn, aAddr.StartRow, aAddr.EndColumn, aAddr.StartRow ); xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x99CCFF ) ); // write headline com.sun.star.table.XCell xCell = xCellRange.getCellByPosition( 0, 0 ); xCell.setFormula( aHeadline ); xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); xPropSet.setPropertyValue( "CharColor", new Integer( 0x003399 ) ); xPropSet.setPropertyValue( "CharWeight", new Float( com.sun.star.awt.FontWeight.BOLD ) ); } // ________________________________________________________________ // Methods to create cell addresses and range addresses. /** Creates a com.sun.star.table.CellAddress and initializes it with the given range. @param xSheet The XSpreadsheet interface of the spreadsheet. @param aCell The address of the cell (or a named cell). */ public com.sun.star.table.CellAddress createCellAddress( com.sun.star.sheet.XSpreadsheet xSheet, String aCell ) throws RuntimeException, Exception { com.sun.star.sheet.XCellAddressable xAddr = (com.sun.star.sheet.XCellAddressable) UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class, xSheet.getCellRangeByName( aCell ).getCellByPosition( 0, 0 ) ); return xAddr.getCellAddress(); } /** Creates a com.sun.star.table.CellRangeAddress and initializes it with the given range. @param xSheet The XSpreadsheet interface of the spreadsheet. @param aRange The address of the cell range (or a named range). */ public com.sun.star.table.CellRangeAddress createCellRangeAddress( com.sun.star.sheet.XSpreadsheet xSheet, String aRange ) { com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable) UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xSheet.getCellRangeByName( aRange ) ); return xAddr.getRangeAddress(); } // ________________________________________________________________ // Methods to convert cell addresses and range addresses to strings. /** Returns the text address of the cell. @param nColumn The column index. @param nRow The row index. @return A string containing the cell address. */ public String getCellAddressString( int nColumn, int nRow ) { String aStr = ""; if (nColumn > 25) aStr += (char) ('A' + nColumn / 26 - 1); aStr += (char) ('A' + nColumn % 26); aStr += (nRow + 1); return aStr; } /** Returns the text address of the cell range. @param aCellRange The cell range address. @return A string containing the cell range address. */ public String getCellRangeAddressString( com.sun.star.table.CellRangeAddress aCellRange ) { return getCellAddressString( aCellRange.StartColumn, aCellRange.StartRow ) + ":" + getCellAddressString( aCellRange.EndColumn, aCellRange.EndRow ); } /** Returns the text address of the cell range. @param xCellRange The XSheetCellRange interface of the cell range. @param bWithSheet true = Include sheet name. @return A string containing the cell range address. */ public String getCellRangeAddressString( com.sun.star.sheet.XSheetCellRange xCellRange, boolean bWithSheet ) { String aStr = ""; if (bWithSheet) { com.sun.star.sheet.XSpreadsheet xSheet = xCellRange.getSpreadsheet(); com.sun.star.container.XNamed xNamed = (com.sun.star.container.XNamed) UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, xSheet ); aStr += xNamed.getName() + "."; } com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable) UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange ); aStr += getCellRangeAddressString( xAddr.getRangeAddress() ); return aStr; } /** Returns a list of addresses of all cell ranges contained in the collection. @param xRangesIA The XIndexAccess interface of the collection. @return A string containing the cell range address list. */ public String getCellRangeListString( com.sun.star.container.XIndexAccess xRangesIA ) throws RuntimeException, Exception { String aStr = ""; int nCount = xRangesIA.getCount(); for (int nIndex = 0; nIndex < nCount; ++nIndex) { if (nIndex > 0) aStr += " "; Object aRangeObj = xRangesIA.getByIndex( nIndex ); com.sun.star.sheet.XSheetCellRange xCellRange = (com.sun.star.sheet.XSheetCellRange) UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, aRangeObj ); aStr += getCellRangeAddressString( xCellRange, false ); } return aStr; } // ________________________________________________________________ // Connect to a running office that is accepting connections. private void connect() { if (mxRemoteContext == null && mxRemoteServiceManager == null) { try { // First step: get the remote office component context mxRemoteContext = com.sun.star.comp.helper.Bootstrap.bootstrap(); System.out.println("Connected to a running office ..."); mxRemoteServiceManager = mxRemoteContext.getServiceManager(); } catch( Exception e) { System.err.println("ERROR: can't get a component context from a running office ..."); e.printStackTrace(); System.exit(1); } } } /** Creates an empty spreadsheet document. @return The XSpreadsheetDocument interface of the document. */ private com.sun.star.sheet.XSpreadsheetDocument initDocument() throws RuntimeException, Exception { XComponentLoader aLoader = (XComponentLoader) UnoRuntime.queryInterface( XComponentLoader.class, mxRemoteServiceManager.createInstanceWithContext( "com.sun.star.frame.Desktop", mxRemoteContext)); XComponent xComponent = aLoader.loadComponentFromURL( "private:factory/scalc", "_blank", 0, new com.sun.star.beans.PropertyValue[0] ); return (com.sun.star.sheet.XSpreadsheetDocument)UnoRuntime.queryInterface( com.sun.star.sheet.XSpreadsheetDocument.class, xComponent ); } // ________________________________________________________________ }