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.
_block
_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 << internal_text_output_stream.new()
_for a_record _over records.fast_elements()
_loop
# using OLE, set your cell range...
data_list {a_record.attr1,a_record.attr2,a_record.attr3}
temp_stream.reset()
temp_stream.write_list_with_separator(data_list,delim_char)
a_frame.clipboard_text << temp_stream.string
# using OLE, do a "Paste" into the current cell
_endloop
_endblock
$
- 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
Posts
1 comment:
[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 << l_workbooks.open(l_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.]
Post a Comment