Clear excel before export

Question

question

Lambert asked Apr 04 2023 at 12:57 PM Lambert commented Apr 05 2023 at 8:26 PM

Clear excel before export

Hi, when I use the export interface to export a global table, it is not clearing previous contents from the excel file.

Therefore if I export 3 rows but previously there were 5, I still see two olds from the previous export.

What's the best way to clear the excel file before the export?

thank you

Software Version:

FlexSim 23.0.6

excel export

1680612957964.png (45.2 KiB)

Answer 1 · 2023-04-04T16:05:56Z

Kavika F answered Apr 04 2023 at 4:05 PM Lambert commented Apr 05 2023 at 8:25 PM

Hey @Lambert, in the Excel Interface > Custom tab, you can write custom code for importing/exporting.

You can write a small script that deletes the sheet using exceldeletesheet, and when you go to Export, it will recreate the sheet. You could also try another excel command; exceldeleterange - deletes a range from the currently open excel workbook.

1680624086744.png (12.9 KiB)

· 11

Lambert commented · Apr 04 2023 at 4:54 PM

Hi, thanks for getting back.

I cannot do exceldeletesheet("Sheet1"), I think because there is only one sheet in the file.

And when I try exceldeleterange, I get this error:

could you please assist?

thank you

0 ·

1680627234863.png (22.7 KiB)

Kavika F ♦ Lambert commented · Apr 04 2023 at 5:00 PM

Did you try excelsetsheet() before deleting the range? I think you may need to set the sheet first.

0 ·

Lambert Kavika F ♦ commented · Apr 04 2023 at 6:07 PM

yes, same error :(

0 ·

1680631655422.png (12.7 KiB)

Branden Gunn commented · Apr 04 2023 at 7:15 PM

I was able to reproduce this and make a workaround.

excelsetsheet("Sheet1"); // set the active sheet
 
// NOTE: This doesn't error, but it doesn't have the desired result of deleting data.
excelsetrange("MyRange",1,1,10,20); // creates a named range
exceldeleterange("MyRange"); // deletes the named range (NOTE: this does not delete the data!)
 
// HACK: It's annoying to delete the data within the range (you'd need to loop or something), so instead, delete the sheet, using a placeholder sheet to prevent errors.
excelcreatesheet("SheetTemp"); // create a second temporary sheet
exceldeletesheet("Sheet1"); // delete the original sheet
excelcreatesheet("Sheet1"); // re-create the first sheet
exceldeletesheet("SheetTemp"); // delete the temporary sheet
 
excelclose(1); // save and close the workbook

0 ·

Lambert Branden Gunn commented · Apr 04 2023 at 8:27 PM

unfortunately when I run this in a script, FlexSim hangs and I need to close it via crtl+alt+supr

excelopen("export.xlsx");
excelcreatesheet("SheetTemp"); // create a second temporary sheet
exceldeletesheet("Sheet1"); // delete the original sheet
excelcreatesheet("Sheet1"); // re-create the first sheet
exceldeletesheet("SheetTemp"); // delete the temporary sheet
excelclose(1); // save and close the workbook

I cannot make it work :(

0 ·

Branden Gunn Lambert commented · Apr 05 2023 at 2:17 PM

@Lambert

It looks like you are running it directly via the script window? I did my testing with the "Custom Export" part of the Excel Import/Export interface. I don't know if the script window is different. I also did not copy/paste the built in part of the example code, but am including it below.

1. Add The example code from the Plus button

2. Edit the example code to include the changes

3. Then you can use the "Execute" button in the Custom tab to run the code.

Please note that the example code only clears the Sheet, it does not do your data export. You still need to run the regular excel export to write your data. You can do the export from the custom script as well, but that involves knowledge of what you are exporting. You could also try adding the excelmultitableexport() command which will run the programmed exports from the Export tab.

0 ·

2023-04-05-10-08-50-excel-interface.png (11.9 KiB)

2023-04-05-10-10-11-custom-code.png (38.8 KiB)

Show more comments

Answer 2 · 2023-04-05T06:30:26Z

Joerg Vogel answered Apr 05 2023 at 6:30 AM Lambert commented Apr 05 2023 at 8:26 PM

And if you enlarge your table you import into your excel sheet to contain more rows? You can resize a FlexSim table easily.

· 2

Branden Gunn commented · Apr 05 2023 at 2:24 PM

Depending on the complexity of the data and the excel file, that could be tricky to write. I guess you could try to query excel for how many rows are already populated in the sheet, and then expand the table to that many rows before export, but I'm not sure how easy the first part is.

To me this is generally a bug, or at least surprising behavior. If you export data to a sheet in excel, I would expect it to overwrite the whole sheet. I'm thinking of some tables I have that have 7000-10000 rows, depending on how the simulation happened. If I were to write out the results of a run with 7000 rows on an excel sheet that had 10000 already existing from a different run (for debugging or inspection of results), then the results would be erroneous or confusing when trying to debug, which is likely what happened to Lambert here.

I can see some use cases where you don't want it to clear other parts of the sheet, but I would expect those to be in the minority. The user manual should at least tell you that this could happen (writing fewer rows than before will leave the old data there).

0 ·

Lambert Branden Gunn commented · Apr 05 2023 at 8:26 PM

thank you both

0 ·

question