I'm currently using pandas to read an Excel file and present its sheet names to the user, so he can select which sheet he would like to use. The problem is that the files are really big (70 columns x 65k rows), taking up to 14s to load on a notebook (the same data in a CSV file is taking 3s).
My code in panda goes like this:
xls = pandas.ExcelFile(path) sheets = xls.sheet_names
I tried xlrd before, but obtained similar results. This was my code with xlrd:
xls = xlrd.open_workbook(path) sheets = xls.sheet_names
So, can anybody suggest a faster way to retrieve the sheet names from an Excel file than reading the whole file?
Right-click the ◀︎▶︎ at the left of the sheet tabs to bring up the Activate window... It provides a sequential list of the sheet names which is scrollable. Select the one you want then click the OK button. Use Edit> Find> Go To [Control+G or F5/fn+F5] in the same manner as what OssieMac first suggested.
you can use the xlrd library and open the workbook with the "on_demand=True" flag, so that the sheets won't be loaded automaticaly.
Than you can retrieve the sheet names in a similar way to pandas:
import xlrd xls = xlrd.open_workbook(r'<path_to_your_excel_file>', on_demand=True) print xls.sheet_names() # <- remeber: xlrd sheet_names is a function, not a property
I have tried xlrd, pandas, openpyxl and other such libraries and all of them seem to take exponential time as the file size increase as it reads the entire file. The other solutions mentioned above where they used 'on_demand' did not work for me. The following function works for xlsx files.
def get_sheet_details(file_path): sheets = [] file_name = os.path.splitext(os.path.split(file_path)[-1])[0] # Make a temporary directory with the file name directory_to_extract_to = os.path.join(settings.MEDIA_ROOT, file_name) os.mkdir(directory_to_extract_to) # Extract the xlsx file as it is just a zip file zip_ref = zipfile.ZipFile(file_path, 'r') zip_ref.extractall(directory_to_extract_to) zip_ref.close() # Open the workbook.xml which is very light and only has meta data, get sheets from it path_to_workbook = os.path.join(directory_to_extract_to, 'xl', 'workbook.xml') with open(path_to_workbook, 'r') as f: xml = f.read() dictionary = xmltodict.parse(xml) for sheet in dictionary['workbook']['sheets']['sheet']: sheet_details = { 'id': sheet['sheetId'], # can be @sheetId for some versions 'name': sheet['name'] # can be @name } sheets.append(sheet_details) # Delete the extracted files directory shutil.rmtree(directory_to_extract_to) return sheets
Since all xlsx are basically zipped files, we extract the underlying xml data and read sheet names from the workbook directly which takes a fraction of a second as compared to the library functions.
Benchmarking: (On a 6mb xlsx file with 4 sheets)
Pandas, xlrd: 12 seconds
openpyxl: 24 seconds
Proposed method: 0.4 seconds
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With