Print a dataframe to a specific column/row location like (1,2) using xlwings

Question

Trying to find out how to print to a specific column/row similar to how pd.to_excel(startcol = 1, startrow = 1) works. I have to do this in an open excel workbook, and found the library xlwings. I'm currently using openpyxl, how would I do this in xlwings? I read the documentation printing to specific cells like A1, but not by specifying columns/rows.

#Write to Excel

book = load_workbook('Test.xlsx')
writer = pd.ExcelWriter('Test.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)


def addtoexcel(df, row):
  i = 0
  df[["val1", "val2"]] = df[["val1", "val2"]].apply(pd.to_numeric)
  line = int(df.loc[i][1])
  for i in range(1, line+1):
     if line ==i:
        df = df.T
        df.to_excel(writer, "test", index = False, header = False, startcol = line+2, startrow = row)

How can I print in xlwings by specifying column/row like (1,1)?

Steven Van Dorpe Steven Van Dorpe · Accepted Answer · 2019-08-30T09:27:39

You can easily print a pandas dataframe to excel using xlwings. The range object takes a row and a column number as arguments (or just a cell reference as a string). Consider the following code:

import xlwings as xw
import pandas as pd

row = 1
column = 2
path = 'your/path/file.xlsx'

df = pd.DataFrame({'A' : [5,5,5],
                  'B' : [6,6,6]})

wb = xw.Book(path)
sht = wb.sheets["Sheet1"]

sht.range(row, column).value = df

You can also add options to include index/header:

sht.range(row, column).options(index=False, header=False).value = df

Print a dataframe to a specific column/row location like (1,2) using xlwings

1 Answers