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.
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 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.
If your module not found then you will get below message:
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.
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.
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: