What is .XLSX file format?
XLSX files are spreadsheet file formats from Microsoft Corporation which is used to enter and store data and, also for calculation. In Microsoft Excel (2007/2010/), xlsx files are used to store spreadsheets and workbooks. In python data from this file format can be read and also exported. Excel and Python both are widely used as part of data analytics and data science projects. In this article, we shall cover how to read and write data in excel files from python.
1)Reading an Excel file using Python
A spreadsheet can be retrieved using the xlrd module. You can use Python for reading, writing, and editing data. Additionally, the user might have to peruse various sheets and retrieve data based on some criteria or modify some rows and columns.
Command to install xlrd module
pip install xlrd
Here, stock.xlsx file is taken which has the data content as given below.
Now we will extract a specific cell from the above excel sheet using the following code.
# Reading an excel file using Python
import xlrd
# Give the location of the file
loc = (“stock_data.xlsx”)
# To open Workbook
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
# For row 0 and column 0
print(sheet.cell_value(0, 0))
Output: Tickers
Now we will be extracting the number of rows.
# Program to extract a number of rows using Python
import xlrd
# Give the location of the file
loc = (“/content/drive/MyDrive/DBA/stock_data.xlsx”)
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
sheet.cell_value(0, 0)
# Extracting number of rows
print(sheet.nrows)
Output: 6
Now we will extract all column names
# Program for extracting all column names in Python
import xlrd
loc = (“stock_data.xlsx”)
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
# For row 0 and column 0
sheet.cell_value(0, 0)
for i in range(sheet.ncols):
print(sheet.cell_value(0, i))
Output:
Tickers
Eps
Revenue
Price
People
2)Writing to an Excel sheet using Python.
In the xlwt module, multiple operations can be performed on spreadsheets.This can include writing or editing data using Python. In addition, the user might have to modify some rows and columns or go through multiple sheets to retrieve data based on the available criteria.
Now let’s create and write to an excel sheet using python.
# Writing to an excel sheet using Python
import xlwt
from xlwt import Workbook
# Workbook is created
wb = Workbook()
# add_sheet is used to create sheet.
sheet1 = wb.add_sheet(‘Sheet 1’)
sheet1.write(1, 0, ‘BANGALORE CITY’)
sheet1.write(2, 0, ‘MANGALORE CITY’)
sheet1.write(3, 0, ‘CLEMEN TOWN’)
sheet1.write(4, 0, ‘RAJPUR ROAD’)
sheet1.write(5, 0, ‘CLOCK TOWER’)
sheet1.write(0, 1, ‘BANGALORE CITY’)
sheet1.write(0, 2, ‘MANGALORE CITY’)
sheet1.write(0, 3, ‘CLEMEN TOWN’)
sheet1.write(0, 4, ‘RAJPUR ROAD’)
sheet1.write(0, 5, ‘CLOCK TOWER’)
wb.save(‘xlwt example.xls’)
wb.save function saves your file on your PC.
Output:
We have covered one way of reading data from xlsx file format and also writing data into xlsx file from python. This is important in data visualization as well as analytics and storing output from python projects in xlsx files. There are other libraries also which are available in python and the same can be explored further. Functions in the ‘pandas’ library can be used : pandas.read_excel is used to read an xlsx file and for writing pandas.to_excel function is used.