OpenOffice Automation: Ending

Continuation of the first part of the article.

2. Reuse DLL in your program

Opportunities:

• opening xls file
• opening OpenOffice files
• possibility of opening in hidden mode
• reading data from a spreadsheet
• full range of data manipulation: text insertion, numbers, formatting, merging cells, setting boundaries, setting column width
• unloading xls tables to disk
• The possibility of working under WINE, under the terms of installing OpenOffice under Wine
• use of formulas
• generation of version dependent RDB file on the fly (if necessary)

At work, we use C ++ Builder to write internal programs, so I am doing another wrapper on the DLL for easy work with the API.

//ooapi3.h #ifndef ooapi3H #define ooapi3H #include <windows.h> #include <vcl.h> #include "StrUtils.hpp" class OOAPI3 { private: HANDLE hLib; protected: //   bool (*ooConect)(const char *, bool); void (*ooDisconect)(); bool (*ooSelectSheet)(short); void (*ooSetVal)(int, int, double); void (*ooSetFormula)(int, int, const wchar_t *); bool (*ooSetBold)(int, int); bool (*ooSetFontColor)(int, int, int, int, int); bool (*ooSetBgColor)(int, int, int, int, int); bool (*ooSetFontSize)(int, int, short); bool (*ooSetItalic)(int, int); bool (*ooSetHoriz)(int, int, short); bool (*ooSetBorders)(int, int, bool, bool, bool, bool, short, short, short); bool (*ooSetColWidth)(int, long); bool (*ooMergeRange)(const char *); bool (*ooExportToUrl)(const wchar_t *); double (*ooGetVal)(int, int); wchar_t* (*ooGetText)(int, int); bool (*ooIsInstall)(); bool (*ooIsWin)(); public: __fastcall OOAPI3(); __fastcall ~OOAPI3(); bool __fastcall connect(const char* file, bool hidden = false); bool __fastcall setSheet(short sheet); void __fastcall setVal(int x, int y, double val); void __fastcall setText(int x, int y, AnsiString val); bool __fastcall setBold(int x, int y); bool __fastcall setItalic(int x, int y); bool __fastcall setFontColor(int x, int y, int r, int g, int b); bool __fastcall setBgColor(int x, int y, int r, int g, int b); bool __fastcall setFontSize(int x, int y, short size); bool __fastcall setHoriz(int x, int y, short horiz); bool __fastcall setBorders(int x, int y, bool l, bool t, bool r, bool d, short rd, short gr, short bl); bool __fastcall setBorders(int x, int y, bool l, bool t, bool r, bool d); bool __fastcall setBorders(int x, int y); bool __fastcall setColWidth(int col, long mm); bool __fastcall mergeRange(AnsiString from, AnsiString to); bool __fastcall mergeRange(int x1, int y1, int x2, int y2); bool __fastcall export2XLS(AnsiString to); double __fastcall getVal(int x, int y); AnsiString __fastcall getText(int x, int y); bool __fastcall isWin(); bool __fastcall isInstall(); AnsiString __fastcall getColNameById(int id); }; #endif 

