How to Change the Number Format in OpenPyXL – Python

Post Views: 3,641

Hey Fellow Python coder! I am sure you must have worked with the OpenPyXL library to create and modify Excel data using Python. We can change the number format of excel sheet in Python using OpenPyXL.

Let’s first of all dive into the basics and understand the theoretical concepts before the code implementation.

Introduction to OpenPyXL

OpenPyXL is a Python library that helps you work with Excel files using Python programming language. This library is helpful to automate handling a huge amount of data an Excel sheet contains and reduce manual work for you. With OpenPyXL, you can create new Excel files, modify existing ones, add or remove sheets, and perform various operations on cells like reading values, changing them, or formatting them.

If you don’t have this library installed in your system use the pipcommand in the terminal/command prompt and jupyter/Google colab notebooks. The command is : pip install openpyxl.

Or you can check: How to install openpyxl in Python

Understanding Number Formats in Excel

This section will be helpful if you are not aware of what exactly Number Formats are in Excel files.

To put it into simple words, Number Formats are a way to control how numbers are displayed in the cells without changing their actual values.

The following are the characteristics of number formatting in Excel:

It helps in the Readability of the data present in the Excel sheet.
It enhances the presentation and overall look of the data in the Excel sheet.
It makes sure that different types of data are sorted properly and it is not interpreted in the wrong way.

Still confused? Let’s have a look at some examples to make it even more clear.

When one wants to format any cell in an Excel sheet manually, there are multiple options available which are shown below:

I have also attached a sample illustration of some of the formatted cells in Excel below:

I hope now you have a decent understanding of Number Formats in Excel. Let’s move on to code implementation of the same in the upcoming sections.

Code Implementation in OpenPyXL

In this section, we will aim to create the same Excel sheet that I displayed above to make it easier to understand number formatting better.

Creating an Excel Workbook in OpenPyXL

First of all, we will create an Excel workbook using the OpenPyXL library and the functions present inside the library.

Have a look at the code snippet below:

import openpyxl as xl

workbook = xl.Workbook()
sheet = workbook.active

We will import the openpyxl library and then create a new workbook using the workbookfunction. But by default, the workbook is not in an active state and has no sheets. We will activate the workbook with a new sheet using the activefunction.

Adding Data to the Excel Workbook

Now that our workbook is all set to work on. We will continue adding the data to the Excel sheet. Since we aim to generate the same table presented above we will add the data column-wise.

First, we will add data to the headings of the table using the code below. We will be using the direct assignment method to assign these static values.

# HEADINGS
sheet['A1'] = 'S.No.'
sheet['B1'] = 'Judging Parameter'
sheet['C1'] = 'Number Format Used'
sheet['D1'] = 'Non-Formatted Value'
sheet['E1'] = 'Formatted Value'

Now let’s target one column at a time starting with assigning serial numbers to the S.No. column using the code snippet below.

# COLUMN 1 - Set Serial Number
row_start = 2
row_end = 8

for i in range(row_start,row_end+1):
  sheet.cell(row = i, column = 1, value = i-1)

In this function, we are going to use the sheet.cellmethod which takes three parameters to set the values.

The parameters needed are the row and column values to target the right cell. For instance, for ‘A5’, the value for the column is 1, and the row value is 5.

The next parameter is the value that we need to set for the particular cell. In our case, since the first row is assigned for the headings we will start the row from the 2nd row, and the ending according to the table end on the 8th row.

We will be setting the values for the columns ‘Judging Parameter’ and ‘Number Format Type’ in a similar fashion to how we have assigned values to the heading cell. Have a look at the code snippet.

# COLUMN 2 - JUDGING PARAMETER
sheet['B2'] = 'Name of the Movie'
sheet['B3'] = 'Release Date'
sheet['B4'] = 'Rating'
sheet['B5'] = 'Box Office Revenue'
sheet['B6'] = 'Average Viewer Count'
sheet['B7'] = 'Audience Score'
sheet['B8'] = 'Duration of the Movie'

# COLUMN 3 - Number Formats Used
sheet['C2'] = 'General'
sheet['C3'] = 'Date'
sheet['C4'] = 'Number'
sheet['C5'] = 'Currency'
sheet['C6'] = 'Accounting'
sheet['C7'] = 'Percentage'
sheet['C8'] = 'Time'

