How to add a border in excel using Python Pandas?

ByTechSorber

Database or excel, two different types of software, are the best ways to store data, especially, in an organized way. There are many incidents when we would want to create data in a proper format, like jason in Python Dictionaries. Python plays a very important role in fulfilling such requirements.

Before doing anything, we will figure out if this job can be done using Pandas.

Can you use pandas in Excel?

Pandas is built on top of the Python programming language which is a fast, flexible, and easy-to-use open-source data analysis and manipulation tool. Python can help you even build websites!

Along with pandas using two other packages of python, we are going to learn here, how we can add borders and other cosmetic changes to the excel sheet that we will be creating.

You obviously need to install these packages. To do this is very simple in Python. You need to have pip installed in python. PIP is a program in python that assists you in installing packages in Python.

What is PIP and how to install PIP?

PIP can be expanded to either “preferred installer program” or “pip Installs packages“.

Before installing pip, make sure that pip is already installed on your Python. To do this run your terminal, console, or command prompt based on the operating system that you are using.

Type the below command:

pip help

If the command says it cannot be recognized, you have to install pip, or else, you are ready to write the program.

To install PIP, visit this link and download the file. The file is get-pip.py.

Once you download that, you can install it using 

curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py

or

python get-pip.py

from the path, you have downloaded it to.

Add the environment variable for pip so you can use the pip command from across the command prompt.

Once you have pip installed, you can install most of the packages that come with python.

How to install pandas?

Now that you have installed pandas, all you have to do is execute the below code:

pip install pandas

Similarly, you can install the other two packages that are needed by our program that we are going to write in the coming section.

pip install numpy

If you want to explore the possibilities of pandas in creating an excel file with a bar graph, please follow this link here, or merge excel cells from this article here.

How to use pandas in Excel?

Import pandas into our program as below:

import pandas as pan

In our case, this is used to write text into excel, using the help of one more tool, “xlsxwriter“, imported as follows:

import xlsxwriter

To store a multidimensional array, we would need to import NumPy, which does the job:

import numpy as nump

Until now it is straightforward. Now you need to create an array using NumPy you just imported.

datfrm = pan.DataFrame(nump.array([[10, 20, 30], [40, 50, 60], [70, 80, 90]]), columns=['I', 'II', 'III'])

In the above line, we are creating a multidimensional array first using NumPy, and that array will be an input to the data frame that is created using pandas. this is stored in the variable – datfrm.

To create an excel file use pandas and the function ExcelWriter where you pass the file name with xlsx extension and xlsxwriter engine as the two parameters. This creates an instance of the excel sheet where you initiate creating a sheet in the next line of code.

excelwriter = pan.ExcelWriter('sample.xlsx', engine='xlsxwriter')

As told earlier, this is where we are going to initiate creating the first sheet inside of excel, “Border_Sheet” and write the data frame that we created. The function “to_excel” is used to write a single object to an Excel .xlsx file. It is only necessary to specify a target file name.

datfrm.to_excel(writer, sheet_name='Border_Sheet')

So, once the sheet is created and data is written, we assign the excel as a whole and the sheet to the respective variables, like below.

excelbook = writer.book
excelsheet = writer.sheets['Border_Sheet']

How do you add a border in Excel using Python?

Now that both the excel file and the sheet within are assigned, we can make use of these two variables in our next line of code. We can declare a format for the border. Essentially, the number after bottom, top, etc… specifies the thickness of the border.

border_format = workbook.add_format({'bottom':1, 'top':1, 'left':1, 'right':1})

We can add several such kinds of formats to beautify or specify the excel sheets. One more way of adding a different kind of format goes as follows:

border_format = workbook.add_format({'bold': True, 'font_color': 'red'})

While the previous line of code adds a border around the data, the above line of code makes the text bold and the color of the font red.

The last line of code before saving or creating the excel goes as follows.

worksheet.conditional_format(xlsxwriter.utility.xl_range(2, 2, len(datfrm), len(datfrm.columns)), {'type': 'no_errors', 'format': border_format})

The above code specifies the range from where to where the border is applied to the data. It starts from the second cell and goes up to the last cell that contains the data frame. both with respect to the rows and columns.

While it is done, it assigns the format, which in this case is the border_format that we just created in the previous line of code.

Once everything is done, the xlsx file is created or saved in the path provided or in the present working directory, from the following line of code. 

writer.save()

Here is the full code that you can copy-paste to your favorite editor and run.

import pandas as pan
import xlsxwriter
import numpy as nump

datfrm = pan.DataFrame(nump.array([[10, 20, 30], [40, 50, 60], [70, 80, 90]]), columns=['I', 'II', 'III'])
excelwriter = pan.ExcelWriter('sample.xlsx', engine='xlsxwriter')
datfrm.to_excel(writer, sheet_name='Border_Sheet')
excelbook = writer.book
excelsheet = writer.sheets['Border_Sheet']

border_format = workbook.add_format({'bottom':1, 'top':1, 'left':1, 'right':1})
worksheet.conditional_format(xlsxwriter.utility.xl_range(2, 2, len(datfrm), len(datfrm.columns)), {'type': 'no_errors', 'format': border_format}) writer.save()

Similar Posts