Monday, May 14, 2007

Magik OLE Excel Reporting Performance Enhancements

I recently received a question about how to speed up Magik reporting to Excel using the OLE functionality. I you have ever used OLE from Magik to access other APIs you will know that it is a powerful but not overly fast way of connecting to 3rd party applications.

The questioner had indicated that they were writing data from Magik to Excel one cell at a time and then formatting each cell individually, too. While this is a logical way of doing a report it was taking a very long time to create a typical customer report.

My suggestions to improve data transfer and formatting performance were:

  • If your report is basically a row report, then you can copy one row at a time onto the Windows text clipboard in Magik. Then you can use an OLE call on Excel to paste the copied text into a range of cells. Be sure that the copied text has a delimiter that Excel can parse on. That way you can pass a large amount of data from Magik to Excel with a single OLE call.

_constant delim_char << tab_char
_local a_frame << smallworld_product.applications.an_element().top_frame

# using a text output stream will let you cleanly create
# a delimited string later on.
_local temp_stream <<

_for a_record _over records.fast_elements()
# using OLE, set your cell range...

data_list {a_record.attr1,a_record.attr2,a_record.attr3}

a_frame.clipboard_text << temp_stream.string

# using OLE, do a "Paste" into the current cell
  • As for formatting: is there similar formatting for a row or column of cells? If there is, then keep track of how many rows or columns you have as you are copying/pasting the data into Excel. Then, once you have finished getting the data into Excel, format a range of columns or a range of rows or a range of cells that require common formatting. Again, that way you do the formatting with a minimum number of OLE calls
  • Have you looked at the Smallworld Explorer “Export to Excel” functionality? I think that core code (4.0 SWAF) might make use of an Excel template.
    • see collection_export_engine.export_to_excel

1 comment:

Alfred Sawatzky said...

[Thanks to Andrew Doak. I received a note from him yesterday with another approach to improve performance...]

To add to your comments about the speed of using Excel from Smallworld via OLE, one technique we have found very useful is to make use of csv files.

Create a comma separated text file and then open it into excel. (This is especially good for creating multiple sheets)

[Some of the code classes/methods that you could use include...]
(l_excel an OLE client handle on Excel)
l_workbooks << l_excel.workbooks()
l_workbook << l_workbooks.add()
l_csv_file <<

Of course you still have the formatting issues, but you can very quickly create/open a csv file in excel (and maybe use a "Save As" for a single file after column formatting)

[The clipboard text approach I mentioned in the original article will likely have a maximum size limit. For really large reports, using the CSV approach mentioned by Andrew might be a better-performing solution than the clipboard text.]