Criando arquivos Excel com Python e XlsxWriter

Prévia do material em texto

Creating Excel files with Python and
XlsxWriter
Release 1.4.3
John McNamara
May 12, 2021
CONTENTS
1 Introduction 3
2 Getting Started with XlsxWriter 5
2.1 Installing XlsxWriter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.2 Running a sample program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.3 Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3 Tutorial 1: Create a simple XLSX file 9
4 Tutorial 2: Adding formatting to the XLSX File 13
5 Tutorial 3: Writing different types of data to the XLSX File 17
6 The Workbook Class 21
6.1 Constructor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
6.2 workbook.add_worksheet() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
6.3 workbook.add_format() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
6.4 workbook.add_chart() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.5 workbook.add_chartsheet() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
6.6 workbook.close() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
6.7 workbook.set_size() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
6.8 workbook.tab_ratio() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
6.9 workbook.set_properties() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
6.10 workbook.set_custom_property() . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
6.11 workbook.define_name() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
6.12 workbook.add_vba_project() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
6.13 workbook.set_vba_name() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
6.14 workbook.worksheets() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
6.15 workbook.get_worksheet_by_name() . . . . . . . . . . . . . . . . . . . . . . . . . . 38
6.16 workbook.get_default_url_format() . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
6.17 workbook.set_calc_mode() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
6.18 workbook.use_zip64() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
6.19 workbook.read_only_recommended() . . . . . . . . . . . . . . . . . . . . . . . . . . 39
7 The Worksheet Class 41
7.1 worksheet.write() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
i
7.2 worksheet.add_write_handler() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
7.3 worksheet.write_string() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
7.4 worksheet.write_number() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
7.5 worksheet.write_formula() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
7.6 worksheet.write_array_formula() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
7.7 worksheet.write_dynamic_array_formula() . . . . . . . . . . . . . . . . . . . . . . . 50
7.8 worksheet.write_blank() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
7.9 worksheet.write_boolean() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
7.10 worksheet.write_datetime() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
7.11 worksheet.write_url() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
7.12 worksheet.write_rich_string() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
7.13 worksheet.write_row() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
7.14 worksheet.write_column() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
7.15 worksheet.set_row() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
7.16 worksheet.set_row_pixels() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
7.17 worksheet.set_column() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
7.18 worksheet.set_column_pixels() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
7.19 worksheet.insert_image() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
7.20 worksheet.insert_chart() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
7.21 worksheet.insert_textbox() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
7.22 worksheet.insert_button() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
7.23 worksheet.data_validation() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
7.24 worksheet.conditional_format() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
7.25 worksheet.add_table() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
7.26 worksheet.add_sparkline() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
7.27 worksheet.write_comment() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
7.28 worksheet.show_comments() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
7.29 worksheet.set_comments_author() . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
7.30 worksheet.get_name() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
7.31 worksheet.activate() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
7.32 worksheet.select() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
7.33 worksheet.hide() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
7.34 worksheet.set_first_sheet() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
7.35 worksheet.merge_range() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
7.36 worksheet.autofilter() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
7.37 worksheet.filter_column() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
7.38 worksheet.filter_column_list() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
7.39 worksheet.set_selection() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
7.40 worksheet.freeze_panes() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
7.41 worksheet.split_panes() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
7.42 worksheet.set_zoom() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
7.43 worksheet.right_to_left() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
7.44 worksheet.hide_zero() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
7.45 worksheet.set_background() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
7.46 worksheet.set_tab_color() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
7.47 worksheet.protect() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
7.48 worksheet.unprotect_range() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
7.49 worksheet.set_default_row() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
ii
7.50 worksheet.outline_settings() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
7.51 worksheet.set_vba_name() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
7.52 worksheet.ignore_errors() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
8 The Worksheet Class (Page Setup) 103
8.1 worksheet.set_landscape() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
8.2 worksheet.set_portrait() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
8.3 worksheet.set_page_view() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
8.4 worksheet.set_paper() . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . . . . . 104
8.5 worksheet.center_horizontally() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
8.6 worksheet.center_vertically() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
8.7 worksheet.set_margins() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
8.8 worksheet.set_header() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
8.9 worksheet.set_footer() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
8.10 worksheet.repeat_rows() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
8.11 worksheet.repeat_columns() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
8.12 worksheet.hide_gridlines() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
8.13 worksheet.print_row_col_headers() . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
8.14 worksheet.hide_row_col_headers() . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
8.15 worksheet.print_area() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
8.16 worksheet.print_across() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
8.17 worksheet.fit_to_pages() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
8.18 worksheet.set_start_page() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
8.19 worksheet.set_print_scale() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
8.20 worksheet.set_h_pagebreaks() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
8.21 worksheet.set_v_pagebreaks() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
9 The Format Class 117
9.1 Creating and using a Format object . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
9.2 Format Defaults . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
9.3 Modifying Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
9.4 Number Format Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
9.5 Number Formats in different locales . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
9.6 Format methods and Format properties . . . . . . . . . . . . . . . . . . . . . . . . . 125
9.7 format.set_font_name() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
9.8 format.set_font_size() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
9.9 format.set_font_color() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
9.10 format.set_bold() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
9.11 format.set_italic() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
9.12 format.set_underline() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
9.13 format.set_font_strikeout() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
9.14 format.set_font_script() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
9.15 format.set_num_format() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
9.16 format.set_locked() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
9.17 format.set_hidden() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
9.18 format.set_align() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
9.19 format.set_center_across() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
9.20 format.set_text_wrap() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
iii
9.21 format.set_rotation() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
9.22 format.set_reading_order() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
9.23 format.set_indent() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
9.24 format.set_shrink() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
9.25 format.set_text_justlast() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
9.26 format.set_pattern() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
9.27 format.set_bg_color() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
9.28 format.set_fg_color() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
9.29 format.set_border() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
9.30 format.set_bottom() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
9.31 format.set_top() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
9.32 format.set_left() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
9.33 format.set_right() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
9.34 format.set_border_color() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
9.35 format.set_bottom_color() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
9.36 format.set_top_color() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
9.37 format.set_left_color() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
9.38 format.set_right_color() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
9.39 format.set_diag_border() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
9.40 format.set_diag_type() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
9.41 format.set_diag_color() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
10 The Chart Class 149
10.1 chart.add_series() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
10.2 chart.set_x_axis() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
10.3 chart.set_y_axis() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
10.4 chart.set_x2_axis() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
10.5 chart.set_y2_axis() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
10.6 chart.combine() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
10.7 chart.set_size() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
10.8 chart.set_title() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
10.9 chart.set_legend() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
10.10chart.set_chartarea() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
10.11chart.set_plotarea() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
10.12chart.set_style() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
10.13chart.set_table() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
10.14chart.set_up_down_bars() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
10.15chart.set_drop_lines() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
10.16chart.set_high_low_lines() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
10.17chart.show_blanks_as() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
10.18chart.show_hidden_data() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
10.19chart.set_rotation() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
10.20chart.set_hole_size() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
11 The Chartsheet Class 175
11.1 chartsheet.set_chart() . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . 176
11.2 Worksheet methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
11.3 Chartsheet Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
iv
12 The Exceptions Class 179
12.1 Exception: XlsxWriterException . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
12.2 Exception: XlsxFileError . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
12.3 Exception: XlsxInputError . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
12.4 Exception: FileCreateError . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
12.5 Exception: UndefinedImageSize . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
12.6 Exception: UnsupportedImageFormat . . . . . . . . . . . . . . . . . . . . . . . . . . 181
12.7 Exception: FileSizeError . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
12.8 Exception: EmptyChartSeries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
12.9 Exception: DuplicateTableName . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
12.10Exception: InvalidWorksheetName . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
12.11Exception: DuplicateWorksheetName . . . . . . . . . . . . . . . . . . . . . . . . . . 184
13 Working with Cell Notation 185
13.1 Row and Column Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
13.2 Relative and Absolute cell references . . . . . . . . . . . . . . . . . . . . . . . . . . 186
13.3 Defined Names and Named Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
13.4 Cell Utility Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
14 Working with and Writing Data 191
14.1 Writing data to a worksheet cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
14.2 Writing unicode data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
14.3 Writing lists of data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
14.4 Writing dicts of data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
14.5 Writing dataframes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
14.6 Writing user defined types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
15 Working with Formulas 205
15.1 Non US Excel functions and syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
15.2 Formula Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
15.3 Dynamic Array support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
15.4 Dynamic Arrays - The Implicit Intersection Operator “@” . . . . . . . . . . . . . . . . 209
15.5 Dynamic Arrays - The Spilled Range Operator “#” . . . . . . . . . . . . . . . . . . . 211
15.6 The Excel 365 LAMBDA() function . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
15.7 Formulas added in Excel 2010 and later . . . . . . . . . . . . . . . . . . . . . . . . . 214
15.8 Using Tables in Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
15.9 Dealing with formula errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
16 Working with Dates and Time 221
16.1 Default Date Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
16.2 Timezone Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
17 Working with Colors 227
18 Working with Charts 229
18.1 Chart Value and Category Axes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
18.2 Chart Series Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
18.3 Chart series option: Marker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
18.4 Chart series option: Trendline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
v
18.5 Chart series option: Error Bars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
18.6 Chart series option: Data Labels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
18.7 Chart series option: Custom Data Labels . . . . . . . . . . . . . . . . . . . . . . . . 251
18.8 Chart series option: Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
18.9 Chart series option: Smooth . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
18.10Chart Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
18.11Chart formatting: Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
18.12Chart formatting: Border . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
18.13Chart formatting: Solid Fill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
18.14Chart formatting: Pattern Fill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
18.15Chart formatting: Gradient Fill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
18.16Chart Fonts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
18.17Chart Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
18.18Date Category Axes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272
18.19Chart Secondary Axes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272
18.20Combined Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
18.21Chartsheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
18.22Charts from Worksheet Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
18.23Chart Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
18.24Chart Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
19 Working with Object Positioning 279
19.1 Object scaling due to automatic row height adjustment . . . . . . . . . . . . . . . . 280
19.2 Object Positioning with Cell Moving and Sizing . . . . . . . . . . . . . . . . . . . . . 281
19.3 Image sizing and DPI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
19.4 Reporting issues with image insertion . . . . . . . . . . . . . . . . . . . . . . . . . . 284
20 Working with Autofilters 285
20.1 Applying an autofilter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
20.2 Filter data in an autofilter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286
20.3 Setting a filter criteria for a column . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
20.4 Setting a column list filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
20.5 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
21 Working with Data Validation 291
21.1 data_validation() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
21.2 Data Validation Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
22 Working with Conditional Formatting 303
22.1 The conditional_format() method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
22.2 Conditional Format Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
22.3 Conditional Formatting Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
23 Working with Worksheet Tables 329
23.1 add_table() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330
23.2 data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
23.3 header_row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
23.4 autofilter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
vi23.5 banded_rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
23.6 banded_columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
23.7 first_column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
23.8 last_column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
23.9 style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
23.10name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
23.11total_row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339
23.12columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339
23.13Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
24 Working with Textboxes 345
24.1 Textbox options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
24.2 Textbox size and positioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
24.3 Textbox Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
24.4 Textbox formatting: Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
24.5 Textbox formatting: Border . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
24.6 Textbox formatting: Solid Fill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
24.7 Textbox formatting: Gradient Fill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
24.8 Textbox formatting: Fonts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
24.9 Textbox formatting: Align . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
24.10Textbox formatting: Text Rotation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
24.11Textbox Textlink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
24.12Textbox Hyperlink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
25 Working with Sparklines 361
25.1 The add_sparkline() method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
25.2 range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364
25.3 type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364
25.4 style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364
25.5 markers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
25.6 negative_points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
25.7 axis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
25.8 reverse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
25.9 weight . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
25.10high_point, low_point, first_point, last_point . . . . . . . . . . . . . . . . . . . . . . . 366
25.11max, min . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
25.12empty_cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
25.13show_hidden . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
25.14date_axis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
25.15series_color . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
25.16location . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
25.17Grouped Sparklines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
25.18Sparkline examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
26 Working with Cell Comments 369
26.1 Setting Comment Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
27 Working with Outlines and Grouping 373
vii
27.1 Outlines and Grouping in XlsxWriter . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
28 Working with Memory and Performance 379
28.1 Performance Figures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
29 Working with VBA Macros 381
29.1 The Excel XLSM file format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
29.2 How VBA macros are included in XlsxWriter . . . . . . . . . . . . . . . . . . . . . . 382
29.3 The vba_extract.py utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
29.4 Adding the VBA macros to a XlsxWriter file . . . . . . . . . . . . . . . . . . . . . . . 382
29.5 Setting the VBA codenames . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
29.6 What to do if it doesn’t work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
30 Working with Python Pandas and XlsxWriter 385
30.1 Using XlsxWriter with Pandas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
30.2 Accessing XlsxWriter from Pandas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
30.3 Adding Charts to Dataframe output . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
30.4 Adding Conditional Formatting to Dataframe output . . . . . . . . . . . . . . . . . . 388
30.5 Formatting of the Dataframe output . . . . . . . . . . . . . . . . . . . . . . . . . . . 389
30.6 Formatting of the Dataframe headers . . . . . . . . . . . . . . . . . . . . . . . . . . 391
30.7 Adding a Dataframe to a Worksheet Table . . . . . . . . . . . . . . . . . . . . . . . 392
30.8 Adding an autofilter to a Dataframe output . . . . . . . . . . . . . . . . . . . . . . . 393
30.9 Handling multiple Pandas Dataframes . . . . . . . . . . . . . . . . . . . . . . . . . . 395
30.10Passing XlsxWriter constructor options to Pandas . . . . . . . . . . . . . . . . . . . 396
30.11Saving the Dataframe output to a string . . . . . . . . . . . . . . . . . . . . . . . . . 396
30.12Additional Pandas and Excel Information . . . . . . . . . . . . . . . . . . . . . . . . 397
31 Examples 399
31.1 Example: Hello World . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
31.2 Example: Simple Feature Demonstration . . . . . . . . . . . . . . . . . . . . . . . . 400
31.3 Example: Catch exception on closing . . . . . . . . . . . . . . . . . . . . . . . . . . 401
31.4 Example: Dates and Times in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . 402
31.5 Example: Adding hyperlinks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
31.6 Example: Array formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
31.7 Example: Dynamic array formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
31.8 Example: Applying Autofilters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
31.9 Example: Data Validation and Drop Down Lists . . . . . . . . . . . . . . . . . . . . . 418
31.10Example: Conditional Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423
31.11Example: Defined names/Named ranges . . . . . . . . . . . . . . . . . . . . . . . . 430
31.12Example: Merging Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432
31.13Example: Writing “Rich” strings with multiple formats . . . . . . . . . . . . . . . . . 433
31.14Example: Merging Cells with a Rich String . . . . . . . . . . . . . . . . . . . . . . . 435
31.15Example: Inserting images into a worksheet . . . . . . . . . . . . . . . . . . . . . . 437
31.16Example: Inserting images from a URL or byte stream into a worksheet . . . . . . . 439
31.17Example: Left to Right worksheets and text . . . . . . . . . . . . . . . . . . . . . . . 441
31.18Example: Simple Django class . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . 442
31.19Example: Simple HTTP Server (Python 2) . . . . . . . . . . . . . . . . . . . . . . . 443
31.20Example: Simple HTTP Server (Python 3) . . . . . . . . . . . . . . . . . . . . . . . 445
viii
31.21Example: Adding Headers and Footers to Worksheets . . . . . . . . . . . . . . . . 446
31.22Example: Freeze Panes and Split Panes . . . . . . . . . . . . . . . . . . . . . . . . 450
31.23Example: Worksheet Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452
31.24Example: Writing User Defined Types (1) . . . . . . . . . . . . . . . . . . . . . . . . 460
31.25Example: Writing User Defined Types (2) . . . . . . . . . . . . . . . . . . . . . . . . 462
31.26Example: Writing User Defined types (3) . . . . . . . . . . . . . . . . . . . . . . . . 464
31.27Example: Ignoring Worksheet errors and warnings . . . . . . . . . . . . . . . . . . . 466
31.28Example: Sparklines (Simple) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468
31.29Example: Sparklines (Advanced) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469
31.30Example: Adding Cell Comments to Worksheets (Simple) . . . . . . . . . . . . . . . 476
31.31Example: Adding Cell Comments to Worksheets (Advanced) . . . . . . . . . . . . . 478
31.32Example: Insert Textboxes into a Worksheet . . . . . . . . . . . . . . . . . . . . . . 483
31.33Example: Outline and Grouping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488
31.34Example: Collapsed Outline and Grouping . . . . . . . . . . . . . . . . . . . . . . . 493
31.35Example: Setting Document Properties . . . . . . . . . . . . . . . . . . . . . . . . . 497
31.36Example: Simple Unicode with Python 2 . . . . . . . . . . . . . . . . . . . . . . . . 499
31.37Example: Simple Unicode with Python 3 . . . . . . . . . . . . . . . . . . . . . . . . 500
31.38Example: Unicode - Polish in UTF-8 . . . . . . . . . . . . . . . . . . . . . . . . . . . 500
31.39Example: Unicode - Shift JIS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502
31.40Example: Setting the Worksheet Background . . . . . . . . . . . . . . . . . . . . . . 504
31.41Example: Setting Worksheet Tab Colors . . . . . . . . . . . . . . . . . . . . . . . . 505
31.42Example: Diagonal borders in cells . . . . . . . . . . . . . . . . . . . . . . . . . . . 506
31.43Example: Enabling Cell protection in Worksheets . . . . . . . . . . . . . . . . . . . 508
31.44Example: Hiding Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
31.45Example: Hiding Rows and Columns . . . . . . . . . . . . . . . . . . . . . . . . . . 511
31.46Example: Example of subclassing the Workbook and Worksheet classes . . . . . . 512
31.47Example: Advanced example of subclassing . . . . . . . . . . . . . . . . . . . . . . 514
31.48Example: Adding a VBA macro to a Workbook . . . . . . . . . . . . . . . . . . . . . 518
31.49Example: Excel 365 LAMBDA() function . . . . . . . . . . . . . . . . . . . . . . . . 519
32 Chart Examples 523
32.1 Example: Chart (Simple) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523
32.2 Example: Area Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524
32.3 Example: Bar Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528
32.4 Example: Column Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531
32.5 Example: Line Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535
32.6 Example: Pie Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539
32.7 Example: Doughnut Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542
32.8 Example: Scatter Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546
32.9 Example: Radar Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552
32.10Example: Stock Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 556
32.11Example: Styles Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 557
32.12Example: Chart with Pattern Fills . . . . . . . . . . . . . . . . . . . . . . . . . . . . 559
32.13Example: Chart with Gradient Fills . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561
32.14Example: Secondary Axis Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562
32.15Example: Combined Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564
32.16Example: Pareto Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567
32.17Example: Gauge Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569
ix
32.18Example: Clustered Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571
32.19Example: Date Axis Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572
32.20Example: Charts with Data Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 574
32.21Example: Charts with Data Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577
32.22Example: Charts with Data Labels . . . . . . . . . . . . . . . . . . . . . . . . . . . . 584
32.23Example: Chartsheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594
33 Pandas with XlsxWriter Examples 597
33.1 Example: Pandas Excel example . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597
33.2 Example: Pandas Excel with multiple dataframes . . . . . . . . . . . . . . . . . . . 599
33.3 Example: Pandas Excel dataframe positioning . . . . . . . . . . . . . . . . . . . . . 600
33.4 Example: Pandas Excel output with a chart . . . . . . . . . . . . . . . . . . . . . . . 601
33.5 Example: Pandas Excel output with conditional formatting . . . . . . . . . . . . . . 603
33.6 Example: Pandas Excel output with an autofilter . . . . . . . . . . . . . . . . . . . . 604
33.7 Example: Pandas Excel output with a worksheet table . . . . . . . . . . . . . . . . . 606
33.8 Example: Pandas Excel output with datetimes . . . . . . . . . . . . . . . . . . . . . 608
33.9 Example: Pandas Excel output with column formatting . . . . . . . . . . . . . . . . 610
33.10Example: Pandas Excel output with user defined header format . . . . . . . . . . . 612
33.11Example: Pandas Excel output with a line chart . . . . . . . . . . . . . . . . . . . . 613
33.12Example: Pandas Excel output with a column chart . . . . . . . . . . . . . . . . . . 615
34 Alternative modules for handling Excel files 617
34.1 OpenPyXL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617
34.2 Xlwings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617
34.3 XLWT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617
34.4 XLRD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617
35 Libraries that use or enhance XlsxWriter 619
35.1 Pandas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619
35.2 XlsxPandasFormatter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619
36 Known Issues and Bugs 621
36.1 “Content is Unreadable. Open and Repair” . . . . . . . . . . . . . . . . . . . . . . . 621
36.2 “Exception caught in workbook destructor. Explicit close() may be required” . . . . . 621
36.3 Formulas displayed as #NAME? until edited . . . . . . . . . . . . . . . . . . . . . . . 622
36.4 Formula results displaying as zero in non-Excel applications . . . . . . . . . . . . . 622
36.5 Images not displayed correctly in Excel 2001 for Mac and non-Excel applications . . 622
36.6 Charts series created from Worksheet Tables cannot have user defined names . . . 622
37 Reporting Bugs 623
37.1 Upgrade to the latest version of the module . . . . . . . . . . . . . . . . . . . . . . . 623
37.2 Read the documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623
37.3 Look at the example programs . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . 623
37.4 Use the official XlsxWriter Issue tracker on GitHub . . . . . . . . . . . . . . . . . . . 623
37.5 Pointers for submitting a bug report . . . . . . . . . . . . . . . . . . . . . . . . . . . 623
38 Frequently Asked Questions 625
38.1 Q. Can XlsxWriter use an existing Excel file as a template? . . . . . . . . . . . . . . 625
38.2 Q. Why do my formulas show a zero result in some, non-Excel applications? . . . . 625
x
38.3 Q. Why do my formulas have a @ in them? . . . . . . . . . . . . . . . . . . . . . . . 626
38.4 Q. Can I apply a format to a range of cells in one go? . . . . . . . . . . . . . . . . . 626
38.5 Q. Is feature X supported or will it be supported? . . . . . . . . . . . . . . . . . . . . 626
38.6 Q. Is there an “AutoFit” option for columns? . . . . . . . . . . . . . . . . . . . . . . . 626
38.7 Q. Can I password protect an XlsxWriter xlsx file . . . . . . . . . . . . . . . . . . . . 626
38.8 Q. Do people actually ask these questions frequently, or at all? . . . . . . . . . . . . 627
39 Changes in XlsxWriter 629
39.1 Release 1.4.3 - May 12 2021 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
39.2 Release 1.4.2 - May 7 2021 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
39.3 Release 1.4.1 - May 6 2021 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
39.4 Release 1.4.0 - April 23 2021 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
39.5 Release 1.3.9 - April 15 2021 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
39.6 Release 1.3.8 - March 29 2021 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630
39.7 Release 1.3.7 - October 13 2020 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630
39.8 Release 1.3.6 - September 23 2020 . . . . . . . . . . . . . . . . . . . . . . . . . . . 630
39.9 Release 1.3.5 - September 21 2020 . . . . . . . . . . . . . . . . . . . . . . . . . . . 630
39.10Release 1.3.4 - September 16 2020 . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
39.11Release 1.3.3 - August 13 2020 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
39.12Release 1.3.2 - August 6 2020 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
39.13Release 1.3.1 - August 3 2020 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
39.14Release 1.3.0 - July 30 2020 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
39.15Release 1.2.9 - May 29 2020 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
39.16Release 1.2.8 - February 22 2020 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632
39.17Release 1.2.7 - December 23 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . 632
39.18Release 1.2.6 - November 15 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . 632
39.19Release 1.2.5 - November 10 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . 632
39.20Release 1.2.4 - November 9 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632
39.21Release 1.2.3 - November 7 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633
39.22Release 1.2.2 - October 16 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633
39.23Release 1.2.1 - September 14 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . 633
39.24Release 1.2.0 - August 26 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633
39.25Release 1.1.9 - August 19 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633
39.26Release 1.1.8 - May 5 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633
39.27Release 1.1.7 - April 20 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634
39.28Release 1.1.6 - April 7 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634
39.29Release 1.1.5 - February 22 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634
39.30Release 1.1.4 - February 10 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634
39.31Release 1.1.3 - February 9 2019 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634
39.32Release 1.1.2 - October 20 2018 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634
39.33Release 1.1.1 - September 22 2018 . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
39.34Release 1.1.0 - September 2 2018 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
39.35Release 1.0.9 - August 27 2018 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
39.36Release 1.0.8 - August 27 2018 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
39.37Release 1.0.7 - August 16 2018 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636
39.38Release 1.0.6 - August 15 2018 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636
39.39Release 1.0.5 - May 19 2018 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636
39.40Release 1.0.4 - April 14 2018 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636
xi
39.41Release 1.0.3 - April 10 2018 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636
39.42Release 1.0.2 - October 14 2017 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636
39.43Release 1.0.1 - October 14 2017 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637
39.44Release 1.0.0 - September 16 2017 . . . . . . . . . . . . . . . . . . . . . . . . . . . 637
39.45Release 0.9.9 - September 5 2017 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637
39.46Release 0.9.8 - July 1 2017 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637
39.47Release 0.9.7 - June 25 2017 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637
39.48Release 0.9.6 - Dec 26 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637
39.49Release 0.9.5 - Dec 24 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637
39.50Release 0.9.4 - Dec 2 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
39.51Release 0.9.3 - July 8 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
39.52Release 0.9.2 - June 13 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
39.53Release 0.9.1 - June 8 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
39.54Release 0.9.0 - June 7 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
39.55Release 0.8.9 - June 1 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
39.56Release 0.8.8 - May 31 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
39.57Release 0.8.7 - May 13 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
39.58Release 0.8.6 - April 27 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
39.59Release 0.8.5 - April 17 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
39.60Release 0.8.4 - January 16 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
39.61Release 0.8.3 - January 14 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
39.62Release 0.8.2 - January 13 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
39.63Release 0.8.1 - January 12 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
39.64Release 0.8.0 - January 10 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
39.65Release 0.7.9 - January 9 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
39.66Release 0.7.8 - January 6 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
39.67Release 0.7.7 - October 19 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
39.68Release 0.7.6 - October 7 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
39.69Release 0.7.5 - October 4 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
39.70Release 0.7.4 - September 29 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
39.71Release 0.7.3 - May 7 2015 . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . 641
39.72Release 0.7.2 - March 29 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
39.73Release 0.7.1 - March 23 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
39.74Release 0.7.0 - March 21 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
39.75Release 0.6.9 - March 19 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
39.76Release 0.6.8 - March 17 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
39.77Release 0.6.7 - March 1 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642
39.78Release 0.6.6 - January 16 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642
39.79Release 0.6.5 - December 31 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . 642
39.80Release 0.6.4 - November 15 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . 642
39.81Release 0.6.3 - November 6 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642
39.82Release 0.6.2 - November 1 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642
39.83Release 0.6.1 - October 29 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
39.84Release 0.6.0 - October 15 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
39.85Release 0.5.9 - October 11 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
39.86Release 0.5.8 - September 28 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
39.87Release 0.5.7 - August 13 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
39.88Release 0.5.6 - July 22 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 644
xii
39.89Release 0.5.5 - May 6 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 644
39.90Release 0.5.4 - May 4 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 644
39.91Release 0.5.3 - February 20 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 644
39.92Release 0.5.2 - December 31 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . 644
39.93Release 0.5.1 - December 2 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
39.94Release 0.5.0 - November 17 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
39.95Release 0.4.9 - November 17 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
39.96Release 0.4.8 - November 13 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
39.97Release 0.4.7 - November 9 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
39.98Release 0.4.6 - October 23 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
39.99Release 0.4.5 - October 21 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
39.100Release 0.4.4 - October 16 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
39.101Release 0.4.3 - September 12 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
39.102Release 0.4.2 - August 30 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
39.103Release 0.4.1 - August 28 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
39.104Release 0.4.0 - August 26 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
39.105Release 0.3.9 - August 24 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647
39.106Release 0.3.8 - August 23 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647
39.107Release 0.3.7 - August 16 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647
39.108Release 0.3.6 - July 26 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647
39.109Release 0.3.5 - June 28 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647
39.110Release 0.3.4 - June 27 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647
39.111Release 0.3.3 - June 10 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648
39.112Release 0.3.2 - May 1 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648
39.113Release 0.3.1 - April 27 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648
39.114Release 0.3.0 - April 7 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648
39.115Release 0.2.9 - April 7 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648
39.116Release 0.2.8 - April 4 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649
39.117Release 0.2.7 - April 3 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649
39.118Release 0.2.6 - April 1 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649
39.119Release 0.2.5 - April 1 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649
39.120Release 0.2.4 - March 31 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649
39.121Release 0.2.3 - March 27 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
39.122Release 0.2.2 - March 27 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
39.123Release 0.2.1 - March 25 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
39.124Release 0.2.0 - March 24 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
39.125Release 0.1.9 - March 19 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
39.126Release 0.1.8 - March 18 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
39.127Release 0.1.7 - March 18 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651
39.128Release 0.1.6 - March 17 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651
39.129Release 0.1.5 - March 10 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651
39.130Release 0.1.4 - March 8 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651
39.131Release 0.1.3 - March 7 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651
39.132Release 0.1.2 - March 6 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
39.133Release 0.1.1 - March 3 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
39.134Release 0.1.0 - February 28 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
39.135Release 0.0.9 - February 27 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
39.136Release 0.0.8 - February 26 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
xiii
39.137Release 0.0.7 - February 25 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
39.138Release 0.0.6 - February 22 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
39.139Release 0.0.5 - February 21 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
39.140Release 0.0.4 - February 20 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
39.141Release 0.0.3 - February 19 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 654
39.142Release 0.0.2 - February 18 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 654
39.143Release 0.0.1 - February 17 2013 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 654
40 Author 655
40.1 Asking questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 655
40.2 Sponsorship and Donations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 655
41 License 657
xiv
Creating Excel files with Python and XlsxWriter, Release 1.4.3
XlsxWriter is a Python module for creating Excel XLSX files.
XlsxWriter is a Python module that can be used to write text, numbers, formulas and hyperlinks
to multiple worksheets in an Excel 2007+ XLSX file. 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.
CONTENTS 1
CreatingExcel files with Python and XlsxWriter, Release 1.4.3
• Textboxes.
• Integration with Pandas.
• Memory optimization mode for writing large files.
It supports Python 2.7, 3.4+ and PyPy and uses standard libraries only.
2 CONTENTS
CHAPTER
ONE
INTRODUCTION
XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format.
It can be used to write text, numbers, and formulas to multiple worksheets and it supports features
such as formatting, images, charts, page setup, autofilters, conditional formatting and many others.
XlsxWriter has some advantages and disadvantages over the alternative Python modules for writ-
ing Excel files.
• Advantages:
– It supports more Excel features than any of the alternative modules.
– It has a high degree of fidelity with files produced by Excel. In most cases the files
produced are 100% equivalent to files produced by Excel.
– It has extensive documentation, example files and tests.
– It is fast and can be configured to use very little memory even for very large output files.
• Disadvantages:
– It cannot read or modify existing Excel XLSX files.
XlsxWriter is licensed under a BSD License and the source code is available on GitHub.
To try out the module see the next section on Getting Started with XlsxWriter .
3
https://github.com/jmcnamara/XlsxWriter
Creating Excel files with Python and XlsxWriter, Release 1.4.3
4 Chapter 1. Introduction
CHAPTER
TWO
GETTING STARTED WITH XLSXWRITER
Here are some easy instructions to get you up and running with the XlsxWriter module.
2.1 Installing XlsxWriter
The first step is to install the XlsxWriter module. There are several ways to do this.
2.1.1 Using PIP
The pip installer is the preferred method for installing Python modules from PyPI, the Python
Package Index:
$ pip install XlsxWriter
# Or to a non system dir:$ pip install --user XlsxWriter
2.1.2 Installing from a tarball
If you download a tarball of the latest version of XlsxWriter you can install it as follows (change the
version number to suit):
$ tar -zxvf XlsxWriter-1.2.3.tar.gz
$ cd XlsxWriter-1.2.3$ python setup.py install
A tarball of the latest code can be downloaded from GitHub as follows:
$ curl -O -L http://github.com/jmcnamara/XlsxWriter/archive/master.tar.gz
$ tar zxvf master.tar.gz$ cd XlsxWriter-master/$ python setup.py install
5
https://pip.pypa.io/en/latest/
https://pypi.org/
Creating Excel files with Python and XlsxWriter, Release 1.4.3
2.1.3 Cloning from GitHub
The XlsxWriter source code and bug tracker is in the XlsxWriter repository on GitHub. You can
clone the repository and install from it as follows:
$ git clone https://github.com/jmcnamara/XlsxWriter.git
$ cd XlsxWriter$ python setup.py install
2.2 Running a sample program
If the installation went correctly you can create a small sample program like the following to verify
that the module works correctly:
import xlsxwriter
workbook = xlsxwriter.Workbook('hello.xlsx')worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
workbook.close()
Save this to a file called hello.py and run it as follows:
$ python hello.py
This will output a file called hello.xlsx which should look something like the following:
6 Chapter 2. Getting Started with XlsxWriter
https://github.com/jmcnamara/XlsxWriter
Creating Excel files with Python and XlsxWriter, Release 1.4.3
If you downloaded a tarball or cloned the repo, as shown above, you should also have a directory
called examples with some sample applications that demonstrate different features of XlsxWriter.
2.3 Documentation
The latest version of this document is hosted on Read The Docs. It is also available as a PDF.
Once you are happy that the module is installed and operational you can have a look at the rest of
the XlsxWriter documentation. Tutorial 1: Create a simple XLSX file is a good place to start.
2.3. Documentation 7
https://github.com/jmcnamara/XlsxWriter/tree/master/examples
https://xlsxwriter.readthedocs.io
https://raw.githubusercontent.com/jmcnamara/XlsxWriter/master/docs/XlsxWriter.pdf
Creating Excel files with Python and XlsxWriter, Release 1.4.3
8 Chapter 2. Getting Started with XlsxWriter
CHAPTER
THREE
TUTORIAL 1: CREATE A SIMPLE XLSX FILE
Let’s start by creating a simple spreadsheet using Python and the XlsxWriter module.
Say that we have some data on monthly outgoings that we want to convert into an Excel XLSX
file:
expenses = (['Rent', 1000],['Gas', 100],['Food', 300],['Gym', 50],)
To do that we can start with a small program like the following:
import xlsxwriter
# Create a workbook and add a worksheet.workbook = xlsxwriter.Workbook('Expenses01.xlsx')worksheet = workbook.add_worksheet()
# Some data we want to write to the worksheet.expenses = (['Rent', 1000],['Gas', 100],['Food', 300],['Gym', 50],)
# Start from the first cell. Rows and columns are zero indexed.row = 0col = 0
# Iterate over the data and write it out row by row.
for item, cost in (expenses):worksheet.write(row, col, item)worksheet.write(row, col + 1, cost)row += 1
# Write a total using a formula.worksheet.write(row, 0, 'Total')worksheet.write(row, 1, '=SUM(B1:B4)')
9
Creating Excel files with Python and XlsxWriter, Release 1.4.3
workbook.close()
If we run this program we should get a spreadsheet that looks like this:
This is a simple example but the steps involved are representative of all programs that use Xl-
sxWriter, so let’s break it down into separate parts.
The first step is to import the module:
import xlsxwriter
The next step is to create a new workbook object using the Workbook() constructor.
Workbook() takes one, non-optional, argument which is the filename that we want to create:
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
Note: XlsxWriter can only create new files. It cannot read or modify existing files.
The workbook object is then used to add a new worksheet via the add_worksheet() method:
worksheet = workbook.add_worksheet()
10 Chapter 3. Tutorial 1: Create a simple XLSX file
Creating Excel files with Python and XlsxWriter, Release 1.4.3
By default worksheet names in the spreadsheet will be Sheet1, Sheet2 etc., but we can also
specify a name:
worksheet1 = workbook.add_worksheet() # Defaults to Sheet1.worksheet2 = workbook.add_worksheet('Data') # Data.worksheet3 = workbook.add_worksheet() # Defaults to Sheet3.
We can then use the worksheet object to write data via the write() method:
worksheet.write(row, col, some_data)
Note: Throughout XlsxWriter, rows and columns are zero indexed. The first cell in a worksheet,A1, is (0, 0).
So in our example we iterate over our data and write it out as follows:
# Iterate over the data and write it out row by row.
for item, cost in (expenses):worksheet.write(row, col, item)worksheet.write(row, col + 1, cost)row += 1
We then add a formula to calculate the total of the items in the second column:
worksheet.write(row, 1, '=SUM(B1:B4)')
Finally, we close the Excel file via the close() method:
workbook.close()
And that’s it. We now have a file that can be read by Excel and other spreadsheet applications.
In the next sections we will see how we can use the XlsxWriter module to add formatting and other
Excel features.
11
Creating Excel files with Python and XlsxWriter, Release 1.4.3
12 Chapter 3. Tutorial 1: Create a simple XLSX file
CHAPTER
FOUR
TUTORIAL 2: ADDING FORMATTING TO THE XLSX FILE
In the previous section we created a simple spreadsheet using Python and the XlsxWriter module.
This converted the required data into an Excel file but it looked a little bare. In order to make the
information clearer we would like to add some simple formatting, like this:
The differences here are that we have added Item and Cost column headers in a bold font, we
have formatted the currency in the second column and we have made the Total string bold.
To do this we can extend our program as follows:
13
Creating Excel files with Python and XlsxWriter, Release 1.4.3
import xlsxwriter
# Create a workbookand add a worksheet.workbook = xlsxwriter.Workbook('Expenses02.xlsx')worksheet = workbook.add_worksheet()
# Add a bold format to use to highlight cells.bold = workbook.add_format({'bold': True})
# Add a number format for cells with money.money = workbook.add_format({'num_format': '$#,##0'})
# Write some data headers.worksheet.write('A1', 'Item', bold)worksheet.write('B1', 'Cost', bold)
# Some data we want to write to the worksheet.expenses = (['Rent', 1000],['Gas', 100],['Food', 300],['Gym', 50],)
# Start from the first cell below the headers.row = 1col = 0
# Iterate over the data and write it out row by row.
for item, cost in (expenses):worksheet.write(row, col, item)worksheet.write(row, col + 1, cost, money)row += 1
# Write a total using a formula.worksheet.write(row, 0, 'Total', bold)worksheet.write(row, 1, '=SUM(B2:B5)', money)
workbook.close()
The main difference between this and the previous program is that we have added two Format
objects that we can use to format cells in the spreadsheet.
Format objects represent all of the formatting properties that can be applied to a cell in Excel such
as fonts, number formatting, colors and borders. This is explained in more detail in The Format
Class section.
For now we will avoid getting into the details and just use a limited amount of the format function-
ality to add some simple formatting:
# Add a bold format to use to highlight cells.bold = workbook.add_format({'bold': True})
14 Chapter 4. Tutorial 2: Adding formatting to the XLSX File
Creating Excel files with Python and XlsxWriter, Release 1.4.3
# Add a number format for cells with money.money = workbook.add_format({'num_format': '$#,##0'})
We can then pass these formats as an optional third parameter to the worksheet.write() method
to format the data in the cell:
write(row, column, token, [format])
Like this:
worksheet.write(row, 0, 'Total', bold)
Which leads us to another new feature in this program. To add the headers in the first row of the
worksheet we used write() like this:
worksheet.write('A1', 'Item', bold)worksheet.write('B1', 'Cost', bold)
So, instead of (row, col) we used the Excel ’A1’ style notation. See Working with Cell Nota-
tion for more details but don’t be too concerned about it for now. It is just a little syntactic sugar to
help with laying out worksheets.
In the next section we will look at handling more data types.
15
Creating Excel files with Python and XlsxWriter, Release 1.4.3
16 Chapter 4. Tutorial 2: Adding formatting to the XLSX File
CHAPTER
FIVE
TUTORIAL 3: WRITING DIFFERENT TYPES OF DATA TO THE XLSX
FILE
In the previous section we created a simple spreadsheet with formatting using Python and the
XlsxWriter module.
This time let’s extend the data we want to write to include some dates:
expenses = (['Rent', '2013-01-13', 1000],['Gas', '2013-01-14', 100],['Food', '2013-01-16', 300],['Gym', '2013-01-20', 50],)
The corresponding spreadsheet will look like this:
17
Creating Excel files with Python and XlsxWriter, Release 1.4.3
The differences here are that we have added a Date column with formatting and made that column
a little wider to accommodate the dates.
To do this we can extend our program as follows:
from datetime import datetime
import xlsxwriter
# Create a workbook and add a worksheet.workbook = xlsxwriter.Workbook('Expenses03.xlsx')worksheet = workbook.add_worksheet()
# Add a bold format to use to highlight cells.bold = workbook.add_format({'bold': 1})
# Add a number format for cells with money.money_format = workbook.add_format({'num_format': '$#,##0'})
# Add an Excel date format.date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})
# Adjust the column width.worksheet.set_column(1, 1, 15)
# Write some data headers.
18 Chapter 5. Tutorial 3: Writing different types of data to the XLSX File
Creating Excel files with Python and XlsxWriter, Release 1.4.3
worksheet.write('A1', 'Item', bold)worksheet.write('B1', 'Date', bold)worksheet.write('C1', 'Cost', bold)
# Some data we want to write to the worksheet.expenses = (['Rent', '2013-01-13', 1000],['Gas', '2013-01-14', 100],['Food', '2013-01-16', 300],['Gym', '2013-01-20', 50],)
# Start from the first cell below the headers.row = 1col = 0
for item, date_str, cost in (expenses):# Convert the date string into a datetime object.date = datetime.strptime(date_str, "%Y-%m-%d")
worksheet.write_string (row, col, item )worksheet.write_datetime(row, col + 1, date, date_format )worksheet.write_number (row, col + 2, cost, money_format)row += 1
# Write a total using a formula.worksheet.write(row, 0, 'Total', bold)worksheet.write(row, 2, '=SUM(C2:C5)', money_format)
workbook.close()
The main difference between this and the previous program is that we have added a new Format
object for dates and we have additional handling for data types.
Excel treats different types of input data, such as strings and numbers, differently although it gen-
erally does it transparently to the user. XlsxWriter tries to emulate this in the worksheet.write()
method by mapping Python data types to types that Excel supports.
The write() method acts as a general alias for several more specific methods:
• write_string()
• write_number()
• write_blank()
• write_formula()
• write_datetime()
• write_boolean()
• write_url()
In this version of our program we have used some of these explicit write_ methods for different
types of data:
19
Creating Excel files with Python and XlsxWriter, Release 1.4.3
worksheet.write_string (row, col, item )worksheet.write_datetime(row, col + 1, date, date_format )worksheet.write_number (row, col + 2, cost, money_format)
This is mainly to show that if you need more control over the type of data you write to a worksheet
you can use the appropriate method. In this simplified example the write() method would
actually have worked just as well.
The handling of dates is also new to our program.
Dates and times in Excel are floating point numbers that have a number format applied to display
them in the correct format. If the date and time are Python datetime objects XlsxWriter makes
the required number conversion automatically. However, we also need to add the number format
to ensure that Excel displays it as as date:
from datetime import datetime...
date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})...
for item, date_str, cost in (expenses):# Convert the date string into a datetime object.date = datetime.strptime(date_str, "%Y-%m-%d")...worksheet.write_datetime(row, col + 1, date, date_format )...
Date handling is explained in more detail in Working with Dates and Time.
The last addition to our program is the set_column() method to adjust the width of column ‘B’
so that the dates are more clearly visible:
# Adjust the column width.worksheet.set_column('B:B', 15)
That completes the tutorial section.
In the next sections we will look at the API in more detail starting with The Workbook Class.
20 Chapter 5. Tutorial 3: Writing different types of data to the XLSX File
https://docs.python.org/3/library/datetime.html#module-datetime
CHAPTER
SIX
THE WORKBOOK CLASS
The Workbook class is the main class exposed by the XlsxWriter module and it is the only class
that you will need to instantiate directly.
The Workbook class represents the entire spreadsheet as you see it in Excel and internally it
represents the Excel file as it is written on disk.
6.1 Constructor
Workbook(filename[, options ])
Create a new XlsxWriter Workbook object.
Parameters
• filename (string) – The name of the new Excel file to create.
• options (dict) – Optional workbook parameters. See below.
Return type A Workbook object.
The Workbook() constructor is used to create a new Excel workbook with a given filename:
import xlsxwriter
workbook = xlsxwriter.Workbook('filename.xlsx')worksheet = workbook.add_worksheet()
worksheet.write(0, 0, 'Hello Excel')
workbook.close()
21
https://docs.python.org/3/library/string.html#module-string
https://docs.python.org/3/library/stdtypes.html#dictCreating Excel files with Python and XlsxWriter, Release 1.4.3
The constructor options are:
• constant_memory: Reduces the amount of data stored in memory so that large files can
be written efficiently:
workbook = xlsxwriter.Workbook(filename, {'constant_memory': True})
Note, in this mode a row of data is written and then discarded when a cell in a new row
is added via one of the worksheet write_() methods. Therefore, once this mode is ac-
tive, data should be written in sequential row order. For this reason the add_table() andmerge_range() Worksheet methods don’t work in this mode.
See Working with Memory and Performance for more details.
• tmpdir: XlsxWriter stores workbook data in temporary files prior to assembling the final
XLSX file. The temporary files are created in the system’s temp directory. If the default
temporary directory isn’t accessible to your application, or doesn’t contain enough space,
you can specify an alternative location using the tmpdir option:
workbook = xlsxwriter.Workbook(filename, {'tmpdir': '/home/user/tmp'})
The temporary directory must exist and will not be created.
• in_memory: To avoid the use of temporary files in the assembly of the final XLSX file, for
example on servers that don’t allow temp files, set the in_memory constructor option to
22 Chapter 6. The Workbook Class
Creating Excel files with Python and XlsxWriter, Release 1.4.3
True:
workbook = xlsxwriter.Workbook(filename, {'in_memory': True})
This option overrides the constant_memory option.
Note: This option used to be the recommended way of deploying XlsxWriter on Google APP
Engine since it didn’t support a /tmp directory. However, the Python 3 Runtime Environment
in Google App Engine now supports a filesystem with read/write access to /tmp which means
this option isn’t required. The /tmp dir isn’t supported in the Python 2 Runtime Environment
so this option is still required there.
• strings_to_numbers: Enable the worksheet.write() method to convert strings to num-
bers, where possible, using float() in order to avoid an Excel warning about “Numbers
Stored as Text”. The default is False. To enable this option use:
workbook = xlsxwriter.Workbook(filename, {'strings_to_numbers': True})
• strings_to_formulas: Enable the worksheet.write() method to convert strings to formu-
las. The default is True. To disable this option use:
workbook = xlsxwriter.Workbook(filename, {'strings_to_formulas': False})
• strings_to_urls: Enable the worksheet.write() method to convert strings to urls. The
default is True. To disable this option use:
workbook = xlsxwriter.Workbook(filename, {'strings_to_urls': False})
• use_future_functions: Enable the use of newer Excel “future” functions without having to
prefix them with with _xlfn.. The default is False. To enable this option use:
workbook = xlsxwriter.Workbook(filename, {'use_future_functions': True})
See also Formulas added in Excel 2010 and later .
• max_url_length: Set the maximum length for hyperlinks in worksheets. The default is 2079
and the minimum is 255. Versions of Excel prior to Excel 2015 limited hyperlink links and
anchor/locations to 255 characters each. Versions after that support urls up to 2079 charac-
ters. XlsxWriter versions >= 1.2.3 support the new longer limit by default. However, a lower
or user defined limit can be set via the max_url_length option:
workbook = xlsxwriter.Workbook(filename, {'max_url_length': 255})
• nan_inf_to_errors: Enable the worksheet.write() and write_number() methods to
convert nan, inf and -inf to Excel errors. Excel doesn’t handle NAN/INF as numbers
so as a workaround they are mapped to formulas that yield the error codes #NUM! and#DIV/0!. The default is False. To enable this option use:
workbook = xlsxwriter.Workbook(filename, {'nan_inf_to_errors': True})
• default_date_format: This option is used to specify a default date format string for use
with the worksheet.write_datetime() method when an explicit format isn’t given. See
6.1. Constructor 23
https://cloud.google.com/appengine/docs/standard/python3/runtime#filesystem
Creating Excel files with Python and XlsxWriter, Release 1.4.3
Working with Dates and Time for more details:
xlsxwriter.Workbook(filename, {'default_date_format': 'dd/mm/yy'})
• remove_timezone: Excel doesn’t support timezones in datetimes/times so there isn’t any
fail-safe way that XlsxWriter can map a Python timezone aware datetime into an Excel date-
time in functions such as write_datetime(). As such the user should convert and re-
move the timezones in some way that makes sense according to their requirements. Al-
ternatively the remove_timezone option can be used to strip the timezone from datetime
values. The default is False. To enable this option use:
workbook = xlsxwriter.Workbook(filename, {'remove_timezone': True})
See also Timezone Handling in XlsxWriter .
• use_zip64: Use ZIP64 extensions when writing the xlsx file zip container to allow files
greater than 4 GB. This is the same as calling use_zip64() after creating the Workbook
object. This constructor option is just syntactic sugar to make the use of the option more
explicit. The following are equivalent:
workbook = xlsxwriter.Workbook(filename, {'use_zip64': True})
# Same as:workbook = xlsxwriter.Workbook(filename)workbook.use_zip64()
See the note about the Excel warning caused by using this option in use_zip64().
• date_1904: Excel for Windows uses a default epoch of 1900 and Excel for Mac uses an
epoch of 1904. However, Excel on either platform will convert automatically between one
system and the other. XlsxWriter stores dates in the 1900 format by default. If you wish
to change this you can use the date_1904 workbook option. This option is mainly for
enhanced compatibility with Excel and in general isn’t required very often:
workbook = xlsxwriter.Workbook(filename, {'date_1904': True})
When specifying a filename it is recommended that you use an .xlsx extension or Excel will
generate a warning when opening the file.
The Workbook() method also works using the with context manager. In which case it doesn’t
need an explicit close() statement:
with xlsxwriter.Workbook('hello_world.xlsx') as workbook:worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
It is possible to write files to in-memory strings using BytesIO as follows:
from io import BytesIO
output = BytesIO()workbook = xlsxwriter.Workbook(output)
24 Chapter 6. The Workbook Class
Creating Excel files with Python and XlsxWriter, Release 1.4.3
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello')workbook.close()
xlsx_data = output.getvalue()
To avoid the use of any temporary files and keep the entire file in-memory use the in_memory
constructor option shown above.
See also Example: Simple HTTP Server (Python 2) and Example: Simple HTTP Server (Python
3).
6.2 workbook.add_worksheet()
add_worksheet([name ])
Add a new worksheet to a workbook.
Parameters name (string) – Optional worksheet name, defaults to Sheet1, etc.
Return type A worksheet object.
Raises
• DuplicateWorksheetName – if a duplicate worksheet name is used.
• InvalidWorksheetName – if an invalid worksheet name is used.
• ReservedWorksheetName – if a reserved worksheet name is used.
The add_worksheet() method adds a new worksheet to a workbook.
At least one worksheet should be added to a new workbook. The Worksheet object is used to
write data and configure a worksheet in the workbook.
The name parameter is optional. If it is not specified, or blank, the default Excel convention will be
followed, i.e. Sheet1, Sheet2, etc.:
worksheet1 = workbook.add_worksheet() # Sheet1worksheet2 = workbook.add_worksheet('Foglio2') # Foglio2worksheet3 = workbook.add_worksheet('Data') # Dataworksheet4 = workbook.add_worksheet() # Sheet4
6.2. workbook.add_worksheet() 25
https://docs.python.org/3/library/string.html#module-string
Creating Excel files with Python and XlsxWriter, Release 1.4.3
The worksheet name must be a valid Excel worksheet name: