Skip to content

Tag: excel

Extracting all data validation formula values(dropdown) from excel sheet

How can we extract the data validation dropdown values(not just formulas/references or a single valued formula result) from a given excel sheet using python? Currently openpyxl helps us to get the formulas but not the values directly. It even fails in cases where file contain extensions(extLst) to the OOXML specifications that are not supported.These datavalidation formulas can contain functions like

How to normalise a date columnin pandas dataframe to the same format

I have a dataframe made from pulling in different excel sheets. I am trying to normalise the date_time column to just a standard DD/MM/YYY format. Is that possible? 1 DATE Column 3 Column 4 Column 5 Column 6 2 01/03/2021 00:00 3 01/03/2021 00:00 4 01/03/2021 00:00 5 01/03/2021 00:00 6 01/03/2021 00:00 … … 122350 11/24/2022 122351 11/24/2022 122352

Name ‘ ‘ is not defined – python

from bunch of files I want to extract some info (code is below), but I came across error: “name ‘GH_Pos21X_true’ is not defined”, full error: Where should I defined it? Code: I can’t see a problem, maybe someone could help me find my mistake? I will be grateful. If I don’t set this variable as global I have this error:

Converting Excel to PDF with pywin32 [Error]

I try to export one of my xlsx files as PDF with pywin32: Unfortunately I get this com_error: In the end I want to do use this conversion on 30 xlsx files at once. I know how to write the loop for this but can not fix the above mentioned error. Answer When using Python paths on Windows, you need

Pandas Set Index Based On Column Value

I’m new to pandas, I have a Dataframe read from excel file, like this screenshot below where Products is the header and Google and Meta is the group (index) Using pandas I would like the dataframe to be like this Thank You Answer Using pd.assign and np.where