A Guide to write Excel File using Python

Introduction

This tutorial shows a guide on how to write excel file using Python. You know that excel is great for generating reporting data. It also allows you to perform calculation on data, build complex formula which could be used to apply on different data. You can use this easily accessible excel tool to organize, analyze and store your data in tables. What’s more, this software is widely used in many different application fields all over the world and it also applies to data science.

You might have seen various operations on excel files using wonderful API – Apache POI in Java technology and it requires few more code has to be written to read from or write to excel files. But to write excel file using Python is very easy with a few lines of code.

I will write excel file using Python here in Object Oriented Programming way. I will create class and build objects and pass this list of objects to a function as an argument and iterate through this list of objects and finally write each attribute to the excel row’s column.

Related Posts:

Prerequisites

Python 3.6.5 – 3.9.1, xlwt 1.3.0 (pip install xlwt)

There are various packages available to work with the excel spreadsheets and such packages are pandas, openpyxl, xlrd, xlutils and pyexcel. In this example I am going to use xlwt package to write excel file using Python.

Write Excel using Python

Preparing Workspace

Preparing your workspace is one of the first things that you can do to make sure that you start off well. The first step is to check your working directory.

When you are working in the Python terminal, you need first navigate to the directory, where your file is located and then start up Python, i.e., you have to make sure that your file is located in the directory where you want to work from.

Installing Required Modules

Check for modules xlwt in Python terminal. Type the command as shown below image to check xlwt. If you do not get any error message then those modules exist otherwise you have to install those non-existence modules.

>>>import xlwt

If your module not found then you will get below message:

python write excel file

For example, if you do not have xlwt module then please find below steps to install it.

Installing xlwt Module

Generally you can install xlwt package using the command pip install xlwt in the command line tool.

python write excel file

Create Python Script

In the below image you see I have opened a command line tool (cmd prompt) and navigated to the directory where I have to write excel file using Python program.

excel file using python

Now create a Python script write_excel.py under the C:\py_scripts for writing an excel file. Here py is extension of the Python file.

In the below Python script I have imported xlwt module.

First I create a class OrderDetail and from this object I will create several instances and set the attributes with values and finally I write those attribute value to excel file.

Then I define a function write_to_excel() that takes three parameters – file name, sheet, and a list of class objects.

Inside the function I first open WorkBook instance and add a sheet. Then I print total items in the list.

Then I iterate through the list of objects and print each attribute in the cmd console.

Next I write column headers to the excel file.

Then again I iterate through list of objects and write each attribute to the excel column. Finally I save the excel file to disk in the same directory where write_excel.py file exists.

Finally I create four objects of type OrderDetail and pass those four objects as a list to the write_to_excel() function to write the excel file using Python.

import xlwt

class OrderDetail(object):

    def __init__(self, order_date, region, rep, item, units, unit_cost, total):
        self.order_date = order_date
        self.region = region
        self.rep = rep
        self.item = item
        self.units = units
        self.unit_cost = unit_cost
        self.total = total

def write_to_excel(filename, sheet, odList):
	book = xlwt.Workbook()
	sh = book.add_sheet(sheet)
	
	#total items
	total_items = len(odList)
	print("total_items: ", total_items)
	
	#print on console
	for od in odList:
		print(od.order_date)
		print(od.region)
		print(od.rep)
		print(od.item)
		print(od.units)
		print(od.unit_cost)
		print(od.total)
		print('\n')
	
	#write headers
	sh.write(0, 0, 'OrderDate')
	sh.write(0, 1, 'Region')
	sh.write(0, 2, 'rep')
	sh.write(0, 3, 'Item')
	sh.write(0, 4, 'Units')
	sh.write(0, 5, 'Unit Cost')
	sh.write(0, 6, 'Total')
	
	#write row values
	for idx in range(len(odList)):
		#print(idx)
		sh.write(idx+1, 0, odList[idx].order_date)
		sh.write(idx+1, 1, odList[idx].region)
		sh.write(idx+1, 2, odList[idx].rep)
		sh.write(idx+1, 3, odList[idx].item)
		sh.write(idx+1, 4, odList[idx].units)
		sh.write(idx+1, 5, odList[idx].unit_cost)
		sh.write(idx+1, 6, odList[idx].total)	
		
	book.save(filename)

od1 = OrderDetail('2016-1-6', 'East', 'Jones', 'Pencil', 95, 1.99, 189.05)
od2 = OrderDetail('2016-1-23', 'Central', 'Kivell', 'Binder', 50, 19.99, 999.50)
od3 = OrderDetail('2016-2-9', 'Central', 'Jardine', 'Pencil', 36, 4.99, 179.64)
od4 = OrderDetail('2016-2-26', 'Central', 'Gill', 'Pen', 27, 19.99, 539.73)

odList = [od1, od2, od3, od4]

write_to_excel('OrderDetails.xls', 'Sheet1', odList)

Testing the Python Script

Now run the above script using command python write_excel.py, you should see an excel file called OrderDetails.xls has been generated and you should see the below output:

write excel file using python

Source Code

Download

Leave a Reply

Your email address will not be published. Required fields are marked *