{"id":34411,"date":"2022-09-28T15:00:39","date_gmt":"2022-09-28T15:00:39","guid":{"rendered":"https:\/\/www.askpython.com\/?p=34411"},"modified":"2023-02-16T19:56:39","modified_gmt":"2023-02-16T19:56:39","slug":"python-read-excel","status":"publish","type":"post","link":"https:\/\/www.askpython.com\/python\/examples\/python-read-excel","title":{"rendered":"Python Read Excel- Different ways to read an excel file using Python"},"content":{"rendered":"\n

An Excel file is a spreadsheet file containing some cells in rows and columns (Tabular view) and can help in the arrangement, calculation, sorting, and managing of data. The data in the spreadsheet may be numeric, text, formulas, hyperlinks, functions, etc. An XLS file stores data as binary streams. It can only be created by the most popular MS Excel or other spreadsheet programs. The file format .xlsx always indicates an excel file on its own. <\/p>\n\n\n\n

The following image depicts an excel file created by the MS-excel program:<\/p>\n\n\n\n

\"Excel
Excel File By MS Excel<\/figcaption><\/figure>\n\n\n\n

How to read Excel files using Python<\/h2>\n\n\n\n

To read excel files using Python, we need to use some popular Python modules and methods. Let’s understand those as well.<\/p>\n\n\n\n

Using Python xlrd module <\/h3>\n\n\n\n

xlrd <\/code>is a python library or module to read and manage information from Excel files ( i.e. files in .xlsx <\/code>format ). This Module will not be applicable for anything other than<\/strong> .xlsx\u00a0<\/code>files.<\/strong><\/p>\n\n\n\n

Let’s have a quick look at how to install xlrd <\/code>module.<\/p>\n\n\n

\nC:\\Users\\pc> pip install xlrd\n<\/pre><\/div>\n\n\n
As you are using python, You must have downloaded the <\/strong>pip<\/code><\/strong> package installer<\/a>. You can also use another Python package manager of your choice. <\/p>\n\n\n\n
\"Installing
Installing Xlrd Module<\/figcaption><\/figure>\n\n\n\n
In this method, We are going to use xlwings <\/code>module along with the method associated with it (i.e. xlwings.Book()<\/code> ). <\/p>\n\n\n\n
This method will automatically open our .xlsx in the background for us in its original program (i.e. MS-Excel) where we can operate and manage our data.<\/p>\n\n\n
\n#importing the xlwings module as xw\nimport xlwings as xw\n\n#you can get ur excel file already been opened after execution of this command\nws = xw.Book("C:\\\\Users\\\\pc\\\\Desktop\\\\students.xlsx").sheets['Sheet1']\n<\/pre><\/div>\n\n\n
From the above code snippet, We can get our Excel automatically opened on our desktop where we can access it.<\/p>\n\n\n\n
Using Python pandas module <\/h3>\n\n\n\n
Pandas<\/a><\/strong> is an open-source Python library or module that provides in-built high-performance data structures and data analysis tools. It is most preferably used to analyze data along with two other core python libraries- Matplotlib <\/code>for data visualization and NumPy <\/code>for mathematical operations. <\/p>\n\n\n\n
We are going to install this module in the same way as our previous module using the pip installer as follows.<\/p>\n\n\n
\nC:\\Users\\pc> pip install pandas\n<\/pre><\/div>\n\n\n
The above code snippet will install the pandas<\/code> module for us as follows.<\/p>\n\n\n\n
\"Installing
Installing Panda Module<\/figcaption><\/figure>\n\n\n\n
To read excel files, let’s run the following snippet of code. <\/p>\n\n\n
\n# importing pandas module as pd\nimport pandas as pd\n\n#using read_excel() method to read our excel file and storing the same in the variable named "df "\ndf = pd.read_excel("C:\\\\Users\\\\pc\\\\Desktop\\\\students.xlsx")\n\n#printing our spreadsheet using print() method\nprint(df)\n<\/pre><\/div>\n\n\n
In the above method, We are using read_excel<\/code>() method to read our .xlsx <\/code>file. We can use this method along with the pandas module as panda.read_excel()<\/em><\/code> to read the excel file data into a DataFrame object (Here it is ‘ df <\/code>‘). <\/p>\n\n\n\n
The above code snippet will print our spreadsheet as follows.<\/p>\n\n\n\n
\"Read
Read Excel Method<\/figcaption><\/figure>\n\n\n\n
Using Python openpyxl module<\/h3>\n\n\n\n
Openpyxl <\/code>is a Python library or module used to read or write from an Excel file. This module needs to be installed to use certain methods like load_workbook(), otherwise, we can’t use those methods, It will throw error<\/code>. Let’s install this module using our command prompt. <\/p>\n\n\n
\nC:\\Users\\pc> pip install openpyxl\n<\/pre><\/div>\n\n\n
The above code snippet will install our openpyxl <\/code>module as follows.<\/p>\n\n\n\n
\"Installing
Installing Openpyxl Module<\/figcaption><\/figure>\n\n\n\n
In our second method, We are going to use our openpyxl module along with load_workbook() method as our following code snippet.<\/p>\n\n\n
\n# importing openpyxl module \nimport openpyxl\n\n#using load_workbook() method to read our excel file and storing to dataframe object table1\ntable1 = openpyxl.load_workbook("C:\\\\Users\\\\pc\\\\Desktop\\\\students.xlsx")\n\n#To access the table1 we need to activate and store to an another object (Here it is table2)\ntable2 = table1.active\n\nfor row in range(1, table2.max_row):\n    for col in table2.iter_cols(1, table2.max_column):\n        print(col[row].value, end = " ")\n    print("\\n")\n\n<\/pre><\/div>\n\n\n
In the above code snippet, We are using load_workbook() method to read our required excel file along with openpyxl module. We couldn’t use this method without importing this library or module. Not only this<\/strong>, This module is responsible for reading the location as a parameter( Here it is “C:\\Users\\pc\\Desktop\\students.xlsx” ) in our read_excel() method.<\/strong> <\/p>\n\n\n\n
After reading our excel file and assigning it to table1, It needs to be activated. Otherwise, If we print the table1, the following output occurs.<\/p>\n\n\n\n
\"Printing
Printing Table1<\/figcaption><\/figure>\n\n\n\n
We are going to access table2 by using for loop as the above code snippet. We will get our results as follows.<\/p>\n\n\n\n
\"Output
Output By Method 2<\/figcaption><\/figure>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
In this article, We covered Different methods to read our Excel file using Python. We discussed some popular Modules along with some required methods of Python for our appropriate output. Hope You must have practiced and enjoyed our code snippets. We must visit again with some more exciting topics.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
An Excel file is a spreadsheet file containing some cells in rows and columns (Tabular view) and can help in the arrangement, calculation, sorting, and managing of data. The data in the spreadsheet may be numeric, text, formulas, hyperlinks, functions, etc. An XLS file stores data as binary streams. It can only be created by […]<\/p>\n","protected":false},"author":47,"featured_media":34434,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-34411","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-examples"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/posts\/34411","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/users\/47"}],"replies":[{"embeddable":true,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/comments?post=34411"}],"version-history":[{"count":0,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/posts\/34411\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/media\/34434"}],"wp:attachment":[{"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/media?parent=34411"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/categories?post=34411"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.askpython.com\/wp-json\/wp\/v2\/tags?post=34411"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}