”;
The XlsxWriter library 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 list 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 calling 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 −
Returns −
|
write_string() |
Writes a string to the cell specified by row and column.
Parameters −
Returns −
|
write_number() |
Writes numeric types to the cell specified by row and column.
Parameters −
Returns −
|
write_formula() |
Writes a formula or function to the cell specified by row and column.
Parameters −
Returns −
|
insert_image() |
Used to insert an image into a worksheet. The image can be in PNG, JPEG, GIF, BMP, WMF or EMF format.
Parameters −
Returns −
|
insert_chart() |
Used to insert a chart into a worksheet. A chart object is created via the Workbook add_chart() method.
Parameters −
|
conditional_format() |
Used to add formatting to a cell or range of cells based on user-defined criteria.
Parameters −
Returns −
|
add_table() |
Used to group a range of cells into an Excel Table.
Parameters −
|
autofilter() |
Set the auto-filter area in the worksheet. It adds drop down lists to the headers of a 2D range of worksheet data. User can filter the data based on simple criteria.
Parameters −
|
Format Class
Format objects are created by calling the workbook add_format() method. Methods and properties available to this object are related to fonts, colors, patterns, borders, alignment 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_italic() | Italic | ”italic” |
set_underline() | Underline | ”underline” |
set_font_strikeout() | Strikeout | ”font_strikeout” |
set_font_script() | Super/Subscript | ”font_script” |
Alignment formatting methods and properties
Method Name | Description | Property |
---|---|---|
set_align() | Horizontal align | ”align” |
set_align() | Vertical align | ”valign” |
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 calling insert_chart() method.
worksheet.insert_chart(''A7'', chart)
XlxsWriter supports the following chart types −
-
area − Creates an Area (filled line) style chart.
-
bar − Creates a Bar style (transposed histogram) chart.
-
column − Creates a column style (histogram) chart.
-
line − 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 −
|
set_x_axis(options) |
Set the chart X-axis options including
|
set_y_axis(options) |
Set the chart Y-axis options including −
|
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 −
|
set_legend() |
This method formats the chart legends with the following properties −
|
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 calling the add_chartsheet() method from a Workbook object −
chartsheet = workbook.add_chartsheet()
Some functionalities 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 handling 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 |
InvalidWorksheetName
if a worksheet name is too long or contains invalid characters. |
9 |
DuplicateWorksheetName
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 handling of the above exception, another exception occurred: Traceback (most recent call last): File "hello.py", line 4, in <module> workbook.close() File "e:xlsxenvlibsite-packagesxlsxwriterworkbook.py", line 326, in close raise FileCreateError(e) xlsxwriter.exceptions.FileCreateError: [Errno 13] Permission denied: ''hello.xlsx''
Handling the Exception
We can use Python”s exception handling 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.
”;