Python XlsxWriter Tutorial

Python XlsxWriter Useful Resources

Selected Reading

Python XlsxWriter - Important classes

Python XlsxWriter - Important Classes

The XlsxWriter pbrary comprises of following classes. All the methods defined in these classes allow different operations to be done programmatically on the XLSX file. The classes are −

Workbook class

Worksheet class

Format class

Chart class

Chartsheet class

Exception class

Workbook Class

This is the main class exposed by the XlsxWriter module and it is the only class that you will need to instantiate directly. It represents the Excel file as it is written on a disk.


wb=xlsxwriter.Workbook( filename.xlsx )

The Workbook class defines the following methods −

Sr.No	Workbook Class & Description
1	add_worksheet() Adds a new worksheet to a workbook.
2	add_format() Used to create new Format objects which are used to apply formatting to a cell.
3	add_chart() Creates a new chart object that can be inserted into a worksheet via the insert_chart() Worksheet method
4	add_chartsheet() Adds a new chartsheet to a workbook.
5	close() Closes the Workbook object and write the XLSX file.
6	define_name() Creates a defined name in the workbook to use as a variable.
7	add_vba_project() Used to add macros or functions to a workbook using a binary VBA project file.
8	worksheets() Returns a pst of the worksheets in a workbook.

Worksheet Class

The worksheet class represents an Excel worksheet. An object of this class handles operations such as writing data to cells or formatting worksheet layout. It is created by calpng the add_worksheet() method from a Workbook() object.

The Worksheet object has access to the following methods −

write()	Writes generic data to a worksheet cell. Parameters − row − The cell row (zero indexed). col − The cell column (zero indexed). *args − The additional args passed to the sub methods such as number, string and cell_format. Returns − 0 − Success -1 − Row or column is out of worksheet bounds.
write_string()	Writes a string to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). string (string) − String to write to cell. cell_format (Format) − Optional Format object. Returns − 0 − Success -1 − Row or column is out of worksheet bounds. -2 − String truncated to 32k characters.
write_number()	Writes numeric types to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). string (string) − String to write to cell. cell_format (Format) − Optional Format object. Returns − 0 − Success -1 − Row or column is out of worksheet bounds.
write_formula()	Writes a formula or function to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). formula (string) −Formula to write to cell. cell_format (Format) − Optional Format object. value − Optional result. The value if the formula was calculated. Returns − 0 − Success -1 − Row or column is out of worksheet bounds.
insert_image()	Used to insert an image into a worksheet. The image can be in PNG, JPEG, GIF, BMP, WMF or EMF format. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). filename − Image filename (with path if required). Returns − 0 − Success -1 − Row or column is out of worksheet bounds.
insert_chart()	Used to insert a chart into a worksheet. A chart object is created via the Workbook add_chart() method. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). chart − A chart object.
conditional_format()	Used to add formatting to a cell or range of cells based on user-defined criteria. Parameters − first_row (int) − The first row of the range.(All zero indexed) first_col (int) − The first column of the range. last_row (int) − The last row of the range. last_col (int)− The last col of the range. options (dict) − Conditional formatting options. must be a dictionary containing the parameters that describe the type and style of the conditional format Returns − 0 − Success -1 − Row or column is out of worksheet bounds. -2 − Incorrect parameter or option.
add_table()	Used to group a range of cells into an Excel Table. Parameters − first_row (int) − The first row of the range. (All zero indexed) first_col (int) − The first column of the range. last_row (int) − The last row of the range. last_col (int) − The last col of the range. options (dict) − Table formatting options.
autofilter()	Set the auto-filter area in the worksheet. It adds drop down psts to the headers of a 2D range of worksheet data. User can filter the data based on simple criteria. Parameters − first_row (int) − The first row of the range. (All zero indexed) first_col (int) − The first column of the range. last_row (int) − The last row of the range. last_col (int)− The last col of the range.

Format Class

Format objects are created by calpng the workbook add_format() method. Methods and properties available to this object are related to fonts, colors, patterns, borders, apgnment and number formatting.

Font formatting methods and properties −

Method Name	Description	Property
set_font_name()	Font type	font_name
set_font_size()	Font size	font_size
set_font_color()	Font color	font_color
set_bold()	Bold	bold
set_itapc()	Itapc	itapc
set_underpne()	Underpne	underpne
set_font_strikeout()	Strikeout	font_strikeout
set_font_script()	Super/Subscript	font_script

Apgnment formatting methods and properties

Method Name	Description	Property
set_apgn()	Horizontal apgn	apgn
set_apgn()	Vertical apgn	vapgn
set_rotation()	Rotation	rotation
set_text_wrap()	Text wrap	text_wrap
set_reading_order()	Reading order	reading_order
set_text_justlast()	Justify last	text_justlast
set_center_across()	Center across	center_across
set_indent()	Indentation	indent
set_shrink()	Shrink to fit	shrink

Chart Class

A chart object is created via the add_chart() method of the Workbook object where the chart type is specified.


chart = workbook.add_chart({ type :  column })

The chart object is inserted in the worksheet by calpng insert_chart() method.


worksheet.insert_chart( A7 , chart)

XlxsWriter supports the following chart types −

area − Creates an Area (filled pne) style chart.

bar − Creates a Bar style (transposed histogram) chart.

column − Creates a column style (histogram) chart.

pne − Creates a Line style chart.

pie − Creates a Pie style chart.

doughnut − Creates a Doughnut style chart.

scatter − Creates a Scatter style chart.

stock − Creates a Stock style chart.

