OpenOffice Automation: The Beginning

At work, I needed to develop a mechanism for loading and unloading data from Excel using free mechanisms working under different operating systems.

Because it was necessary for the service to work under Linux, the interaction mechanism through OLE did not fit.

OpenOffice was chosen for implementation with direct access to the API via C ++.







The documentation turned out to be very scarce, and there were almost no examples of work through OLE. So I decided to combine all my research in one article on Habré.

')

Approximate plan of the article:

1. Getting started with the OpenOffice API via C ++

1.1. Header generation

1.2. Generating an RDB Type File

1.3. Visual Studio 2008 Setup

1.4. We collect dynamic DLL

2. Reuse DLL in your program

2.1. An example of a small program for uploading data



In principle, if the capabilities of the DLL are sufficient, then repeating the actions of p. 1 is not necessary.

1. Getting started with the OpenOffice API via C ++

This section is described in some detail in the office wiki: wiki.services.openoffice.org/wiki/SDKInstallation .



To get started with the API, you need to download the SDK from the download.services.openoffice.org/files/stable address. It is important that the SDK version matches the version of the installed office.

Install the SDK in any directory.

Install auxiliary utilities gnu make, zip (if necessary)

1.1. Header generation

Inside the directory with the SDK, there should be a setsdkenv_windows.bat batch file that generates another batch file.

We carry it out by answering questions.

A batch file will be generated with the settings of environment variables.

We open the console, execute the generated batch file, and in the same prepared console we generate header files for a specific office version.



Generation Team:

cppumaker -Gc -BUCR -O "c:\Program Files\OpenOffice_SDK\sdk\inludecpp" "c:\Program Files\OpenOffice.org 3\URE\misc\types.rdb" "c:\Program Files\OpenOffice.org 3\Basis\program\offapi.rdb"



where

c: \ Program Files \ OpenOffice_SDK \ sdk \ inludecpp - the folder where we will generate

c: \ Program Files \ OpenOffice.org 3 \ - path to the office

1.2. Generate RDB file types.

Also, to get started, you need to generate an RDB file specifically for your office version.

An RDB file is something like a file with descriptions of the types and interfaces of a particular version of OpenOffice.



This is done by the command:

"C:\Program Files\OpenOffice.org 3\URE\bin\regmerge" "d:\oo\OOAPI\Debug\OOAPI.rdb" / "c:\Program Files\OpenOffice.org 3\URE\misc\types.rdb"



where C: \ Program Files \ OpenOffice.org 3 \ is the path to the office

d: \ oo \ OOAPI \ Debug \ - the path where the file will be generated



After that we register it with the commands:

"C:\Program Files\OpenOffice.org 3\URE\bin\regcomp" -register -r "d:\oo\OOAPI\Debug\OOAPI.rdb" -c connector.uno.dll

"C:\Program Files\OpenOffice.org 3\URE\bin\regcomp" -register -r "d:\oo\OOAPI\Debug\OOAPI.rdb" -c remotebridge.uno.dll

"C:\Program Files\OpenOffice.org 3\URE\bin\regcomp" -register -r "d:\oo\OOAPI\Debug\OOAPI.rdb" -c bridgefac.uno.dll

"C:\Program Files\OpenOffice.org 3\URE\bin\regcomp" -register -r "d:\oo\OOAPI\Debug\OOAPI.rdb" -c uuresolver.uno.dll



1.3. Visual Studio 2008 Setup

We connect heading files and:



Tools -> Options-> Projects-> VC ++ Directories -> Include Files

Add the folder with the generated header files: <oo_sdk_path> \ inludecpp

Tools -> Options-> Projects-> VC ++ Directories -> Library files

add <oo_sdk_path> \ lib directory



Tools -> Options-> Projects-> VC ++ Directories -> Executable files

add <office_programm_dir> \ program directory



Project properties:

Change the configuration to "All Configurations"

Add extras

Properties-> Linker-> ComandLine in Additional Options

add if:

isal.lib icppu.lib icppuhelper.lib isal.lib isalhelper.lib ireg.lib irmcxt.lib stlport_vc71.lib



Everything is ready to create console programs for working with the office.

1.4. We collect dynamic DLL

Many useful examples can be found here: wiki.services.openoffice.org/wiki/Calc/API/Programming



