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

[TomHsiung]

Feature Type

• [X] 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