Python to Excel

Question

Using DOcplex module an optimization problem is solved.

There are many decision variables in the solution, for the validation of the mathematical model I want to check them.

How can I write the solution (decision variables) to Excel worksheets?

Answer 1

A great option is to use the pandas library, and specifically pandas.DataFrame.to_excel is what you need.

I suggest you use one column per variable index, and one line per non zero variable.

Here is one way of puting the solution into dataframe form. Something like:

variable_name = []
variable_value = []
for x in variables:
   if value(x)>0:
      variable_name.append(x)
      variable_value.append(value(x))

keys = ["variable_name", "variable_value"]
values = [variable_name, variable_value]
df_solution = DataFrame(dict(zip(keys,values)),columns=keys)
df_solution.to_excel("my_solution.xlsx")

Answer 2

I have used openpyxl several times to create or alter excel files. It is quite easy if one is familiar with Python. With this module you can iterate through your variables and write its value to a cell.

Answer 3

There are both pandas and openpyxl recommendations here. In my experience, if you want to go read and write on the cell level, or do more than basic analysis, use Pandas. Use openpyxl If you want to script basic excel functions