Open XLSX File

Create XLSX with Python

XLSX, because it is an XML-based format, can be created and generated from programming code. This can be done for various purposes, including the presentation of reports, financial tables, calculations, charting data and many more. In addition, XLSX is a format that the users can edit with ease, unlike PDF files. Therefore, having the possibility of generating Excel documents with Python can facilitate the creation and sharing of data files in XLSX format. As an example we will use Python package openpyxl.

Python is an interpreted programming language whose philosophy emphasizes the intelligibility of its code and the intuitive way in which its statements are presented. It is a multiparadigm programming language, since it partially supports:

• Object-oriented programming.
• Imperative programming.
• Functional programming

It is an interpreted, dynamic and multiplatform language as it is supported by Windows, MAC and Linux. Assuming that you already have the Python language installed, then we will proceed to prepare everything to create a XLSX with Python.

Following the steps below, we can configure our code to generate any spreadsheet in an automated way

Openpyxl installation

The openpyxl package can be easily installed with pip, for which it will be necessary to type in the terminal the command:

pip install openpyxl

Create Excel file in python

To start working with openpyxl, you do not need to save any files to the filesystem. You just have to create a book.

import openpyxl
wb = openpyxl.Workbook ()

Create a sheet

In addition to the default sheet, with openpyxl it is possible to create more sheets in a workbook using the workbook's create_sheet () method.

Access a sheet

Sheet names are a very important property since they allow us to access them directly by treating the workbook as a dictionary.

Access a cell

So far we have seen how to create a book, the sheets and how to access them. Now we are going to the important thing, how to access the value of a cell and how to save data. It is possible to access a cell by treating the sheet as a dictionary, in such a way that the name of the cell is used as the key. This results from the combination of the column name and the row number.

Write values in a cell

In the previous section, you will have seen that when displaying the content of a cell (print (a1.value)) it always returned None. This is because the cell does not contain any values.

Save an excel workbook in Python

We end this tutorial by showing you how to save an excel file in Python using openpyxl. To save an excel file using openpyxl, you just have to call the save () method of the workbook indicating the name of the file. This will save the workbook with all the sheets and the data for each of them. If we do it on the previous book, we will have the following result:

wb.save ('products.xlsx')

We have seen how to create Excel documents with Python. A tool that facilitates the creation of automatic reports and spreadsheets from our code. Something that can save us a lot of time and that has many more possibilities as you can see in the package documentation.