Python Program to Plot Different types of Style Charts in Excel Sheet using XlsxWriter module

Below are the ways to plot the different types of style charts in an excel sheet using the XlsxWriter module in Python.

Charts are made up of a series of one or more data points. The series are made up of references to cell ranges. To plot the charts on an excel sheet, first create a chart object of the desired chart type ( i.e Stock chart, etc.). After generating chart objects, insert data into them before adding them to the sheet object.

Also Read: Python Interview Questions on Decision Making and Loops

Install the xlsxwriter using the below code

pip install xlsxwriter

Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/
Collecting xlsxwriter
Downloading XlsxWriter-3.0.3-py3-none-any.whl (149 kB)
|████████████████████████████████| 149 kB 5.0 MB/s
Installing collected packages: xlsxwriter
Successfully installed xlsxwriter-3.0.3

Program to Plot Different types of Style Charts in Excel Sheet using XlsxWriter module in Python

Approach:

  • Import the xlsxwriter library using the import function.
  • Pass the excel file path to the Workbook() function of xlsxwriter which is the file we want to create and store in a variable.
  • Give the chart types as a static list and store them in a variable.
  • Iterate in the chart types list using the for loop.
  • Create the new Worksheet using the add_worsheet() function and apply it to the above workbook object and store it in a variable.
  • Pass the Argument chartType(Iterator) and apply title() function to it.
  • Set the zoom option using the set_zoom() function and pass the zoom value to it as an argument.
  • Take a variable and initialize the StyleNumber with some random value.
  • Creating the 48 Built-in styles where each style is a different style.
  • Here the each chart dimension is 15 X 8.
  • Using the add_chart() method, create a chart object that can be added to a worksheet.
  • Pass the type of chart as an argument to the add_chart() function and apply it to the above workbook object.
  • Add the series of the data using the add_series() function.
  • Set the title to the chartObject using the set_title() function by passing the title styleNumber as value and key as “name” (Here the arguments are object).
  • Turn off the chart legend by passing the key ‘none’ as True to the set_legend() function.
  • Set the style of the chart using the set_style() function.
  • Add the chart Object to the worksheet by passing the row Number, Column, and chart object as argument to the insert_chart() function.
  • Increment the style Number by 1.
  • Create the new Worksheet using the add_worsheet() function and apply it to the above workbook object and store it in a variable.
  • Here this worksheet is used to store the data.
  • Create some random data list and store it in a variable.
  • Write the dataValues to the A1 column of the data Worksheet using the write_column() function and apply on the data Worksheet.
  • Hide the data Worksheet using the hide() function.
  • Close the workbook Object using the close() function.
  • The Exit of the Program.

Below is the Implementation:

# Import the xlsxwriter library using the import function
import xlsxwriter

# Pass the excel file path to the Workbook() function of xlsxwriter 
# which is the file we want to create and store it in a variable
workbookObj = xlsxwriter.Workbook('chartStyles.xlsx')

# Give the chart types as static list and store it in a variable
chartTypes = ['column', 'area']

# Iterate in the chart types list using the for loop
for chartType in chartTypes:
    
  # Create the new Worksheet using the add_worsheet() function and 
  # apply it to the above workbook object and store it in a variable
  #Pass the Argument chartType(Iterator) and apply title() function to it.
  newWorksheet = workbookObj.add_worksheet(chartType.title())

    # set the zoom option using the set_zoom() function and pass the zoom value to it as an argument
  newWorksheet.set_zoom(20)

    # Take a variable and initialize the StyleNumber with some random value
  styleNumber = 1


  # Creating the 48 Built in styles where each style is different style
  # Here the each chart dimension is 15 X 8
  for rowNum in range(0, 90, 15):
    for colNum in range(0, 64, 8):

            # Using the add_chart() method, create a chart object that can be added to a worksheet.
      # Pass the type of chart as an argument to the add_chart() function and apply it to the above workbook object
      chartObj = workbookObj.add_chart({'type': chartType})
            
      # Add the series of the data using the add_series() function
      chartObj.add_series({'values': '= Data !$A$1:$A$6'})

            # Set the title to the chartObject using the set_title() function by passing the title styleNumber as value and key as "name" (Here the arguments are object)
      chartObj.set_title ({'name': 'Style % d' % styleNumber})

      # Turn off the chart legend by passing the key 'none' as True to the set_legend() function
      chartObj.set_legend({'none': True})

      # Set the style of the chart using the set_style() function
      chartObj.set_style(styleNumber)

      # Add the chart Object to the worksheet by passing the 
      # row Number,Column and chartObject as argument to the insert_chart() function
      newWorksheet.insert_chart(rowNum, colNum, chartObj)

            # Increment the style Number by 1
      styleNumber += 1

# Create the new Worksheet using the add_worsheet() function and 
# apply it to the above workbook object and store it in a variable
# Here this worksheet is used to store the data
dataWorksheet = workbookObj.add_worksheet()

# Create some random data list and store it in a variable
dataValues = [20, 30, 40, 10, 20, 60]

# Write the dataValues to the A1 column of the data Worksheet using the write_column() function and apply on the data Worksheet

dataWorksheet.write_column('A1', dataValues)

# Hide the data Worksheet using the hide() function
dataWorksheet.hide()

# Close the workbook Object using the close() function.
workbookObj.close()

Output:

style charts style charts in excel

Leave a Comment