GAMS World Forum

Hi everyone

I would like to export data from my GAMS-Model to excel. In the forum I have found the following solution viewtopic.php?f=3&t=11719. But the problem with the mentioned solution is, that it creates an excel file with way too much content. In fact it exports basically everything which in my case is an excel file with around 100 MB and unfortunately my computer can not handle this excel file, I guess its just too big.

So i found another solution which is the Pandas Excel Writer. This seems to be the perfect solution. I managed to export the the different variables to various excel files. But now i have a new problem with the way the data is displayed in excel. As i would like to evaluate the data with pivot tables i need the index of the multidementional variables to be in every single cell - but the Panda Excel Writer combines the cells of the multidimentional variables. And when I disconnect the cells in excel, the value of the cell remains in just one cell which is also not optimal (espacially because the whole excel sheet contains several 100k rows).

I guess the solution could be the option "toExcelArguments" but I'm just not able to get it run. I'm sorry, I'm still a total beginner in programming but i would greatly appreciate any help. It takes me hours and sometimes even days to get the stuff done.

Thanks in advanced

Best regards, Marco

Hi Marco,

The PandasExcelWriter is based on the pandas.DataFrame class and its I/O API method to_excel. pandas.to_excel per default writes merged cells but allows to change this behavior via the option merge_cells. You can pass merge_cells: False via toExcelArguments as follows:
toExcelArguments is a dictionary containing key and value pairs that will directly be passed to the pandas.to_excel method.

Note that with GAMS 40 we dropped the support for writing variables and equations with the PandasExcelWriter. Instead, you need to use the Projection agent to turn variables and equations into parameters. You can either use asParameter: True with the Projection agent to turn a variable/equation into a parameter with an extra index that contains the suffixes ("level", "marginal", etc.) or specify a specific suffix via the name option, e.g. name: x.l(index1,index2) to only keep the level of variable x with index space (index1,index2). I attached a modified version of your code that disables merged cells and also uses the Projection agent. For variable x, I used asParameter: True and for the remaining variables I only kept the level. Be aware that the PandasExcelWriter attempts to overlay data when the file exists already which may cause problems when the file contains merged cells, so you may need to delete the Excel files first before writing to them again.

Hope that helps.

Best,
Aileen

Hi Aileen

This is amazing, thank you so much for your help=)

Best,
Marco

Hello,

Indeed, your advice was also a great help to me. Thank you very much for sharing.

With the GAMS 46 release the PandasExcelWriter is deprecated and it is recommended to use the new ExcelWriter instead. The ExcelWriter has an option mergedCells that allows to control the writing of merged cells (per default no merged cells will be written). Just as the PandasExcelWriter, the ExcelWriter appends to an existing Excel file and attempts to overlay data if a sheet exists which can cause problems in combination with merged cells. The ExcelWriter has an option clearSheet that helps to get around this issue.