')

 //ooapi3.cpp #include "ooapi3.h" __fastcall OOAPI3::OOAPI3() { //  HMODULE hLib = LoadLibrary("ooapi3.dll"); if(hLib == NULL) { throw; } //     (FARPROC &)ooConect = GetProcAddress(hLib, "connect"); (FARPROC &)ooDisconect = GetProcAddress(hLib, "disconnect"); (FARPROC &)ooSelectSheet = GetProcAddress(hLib, "selectSheet"); (FARPROC &)ooSetVal = GetProcAddress(hLib, "setVal"); (FARPROC &)ooSetFormula = GetProcAddress(hLib, "setText"); (FARPROC &)ooSetBold = GetProcAddress(hLib, "setBold"); (FARPROC &)ooSetFontColor = GetProcAddress(hLib, "setFontColor"); (FARPROC &)ooSetBgColor = GetProcAddress(hLib, "setBgColor"); (FARPROC &)ooSetFontSize = GetProcAddress(hLib, "setFontSize"); (FARPROC &)ooSetItalic = GetProcAddress(hLib, "setItalic"); (FARPROC &)ooSetHoriz = GetProcAddress(hLib, "setHoriz"); (FARPROC &)ooSetBorders = GetProcAddress(hLib, "setBorders"); (FARPROC &)ooSetColWidth = GetProcAddress(hLib, "setColWidth"); (FARPROC &)ooMergeRange = GetProcAddress(hLib, "mergeRange"); (FARPROC &)ooExportToUrl = GetProcAddress(hLib, "exportToUrl"); (FARPROC &)ooGetVal = GetProcAddress(hLib, "getVal"); (FARPROC &)ooGetText = GetProcAddress(hLib, "getText"); (FARPROC &)ooIsWin = GetProcAddress(hLib, "isWin"); (FARPROC &)ooIsInstall = GetProcAddress(hLib, "isInstall"); } //      //hidden - ()   bool __fastcall OOAPI3::connect(const char* file, bool hidden) { // private:factory/scalc -   excel  // // file:///   //hidden:         return ooConect(file, hidden); } //   bool __fastcall OOAPI3::setSheet(short sheet) { return ooSelectSheet(sheet); } //     void __fastcall OOAPI3::setVal(int x, int y, double val) { ooSetVal(x, y, val); } //      void __fastcall OOAPI3::setText(int x, int y, AnsiString val) { //  unicode int iSize = val.WideCharBufSize(); wchar_t *uval = new wchar_t[iSize]; val.WideChar(uval, iSize); ooSetFormula(x, y, uval); delete[] uval; } //     bool __fastcall OOAPI3::setBold(int x, int y) { return ooSetBold(x, y); } // bool __fastcall OOAPI3::setItalic(int x, int y) { return ooSetItalic(x, y); } //    bool __fastcall OOAPI3::setFontColor(int x, int y, int r, int g, int b) { //TODO:     > 0, < 255 return ooSetFontColor(x, y, r, g, b); } //   bool __fastcall OOAPI3::setBgColor(int x, int y, int r, int g, int b) { return ooSetBgColor(x, y, r, g, b); } //   bool __fastcall OOAPI3::setFontSize(int x, int y, short size) { return ooSetFontSize(x, y, size); } //   //1 - , 2 - , 3 -  bool __fastcall OOAPI3::setHoriz(int x, int y, short horiz) { //TODO: -  return ooSetHoriz(x, y, horiz); } //   //, , , , r, g, b bool __fastcall OOAPI3::setBorders(int x, int y, bool l, bool t, bool r, bool d, short rd, short gr, short bl) { return ooSetBorders(x, y, l, t, r, d, rd, gr, bl); } //   //, , ,  bool __fastcall OOAPI3::setBorders(int x, int y, bool l, bool t, bool r, bool d) { //  return ooSetBorders(x, y, l, t, r, d, 0, 0, 0); } //   //   bool __fastcall OOAPI3::setBorders(int x, int y) { return ooSetBorders(x, y, true, true, true, true, 0, 0, 0); } //     bool __fastcall OOAPI3::setColWidth(int col, long mm) { return ooSetColWidth(col, mm); } //   //  A1:C7 bool __fastcall OOAPI3::mergeRange(AnsiString from, AnsiString to) { return ooMergeRange(AnsiString(from+":"+to).c_str()); } // ,    AnsiString __fastcall OOAPI3::getColNameById(int id) { id++; AnsiString abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";//26 AnsiString ret; int x; if(id > 26) { x = (int)id/26; ret = abc.SubString(x, 1); id = id - x*26; } ret += abc.SubString(id, 1); return ret; } //   //   bool __fastcall OOAPI3::mergeRange(int x1, int y1, int x2, int y2) { y1++; y2++; AnsiString from = OOAPI3::getColNameById(x1) + AnsiString(y1); AnsiString to = OOAPI3::getColNameById(x2) + AnsiString(y2); return OOAPI3::mergeRange(from, to); } //   excel   //: file:///C:/.xls bool __fastcall OOAPI3::export2XLS(AnsiString to) { to = ReplaceStr(to, "\\", "/"); to = "file:///"+to; //  unicode int iSize = to.WideCharBufSize(); wchar_t *uval = new wchar_t[iSize]; to.WideChar(uval, iSize); bool r = ooExportToUrl(uval); delete[] uval; return r; } //    double __fastcall OOAPI3::getVal(int x, int y) { return ooGetVal(x, y); } //   AnsiString __fastcall OOAPI3::getText(int x, int y) { return AnsiString(ooGetText(x, y)); } //   :windows/wine bool __fastcall OOAPI3::isWin() { return ooIsWin(); } //   ? bool __fastcall OOAPI3::isInstall() { return ooIsInstall(); } //  ,   __fastcall OOAPI3::~OOAPI3() { ooDisconect(); FreeLibrary(hLib); } 

2.1. An example of a small program for uploading data

An example of using the library. The result of the picture is presented at the beginning of the article.

 OOAPI3 *t = new OOAPI3(); if(!t->isInstall()) return; t->connect("private:factory/scalc"); t->setSheet(0); t->mergeRange(26,1,29,2); t->setVal(1,1, -178.576); t->setText(0, 2, "1"); t->setVal(1,1, 5); t->setVal(0,0, 3.14); t->setBorders(0,0); t->setText(0, 2, "1"); t->setBold(0, 2); t->setFontColor(0, 2, 0, 0, 255); t->setBgColor(0,2,255,255,0); t->setFontSize(0,2,20); t->setItalic(0,2); t->setHoriz(0,2,3);//1-, 2-, 3- t->setColWidth(0, 100); t->setBorders(1,1); t->mergeRange("A1", "C2"); t->export2XLS("C:/export123.xls"); delete t; 

Download a sample program with all the necessary libraries.
In the archive are any to run bilderovskogo exe they can be removed (list of libs in note.txt)

More difficult unloading from real life:


Currently there are problems:
- hiding the office at the time of loading data into it.
- generation of an RDB file under WINE (most likely an incorrect conversion to Unicode in the createProcess function)