This tutorial shows a guide on how to write excel file using Python. We know that excel is great for generating reporting data. It also allows us 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.

We 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.

We will write excel file using Python here in Object Oriented Programming way. We 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.

You may also like to read:

Read excel file using Python

Let’s move on to the example…

Prerequisites

Have Python installed in Windows (or Unix)
Pyhton version and Packages
Here I am using Python 3.6.5 version.

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.

Preparing your 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.

Check 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

write excel file using python

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

Installing Python’s xlwt module in Windows

1. Download xlwt-1.3.0 from appropriate URL. You will get generally zip tar file.

2. Extract the zip folder and navigate to the xlwt-1.3.0 folder using cmd prompt. Make sure you run the cmd prompt in Administrator mode otherwise install may not happen.

3. Now type the below command to install xlwt module.

python setup.py install

Example image given below

write excel file using python

4. Once successfully installed you should see below message at the end

write excel file using python

5. Now check again in Python terminal whether xlwt module exists or not.

write excel file using python

Congratulations! You have successfully installed xlwt module in Python 3.6.5. Now you can easily write excel file using Python 3.6.5.

Example

In the below image you see I have opened a 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 we imported xlwt module.

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

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

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

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

Next we write column headers to the excel file.

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

Finally we 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

You may also like to read:

Read excel file using Python

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

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