Python - Validate if a sheet exists in my document xls

Question

I am trying to design a small program on my free time which loads an xls file, then select a sheet in the document to be scanned.

Step 1: the user imports an .xls file . After importing the program checks whether the file exists. (That I can do )

Step 2: I ask the user to give the name of the document sheet xls to analyze. And that 's where it stops. the program does not detect the sheets available :(

#check if the document exist
while True:
    x = input("Give the name of the document in this repository__")
    input_filename = x + ".xls"
    if os.path.isfile(input_filename):
        print ("the document is been charged")
        break
    else:
        print("xls not found !")

#Load the document
xls_file = pd.ExcelFile(input_filename)

#Select the good sheet in file
print ("this is your sheets in this document",xls_file.sheet_names)
while True:
    feuilles = input("Select yout sheet")
    input_feuilles = feuilles
    if xls_file.sheet_names(input_filename):
        print ("The sheet is been charged !")
        break
    else:
        print("This sheet don't exist!")

I really do not know how to verify that the sheet filled by the user really exists.

SparkAndShine · Accepted Answer · 2020-09-16T09:53:35.420

34

The Python library openpyxl is designed for reading and writing Excel xlsx/xlsm/xltx/xltm files. The following snippet code checks if a specific sheet name exists in a given workbook.

from openpyxl import load_workbook
 
wb = load_workbook(file_workbook, read_only=True)   # open an Excel file and return a workbook
    
if 'sheet1' in wb.sheetnames:
    print('sheet1 exists')

PS: For older Microsoft Excel files (i.e., .xls), use xlrd and xlwt instead.

Install openpyxl with the following command.

$ sudo pip install openpyxl

edited Sep 16 '20 at 09:53

answered Jun 22 '16 at 11:29

SparkAndShine

17,001
22
90
134

My initials of all mt sheet names are Capital. Its `Sheet1`. How can I convert all of them to lowercase in one line – ajinzrathod Sep 15 '20 at 08:06
@ajinzrathod, you might look for this [str.lowers — Python 2.7.18 documentation](https://docs.python.org/2/library/stdtypes.html#str.lower). – SparkAndShine Sep 16 '20 at 09:55

score 2 · Answer 2 · answered Jun 29 '18 at 17:41

The Python library xlsxwriter offers a great interface to Excel with all the formatting & graphing functions you'd expect. The following code fragment checks if a worksheet exists, creates one if it doesn't, and returns the worksheet object:

import xlsxwriter
workbook = xlsxwriter.Workbook(workbook_file)
worksheet = workbook.get_worksheet_by_name(worksheetName)
if worksheet is None:
    worksheet = workbook.add_worksheet(worksheetName)

Install xlsxwriter by the command:

sudo pip install xlsxwriter

Python - Validate if a sheet exists in my document xls

2 Answers2

Linked