3

2 Questions to ask:

Ques 1: I just started studying about xlrd for reading excel file in python. I was wondering if there is a method in xlsrd --> similar to get_active_sheet() in openpyxl or any other way to get the Active sheet ?

get_active_sheet() works this in openpyxl

import openpyxl

wb = openpyxl.load_workbook('example.xlsx')

active_sheet = wb.get_active_sheet()

output : Worksheet "Sheet1"

I had found methods in xlrd for retrieving the names of sheets, but none of them could tell me the active sheet.

Ques 2:

Is xlrd the best packaage in python for reading excel files? I also came across this which had info about other python packages(xlsxwriterxlwtxlutils) for reading and writing excel files.

Which of the above all will be best for making an App which reads an Excel File and applies different validations to to different columns

For eg: Column with Header 'ID' should have unique values and A column with Header 'Country' should have valid Countries.

Improve this question

3 Answers 3

Reset to default
5

The "active sheet" here seems you're referring to the last sheet selected when the workbook was saved/closed. You can get this sheet via the sheet_visible value.

import xlrd
xl = xlrd.open_workbook("example.xls")
for sht in xl.sheets():
    # sht.sheet_visible value of 1 is "active sheet"
    print(sht.name, sht.sheet_selected, sht.sheet_visible)

Usually only one sheet is selected at a time, so it may look like sheet_visible and sheet_selected are the same, but multiple sheets can be selected at a time (ctrl+click multiple sheet tabs, for example).

Another reason this may seem confusing is because Excel uses "visible" in terms of hidden/visible sheets. In xlrd, this is instead sheet.visibility (see https://stackoverflow.com/a/44583134/4258124)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Anyways to get the active cell after getting the active sheet using the way you mentioned?
@Eswar not sure, but I don't think so. Even if you could, I think valid use cases would be extremely slim to none. If you do really need, you could use something like win32com to open Excel and interact with it that way.
2

Welcome to Stack Overflow.

I have been working with Excel files in Python for a while now, so I could help you with your question, I think.

openpyxl and xlrd solve different problems, one is for xlsx files (Excel 2007+), where the other one is for xls files (Excel 1997-2003), respectively.

Xenon said in his answer that Excel doesn't recognize the concept of an active sheet, which is not totally true. If you open an Excel document, go to some other sheet (that isn't the first one) and save and close the document, the next time you open it, Excel will open the document on the last sheet you were on.

However, xlrd does not support this kind of workflow, i.e. asking for the active sheet. If you know the sheet name, then you could use the method sheet_by_name, or if you know the sheet index, you could use the method sheet_by_index.

I don't know if the xlrd is the best package around, but it is pretty solid, and I have had nary a problem using it.

The example given could be solved by first iterating through the first row and keeping a dictionary of which column a header is. Then storing all the values in the ID column in a list and comparing the length of that list with the length of a set created from that list, i.e. len(values) == len(set(values)). Following that, you could iterate through the column with header of Country and check each value if it is in a dictionary you previously made with all the valid counties.

I hope this answer suits your needs.

Summary: Stick with xlrd because is mature enough.

Improve this answer

5 Comments

this is incorrect, see my answer - the OP's desired functionality has existed in xlrd for >10 years
Actually , I doubt the version range you mentioned. I have some excel files created with Excel 2007, that openpyxl could not read many cell values, and gave nontype value. I tried with xlrd , and it read smoothly
I am not sure if I could ask a new question , since there are so many questions asked about this around
cause it seems that even in higher versions of Excel, still the xlrd works better
I even save the file with Excel 2016 but no change
1

You can see all worksheets in a given workbook with the sheet_names() function. Excel has no concept of an "active sheet", but if my assumption that you are referring to the first sheet is correct, you can get the first element of sheet_names() to get the "active sheet."

With regards to your second question, it's not easy to say that a package is better than another package objectively. However, xlrd is widely used, and the most popular Python library for what it does.

I would recommend sticking with it.

Improve this answer

3 Comments

Thanku for your answer, by active sheet in excel I meant the sheet which opens as a default sheet when we open the Excel File. There is a concept of Active sheet ( click here ) in Excel.
this is incorrect, see my answer - the OP's desired functionality has existed in xlrd for >10 years
@NikT what's OP's desired functionality? Can you please explain?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.