/**************************************************************
*
* 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 );
}
// ________________________________________________________________
}