What’s finally left is to display the non-formatted and formatted values of the various parameters in the next two columns. For this tutorial, I have picked data for the movie ‘Underwater’ and hence let’s display the non-formatted data in Column D using the code snippet below.

# COLUMN 4 - Non-Formatted Values
sheet['D2'] = 'Underwater'
sheet['D3'] = 20200110
sheet['D4'] = 6.21
sheet['D5'] = 40000000
sheet['D6'] = 5000000
sheet['D7'] = 0.68
sheet['D8'] = 95

Creating Number Format Style Objects for Each Data in the Workbook

For this tutorial, to format the values using the right number formats we will make use of NameStyle sub-module. You can think of NameStyle as a template/design used for formatting cells by setting a set of formatting properties.

For this tutorial, we will be using two properties namely the ‘name’ and the ‘number_format’. We will define a different NameStyle object for each number format we have. Have a look at the code snippet below:

from openpyxl.styles import NamedStyle

generalStyle = NamedStyle(name='generalStyle', number_format='General')
dateStyle = NamedStyle(name='dateStyle', number_format='YYYY-MM-DD')
numberStyle = NamedStyle(name='numberStyle', number_format='0.00')
currencyStyle = NamedStyle(name='currencyStyle', number_format='"$"#,##0.00')
accountingStyle = NamedStyle(name='accountingStyle', number_format='#,##0.00')
percentageStyle = NamedStyle(name='percentageStyle', number_format='0.00%')
timeStyle = NamedStyle(name='timeStyle', number_format='hh:mm:ss')

For each NameStyle, the name parameter is simply the name assigned to a particular style and the number_format is the specific format that a particular number format type needs.

Let me explain each number format one after another in the list below:

For the first format, the number format is simply General. Next, we have the Date format which is set to YYYY-MM-DD and is simply one of the many formats to represent the date. Next, we simply have the Number format and set it to 0.00 as we want decimal points to also get included.

For Currency, the formatting is a little complicated, first of all, we mention the currency symbol we wish to have ($, €, ₹, or any other ). In this case, we have a Hollywood movie and hence using $ seems the appropriate option. After that, we set the format of the numerical value, in this case, it is set to #,##0.00 which implies using the commas format along with decimals (if needed).

For Accounting, the format is similar to currency just without the dollar sign. In this case, we have mentioned the format as #,##0.00. For Percentage, the number format is quite self-explanatory where its numeric value with % sign.

Lastly, for time the number format is set to hh:mm:ss, which is a time format for hours, minutes, and seconds.

Applying Number Formats to the Data

Our job is not yet done, we have to also apply these formats created for the specific cells with the help of the style function as shown in the snippet below:

sheet['E2'].style = generalStyle
sheet['E3'].style = dateStyle
sheet['E4'].style = numberStyle
sheet['E5'].style = currencyStyle
sheet['E6'].style = accountingStyle
sheet['E7'].style = percentageStyle
sheet['E8'].style = timeStyle

Lastly, let’s add the same data to these cells to see the formatted data using the code snippet below.

But you know the surprising part is that the data needs to be inserted in a particular format itself for openpyxl to recognize the correct format. To achieve this, we make use of the DateTime and the timeDelta library.

Also Read: How to change the format of date in Python

The datetime library helps us to work with dates and times in Python. The timedelta library helps us to manipulate time and dates in Python.

from datetime import datetime
from datetime import timedelta

sheet['E2'] = sheet['D2'].value
sheet['E3'] = datetime.strptime(str(sheet['D3'].value), '%Y%m%d')
sheet['E4'] = sheet['D4'].value
sheet['E5'] = sheet['D5'].value
sheet['E6'] = sheet['D6'].value
sheet['E7'] = sheet['D7'].value
sheet['E8'] = timedelta(minutes=sheet['D8'].value)

For the rest of the values, we have directly supplied the value of the corresponding cells. For dates, we have formatted the value using the strptime function and for the time that is provided in minutes, we will create a timedelta object which will take the minutes value.

Saving the Excel File

Finally, we will save the Excel file using the save function and providing a name for the file using the code snippet below:

workbook.save('Number_Formats.xlsx')

Output of the Code Implementation

When the complete code is executed, the Excel sheet is saved in the file system. Here is a snippet of the same:

Except for the border and styling, the formatted values are visible. Congratulations!