office automation?? lazarus / 5-Lazarus Extra Components ---------------------------------------------------------------------- thales · 2009-08-10 17:25 hi folks. i'm realatively new to lazarus... currently trying to migrate from delphi, and very important thing that i missed here, is the possibility for automating office procedures... i found some topics here in the forum talking about it, but nothing much helpful, so, i kinda have some doubts about what i previously read here... someone said that automating ms office is not possible because lazarus is supposed to work as multi-platform, and since ms office is a windows thing, doesnt fit. ...anyway, we have open office... does anyone know any way to automate that? like, the same way we'd do it in delphi using office's vba interface's commands, it was called 'ole automation', manipulating a variant object? i understand that from delphi 5 on, we have those components that make binding a little bit easier... but since i used delphi 3, (and back then, there were no such components...), i'd do it via code... (i intend to do it in lazarus via code...) which teorically should be pretty much the same...  unfortunately, it's not... could someone please show me some material about how to do that in lazarus? is there something i'd have to integrate to the ide? any help? THALES VAZ ---------------------------------------------------------------------- theo · 2009-08-10 17:40 http://wiki.freepascal.org/Office_Automation ---------------------------------------------------------------------- thales · 2009-08-10 18:48 yeah, i've read that... more than once, actually... but it doesnt explain much, right? also, what is a uno bridge? where do i get the automation servers this wiki mentions? should i install any package? ---------------------------------------------------------------------- theo · 2009-08-10 19:13 I'm not an expert. There is a UNO bridge project here: http://sourceforge.net/projects/uno-pas-bridge/ but it doesn't look very active. If you need to produce or read OpenOffice documents, you could also read or write them via the Freepascal XML and Zip libraries. OO Documents are zipped XML files. ---------------------------------------------------------------------- Phil · 2009-08-10 19:31 Quote from: thales on August 10, 2009, 08:48:52 pmyeah, i've read that... more than once, actually... but it doesnt explain much, right? also, what is a uno bridge? where do i get the automation servers this wiki mentions? should i install any package? MS Office apps and OpenOffice have Automation servers built in so you don't have to install anything else. Try the example code. If you're only working on Windows, that should be all you need, assuming FPC supports COM better than it used to. A "bridge" in this context would be language bindings to the OpenOffice C++ classes (UNO). With Windows, Automation serves as that bridge and is great because it's pretty much language independent and it's object oriented. Thanks. -Phil ---------------------------------------------------------------------- thales · 2009-08-10 20:12 hum... so theorically it should accept the same sintaxis that work with delphi, right? but it doesnt... for example: (creating an excel file and showing it...) arq:=createoleobject('excel.application'); arq.visible:=true; raises an exception on the second line... and the cool thing is, there's nothing inside the error message! just the OK button... ¬¬ that's why i thought of lazarus' interpretation of the automation server... any tips? ---------------------------------------------------------------------- Phil · 2009-08-10 20:33 Quote from: thales on August 10, 2009, 10:12:50 pmhum... so theorically it should accept the same sintaxis that work with delphi, right? but it doesnt... for example: (creating an excel file and showing it...) arq:=createoleobject('excel.application'); arq.visible:=true; raises an exception on the second line... and the cool thing is, there's nothing inside the error message! just the OK button... ¬¬ that's why i thought of lazarus' interpretation of the automation server... any tips? Are you using the latest FPC? 2.2.4 or 2.3.1? As indicated on the Web site, Automation doesn't work with 2.2.2. "sintaxis" - taxis for sin? Thanks. -Phil ---------------------------------------------------------------------- thales · 2009-08-10 20:45 yeah, turns out, i'm using the 2.2.2 version... ¬¬ how can i update it? it's kinda weird, since i am using the latest lazarus release... ---------------------------------------------------------------------- Phil · 2009-08-10 20:54 Quote from: thales on August 10, 2009, 10:45:46 pmyeah, turns out, i'm using the 2.2.2 version... ¬¬ how can i update it? it's kinda weird, since i am using the latest lazarus release... See the Daily Snapshots link. -Phil ---------------------------------------------------------------------- thales · 2009-08-11 03:16 sorry man, i'm kind of a newbie here... what about the snapshots? it says it's unsafe to use them... (untested software...) should i update the compiler? ---------------------------------------------------------------------- Leledumbo · 2009-08-11 08:01 Quotewhat about the snapshots? it says it's unsafe to use them... (untested software...) should i update the compiler?It's entirely up to you. Some freaks (like me ) love to use it because you can get more features (+new bugs, of course). And bugs usually get fixed in days after you report (indirect contribution), so why you have to be afraid? ---------------------------------------------------------------------- theo · 2009-08-11 08:15 When trying OLE stuff, it's a good idea to have the latest FPC Version. Try "Lazarus + fpc 2.2.5" http://www.hu.freepascal.org/lazarus/ ---------------------------------------------------------------------- thales · 2009-08-11 11:35 Quote from: Leledumbo on August 11, 2009, 10:01:53 amIt's entirely up to you. Some freaks (like me ) love to use it because you can get more features (+new bugs, of course). And bugs usually get fixed in days after you report (indirect contribution), so why you have to be afraid? hum... like i said, i'm new here, and i never used the bugtracker... (which i probably will...  ) Quote from: theo on August 11, 2009, 10:15:58 amWhen trying OLE stuff, it's a good idea to have the latest FPC Version. Try "Lazarus + fpc 2.2.5" http://www.hu.freepascal.org/lazarus/ downloading it! but still, i find it weird because, see... it accepts this: arq:=createoleobject('excel.application'); which i think is already automation, right? ---------------------------------------------------------------------- theo · 2009-08-11 12:08 Quote from: thales on August 11, 2009, 01:35:02 pmbut still, i find it weird because, see... it accepts this: arq:=createoleobject('excel.application'); which i think is already automation, right?  "accept" doesn't mean that it is properly working. ;-) ---------------------------------------------------------------------- thales · 2009-08-11 13:41 hum... anyway... i installed lazarus + fpc 2.2.5 and i got the same results... since lazarus is not windows-only, maybe we have to import type libraries for windows apps manually? in delphi it was 'automatic', but delphi is windows-only... does anyone know how to do that? any tips? ---------------------------------------------------------------------- theo · 2009-08-11 14:30 Import them with the delphi tool or ask a friend to do that for you ;-) Or read here: http://forum.lazarus.freepascal.org/index.php?topic=5471.0 ---------------------------------------------------------------------- thales · 2009-08-11 17:01 so, is there any avaliable way to automate office procedures today in lazarus? ---------------------------------------------------------------------- Phil · 2009-08-11 17:11 Quote from: theo on August 11, 2009, 04:30:44 pmImport them with the delphi tool or ask a friend to do that for you ;-) Or read here: http://forum.lazarus.freepascal.org/index.php?topic=5471.0 CreateOleObject uses late binding so no type library is required as with early binding. Thales: I would assume for now that Automation does not work with FPC and move on. If you just need to display a document or spreadsheet, you can do that just by shelling to it. Thanks. -Phil ---------------------------------------------------------------------- thales · 2009-08-11 18:20 Quote from: Phil on August 11, 2009, 07:11:50 pmI would assume for now that Automation does not work with FPC and move on. If you just need to display a document or spreadsheet, you can do that just by shelling to it. Thanks. -Phil nice... and, no, i dont want to display it... i just found more convenient to automate an excel document with reports, charts, etc. instead of generating lazreports... thanks for the help! THALES VAZ ---------------------------------------------------------------------- Phil · 2009-08-11 18:28 Quote from: thales on August 11, 2009, 08:20:40 pmnice... and, no, i dont want to display it... i just found more convenient to automate an excel document with reports, charts, etc. instead of generating lazreports... thanks for the help! THALES VAZ Again, I know this has been repeated many times, but doing Automation would only be for Windows and never for OS X or Linux. If you're only doing Windows, why not stay with Delphi, where that works great? Kind of the whole point of Lazarus and Free Pascal is cross-platform. Using them only on Windows is like buying a bicycle with fat tires and aggressive tread, but oh, no, I'll never leave the pavement... Thanks. -Phil ---------------------------------------------------------------------- thales · 2009-08-11 20:09 Quote from: Phil on August 11, 2009, 08:28:16 pmAgain, I know this has been repeated many times, but doing Automation would only be for Windows and never for OS X or Linux. If you're only doing Windows, why not stay with Delphi, where that works great? Kind of the whole point of Lazarus and Free Pascal is cross-platform. Using them only on Windows is like buying a bicycle with fat tires and aggressive tread, but oh, no, I'll never leave the pavement... good question! actually, this is because people have to pay for delphi... since lazarus is free, well... sounds weird, but as i dont mind sharing ideas and code with people, i think that's ok to use o free ide to make commercial software, right? ---------------------------------------------------------------------- theo · 2009-08-11 20:25 The Turbos are free, but I don't know how much support for your purpose they have: https://downloads.embarcadero.com/free/delphi ---------------------------------------------------------------------- thales · 2009-08-12 11:18 i'm not sure about that, because i downloaded turbo delphi 2006 and it is a 300MB file... and you also have to download and install .net framework and .net framework sdk (there are also other stuff...) which is kinda heavy as hell!!! regards ---------------------------------------------------------------------- felipemdc · 2009-08-12 13:14 What exactly do you need to do? To read, modify and write spreadsheet files you can use FPSpreadsheet (substitutes Excel or OO Calc automation for most uses): http://wiki.lazarus.freepascal.org/FPSpreadsheet To read, modify and write vectorial graphics you can use FPVectorial (substitutes OpenOffice Draw automation for most uses): http://wiki.lazarus.freepascal.org/fpvectorial So the only thing missing is read/modify/write text documents. For this I think there is a RTF component somewhere which could substitute Word automation in some cases. ---------------------------------------------------------------------- thales · 2009-08-12 14:22 Quote from: sekel on August 12, 2009, 03:14:52 pmWhat exactly do you need to do? automate office procedures the same way as in delphi, where you'd use automation servers provided with msoffice... but, i'll try fpspreadsheet... already downloaded the chm file, but i'm not sure how to use that component... thanks THALES VAZ ---------------------------------------------------------------------- felipemdc · 2009-08-13 00:09 Yes, but which specific tasks do you want to perform? "automating" is very unspecific, it could mean any of the 1000s of tasks which can be acomplished with office ---------------------------------------------------------------------- thales · 2009-08-13 11:18 so, what i wanted to do is to get a open dataset (some query result...) and put it into excel... and then build the report there (automatically) where it should place a bmp with the logo of something, a title (in the first row and also making that first row the header for all pages), build charts, specify page setup, etc... so, lots of automatable office stuff... would you like me to put here some examples? THALES VAZ ---------------------------------------------------------------------- felipemdc · 2009-08-16 02:56 A lot is missing from fpspreadsheet to be able to do that ... loading from a TDataset, report generation, charts, etc. ---------------------------------------------------------------------- gakusei · 2009-11-07 09:14 thales, sure you can automate office apps using FPC, but the process wil be more like raw C code, not Delphi-like. If you want more info on OLE stuff - PM me or dig in MSDN ---------------------------------------------------------------------- gakusei · 2009-11-07 14:43 Here's little and dirty example of OLE automation done in raw WinAPI Code: [Select]program oletest; {$APPTYPE CONSOLE} uses   SysUtils, Windows, ActiveX, Ole2; var   unkXl: IUnknown;   dispXl: IDispatch;   clsidXl: TCLSID;   did: TDispID;   name: POleStr;   dps: DISPPARAMS;   didNamed: TDispID;   param: VARIANTARG;   hres: HRESULT; begin   // initialize COM   CoInitialize(nil);   // get CLSID of Excel   CLSIDFromProgID('Excel.Application', clsidXl);   // create Excel instance   CoCreateInstance(clsidXl, nil, CLSCTX_LOCAL_SERVER, IID_IUnknown, unkXl);   // get IDispatch interface of received object   unkXl.QueryInterface(IID_IDispatch, dispXl);   // DO SOME STUFF WITH EXCEL HERE   // for example, we'll just show it ;)   name:= 'Visible';   param.vt:= VT_BOOL;   param.vbool:= true;   dps.cArgs:= 1;   dps.rgvarg:= @param;   didNamed:= DISPID_PROPERTYPUT;   dps.cNamedArgs:= 1;   dps.rgdispidNamedArgs:= @didNamed;   dispXl.GetIDsOfNames(GUID_NULL, @name, 1, LOCALE_SYSTEM_DEFAULT, @did);   hres:= dispXl.Invoke(did, GUID_NULL, LOCALE_SYSTEM_DEFAULT, DISPATCH_PROPERTYPUT,     dps, nil, nil, nil);   MessageBox(0, 'Hit OK to end the app', 'Test', MB_OK or MB_ICONINFORMATION);   // release unused objects   dispXl.Release;   unkXl.Release;   // deinitialize COM   CoUninitialize; end. ---------------------------------------------------------------------- jmLandsvik · 2011-05-11 14:15 Hi all... What is the status of this today ??? I too found this page:  http://wiki.lazarus.freepascal.org/Office_Automation#Using_the_Free_Pa scal_Spreadsheet_Library And I would agree with thales, not too userfriendly.  downloaded ExcelInterface V0.4 from this link (down the page) http://tcoq.free.fr/composants.html and after fixing some hardcoded filenames, it ran, AND worked... THEN, I took the example code below:(Cut/paste from the OA page) Code: [Select] // Initializing the common excel workbook:  ExcelApp := TExcelApplication.Create(nil);  ExcelApp.Active := True;  ExcelApp.Visible := True;  ExcelWbs := ExcelApp.WorkBooks;  ExcelWb := ExcelWbs.Open( TestFileName)  ExcelSheets := ExcelWb.Sheets;  ExcelSheet1 := ExcelSheets.Sheet('Sheet1'); And it opens an excel workbook for me... Then, preceeding with... (again, cut/paste from the OA page) Code: [Select]// adding a value  aCell := aSheet.Cells(1, 1);  aCell.Value := 10; And we have ourself an instant crash... "Project excelTEST.exe raised exception class 'External: SIGSEGV'." (Running:  MS Office 2010  Lazarus 0.9.31 /) I have also tryed to install fpspreadsheet, but after compiling/installing, I can't see any difference, except for under [Additional], where I have TsWorksheetGrid. (perhaps it did not install right?) tnx Jan Magne ---------------------------------------------------------------------- Marc · 2011-05-12 09:18 Quote from: jmLandsvik on May 11, 2011, 04:15:20 pmCode: [Select]// adding a value  aCell := aSheet.Cells(1, 1);  aCell.Value := 10; And we have ourself an instant crash... "Project excelTEST.exe raised exception class 'External: SIGSEGV'." I don't know the code, but the first thing I would look is if aSheet is not nil and second if it returned aCell. (assuming aSheet and aCell are objects and not variants) ---------------------------------------------------------------------- marcov · 2011-05-12 13:25 Also I would use 2.5.1 with COM as much as possible. Many COM related fixes couldn't be merged back. ---------------------------------------------------------------------- mica · 2011-05-12 16:03 you can take a look at this componenet: http://avemey.com/zexmlss/index.php