Write excel files (xlsx) from Python


Following a previous post where we read an Excel spreadsheet and use the data in Python, in this case we are going to make Python write data in an Excel file. In other words, in this example we have some data calculated from our Python code and we want to save it in an Excel file.

Step 0. What you should already have installed:
In this post I’m going to assume that you already have installed Python 2.7 with numpy and openpyxl. If you don’t have Python installed you can check this post on how to do it and go to this one if you want to check how to install openpyxl.

Step 1. Create the data to be printed in the Excel spreadsheet:
I’m going to do a similar example than the previous post where we made Python talk with Excel. In this case I’m going to create two vectors ‘x’ and ‘y’ where the values of the second one are the square of the values of the first vector:

This simple chunk of code imports the numpy library and creates a vector x = ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) and a vector y = ( 1, 4, 6, 16, 25, 36, 49, 64, 81, 100) as we desire. If you run this code you will see the shell printing both vectors

Step 2. Write the data into an Excel file:
Now that we have the values, we need to add the code to copy our values in the spreadsheet using openpyxl

First we import the required libraries and then we open an object which will be our workbook. We then set the active sheet with get_active_sheet() and we give it a name. The next two lines will generate the data to be copy in the Excel spreadsheet. Next line shows how to set the value of an individual cell in the excel file: we use sheet.cell(row=i,column=j).value giving i and j to write in the desired cell. In this two lines we want to write a string in the first two cells of the first row so the first one is in row=1, column=1 and the row=1, column=2.

Next, we do the same for the values inside the vectors but instead doing it one by one, a for loop is used to send each value to its cell. Last line saves the workbook in a new Excel file with the name we want.