question

Hannah S avatar image
0 Likes"
Hannah S asked Ryan Clark commented

How to increase Excel export speed?

During an experiment I write values to ~30 different tables and at the end of the experiment I export all of the tables to Excel. Initially when I ran the export (8 months ago) the export would take 5-10 minutes. Over time the export time has slowly increased to where it now takes an hour to export the tables. Is there a way to speed up the Excel export process? Why was it running so quickly before and so slowly now? This problem is not unique to my computer. My coworker faces the same issue as well. It's also not an issue with the model. The version of the model that ran quickly before runs slowly now.

FlexSim 20.2.3
experimenterexportexcel export
· 1
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

1 Answer

Jordan Johnson avatar image
1 Like"
Jordan Johnson answered Jordan Johnson commented

As far as why it is slowing down, it's hard to say without a model. Are the file sizes of the Excel Workbooks bigger in later versions? Did the slowdown happen when you switched versions of FlexSim? If there is a bug here, it would be good for us to fix.

In the meantime, you need a way to export faster. Statistics Collectors and Calculated Tables are optimized for Excel Export, and can export data in seconds instead of minutes or hours.

So the easiest way to export all your data fast would be to make one Calculated Table per Global Table, with a query like this:

  1. SELECT * FROM GlobalTable1

You'll probably need to click the "Enable Direct Editing" button on the Calculations tab of the Calculated Table, to type in a query like that.

Then, you'll need to change your Excel Export object to export the global tables.

Finally, before exporting, it would probably be good to make sure the Calculated Tables are up-to-date. Run this script to re-run all queries in all Calculated Tables, before you export:

  1. treenode calcTablesFolder = Model.find("Tools/CalculatedTables");
  2. var calcTables = calcTablesFolder.subnodes;
  3. for (int i = 1; i <= calcTables.length; i++) {
  4. treenode calcTable = calcTables[i];
  5. function_s(calcTable, "update", 1);
  6. }

Then click the Export button. Here's an example model:

FastExportDemo.fsm


fastexportdemo.fsm (190.3 KiB)
· 2
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.