Using ExcelWriter for Creating Visualizations in Excel by Python Code

Excel is widely used for data analysis and has a lot of functionalities for analyzing, manipulating, visualizing, etc. Using excel should be one of the main skills required for a Data Analyst, Product Analyst, and Business Analyst. It helps in understanding the data and how we can use it for generating useful insights.

Python is also widely used for Data Analysis purposes and also overcomes the drawbacks of Excel. With a little knowledge of Python, we can enhance our Data Analysis skills and also generate more useful insights.

Combining both Excel and Python can be fruitful and makes Data Analysis more interesting. ExcelWriter is used for performing different Excel operations using Python. It provides a wide variety of functionalities like creating an Excel, writing data into an Excel sheet, creating visualizations in Excel, etc.

In this article, we will explore ExcelWriter and create some visualizations using it.

Let’s get started…

Excelwriter is defined in Pandas so we don’t need to install it separately.

In this step, we will import all the libraries that are required for reading Excel and creating visualizations in Excel using Python.

import pandas as pd
import numpy as np

For this article, we will the famous Diabetes dataset that can be downloaded from online sources. Also, we will create an excel file using excel writer where we will create visualizations.

df = pd.DataFrame(pd.read_csv("/content/test.csv"))
writer = pd.ExcelWriter('diabetes.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow=2)

In this step, we will create different types of visualization, write them to the excel file we created and then save the visualization. Before creating the visualizations we will also create objects for the writer to use for further manipulation.

The code given below will create the objects and different graphs that are Bar Chart, Scatter Plot, Line Plot, and Area Chart.

book =
sheet = writer.sheets['Sheet1']
# Title
bold = book.add_format({'bold': True, 'size': 24})
sheet.write('A1', 'Diabetes', bold)

format1 = book.add_format({'font_color': '#E93423'})
sheet.conditional_format('B4:E8', {'type': 'cell', 'criteria': '<=', 'value': 0, 'format': format1})
# Bar Chart
chart = book.add_chart({'type': 'column'})
chart.add_series({'values': '=Sheet1!B4:B90', 'name': '=Sheet1!B3', 'categories': '=Sheet1!$A$4:$A$8'})

Continue reading:—-7f60cf5620c9—4