ValueError: Value must be one of ['field', 'data', 'selection'}

Question

I am using excel file which has multiple sheets which contains charts and graphs and wanna add new sheet but i am getting ValueError: Value must be one of ['field', 'data', 'selection'}

I am using below code taking reference from @Stefano Fedele solution for adding new sheet in already existing file using pandas without loosing data.

import pandas as pd
import numpy as np
from openpyxl import load_workbook

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

book = load_workbook(path)
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

df3.to_excel(writer, sheet_name = 'x3')
df4.to_excel(writer, sheet_name = 'x4')
writer.close()

Any solution for how to add new sheets in a already existing Excel containing charts and graphs etc?

Which line is it throwing that error? – Ashyam Feb 02 '23 at 07:42 — Ashyam, Feb 02 '23 at 07:42
Use openpyxl directly. – Charlie Clark Feb 02 '23 at 09:21 — Charlie Clark, Feb 02 '23 at 09:21

score 0 · Answer 1 · answered Feb 02 '23 at 10:55

Try using openpyxl alone and not mix it up with pandas.ExcelWriter

import pandas as pd
import numpy as np
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

book = load_workbook(path)

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

sheet = book.create_sheet("x8")
book.active=sheet
for r in dataframe_to_rows(df3, index=False, header=True):
    sheet.append(r)

sheet = book.create_sheet("x9")
book.active=sheet
for r in dataframe_to_rows(df4, index=False, header=True):
    sheet.append(r)


book.save(path)

ValueError: Value must be one of ['field', 'data', 'selection'}

1 Answers1