Tutorial 2: Adding formatting to the XLSX File
In the previous section we created a simple spreadsheet using Python and the XlsxWriter module.
This converted the required data into an Excel file but it looked a little bare. In order to make the information clearer we would like to add some simple formatting, like this:
The differences here are that we have added Item and Cost column headers in a bold font, we have formatted the currency in the second column and we have made the Total string bold.
To do this we can extend our program as follows:
(The significant changes are shown with a red line.)
import xlsxwriter # Create a workbook and add a worksheet. workbook = xlsxwriter.Workbook('Expenses02.xlsx') worksheet = workbook.add_worksheet() # Add a bold format to use to highlight cells. bold = workbook.add_format('bold': True>) # Add a number format for cells with money. money = workbook.add_format('num_format': '$#,##0'>) # Write some data headers. worksheet.write('A1', 'Item', bold) worksheet.write('B1', 'Cost', bold) # Some data we want to write to the worksheet. expenses = ( ['Rent', 1000], ['Gas', 100], ['Food', 300], ['Gym', 50], ) # Start from the first cell below the headers. row = 1 col = 0 # Iterate over the data and write it out row by row. for item, cost in (expenses): worksheet.write(row, col, item) worksheet.write(row, col + 1, cost, money) row += 1 # Write a total using a formula. worksheet.write(row, 0, 'Total', bold) worksheet.write(row, 1, '=SUM(B2:B5)', money) workbook.close()
The main difference between this and the previous program is that we have added two Format objects that we can use to format cells in the spreadsheet.
Format objects represent all of the formatting properties that can be applied to a cell in Excel such as fonts, number formatting, colors and borders. This is explained in more detail in The Format Class section.
For now we will avoid getting into the details and just use a limited amount of the format functionality to add some simple formatting:
# Add a bold format to use to highlight cells. bold = workbook.add_format('bold': True>) # Add a number format for cells with money. money = workbook.add_format('num_format': '$#,##0'>)
We can then pass these formats as an optional third parameter to the worksheet. write() method to format the data in the cell:
write(row, column, token, [format])
worksheet.write(row, 0, 'Total', bold)
Which leads us to another new feature in this program. To add the headers in the first row of the worksheet we used write() like this:
worksheet.write('A1', 'Item', bold) worksheet.write('B1', 'Cost', bold)
So, instead of (row, col) we used the Excel ‘A1’ style notation. See Working with Cell Notation for more details but don’t be too concerned about it for now. It is just a little syntactic sugar to help with laying out worksheets.
In the next section we will look at handling more data types.
© Copyright 2013-2023, John McNamara.
Created using Sphinx 1.8.6.
Python scripts to format data in Microsoft Excel
In this article, we will use Python scripts for data formatting in Microsoft Excel sheet with various examples.
Introduction
Usually, DBAs share export required data in an Excel sheet, do the formatting, and share it with the concerned authorities. It is a regular practice. Python integrates with Microsoft Excel very well.
This article covers the following topics:
- Create a sample excel file using the Python
- Import data from an excel file using Python
- Format data in excel sheet using Python
- Prepare excel charts using Python
Pre-requisites
- In this article, we use SQL Notebooks of Azure Data Studio. Refer to SQL Notebooks introduction and overview for detailed information on it
- Download the Python latest version 3.8.2 for Windows from this URL
- Configure SQL Notebook for Python kernel. Refer to the article Use Python SQL scripts in SQL Notebooks of Azure Data Studio for it
- Install XlsxWriter and xlrd Python module using pip utility
- XlsxWriter: We use the XlsxWriter Python module to write the Microsoft Excel files in XLSX format. It provides various formatting options, charts, textboxes, Rich multi-format strings, Macros for excel files For the installation of XlsxWrite, use the following command in Python3 kernel of the SQL Notebook:
It downloads the package and installs it as shown in the following screenshot:
Xlrd: It is also a Python library and useful to read data from the excel files. It supports both XLS and XLSX extension for reading data and formatting information from Excel files. It gives many formatting features as well for excel files Use the following command for installing the xlrd Python module:
Work with excel files using Python scripts and libraries
In the previous step, we installed the Python libraries XlsxWriter and xlrd using SQL Notebooks of Azure Data Studio.
Create a basic excel file
Let’s create a sample excel file without any formatting. Run the following code in Python: