Create excel file, Insert image, Draw Bar Graphs in excel files in python using xlsxwriter

Xlsxwriter is a python module through which we can write data to Excel 2007+ XLSX file format. In this blog post we learn to write data, insert images and draw graphs in the excel file.

Write Data to excel file:

To  write data into excel file first we need to create a file and add a sheet to the file. By default the names of the sheets will be Sheet1, Sheet2 and etc., or we can create sheets with our own names.

import xlsxwriter

# Create an new Excel file and add a worksheet.

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet() # Sheet1
worksheet = workbook.add_worksheet(‘WorkLog’) # WorkLog

In the above snippet we are create an excel file with name test.xlsx and add  two sheets to the file as ‘Sheet1’ and ‘WorkLog’

Now we can write the data to the excel sheet with in two ways using write(), one way is write(row, column, data) where row is the row number and column is the column number and data is the data that we need to write into the file. Second way is to write the into the cell name directly

worksheet.write(0, 0, ‘MicroPyramid’)
worksheet.write(‘A1’, ‘MicroPyramid’)

The above snippet will write the data in the first cell of the sheet. In the similar way we can write data into other cells by finding the row and column numbers.

Insert Image to Excel File:

We can insert image into the excel sheet with insert_image() function. We can also set the height and width of the images in the excel sheet by setting the width and height of the cell where the image is going to be inserted.

worksheet.insert_image('B5', 'logo.png')
worksheet.insert_image(2, 4', 'logo.png')

Draw Charts in the excel Sheet:

We can also draw charts in the excel sheets. We can draw Bar and line charts in the excel sheet using xlsxwriter module using add_chart() method

chart = workbook.add_chart({'type': 'column'})
# Write some data to add to plot on the chart.

data = [
    [5, 10, 15, 20, 25],   

worksheet.write_column('A1', data[0])
# Configure the charts. In simplest case we just add some data series.
chart.add_series({'values': '=Sheet1!$A$1:$A$5'})
# Insert the chart into the worksheet.
worksheet.insert_chart('A7', chart)

Here ‘type’ can be of any thing in ‘bar’ or ‘column’ or anything.


UPDATE: we recently found amonther blog post useful to work on excel with python here

Posted On 24 May 2015 By MicroPyramid

Need any Help in your Project?Let's Talk

Latest Comments
Which is the best Python framework for web development?

Python is a general-purpose programming language. We can use python for so many applications and including web applications. Which provides common backend fast reliable and …

Continue Reading...
Unit testing with selenium-python

Unit testing with selenium-python. Unit test case example to test the front-end

Continue Reading...
Tips to choose the best custom software development company

Choosing the best company for your software development needs is the most important step. This blog explains you tips to outsource custom software development services.

Continue Reading...
WSGI explanation with simple APP

The main goal of WSGI is to facilitate easy interconnection of servers and web frameworks/applications. WSGI defines a standerd API for web servers(uWSGI, Twisted, Gunicorn) …

Continue Reading...
Understanding self and __init__ method in python Class.

Understand self and __init__ method in python Class?
Before understanding the "self" and "__init__" methods in python class, it's very helpful if we have the idea …

Continue Reading...

Subscribe To our news letter

Subscribe and Stay Updated about our Webinars, news and articles on Django, Python, Machine Learning, Amazon Web Services, DevOps, Salesforce, ReactJS, AngularJS, React Native.
* We don't provide your email contact details to any third parties