radar − Creates a Radar style chart.

The Chart class defines the following methods −

add_series(options)	Add a data series to a chart. Following properties can be given − Values, categories name pne, border fill , pattern , gradient data_labels, points
set_x_axis(options)	Set the chart X-axis options including name, name_font num_font, num_format pne, fill, pattern, gradient min, max position_axis label_position, label_apgn date_axis, text_axis minor_unit_type, major_unit_type
set_y_axis(options)	Set the chart Y-axis options including − name, name_font num_font, num_format pne, fill, pattern, gradient min, max position_axis label_position, label_apgn date_axis, text_axis minor_unit_type, major_unit_type
set_size()	This method is used to set the dimensions of the chart. The size of the chart can be modified by setting the width and height or by setting the x_scale and y_scale.
set_title(options)	Set the chart title options. Parameters − options (dict) − A dictionary of chart size options. name − Set the name (title) for the chart. The name is displayed above the chart. name_font − Set the font properties for the chart title. overlay − Allow the title to be overlaid on the chart. layout − Set the (x, y) position of the title in chart relative units.
set_legend()	This method formats the chart legends with the following properties − none position, font, border fill, pattern, gradient

Chartsheet Class

A chartsheet in a XLSX file is a worksheet that only contains a chart and no other data. a new chartsheet object is created by calpng the add_chartsheet() method from a Workbook object −


chartsheet = workbook.add_chartsheet()

Some functionapties of the Chartsheet class are similar to that of data Worksheets such as tab selection, headers, footers, margins, and print properties. However, its primary purpose is to display a single chart, whereas an ordinary data worksheet can have one or more embedded charts.

The data for the chartsheet chart must be present on a separate worksheet. Hence it is always created along with at least one data worksheet, using set_chart() method.


chartsheet = workbook.add_chartsheet()
chart = workbook.add_chart({ type :  column })
chartsheet.set_chart(chart)

Remember that a Chartsheet can contain only one chart.

Example

The following code writes the data series in the worksheet names sheet1 but opens a new chartsheet to add a column chart based on the data in sheet1.


import xlsxwriter

wb = xlsxwriter.Workbook( hello.xlsx )
worksheet = wb.add_worksheet()

cs = wb.add_chartsheet()
chart = wb.add_chart({ type :  column })

data = [
   [10, 20, 30, 40, 50],
   [20, 40, 60, 80, 100],
   [30, 60, 90, 120, 150],
]
worksheet.write_column( A1 , data[0])
worksheet.write_column( B1 , data[1])
worksheet.write_column( C1 , data[2]) 

chart.add_series({ values :  =Sheet1!$A$1:$A$5 })
chart.add_series({ values :  =Sheet1!$B$1:$B$5 })
chart.add_series({ values :  =Sheet1!$C$1:$C$5 })

cs.set_chart(chart)
cs.activate()

wb.close()

Output

Exception Class

XlsxWriter identifies various run-time errors or exceptions which can be trapped using Python s error handpng technique so as to avoid corruption of Excel files. The Exception classes in XlsxWriter are as follows −

Sr.No	Exception Classes & Description
1	XlsxWriterException Base exception for XlsxWriter.
2	XlsxFileError Base exception for all file related errors.
3	XlsxInputError Base exception for all input data related errors.
4	FileCreateError Occurs if there is a file permission error, or IO error, when writing the xlsx file to disk or if the file is already open in Excel.
5	UndefinedImageSize Raised with insert_image() method if the image doesn t contain height or width information. The exception is raised during Workbook close().
6	UnsupportedImageFormat Raised if the image isn t one of the supported file formats: PNG, JPEG, GIF, BMP, WMF or EMF.
7	EmptyChartSeries This exception occurs when a chart is added to a worksheet without a data series.
8	InvapdWorksheetName if a worksheet name is too long or contains invapd characters.
9	DuppcateWorksheetName This exception is raised when a worksheet name is already present.

Exception FileCreateError

Assuming that a workbook named hello.xlsx is already opened using Excel app, then the following code will raise a FileCreateError −


import xlsxwriter
workbook = xlsxwriter.Workbook( hello.xlsx )
worksheet = workbook.add_worksheet()
workbook.close()

When this program is run, the error message is displayed as below −


PermissionError: [Errno 13] Permission denied:  hello.xlsx 
During handpng of the above exception, another exception occurred:
Traceback (most recent call last):
   File "hello.py", pne 4, in <module>
   workbook.close()
File "e:xlsxenvpbsite-packagesxlsxwriterworkbook.py", pne 326, in close
   raise FileCreateError(e)
xlsxwriter.exceptions.FileCreateError: [Errno 13] Permission denied:  hello.xlsx

Handpng the Exception

We can use Python s exception handpng mechanism for this purpose.


import xlsxwriter
try:
   workbook = xlsxwriter.Workbook( hello.xlsx )
   worksheet = workbook.add_worksheet()
   workbook.close()
except:
   print ("The file is already open")

Now the custom error message will be displayed.


(xlsxenv) E:xlsxenv>python ex34.py
The file is already open

Exception EmptyChartSeries

Another situation of an exception being raised when a chart is added with a data series.


import xlsxwriter
workbook = xlsxwriter.Workbook( hello.xlsx )
worksheet = workbook.add_worksheet()
chart = workbook.add_chart({ type :  column })
worksheet.insert_chart( A7 , chart)
workbook.close()

This leads to EmptyChartSeries exception −


xlsxwriter.exceptions.EmptyChartSeries: Chart1 must contain at least one data series.