2

I need to load an existing Excel sheet, write a pandas dataframe to a named range and subsequently save the sheet under a new name. How am I supposed to do that?

import openpyxl
import pandas.util.testing as tm
from openpyxl import Workbook, load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

# Load Excel sheet, which contains the named range ('TestRange') in range A1:C2
wb = load_workbook('template.xlsx')
ws = wb['Tab1']

# Create dummy 2x3 dataframe
tm.N, tm.K = 2, 3
df = tm.makeDataFrame()

# Write dateframe to 'TestRange'
# That's the part in question!

# Save sheet under new name
wb.save('df1.xlsx')
Andi
  • 3,196
  • 2
  • 24
  • 44
  • You can do this easily using the API provided by openpyxl. – Charlie Clark Dec 21 '19 at 10:46
  • @CharlieClark Apparently, you know the answer. I already read the docs at https://openpyxl.readthedocs.io/en/stable/pandas.html. However, I am still struggling. Would you mind to post the solution? – Andi Dec 23 '19 at 10:27
  • Duplicate of https://stackoverflow.com/questions/51531715/pandas-dataframe-to-excel-with-defined-name-range – BSalita Jan 02 '20 at 17:06
  • @BSalita I don't consider it to be a duplicate question. My Excel sheet already contains a named range. I simply want to write the dataframe values into that named range. – Andi Jan 09 '20 at 07:58

0 Answers0