Python Read Excel File

Introduction to Python Read Excel File

To do tasks like reading and writing to an Excel sheet file, programmers can use the Python language. Developers are able to carry them out thanks to a plethora of Libraries. Python includes a variety of libraries that hold a list of functions to work with the file when we have to deal with input-output processes, particularly Excel sheets. The “xlrd” Python Library will be used to interpret the spreadsheets. The xlrd library can read and parse Excel files on Linux, Mac OS X, and Windows. Additionally, it is Unicode-aware and compatible with Excel’s Dates Formats.

formats.

How to Read Excel File in Python Using Various Methods?

Next, we’ll show how to read an Excel file in Python with the help of the python excel library. Python’s xlrd library will be used to read in our Excel file and do the necessary calculations. Our demonstration file will be an Excel spreadsheet with a single sheet, three columns, and five rows (including the header row).

Method #1

The following is an attempt at printing the name of the first column in our Excel sheet as an example. Code:

Explained Code Snippet: import xlrd

print(excel_sheet.cell_value(0, 0))
Began with loading xlrd, the main library. We then followed the path of the read-only Excel file with a variable to store the file’s contents. The file’s contents are stored in a second variable; to access it, we use the open_workbook function and supply the file’s path. Next, we sort the information by sheet, use the sheet_by_index function to locate the correct data, and then output the result. sheet_by_index will select data from the last line of the last sheet by going to the last column of the last row. Here’s a screenshot for your perusal. Output:

The result, “Name,” is the value in the first row of the first column, therefore makes sense. The next demo shows how to use the xlrd library to count the number of active rows in an Excel document. The formula is as

follows.

Code:

Method #2

Similar to the previous code, import xlrd

print(excel_sheet.nrows)
began by importing the xlrd library and declaring the path to the input file. Then, we have an Excel sheet with the content in it and can read it using the index. Then, we make a call to the cell_value() method, giving it the coordinates of the zero-th column and row. Since we anticipate that the output would be the total number of rows, we have labeled the final line as nrows. Please see the accompanying screenshot for clarification. Output:

Explanation of Output: Our sheet has 5 rows total (counting the header row), hence the output is 5. Next, we’ll see how python code works by writing some code that opens an Excel file and outputs the number of columns in that file.

has.

Code:

Method #3

import xlrd

print(excel_sheet.ncols)
Code Example Here, we’ve set up an Excel sheet to store data in the same way we did in the previous examples, passing the sheet’s index and two parameters to the cell_value() method. Finally, we have a print statement that outputs the total number of columns in our Excel sheet using the ncols variable. Excel spreadsheets can reveal how many columns they have by referring to the ncols cell. The results are displayed in the following screenshot:

There are three columns in total on the Excel file, which is why the output is 3. So far, we’ve used sample data in columns and rows to test out Excel operations; in the next example, we’ll try to read an entire column of data. The column-reading code is as follows

below:

Code:

Method #4

Code Explanation: import xlrd
fpath = (r "C:\Users\KshirsagarS\Downloads\read_excel_py.xlsx")
workbook = xlrd.open_workbook(fpath)
excel_sheet = workbook.sheet_by_index(0)
excel_sheet.cell_value(0, 0)
for i in range(excel_sheet.nrows):
print(excel_sheet.cell_value(i, 0))
File pat, followed by our workbook and excel sheet, remain unchanged from the beginning of our code. After that, we tallied our values in cells. In our loop, we’ll test each cell. They’ll function in a fashion that outputs the entire contents of the first column of data for each row. We anticipate a total of five rows in the output, which will match the number of names we have in our spreadsheet. For a clearer picture of the output, please see the attached screenshot: Output:

Just as we predicted, the output consists of five rows: a header row that doubles as the column’s name, followed by four values that make up the entire column (). To allow reading from anywhere in the sheet, some modifications to the loop structure are required. The cell_value function is crucial because it transfers information between the input and the output.

We used the popular and highly recommended library xlrd, which is itself the product of much development work. If you’re a python programmer looking to simplify your work with Excel spreadsheets, the xlrd library is for you. One of the various libraries available to python programmers for interacting with Excel is the xlrd library.