ENH: Drop list menu lost when reading xlsx files from pandas

[TomHsiung]

Feature Type

• Adding new functionality to pandas

• Changing existing functionality in pandas

• Removing existing functionality in pandas

Problem Description

import pandas as pd
import numpy as np
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl import load_workbook

Create a table by pandas

month = [7, 7, 7]
date = [27, 27, 27]
amount = [1.2, 1.99, 60.8]
kind = ['Meat', 'Meat', 'Meat']
quarter = ['3', '3', '3']
df = pd.DataFrame({
'Month': month,
'Day': date,
'Cost': amount,
'Category': kind,
'Quarter': quarter
})
df.info()
print(df.head(10))
print(len(df))
df.to_excel('~/Downloads/food_1.xlsx', index = False) #save manipulated table to a Excel file

Open the table by openpyxl and add modifications (drop-down list menu)

wb = load_workbook('/Users/username/Downloads/food_1.xlsx')
ws = wb.active
dv1 = DataValidation(type = "list", formula1 = '"Care, Computer, Convenience, Fruit, Housework, Meat, Others, Restaurant, Veg"', allow_blank = True)
dv2 = DataValidation(type = "list", formula1 = '"1, 2, 3, 4"', allow_blank = True)
ws.add_data_validation(dv1)
ws.add_data_validation(dv2)
dv1.add('d2:d51')
dv2.add('e2:e51')
wb.save('/Users/username/Downloads/food_2.xlsx')

I write data into a new xlsx table via pandas. Later, I add drop list menu function by openpyxl. But, if I want to add more rows later by pandas again, like this,

import pandas as pd
df = pd.read_excel('~/Downloads/Predict.xlsx')

The pre-defined drop list menu function is lost, after the edited xlsx file is saved by pandas.

Feature Description

Maybe it is a reasonable function to add pandas's self features to support drop-down list menu.

Alternative Solutions

Or, maybe it is reasonable to enable pandas's support for the drop-down list menu function of openpyxl

Additional Context

Thanks!

[rhshadrach]

Thanks for the request. I do not think pandas can support Excel features like this in maintainable manner. I recommend having a function to add the drop-down list after writing to the Excel file if it needs to happen multiple times.

[TomHsiung]

Thank you for your update, pal. Yep, there are some methods to achieve the goal. I was thinking of a more native way, using only pandas to do this. Anyway, many thanks for your clarification.

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

Load existing excel file

wb = load_workbook('/Users/username/food_1Aug2024.xlsx')
ws = wb.active

Add new rows via pandas

new_rows = pd.DataFrame({
'Month': [8, 8, 8, 8, 8],
'Day': [7, 7, 7, 7, 7],
'Cost': [20.8, 18.99, 3.99, 35.48, 2.98],
'Category': ['Care', 'Meat', 'Veg', 'Meat', 'Veg'],
'Quarter': [3, 3, 3, 3, 3],
})

Convert the DataFrame to rows of data

for r in dataframe_to_rows(new_rows, index = False, header = False):
ws.append(r)

Save updated data to a new excel file

wb.save('/Users/username/food_7Aug2024.xlsx')

[mroeschke]

Agreed that it wouldn't be suitable to maintain this feature in pandas so closing