How do I open an Excel file and use Python to read it?

How do I open an Excel file and use Python to read it?

Problem

When working with data, spreadsheets are essential. This tutorial will show you how to use Python to open and analyze excel worksheets (xls/xlsx file formats).

Solution

Two libraries in Python can be used to interact with the Excel sheet. We'll discuss how we can use the libraries in our Python code to read the excel sheets.

Use xlrd module

To open and read excel files in Python code, import xldr can be used. It is not supported to be read from the.xlsx file. We can read data from an Excel sheet by using the columns and rows count. Let's see the output of Excel using an example code.

Note:

pip installation xlrd Ensure that you have installed xlrd before we import it in the code. Code:

import xlrd list_of_rows = [] file_path = "D:/applications.xls" excel_workbook = xlrd.open_workbook(file_path) excel_tab = excel_workbook.sheet_by_index(0) def getrowvalue(value,count): #function to get the value print("Row",count,":",value) for i in range(excel_tab.nrows): for j in range(excel_tab.ncols): list_of_rows.append(excel_tab.cell_value(i,j)) getrowvalue(list_of_rows,i) list_of_rows = [] Output:

Row 0. : [Name', OS'] Row 1: [ABC-APP'. 'Windows.'] Row 2: [CDA-APP'. 'Unix.'] Row 3: [LKUYKI. 'Windows.'] Row 4: [CRYSTAL. 'Windows.'] Using openpyxl module: Openpyxl is the same as xlrd. The only difference with xlrd and openpyxl is that it can read.xlsx files using this module. The range for the number columns and rows begins with 1.

Note:

pip openpyxl first before you import openpyxl into the code. Code:

import openpyxl list_of_rows = [] file_path = "D:/applications.xlsx" excel_workbook = openpyxl.load_workbook(file_path) excel_workbook.active = 0 excel_tab = excel_workbook.active print(excel_tab) def getrowvalue(value,count): print("Row",count,":",value) for i in range(1, excel_tab.max_row+1): for j in range(1, excel_tab.max_column+1): list_of_rows.append(excel_tab.cell(row = i, column = j).value) getrowvalue(list_of_rows,i) list_of_rows = [] Output:

Row 1: ["Name", 'OS", Row 2: [’ABC-APP'], 'Windows’] Row 3: [’CDA-APP’, Windows’] Row 4: [’LKUYKI’, ‘Windows’] Row 5: [’CRYSTAL’,?Windows’]