Build Excel Chart with Python by XLSXWRITER. Full Explanation and Example

This Python video tutorial shows how to build a custom and stylish Chart in Excel by Python programming language using xlsxwriter module. Video perfectly fits to Python users who are interesting into Python automation tasks between Python and Excel.

XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format. XlsxWriter can be used to write text, numbers, formulas and hyperlinks to multiple worksheets and it supports features such as formatting and much more, including: 100 percent compatible Excel XLSX files.

This video demonstrate how to create a custom and fully labeled chart with one and two series of data in Excel by Python. Jupyter Notebook framework was used.

The contect of video is:
#1 - 0:05 How to set up a Worksheet in Excel by Python
#2 - 0:54 How to define a Series of Data (I used random data as example dataset)
#3 - 2:09 Create a new Chart object
#4 - 2:39 Add Series of Data to the Chart object
#! - 3:23 Save new Excel file from Python
#5 - 4:10 Change and modify Chart style
#6 - 5:06 Add Data Labels to the Chart and set a Font to these labels
#7 - 6:07 Add Trendline to the Chart (and display an equation trend function)
#8 - 7:36 Add Markers to the graph and give them a style (shape, color, etc.)
#9 - 8:58 Add more than one Series of Data to the Chart (graph) object.
#10 - 10:19 Change Chart type
#11 - 10:51 Build a Chart on the Specific cell in Excel.
#12 - 11:48 XLSXWRITER Official Documentation

Remember that XLSXWRITER support all standard colors as green, red, black, etc. and HEX color codes also.

Important to know: XLSXWRITER cannot be used to modify or write to an existing Excel XLSX file. Use openpyxl for such purposes. OpenPyxl can read existing excel files and write data back to it.

I hope this video help you to build a really impressive and full custom made charts in Excel in Python side. I am sure that this is important for construct Python based automation systems in business world. Xlsxwriter is like a bridge between Python and Excel.

If you have any comments, please write it down below!
__________________

And you always contact me on LinkedIn. - Vytautas Bielinskas

Best regards from Lithuania

[derrik-bosse]

Best one on the topic, and I've seen almost everything

[victormachado]

Excellent video, thanks for sharing your knowledge, I'm new to Python and I've learned a lot by following your videos

[boriskrasilnikovs]

Thank you for that Video Tutorial.
How can i define a template for charting?
I would like to thank you in advance.

[akvikramrk]

how do we plot graph with time vs data in xlswriter

[leica]

Excellent video - I am just figuring out how to make scatter plots in xlsxwriter. Is the Jupyter notebook available for download?

[khalilj.]

Amazing!
Thank you for this great and valuable content. I’m not a pro in Python but I try to learn it. I have a problem to get this script to work, or it halv-works.
I try to generate combinations and print those to an excel file. When I run the script only the Combinations are shown on screen, but no excel file is created for those combinations. What I’m doing wrong here? Strange thing is I got no error messsge either. Can you please give me any advice, I appreciate any suggestion or help. Thank you
Here’s the code:

import itertools
import os
import xlsxwriter

row = 0
col = 0

combin = itertools.combinations(range(1, 15), 7)
count_combin = []
count = 0

for i in combin:
count_combin.append(i)
print(count_combin.index(i)+1, i)
workbook = xlsxwriter.Workbook( os.getcwd() + '\\' + 'combination.xlsx')
worksheet =
worksheet.write(row, col, i[0])
worksheet.write(row, col+1, i[1])
worksheet.write(row, col+2, i[2])
worksheet.write(row, col+3, i[3])
worksheet.write(row, col+4, i[4])
worksheet.write(row, col+5, i[5])
worksheet.write(row, col+6, i[6])

row += 1

workbook.close

print('number of combinations', len(count_combin))


Hello!
Thank’s for a all this valuable content. I’m not a pro in Python but I try to learn it. I have a problem to get this script to work, or it halv-works but something is missing or incorrect.
I try to generate combinations and print those to an excel file. When I run the script only the Combinations are shown on screen, but no excel file is created for those combinations. What I’m doing wrong here? Strange thing is I got no error messsge either. Can you please give me any advice, I appreciate any suggestion or help. Thank you
Here’s the code:

import itertools
import os
import xlsxwriter

row = 0
col = 0

combin = itertools.combinations(range(1, 15), 7)
count_combin = []
count = 0

for i in combin:
count_combin.append(i)
print(count_combin.index(i)+1, i)
workbook = xlsxwriter.Workbook( os.getcwd() + '\\' + 'combination.xlsx')
worksheet =
worksheet.write(row, col, i[0])
worksheet.write(row, col+1, i[1])
worksheet.write(row, col+2, i[2])
worksheet.write(row, col+3, i[3])
worksheet.write(row, col+4, i[4])
worksheet.write(row, col+5, i[5])
worksheet.write(row, col+6, i[6])

row += 1

workbook.close

print('number of combinations', len(count_combin))

[osmanbayar]

Is there any way to add charts on existing excel file?

[ajayvaishnav]

hello, line chart is not displayed
I don't know the reason...may be it won't work on ms excel 2010
coz i used excel 2010
there were no errors while running whole command again.
please reply

[praveenthallam]

Hi, graph will have x axis, y axis data,
But here u gave only one axis data? ❓