3

I'm using Delphi 7 and I'd like to export the contents of a list from my program to OpenOffice Calc using automation, instead of using files.

The task is simple: create new document, iterate through rows/columns and change cell data.

I've found some code but it's not complete, and I was hoping someone has some example code ready to accomplish this very simple task. It could save me a few hours of trying.

Thanks in advance!

Edit: I'd like to automate OpenOffice Calc to achieve what I wrote above. Thanks!

Warren P
  • 65,725
  • 40
  • 181
  • 316
Steve
  • 2,510
  • 4
  • 34
  • 53
  • OpenOffice Calc supports importing data in several formats (HTML, tab delimited, CSV, etc.). What exactly is your question - how to create the import data file from Delphi, or how to import that data into Calc, or how to automate Calc and programmatically create a spreadsheet? – Ken White Apr 20 '11 at 17:07
  • How to automate Calc and programmatically create a spreasheet – Steve Apr 20 '11 at 17:31
  • @steve the problem with the question is that you haven't told us what you want. – David Heffernan Apr 20 '11 at 18:40
  • There steve, I helped you ask your question. Hope you like the way I made the question explicit at the top instead of at the bottom. – Warren P Apr 20 '11 at 20:59
  • 1
    Be advised that OOo 3.3 COM automation is totally broken, use OOo 3.2 instead. See http://openoffice.org/bugzilla/show_bug.cgi?id=117010 – The_Fox Apr 21 '11 at 08:33
  • @The_Fox: And if it breaks once, it will break again. Whereas opening XLS files or CSV files from disk, is unlikely to break. :-) – Warren P Apr 26 '11 at 18:49

2 Answers2

2

The easiest solution is to write CSV file output, and open that in OpenOffice.

There are also libraries to write .XLS files which both OpenOffice Calc and Excel can read. CSV is so simple, I wonder that you need an example. Create a TStringList, and add strings to it, in comma separated format. Save to file.

The so called "programmatic" method involves OLE automation.

uses
  OleAuto;

var
 mgr,calc,sheets,sheet1,dt,args:Variant;
begin
   args = VarArrayCreate(...);    
   mgr := CreateOleObject('com.sun.star.ServiceManager');
   dt := mgr.createInstance('com.sun.star.frame.Desktop')
   calc = dt.loadComponentFromURL('private:factory/scalc', '_blank', 0, args)
   sheets = calc.getSheets()
   sheet1 = sheets.getByIndex(0)
   ...
Warren P
  • 65,725
  • 40
  • 181
  • 316
  • Thanks but I'd just like to export it without saving. My program does the same with Excel and in order to be consistent I would like to do the same with OO. Whether the user saves the sheet or not - that's his/her choice. – Steve Apr 20 '11 at 17:30
  • That's a lot more work. For what? Create a temporary folder, launch the program, later on, delete the temporary file. I am not aware of OO support for OLE Automation, I know how to do this with MS Excel, but not with OOCalc. – Warren P Apr 20 '11 at 18:16
2

Open Office supports Automation

see: http://udk.openoffice.org/common/man/tutorial/office_automation.html

Open Office info for Delphi can be found at:
http://development.openoffice.org/#OLE

The site ooomacros.org seems to be down, luckily the wayback machine still has a copy:
http://replay.web.archive.org/20090608051118/http://www.ooomacros.org/dev.php

Good luck.

Johan
  • 74,508
  • 24
  • 191
  • 319