CODEWITHSUNDEEP
pythonpython-3.xpandasxlwings
mariancatholic
mariancatholic

Reputation: 91

From pandas dataframe to excel with xlwings?

I have a pandas dataframe where I did a 24x 12 matrix with colors to be able to represent my data better.

enter image description here

What I want to do is put this in excel with xlwings. But I don't know how. I have already used xlwings with matplotlib and other graphical tools but I am stuck with this one.

import pandas as pd
import xlwings as xw

wb = xw.Book()

Ideas?

Upvotes: 2

Views: 19012

Answers (1)

mouwsy
mouwsy

Reputation: 1933

You can paste the dataframe to xlwings to a specified range. See the following test example.

import xlwings as xw
import pandas as pd

df = pd.DataFrame([pd._testing.rands_array(5, 3),
                  pd._testing.rands_array(5, 3),
                  pd._testing.rands_array(5, 3),
                  ])

wb = xw.Book("test.xlsx")
ws = wb.sheets["Sheet1"]

ws["A1"].options(pd.DataFrame, header=1, index=True, expand='table').value = df

# If formatting of column names and index is needed as xlsxwriter does it, the following lines will do it.
ws["A1"].expand("right").api.Font.Bold = True
ws["A1"].expand("down").api.Font.Bold = True
ws["A1"].expand("right").api.Borders.Weight = 2
ws["A1"].expand("down").api.Borders.Weight = 2

wb.save(filename)
app.quit()

Upvotes: 5

Related Questions