Create and write on excel file using xlsxwriter module

In this article we look at 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 many more, including:

• 100% compatible Excel XLSX files.
• Full formatting.
• Merged cells.
• Defined names.
• Charts.
• Autofilters.
• Data validation and drop down lists.
• Conditional formatting.
• Worksheet PNG/JPEG/GIF/BMP/WMF/EMF images.
• Rich multi-format strings.
• Cell comments.
• Integration with Pandas.
• Textboxes.
• Support for adding Macros.
• Memory optimization mode for writing large files.

Installation

pip install XlsxWriter

Examples

Here are some basic examples

# import xlsxwriter module
import xlsxwriter
 
# create workbook
workbook = xlsxwriter.Workbook('test.xlsx')
 
# add new worksheet via the add_worksheet() method.
worksheet = workbook.add_worksheet()
 
# write data via the write() method.
worksheet.write('A1', 'Country')
worksheet.write('B1', 'Capital')
worksheet.write('C1', 'Domain Name')
worksheet.write('D1', 'Population')
 
# close the Excel file via the close() method.
workbook.close()

This example writes data to

# import xlsxwriter module
import xlsxwriter
 
workbook = xlsxwriter.Workbook('test2.xlsx')
worksheet = workbook.add_worksheet()
 
# Start from the first cell.
# Rows and columns are zero indexed.
row = 0
column = 0
 
content = ["Germany", "France", "Spain", "Italy", "Portugal"]
 
# iterating through content list
for item in content :
 
    # write operation perform
    worksheet.write(row, column, item)
 
    # incrementing the value of row by one
    # with each iterations.
    row += 1
     
workbook.close()

# import xlsxwriter module
import xlsxwriter
 
workbook = xlsxwriter.Workbook('test3.xlsx')
 
# By default worksheet names in the spreadsheet will be
worksheet = workbook.add_worksheet("Countries")
 
# Some data we want to write to the worksheet.
countries = (
    ['Germany', 'Berlin'],
    ['Spain', 'Madrid'],
    ['France', 'Paris'],
    ['Italy', 'Rome'],
)
 
# Start from the first cell.
row = 0
col = 0
 
# Iterate over the data and write it out row by row.
for name, score in (countries):
    worksheet.write(row, col, name)
    worksheet.write(row, col + 1, capital)
    row += 1
 
workbook.close()

Links

https://xlsxwriter.readthedocs.io/