The work plan of the DLL is like this:

- When the program starts, we check the presence of the RDB file, if it is not there, then it is generated.

Functions connect, genRdb

If there are no administrator rights on the computer, then it is better to generate the RDB in advance for the standard office version.

- We start the OpenOffice server, set the port for listening to it

startServer, the address of the office server is obtained from the registry by the getOOPath function

- The program connects to the server office

- a new office frame is created: a new file or an xls file is imported.

- After that, you can send commands to manage the content

- export xls file to disk

exportToUrl function

- the reverse mechanism is also possible: imls xls file and reading data from it

functions getVal, getText



I am attaching the DLL code, I hope comments will be enough. The full project can be downloaded here .



ooapi.h:

 //    #define OOAPI3 __declspec(dllexport) #define WNT 1 #include <stdio.h> #include <wchar.h> #include <sal/main.h> #include <cppuhelper/bootstrap.hxx> #include <osl/file.hxx> #include <osl/process.h> // ,    #include <com/sun/star/beans/XPropertySet.hpp> #include <com/sun/star/bridge/XUnoUrlResolver.hpp> #include <com/sun/star/frame/XComponentLoader.hpp> #include <com/sun/star/lang/XMultiComponentFactory.hpp> #include <com/sun/star/registry/XSimpleRegistry.hpp> #include <com/sun/star/sheet/XSpreadsheetDocument.hpp> #include <com/sun/star/sheet/XSpreadsheet.hpp> #include <com/sun/star/container/XIndexAccess.hpp> #include <com/sun/star/table/XCellRange.hpp> #include <com/sun/star/table/BorderLine.hpp> #include <com/sun/star/table/CellHoriJustify.hpp> #include <com/sun/star/table/XColumnRowRange.hpp> #include <com/sun/star/table/XMergeableCell.hpp> #include <com/sun/star/table/XMergeableCellRange.hpp> #include <com/sun/star/table/TableBorder.hpp> #include <com/sun/star/beans/XPropertySet.hpp> #include <com/sun/star/frame/XStorable.hpp> #include <com/sun/star/util/XMergeable.hpp> #include <string> #include <locale> #include <io.h> #include <iostream> #include <sstream> using namespace rtl; using namespace std; using namespace com::sun::star::uno; using namespace com::sun::star::lang; using namespace com::sun::star::beans; using namespace com::sun::star::bridge; using namespace com::sun::star::frame; using namespace com::sun::star::registry; using namespace com::sun::star::sheet; using namespace com::sun::star::table; using namespace com::sun::star::container; using namespace com::sun::star::util; //  OOAPI3 bool connect(const char *file, bool hidden); OOAPI3 void disconnect(); OOAPI3 bool selectSheet(short sheet); OOAPI3 void setVal(int, int, double); OOAPI3 void setText(int x, int y, const wchar_t *text); OOAPI3 bool setBold(int x, int y); OOAPI3 bool setFontColor(int x, int y, int r, int g, int b); OOAPI3 bool setBgColor(int x, int y, int r, int g, int b); OOAPI3 bool setFontSize(int x, int y, short size); OOAPI3 bool setItalic(int x, int y); OOAPI3 bool setHoriz(int x, int y, short hor); OOAPI3 bool setBorders(int x, int y, bool lft, bool tp, bool rt, bool dn, short r, short g, short b); OOAPI3 bool setColWidth(int col, long width); OOAPI3 bool mergeRange(const char *range); OOAPI3 bool exportToUrl(const wchar_t *url); OOAPI3 double getVal(int x, int y); OOAPI3 wchar_t* getText(int x, int y); OOAPI3 bool isWin(); OOAPI3 bool isInstall(); 

