from openpyxl import load_workbook
wb = load_workbook("C:\ktl.xlsx")
ws = wb.active
This is the code I have so far, as I don't know what to do next. So basically I want to open "C:\ktl.xlsx" and make python filter column five for me, when I give the word "SPT098". So it should filter column five for that word and all the rows related to that and then save the results in another excel file.
Here is a way to do what you are looking for. I am assuming some random data in kt1.xlsx. This script will use pandas (not openpyxl, hope that is ok) to read the data into a dataframe df, ask the user for the value to be filtered and create and store data filtered by that column into a new excel file, output.xlsx.
The sample data in ktl.xlsx
Col_Date Col_Num Col_2 Name FilterByCol
25-06-2022 4.00 700 532 aa SPT098
25-06-2022 5.00 1100 433 aa SPT099
25-06-2022 6.00 800 754 aa SPT064
25-06-2022 7.00 1200 288 aa SPT153
25-06-2022 8.00 700 643 bb SPT098
25-06-2022 9.00 1400 668 bb SPT099
25-06-2022 10.00 1600 286 bb SPT064
03-06-2022 11.00 397 46 cc SPT153
03-06-2022 12.00 100 7 cc SPT098
03-06-2022 13.00 400 25 cc SPT099
03-06-2022 14.00 500 41 cc SPT064
03-06-2022 15.00 400 0 cc SPT153
03-06-2022 16.00 300 23 dd SPT098
03-06-2022 17.00 500 50 dd SPT099
03-06-2022 18.00 300 0 dd SPT064
03-06-2022 19.00 400 15 dd SPT153
Code
import pandas as pd
df = pd.read_excel("C:\ktl.xlsx", "Sheet1") #Read data from Sheet1 into df
filter_val = input("Please provide the filter_by value: ") #Ask user input - SPT098
if filter_val not in df.iloc[:,4].unique():
print("Invalid entry. Exiting...") # Exit in case of incorrect data
else:
df=df[df['FilterByCol'] == filter_val]
df.to_excel("output.xlsx", index=False)
print("Processing complete. Filtered data written to output.xlsx")
Output in excel for SPT098
