Openpyxl Library To Write Excel File With Styles

Openpyxl probably the most popular python library for read/write Excel xlsx files. In this article, I will be sharing with you how to write data into excel file with some formatting.

Let’s get started with openpyxl.

If you have not yet installed this openpyxl library in your working environment, you may use the below command to install it.

And we shall import the library and modules at the beginning of the script:

import openpyxl 
from openpyxl.styles import Alignment, Border, Side, Font

Now I am going to create a new excel with the sheet name as “Demo”:

workbook = openpyxl.Workbook() 
sheet = workbook.active sheet.title = "Demo"

Assuming if you have the below data that you want to write into the excel file:

raw_data = [["University Name", "No. of Students", "Address", "Contact"], ["National University of Singapore", "35908", "21 Lower Kent Ridge Rd, Singapore 119077", "68741616"], ["Nanyang Technological University", "31687", "50 Nanyang Ave, 639798", "67911744"], ["Singapore Management University", "8182", "81 Victoria St, Singapore 188065", "68280100"]]

You can loop through the list to get the value and assign it to a particular excel cell. Note that the excel row and columns always starts from 1.

for row_idx, rec in enumerate(raw_data): 
    for col_idx, val in enumerate(rec): 
        sheet.cell(row=row_idx+1, column=col_idx+1).value = val

if you save your data now via the below code, you will see that the saved excel does not come with any formatting (default formatting)

workbook.save("Demo.xlsx")

As you can see, the format does not look good and some of the column width needs to be adjusted in order to see the full content. Let’s apply some styling to the cells.

Let’s draw the borders for each of the cells, you can specify the color of the border as well as the border style. for more border styles, you can refer to this openpyxl document. you can also use different style for different side of the borders.

thin = Side(border_style="thin", color="303030") 
black_border = Border(top=thin, left=thin, right=thin, bottom=thin)

you can also give different width for the different columns as per below :

sheet.column_dimensions["A"].width = 27 sheet.column_dimensions["B"].width = 12 sheet.column_dimensions["C"].width = 33 sheet.column_dimensions["D"].width = 8

Define your own font style:

font = Font(name='Calibri', size=9, bold=True, color='07101c')

Define the alignment style, and you can definitely use different alignment style for different columns. Here I just defined 1 style for all cells.

align = Alignment(horizontal="center", wrap_text= True, vertical="center")

Next, Let’s apply the above styles to each of the cell and save the worksheet:

for label in ["A", "B", "C", "D"]: 
    for col_idx in range(row_num): 
        idx = label + str(col_idx + 1) 
        sheet[idx].alignment = algin 
        sheet[idx].font = font 
        sheet[idx].border = black_border
        workbook.save("Demo.xlsx")

The final output should be similar to the below, which looks much better with the styling.

As per always, welcome to any comments or questions.

Originally published at https://www.codeforests.com on June 11, 2020.