Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Python - Plotting charts in excel sheet with Data Tools using XlsxWriter module
XlsxWriter is a Python library using which one can perform multiple operations on excel files like creating, writing, arithmetic operations and plotting graphs.
Example
# import xlsxwriter module
import xlsxwriter
# Workbook() takes one, non-optional, argument which is the filename #that we want to create.
workbook = xlsxwriter.Workbook('updown_chart.xlsx')
# The workbook object is then used to add new worksheet via the #add_worksheet() method.
worksheet = workbook.add_worksheet()
# Create a new Format object to formats cells in worksheets using #add_format() method .
# here we create italic format object
italic = workbook.add_format({'italic': 1})
# Add the worksheet data that the charts will refer to.
Data1 = ['Subject', 'Mid Exam Score', 'End Exam Score']
Data2 = [
["Math", "Physics", "Computer", "Hindi", "English", "chemistry"],
[90, 78, 60, 80, 60, 90],
[45, 39, 30, 40, 30, 60]
]
# Write a row of data starting from 'A1' with bold format .
worksheet.write_row('A1', Data1, italic)
#Write a column of data starting from 'A2', 'B2', 'C2' respectively
worksheet.write_column('A2', Data2[0])
worksheet.write_column('B2', Data2[1])
worksheet.write_column('C2', Data2[2])
# set the wdith of B and C column
worksheet.set_column('B:C', 15)
# Create a chart object that can be added to a worksheet using add_chart() method.
# here we create a line chart object .
chart1 = workbook.add_chart({'type': 'line'})
# Adding the Up-Down Bars.
chart1.set_up_down_bars()
# Add a data series to a chartusing add_series method.
# Configure the first series. = Sheet1 !$A$1 is equivalent to #['Sheet1', 0, 0].
# note : spaces is not inserted in b/w = and Sheet1, Sheet1 and !
# if space is inserted it throws warning.
chart1.add_series({
'categories': '= Sheet1 !$A$2:$A$7',
'values': '= Sheet1 !$B$2:$B$7',
})
# Configure the second series.
chart1.add_series({
'categories': '= Sheet1 !$A$2:$A$7',
'values': '= Sheet1 !$C$2:$C$7',
})
# Add a chart title.
chart1.set_title({'name': 'Exam Score Distribution'})
# Add x-axis label
chart1.set_x_axis({'name': 'Subjects'})
# Add y-axis label
chart1.set_y_axis({'name': 'Marks'})
# Set an Excel chart style.
chart1.set_style(11)
# add chart to the worksheet with given offset values at the top-#left corner of a chart is anchored to cell D2
worksheet.insert_chart('D2', chart1, {'x_offset': 20, 'y_offset': 5})
# Finally, close the Excel file via the close() method.
workbook.close()
Updated on: 06-Aug-2020
96 Views
Advertisements