How to check which one of the excel sheets has a specific word inside using python?-CodePudding

I have been building a project in python and i have been having a little problem when working with python and excel. I have an excel document with 50 sheets(Sheet1, Sheet2, ...) and I want to find which of the sheets has a word inside them. For example: I am looking for the sheets that have the word "work"(in one of the cells) inside them, and as result have the name of the sheets that have that word inside them(the result can be multiple sheets for this example, like Sheet4, Sheet43, Sheet50). Thank you for reading and for the help.

I tried to find a answer by myself and I failed. Then I tried to find the answer on the internet and most of the posts discus the next problem: finding the sheets that have a specific word in their name. This is not for what I am looking. I am looking for finding the sheets that have a specific word in them(not in the name but in one of the cells). So far I have been using pandas for context.

CodePudding user response：

    import pandas as pd
    exel_data = pd.read_excel("data.xlsx")

##### converting into comma-separated values
    exel_data.to_csv("data.txt")
##### Open in read mode   
    file = open("ptry.txt", "r")
##### reading comma-separated values
    file_str = filex.read()
##### Spliting it on the basis on , (in my case) you can use whatever suit your data type and creating a list
    file_list = file_str.split(",")
#### if "hello world is in it return true else false    
    if "hello world" in file_list:
        print("True")
    else:
        print("false")

CodePudding user response：

You can use the pandas library in Python to open and read the contents of an Excel sheet.

Here's an example:

import pandas as pd

# Open the Excel file and read the contents of the first sheet
df = pd.read_excel('file.xlsx', sheet_name='Sheet1')

# Search for the word "example" in all cells of the dataframe
if df.isin(['example']).any().any():
    print("The word 'example' was found in the sheet")
else:
    print("The word 'example' was not found in the sheet")

in all the sheets in the excel file:

import pandas as pd

# Open the Excel file and read the contents of the first sheet
xls = pd.ExcelFile('file.xlsx')
sheet_names = xls.sheet_names

for sheet_name in sheet_names:
    df = pd.read_excel('file.xlsx', sheet_name=sheet_name)
    if df.isin(['example']).any().any():
        print(f"The word 'example' was found in sheet: {sheet_name}")
    else:
        print(f"The word 'example' was not found in sheet: {sheet_name}")

CodePudding user response：

from openpyxl import load_workbook
xls = load_workbook(filename= excel_path , data_only=True)

for i in xls.sheetnames:
  ws = xls[str(i)]
  for num_row in range (1, ws.max_row  1):
    # print(ws.max_row)
    if ws['A{}'.format(num_row)].value=='work':
        print (str(i))