Introduction
In this tutorial, we will learn how to write an excel file in Python. At the end of this tutorial, you will learn how to open an Microsoft Excel file, write the data into particular column in a particular sheet of the Excel file.
Example
Let us assume that you would like to create an Excel file which consists of 2 columns Name and Age. We will develop the Python code to write the data into an Excel file. And finally we will compute the average age of all the students.
Steps
Step 1 : The first step is to import the xlwt library. We use xlwt package to read the data from Excel spreadsheets. If xlwt library is not installed, you can download from here.
|
1
|
import xlwt
|
Step 2 : In this step, we will create a workbook and an Excel sheet “ExperimentSheet”. Notice the parameter “cell_overwrite_ok” which is set to True. This is necessary if you have to write the existing cell more than once and Python would not raise an exception.
|
1
2
3
4
|
sheetName = "ExperimentSheet"
wb1 = xlwt.Workbook(encoding="UTF-8")
ws1 = wb1.add_sheet(sheetName, cell_overwrite_ok=True)
|
Step 3 : Add the Header : As seen from the Excel sheet, we need to first add two headers, “Name” and “Age” in cell A1 and B1, respectively.
|
1
2
3
4
5
6
7
8
9
|
Header =['Name', 'Age']
colIdx =0 # Start from Column 0
for indData in Header:
col = ws1.col(colIdx)
col.width = 256 * 20
ws1.write(0, colIdx, indData) # Insert in First Row ( Row 0)
colIdx = colIdx+1
|
Similarly, we need to add the name and age of the students. Let us assume we have an array of array consisting of Name and age in Python variable “data”.
|
1
2
3
4
5
6
7
8
9
10
|
rowIndex = 1 # Start from Row 2
for ind_list in data:
index = 0 # Column A
for ind_col in ind_list:
ws1.write(rowIndex, index, str(ind_col)) #Write the data in the specified cell
index = index + 1
rowIndex = rowIndex +1
|
Step 4 : Computing the Average
We need to add the row AVERAGE on column A below all the names of all the students. We need to use the Excel Average Formula AVERAGE(B2:B8).
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
#Add Average Leave a Blank Row
rowIndex = rowIndex + 1
ws1.write(rowIndex, 0, "Average")
#Compute the Average from B2 to B8
len_data = len(data) + 1
start_col = excel_style(2,2) # Starting from Row 2 in Column B
end_col = excel_style(len_data,2) # Column B spanning the length of array data
formula = "AVERAGE(" + start_col + ":" + end_col + ")" #Excel Formula AVERAGE(B2:B8)
ws1.write(rowIndex, 1, xlwt.Formula(formula)) #Insert the Excel formula
|
Notice the function excel_style() which converts number to Excel column name.
|
1
2
3
4
5
|
def excel_style(row, col):
""" Convert given row and column number to an Excel-style cell name. """
quot, rem = divmod(col-1, 26)
return((chr(quot-1 + ord('A')) if quot else '') +
(chr(rem + ord('A')) + str(row)))
|
The last step is to save the Excel file.
|
1
|
wb1.save(filename)
|
Download
The complete Python source can be downloaded from here. Please note, that the code is tested using Python 2.7 . If you are using Python 3.3, you might have to do some minor changes in the code.
I hope, you find this article very useful. If you have any doubts or have any comments concerning this tutorial, Please leave a comment or contact me. I would be glad to help.
SapnaEdu is a portal to learn some really cool Mathematical Shortcuts. I hope, it will help students preparing for various competitive examinations. Some of these shortcuts are explained in Vedic Mathematics and rest of them are from my own experience of teaching students. Get to know about SapnaEdu 