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:
Let’s move on to the example…
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.
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 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
4. Once successfully installed you should see below message at the end
5. Now check again in Python terminal whether xlwt module exists or not.
Congratulations! You have successfully installed xlwt module in Python 3.6.5. Now you can easily write excel file using Python 3.6.5.
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.
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:
You may also like to read:
Thanks for reading.