ooapi.cpp:

 #include "stdafx.h" //  wine, ,     bool isWin() { // wine #ifdef ISWIN return ISWIN; #endif HMODULE h = LoadLibrary(L"ntdll.dll"); bool win = (h != NULL); if(h != NULL) { win = GetProcAddress(h, "wine_get_version") == NULL; FreeLibrary(h); } #define ISWIN win printf("module load on win: %u\n", win); return win; } //    ,     string getOOPath() { //   ,        #ifdef OOPATH return OOPATH; #endif string path; //     HKEY hKey = NULL; wchar_t * buf = NULL; ULONG dim = 0; RegOpenKeyEx( HKEY_LOCAL_MACHINE, TEXT("SOFTWARE\\Classes\\opendocument.CalcDocument.1\\protocol\\StdFileEditing\\server"), 0, KEY_ALL_ACCESS, &hKey ); RegQueryValueEx(hKey, TEXT(""), NULL, NULL, NULL, &dim); buf = new wchar_t[dim + 1]; RegQueryValueEx(hKey, TEXT(""), NULL, NULL, (UCHAR*)buf, &dim); RegCloseKey(hKey); // unicode  string wstring upath(buf); ostringstream stm ; const ctype<char>& ctfacet = use_facet< ctype<char> >( stm.getloc() ) ; for( size_t i=0 ; i < upath.size() ; ++i ) stm << ctfacet.narrow( upath[i], 0 ) ; path = stm.str(); delete buf; if(path == "") return ""; // ,     path = path.substr(0, path.length()-20); printf("server path: %s\n", path.c_str()); #define OOPATH path return path; } //  void createProcess(string app) { STARTUPINFO StartupInfo; ZeroMemory(&StartupInfo,sizeof(StartupInfo)); StartupInfo.cb = sizeof(StartupInfo); StartupInfo.dwFlags = STARTF_USESHOWWINDOW; StartupInfo.wShowWindow = SW_MAXIMIZE; PROCESS_INFORMATION ProcessInfo; // //      wine,    RDB    int len = lstrlenA(app.c_str()); BSTR utext = SysAllocStringLen(NULL, len); ::MultiByteToWideChar(CP_ACP, 0, app.c_str(), len, utext, len); ::SysFreeString(utext); //  if (CreateProcess(NULL, utext,NULL,NULL,false,CREATE_NO_WINDOW|NORMAL_PRIORITY_CLASS, NULL,NULL,&StartupInfo,&ProcessInfo)) { if (ProcessInfo.hProcess != NULL) { //,     while (WaitForSingleObject(ProcessInfo.hProcess,200) == WAIT_TIMEOUT) { Sleep(100); } } } } //  RDB   void genRDB() { string rdb = "OOAPI.rdb"; if(access("OOAPI.rdb",0) != -1) { //rdb   return; } printf("generate RDB\n"); //    wine // RDB  string oopath = getOOPath(); string oogen = "\"" + oopath + "\\URE\\bin\\regmerge\" \"" + rdb + "\" / \"" + oopath + "\\URE\\misc\\types.rdb\" \"" + oopath + "\\Basis\\program\\offapi.rdb\""; createProcess(oogen); // string regpath; regpath = "\"" + oopath + "\\URE\\bin\\regcomp\" -register -r \"" + rdb + "\" -c connector.uno.dll"; createProcess(regpath); regpath = "\"" + oopath + "\\URE\\bin\\regcomp\" -register -r \"" + rdb + "\" -c remotebridge.uno.dll"; createProcess(regpath); regpath = "\"" + oopath + "\\URE\\bin\\regcomp\" -register -r \"" + rdb + "\" -c bridgefac.uno.dll"; createProcess(regpath); regpath = "\"" + oopath + "\\URE\\bin\\regcomp\" -register -r \"" + rdb + "\" -c uuresolver.uno.dll"; createProcess(regpath); } //  ,      ,   -  :) bool isInstall() { return getOOPath() != ""; } // ,      void startServer() { string server_path; server_path = "\"" + getOOPath() + "\\program\\soffice\" \"-accept=socket,host=localhost,port=2083;urp;StarOffice.ServiceManager\""; //createProcess(server_path); //  WinExec(server_path.c_str(), SW_HIDE); } //#########   // Reference< XComponent > xComponent; //  Any rSheet; Reference< XMultiComponentFactory > xMultiComponentFactoryClient_copy; //  ,        bool connect(const char *file, bool hidden) { //    SAL_IMPLEMENT_MAIN_WITH_ARGS //  sal_main() sal_detail_initialize(NULL, NULL); //  ,     OUString sConnectionString(RTL_CONSTASCII_USTRINGPARAM("uno:socket,host=localhost,port=2083;urp;StarOffice.ServiceManager")); //  OUString sFileString = OUString::createFromAscii(file); //     genRDB(); //   startServer(); printf("init server start OK\n"); // RDB  Reference< XSimpleRegistry > xSimpleRegistry(::cppu::createSimpleRegistry() ); xSimpleRegistry->open( OUString( RTL_CONSTASCII_USTRINGPARAM( "OOAPI.rdb") ), sal_True, sal_False ); printf("init rdb OK\n"); //-  , //           UNO  open office Reference< XComponentContext > xComponentContext(::cppu::bootstrap_InitialComponentContext( xSimpleRegistry ) ); Reference< XMultiComponentFactory > xMultiComponentFactoryClient( xComponentContext->getServiceManager() ); xMultiComponentFactoryClient_copy = xMultiComponentFactoryClient; Reference< XInterface > xInterface = xMultiComponentFactoryClient->createInstanceWithContext( OUString::createFromAscii( "com.sun.star.bridge.UnoUrlResolver" ), xComponentContext ); Reference< XUnoUrlResolver > resolver( xInterface, UNO_QUERY ); printf("init OK\n"); // ,       -     int i = 200; while(i > 0) { try { Sleep(300); xInterface = Reference< XInterface >( resolver->resolve( sConnectionString ), UNO_QUERY ); i = 0; } catch ( Exception& e ) { i--; if(i == 0) { //    printf("can not connect to server FAIL\n"); return false; } } } printf("connect to server OK\n"); Reference< XPropertySet > xPropSet( xInterface, UNO_QUERY ); xPropSet->getPropertyValue( OUString::createFromAscii("DefaultContext") ) >>= xComponentContext; Reference< XMultiComponentFactory > xMultiComponentFactoryServer( xComponentContext->getServiceManager() ); Reference < XComponentLoader > xComponentLoader( xMultiComponentFactoryServer->createInstanceWithContext( OUString( RTL_CONSTASCII_USTRINGPARAM("com.sun.star.frame.Desktop" ) ), xComponentContext ), UNO_QUERY ); printf("before create OK\n"); string fl = file; if(fl.find(".xls") != string::npos) { // xls  Sequence<PropertyValue> props(2); props[0].Name = OUString::createFromAscii( "FilterName" ); props[0].Value <<= OUString::createFromAscii( "MS Excel 97" ); // wine ,     if(hidden) { props[1].Name = OUString::createFromAscii( "Hidden" ); props[1].Value <<= hidden; } xComponent = xComponentLoader->loadComponentFromURL(sFileString, OUString( RTL_CONSTASCII_USTRINGPARAM("_blank") ), 0, props); } else { //         Sequence<PropertyValue> props(1); if(hidden) { props[0].Name = OUString::createFromAscii( "Hidden" ); props[0].Value <<= hidden; } //   xComponent = xComponentLoader->loadComponentFromURL(sFileString, OUString( RTL_CONSTASCII_USTRINGPARAM("_blank") ), 0, props); } printf("create oocalc OK\n"); return true; } //   bool selectSheet(short sheet) { //  Reference< XSpreadsheetDocument > rSheetDoc (xComponent, UNO_QUERY); Reference< XSpreadsheets > rSheets = rSheetDoc->getSheets(); //    Reference< XIndexAccess > rSheetsByIndex (rSheets, UNO_QUERY); try { rSheet = rSheetsByIndex->getByIndex( (short)sheet ); } catch( Exception &e ) { disconnect(); return false; } return true; } //   void setVal(int x, int y, double val) { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); //   Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); //   rCell->setValue(val); } //    void setText(int x, int y, const wchar_t *text) { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); rCell->setFormula(OUString::OUString(text)); } //    bool setBold(int x, int y) { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); try { Reference< XPropertySet > rCellProps (rCell, UNO_QUERY); Any mPropVal; mPropVal <<= makeAny((short)150); rCellProps->setPropertyValue(OUString::createFromAscii("CharWeight"), mPropVal); return true; } catch( Exception &e ) { return false; } } //  bool setItalic(int x, int y) { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); try { Reference< XPropertySet > rCellProps (rCell, UNO_QUERY); Any mPropVal4; mPropVal4 <<= makeAny((short)2); rCellProps->setPropertyValue(OUString::createFromAscii("CharPosture"), mPropVal4); return true; } catch( Exception &e ) { return false; } } //   bool setFontColor(int x, int y, int r, int g, int b) { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); try { Reference< XPropertySet > rCellProps (rCell, UNO_QUERY); Any mPropVal1; mPropVal1 <<= makeAny(RGB(b,g,r)); rCellProps->setPropertyValue(OUString::createFromAscii("CharColor"), mPropVal1); return true; } catch( Exception &e ) { return false; } } //   bool setBgColor(int x, int y, int r, int g, int b) { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); try { Reference< XPropertySet > rCellProps (rCell, UNO_QUERY); Any mPropVal1; mPropVal1 <<= makeAny(RGB(b,g,r)); rCellProps->setPropertyValue(OUString::createFromAscii("CellBackColor"), mPropVal1); return true; } catch( Exception &e ) { return false; } } //   bool setFontSize(int x, int y, short size) { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); try { Reference< XPropertySet > rCellProps (rCell, UNO_QUERY); Any mPropVal3; mPropVal3 <<= makeAny((short)size); rCellProps->setPropertyValue(OUString::createFromAscii("CharHeight"), mPropVal3); return true; } catch( Exception &e ) { return false; } } //    bool setHoriz(int x, int y, short hor) { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); try { Reference< XPropertySet > rCellProps (rCell, UNO_QUERY); Any mPropVal5; mPropVal5 <<= makeAny((short)hor); rCellProps->setPropertyValue(OUString::createFromAscii("HoriJustify"), mPropVal5); return true; } catch( Exception &e ) { return false; } } //    bool setBorders(int x, int y, bool lft, bool tp, bool rt, bool dn, short r, short g, short b) { try { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); Reference< XPropertySet > rCellProps (rCell, UNO_QUERY); BorderLine bl; bl.Color = RGB(b,g,r); //rgb   ? bl.OuterLineWidth = 10; TableBorder b; if(lft) b.LeftLine = bl; if(tp) b.TopLine = bl; if(rt) b.RightLine = bl; if(dn) b.BottomLine = bl; b.VerticalLine = b.HorizontalLine = bl; b.IsVerticalLineValid = true; b.IsHorizontalLineValid = true; b.IsLeftLineValid = lft; b.IsRightLineValid = rt; b.IsTopLineValid = tp; b.IsBottomLineValid = dn; rCellProps->setPropertyValue(OUString::createFromAscii("TableBorder"),makeAny(b)); return true; } catch( Exception &e ) { return false; } } //     bool setColWidth(int col, long width) { try { Reference< XColumnRowRange > rSheetColRange (rSheet, UNO_QUERY); Reference< XTableColumns > rSheetColumns = rSheetColRange->getColumns(); Any rCol = rSheetColumns->getByIndex(col); Reference< XPropertySet > rColProps (rCol, UNO_QUERY); rColProps->setPropertyValue(OUString::createFromAscii("Width"),makeAny(width*100)); return true; } catch( Exception &e ) { return false; } } //  bool mergeRange(const char *range) { try { Reference< XCellRange > rSheetCellRange (rSheet, UNO_QUERY); Reference< XCellRange> rCellRange = rSheetCellRange->getCellRangeByName(OUString::createFromAscii(range)); Reference< XMergeable > rSheetCellMerge (rCellRange, UNO_QUERY); rSheetCellMerge->merge(true); return true; } catch( Exception &e ) { return false; } } // xls    bool exportToUrl(const wchar_t *url) { try { Reference<XStorable> xStore (xComponent, UNO_QUERY); Sequence<PropertyValue> storeProps(1); storeProps[0].Name = OUString::createFromAscii( "FilterName" ); storeProps[0].Value <<= OUString::createFromAscii( "MS Excel 97" ); xStore->storeToURL( OUString::OUString(url), storeProps ); return true; } catch( Exception &e ) { return false; } } //    double getVal(int x, int y) { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); return rCell->getValue(); } //    () wchar_t* getText(int x, int y) { Reference< XSpreadsheet > rSpSheet (rSheet, UNO_QUERY); Reference< XCell > rCell = rSpSheet->getCellByPosition(x, y); OUString buf = rCell->getFormula(); return buf.pData->buffer; } // void disconnect() { Reference< XComponent >::query( xMultiComponentFactoryClient_copy )->dispose(); sal_detail_deinitialize(); } 

Final chat: use a DLL to load and unload into Excel.