### Configure Page Setup and Printing Options Source: https://context7.com/jmcnamara/xlsxwriter/llms.txt Provides examples of setting up page properties for printing, including paper size, orientation, margins, headers, footers, print area, and fitting content to pages using XlsxWriter. ```python import xlsxwriter workbook = xlsxwriter.Workbook("print_setup.xlsx") worksheet = workbook.add_worksheet() # Set paper size and orientation worksheet.set_paper(9) # A4 worksheet.set_landscape() # Set margins (in inches) worksheet.set_margins(left=0.75, right=0.75, top=1, bottom=1) # Set header and footer worksheet.set_header("&LCompany Name&CReport Title&R&D") worksheet.set_footer("&CPage &P of &N") # Repeat header row on each page worksheet.repeat_rows(0) # Set print area worksheet.print_area("A1:F50") # Fit to pages worksheet.fit_to_pages(1, 0) # 1 page wide, auto height # Set page breaks worksheet.set_h_pagebreaks([20, 40]) # Horizontal breaks after rows 20 and 40 # Print gridlines and row/column headers worksheet.print_row_col_headers() worksheet.hide_gridlines(0) # Show gridlines # Center on page worksheet.center_horizontally() worksheet.center_vertically() # Write sample data for row in range(50): worksheet.write_row(row, 0, [f"Data {row}-{col}" for col in range(6)]) workbook.close() ``` -------------------------------- ### Install XlsxWriter from Source Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/getting_started.md Commands to install XlsxWriter from a downloaded tarball or a cloned GitHub repository. This requires running the setup.py script. ```bash tar -zxvf XlsxWriter-1.2.3.tar.gz cd XlsxWriter-1.2.3 python setup.py install ``` ```bash git clone https://github.com/jmcnamara/XlsxWriter.git cd XlsxWriter python setup.py install ``` -------------------------------- ### Example 2: Table with Data Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/example_tables.md Creates a table and populates it with provided data. ```APIDOC ## Example 2: Table with Data ### Description This example shows how to create an Excel table and simultaneously populate it with data using the `data` option in the `add_table` method. ### Method `worksheet.add_table(range, options)` ### Endpoint N/A ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body - **range** (string) - Required - The cell range for the table (e.g., "B3:F7"). - **options** (dict) - Optional - Configuration options for the table. - **data** (list of lists) - The data to be inserted into the table. ### Request Example ```python import xlsxwriter workbook = xlsxwriter.Workbook("tables.xlsx") worksheet2 = workbook.add_worksheet() # Some sample data for the table. data = [ ["Apples", 10000, 5000, 8000, 6000], ["Pears", 2000, 3000, 4000, 5000], ["Bananas", 6000, 6000, 6500, 6000], ["Oranges", 500, 300, 200, 700], ] # Set the columns widths. worksheet2.set_column("B:G", 12) # Add a table to the worksheet with data. worksheet2.add_table("B3:F7", {"data": data}) workbook.close() ``` ### Response #### Success Response (None) Modifies the worksheet object by adding and populating a table. #### Response Example N/A ``` -------------------------------- ### Number Format Examples Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/format.md Demonstrates setting number formats using strings and built-in index numbers, including examples for zip codes, general numbers, percentages, dates, and times. ```APIDOC ## Number Formatting ### Description This section covers setting various numeric formats for cells, including custom formats like zip codes, percentages, dates, and using Excel's built-in formats via index. ### `set_num_format(format_string)` Applies a number format to a cell. #### Parameters - **format_string** (string) - The format string or index number. ### Examples ```python # Zip code format cell_format11.set_num_format('00000') worksheet.write(13, 0, 1209, cell_format11) # Using Excel's built-in formats by index cell_format.set_num_format(3) # Equivalent to '#,##0' # Example of a date format cell_format.set_num_format('m/d/yy') # Example of a percentage format cell_format.set_num_format('0%') ``` ### Built-in Format Index Table | Index | Format String | |---------|------------------------------------------------------| | 0 | `General` | | 1 | `0` | | 2 | `0.00` | | 3 | `#,##0` | | 4 | `#,##0.00` | | 5 | `($#,##0_);($#,##0)` | | 6 | `($#,##0_);[Red]($#,##0)` | | 7 | `($#,##0.00_);($#,##0.00)` | | 8 | `($#,##0.00_);[Red]($#,##0.00)` | | 9 | `0%` | | 10 | `0.00%` | | 11 | `0.00E+00` | | 12 | `# ?/?` | | 13 | `# ??/??` | | 14 | `m/d/yy` | | 15 | `d-mmm-yy` | | 16 | `d-mmm` | | 17 | `mmm-yy` | | 18 | `h:mm AM/PM` | | 19 | `h:mm:ss AM/PM` | | 20 | `h:mm` | | 21 | `h:mm:ss` | | 22 | `m/d/yy h:mm` | | 37 | `(#,##0_);(#,##0)` | | 38 | `(#,##0_);[Red](#,##0)` | | 39 | `(#,##0.00_);(#,##0.00)` | | 40 | `(#,##0.00_);[Red](#,##0.00)` | | 41 | `_(* #,##0_);_(* (#,##0);_(* "-");_(@_)` | | 42 | `_($* #,##0_);_($* (#,##0);_($* "-");_(@_)` | | 43 | `_(* #,##0.00_);_(* (#,##0.00);_(* "-??");_(@_)` | | 44 | `_($* #,##0.00_);_($* (#,##0.00);_($* "-??");_(@_)` | | 45 | `mm:ss` | | 46 | `[h]:mm:ss` | | 47 | `mm:ss.0` | | 48 | `##0.0E+0` | | 49 | `@` | *Note: Formats 23-36 are undocumented and may vary. Currency symbols depend on locale.* ``` -------------------------------- ### Basic XlsxWriter Functionality Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/contents.md Provides fundamental examples of using XlsxWriter, including a 'Hello World' example, demonstrating simple features, and handling exceptions during file closing. ```python import xlsxwriter # Example: Hello World workbook = xlsxwriter.Workbook('hello_world.xlsx') worksheet = workbook.add_worksheet() worksheet.write('A1', 'Hello world') workbook.close() # Example: Simple Feature Demonstration workbook = xlsxwriter.Workbook('simple_demo.xlsx') worksheet = workbook.add_worksheet() worksheet.write('A1', 123) worksheet.write('A2', '=SUM(A1, 10)') workbook.close() # Example: Catch exception on closing try: workbook = xlsxwriter.Workbook('exception_close.xlsx') # ... operations ... workbook.close() # This might raise an exception if not properly closed except Exception as e: print(f'An error occurred: {e}') ``` -------------------------------- ### Example 1: Default Table Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/example_tables.md Creates a default table with no data specified. ```APIDOC ## Example 1: Default Table ### Description This example demonstrates creating a basic Excel table using `add_table` without providing any data or specific options. The table will have default formatting and features. ### Method `worksheet.add_table(range)` ### Endpoint N/A ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body - **range** (string) - Required - The cell range for the table (e.g., "B3:F7"). ### Request Example ```python import xlsxwriter workbook = xlsxwriter.Workbook("tables.xlsx") worksheet1 = workbook.add_worksheet() # Set the columns widths. worksheet1.set_column("B:G", 12) # Add a table to the worksheet. worksheet1.add_table("B3:F7") workbook.close() ``` ### Response #### Success Response (None) Modifies the worksheet object by adding a table. #### Response Example N/A ``` -------------------------------- ### Create Gauge Chart with XlsxWriter Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/contents.md Demonstrates how to create a gauge chart (often implemented using a doughnut or pie chart with specific configurations) with XlsxWriter. This example shows a basic setup. ```python import xlsxwriter workbook = xlsxwriter.Workbook('chart_gauge.xlsx') worksheet = workbook.add_worksheet() # Sample data for a gauge chart (often uses a doughnut chart) data = [ ['Label', 'Value'], ['', 70], ['', 30], ] worksheet.write_row('A1', data[0]) worksheet.write_row('A2', data[1]) worksheet.write_row('A3', data[2]) # Create a new chart object. chart = workbook.add_chart({'type': 'doughnut'}) # Add a series to the chart. chart.add_series({ 'name': 'Gauge', 'categories': '=Sheet1!$A$2:$A$3', 'values': '=Sheet1!$B$2:$B$3', 'points': [ {'fill': {'color': '#4E79A7'}}, {'fill': {'color': '#E15759'}}, ], 'doughnut_hole': 75, # Make it a narrow doughnut to resemble a gauge needle }) # Add a title. chart.set_title({'name': 'Gauge Chart Example'}) # Remove legend. chart.set_legend({'none': True}) # Insert the chart into the worksheet. worksheet.insert_chart('D2', chart) workbook.close() ``` -------------------------------- ### Configure Line Formatting Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/working_with_charts.md Examples of configuring line properties including visibility, color, width, dash style, and transparency. ```python chart.add_series({ 'values': '=Sheet1!$A$1:$A$6', 'line': {'none': True}, 'marker': {'type': 'automatic'}, }) chart.add_series({ 'values': '=Sheet1!$A$1:$A$6', 'line': {'color': '#FF0000'}, }) chart.add_series({ 'values': '=Sheet1!$A$1:$A$6', 'line': {'width': 3.25}, }) chart.add_series({ 'values': '=Sheet1!$A$1:$A$6', 'line': {'dash_type': 'dash_dot'}, }) chart.add_series({ 'values': '=Sheet1!$A$1:$A$6', 'line': {'color': 'yellow', 'transparency': 50}, }) ``` -------------------------------- ### Example 7: User Defined Column Headers Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/example_tables.md Illustrates how to define custom headers for a table. ```APIDOC ## Example 7: User Defined Column Headers ### Description This example shows how to create an Excel table with user-defined column headers instead of relying on default headers or data. ### Method `worksheet.add_table(range, options)` ### Endpoint N/A ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body - **range** (string) - Required - The cell range for the table (e.g., "B3:F7"). - **options** (dict) - Optional - Configuration options for the table. - **header** (list) - A list of strings to be used as custom table headers. ### Request Example ```python import xlsxwriter workbook = xlsxwriter.Workbook("tables.xlsx") worksheet7 = workbook.add_worksheet() # Some sample data for the table. data = [ [10000, 5000, 8000, 6000], [2000, 3000, 4000, 5000], [6000, 6000, 6500, 6000], [500, 300, 200, 700], ] # Set the columns widths. worksheet7.set_column("B:G", 12) # Add a table with user defined column headers. worksheet7.add_table("B3:F7", { "header": ["Apples", "Pears", "Bananas", "Oranges"] }) # Table data can also be written separately. worksheet7.write_row("B4", data[0]) worksheet7.write_row("B5", data[1]) worksheet7.write_row("B6", data[2]) worksheet7.write_row("B7", data[3]) workbook.close() ``` ### Response #### Success Response (None) Modifies the worksheet object by adding a table with custom headers. #### Response Example N/A ``` -------------------------------- ### Basic Pie Chart Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/example_chart_pie.md This example shows how to create a basic pie chart with default colors and a title. ```APIDOC ## POST /api/charts/pie ### Description Creates a basic pie chart in an Excel file. ### Method POST ### Endpoint /api/charts/pie ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body - **workbook_name** (string) - Required - The name of the Excel workbook. - **worksheet_name** (string) - Required - The name of the worksheet. - **chart_title** (string) - Required - The title of the pie chart. - **categories** (array) - Required - An array of category names. - **values** (array) - Required - An array of numerical values corresponding to categories. ### Request Example ```json { "workbook_name": "chart_pie.xlsx", "worksheet_name": "Sheet1", "chart_title": "Popular Pie Types", "categories": ["Apple", "Cherry", "Pecan"], "values": [60, 30, 10] } ``` ### Response #### Success Response (200) - **message** (string) - Success message indicating the chart was created. #### Response Example ```json { "message": "Pie chart created successfully." } ``` ``` -------------------------------- ### Install XlsxWriter via PIP Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/getting_started.md Commands to install the XlsxWriter package using the Python pip installer. This is the recommended method for most users. ```bash pip install XlsxWriter pip install --user XlsxWriter ``` -------------------------------- ### Create Radar Chart with XlsxWriter Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/contents.md Demonstrates the creation of a radar chart using XlsxWriter. This example involves setting up data and configuring the chart series for a radar plot. ```python import xlsxwriter workbook = xlsxwriter.Workbook('chart_radar.xlsx') worksheet = workbook.add_worksheet() # Sample data headings = ['Attribute', 'Product A', 'Product B'] attributes = ['Speed', 'Reliability', 'Performance', 'Cost', 'Usability'] product_a = [5, 4, 3, 2, 5] product_b = [3, 5, 4, 4, 3] worksheet.write_row('A1', headings) worksheet.write_column('A2', attributes) worksheet.write_column('B2', product_a) worksheet.write_column('C2', product_b) # Create a new chart object. chart = workbook.add_chart({'type': 'radar'}) # Add a series to the chart. chart.add_series({ 'name': '=Sheet1!$B$1', 'categories': '=Sheet1!$A$2:$A$6', 'values': '=Sheet1!$B$2:$B$6', }) chart.add_series({ 'name': '=Sheet1!$C$1', 'categories': '=Sheet1!$A$2:$A$6', 'values': '=Sheet1!$C$2:$C$6', }) # Add a title. chart.set_title({'name': 'Radar Chart Example'}) # Insert the chart into the worksheet. worksheet.insert_chart('E2', chart) workbook.close() ``` -------------------------------- ### Create Pie Chart with XlsxWriter Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/contents.md Illustrates how to generate a pie chart using XlsxWriter. This example covers setting up data and configuring the chart series for a pie representation. ```python import xlsxwriter workbook = xlsxwriter.Workbook('chart_pie.xlsx') worksheet = workbook.add_worksheet() # Sample data that will be referenced in the chart data = [ ['Task', 'Hours per Day'], ['Work', 11 ], ['Eat', 2 ], ['Commute', 2 ], ['Sleep', 7 ], ['Repeat', 2 ], ] worksheet.write_row('A1', data[0]) worksheet.write_row('A2', data[1]) worksheet.write_row('A3', data[2]) worksheet.write_row('A4', data[3]) worksheet.write_row('A5', data[4]) worksheet.write_row('A6', data[5]) # Create a new chart object. chart = workbook.add_chart({'type': 'pie'}) # Add a series to the chart. chart.add_series({ 'name': 'Progress', 'categories': '=Sheet1!$A$2:$A$7', 'values': '=Sheet1!$B$2:$B$7', 'points': [ {'fill': {'color': '#4E79A7'}}, {'fill': {'color': '#F28E2B'}}, {'fill': {'color': '#E15759'}}, {'fill': {'color': '#76B7B2'}}, {'fill': {'color': '#59A14F'}}, {'fill': {'color': '#EDC948'}}, ], }) # Add a title. chart.set_title({'name': 'Pie Chart Example'}) # Add a legend. chart.set_legend({'position': 'right'}) # Insert the chart into the worksheet. worksheet.insert_chart('D2', chart) workbook.close() ``` -------------------------------- ### Chart with Pattern Fills Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/example_chart_pattern.md This example shows how to create a column chart with different pattern fills for each series, including 'shingle' and 'horizontal_brick' patterns, with custom foreground and background colors, and borders. ```APIDOC ## Chart with Pattern Fills ### Description This endpoint demonstrates creating an Excel chart with pattern fills using the XlsxWriter library. It includes adding data, configuring a column chart, and applying distinct patterns and colors to multiple series. ### Method N/A (This is a script example, not an API endpoint) ### Endpoint N/A ### Parameters N/A ### Request Example ```python import xlsxwriter workbook = xlsxwriter.Workbook("chart_pattern.xlsx") worksheet = workbook.add_worksheet() bold = workbook.add_format({"bold": 1}) # Add the worksheet data that the charts will refer to. headings = ["Shingle", "Brick"] data = [ [105, 150, 130, 90], [50, 120, 100, 110], ] worksheet.write_row("A1", headings, bold) worksheet.write_column("A2", data[0]) worksheet.write_column("B2", data[1]) # Create a new Chart object. chart = workbook.add_chart({"type": "column"}) # Configure the charts. Add two series with patterns. The gap is used to make # the patterns more visible. chart.add_series( { "name": "=Sheet1!$A$1", "values": "=Sheet1!$A$2:$A$5", "pattern": {"pattern": "shingle", "fg_color": "#804000", "bg_color": "#c68c53"}, "border": {"color": "#804000"}, "gap": 70, } ) chart.add_series( { "name": "=Sheet1!$B$1", "values": "=Sheet1!$B$2:$B$5", "pattern": { "pattern": "horizontal_brick", "fg_color": "#b30000", "bg_color": "#ff6666", }, "border": {"color": "#b30000"}, } ) # Add a chart title and some axis labels. chart.set_title({"name": "Cladding types"}) chart.set_x_axis({"name": "Region"}) chart.set_y_axis({"name": "Number of houses"}) # Insert the chart into the worksheet. worksheet.insert_chart("D2", chart) workbook.close() ``` ### Response N/A (This script generates an Excel file, not a direct API response) #### Success Response (200) N/A #### Response Example N/A ``` -------------------------------- ### Nested Chart Formatting Example Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/working_with_charts.md Illustrates how to apply nested formatting properties, such as marker border and fill, within a chart series. ```APIDOC ## POST /chart/series/nested/format ### Description Apply nested formatting properties to chart elements, such as marker border and fill colors. ### Method POST ### Endpoint /chart/series/nested/format ### Parameters #### Request Body - **values** (string) - Required - The range of values for the series. - **line** (object) - Optional - Line formatting options. - **color** (string) - Optional - The color of the line. - **marker** (object) - Optional - Marker formatting options. - **type** (string) - Optional - The type of marker (e.g., 'square'). - **size** (integer) - Optional - The size of the marker. - **border** (object) - Optional - Border properties for the marker. - **color** (string) - Required - The border color. - **fill** (object) - Optional - Fill properties for the marker. - **color** (string) - Required - The fill color. ### Request Example ```json { "values": "=Sheet1!$A$1:$A$6", "line": { "color": "blue" }, "marker": { "type": "square", "size": 5, "border": { "color": "red" }, "fill": { "color": "yellow" } } } ``` ### Response #### Success Response (200) - **status** (string) - Indicates success. #### Response Example ```json { "status": "success" } ``` ``` -------------------------------- ### Create Doughnut Chart with XlsxWriter Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/contents.md Provides an example of generating a doughnut chart using XlsxWriter. It demonstrates how to define the data and chart series for this specific chart type. ```python import xlsxwriter workbook = xlsxwriter.Workbook('chart_doughnut.xlsx') worksheet = workbook.add_worksheet() # Sample data that will be referenced in the chart data = [ ['Task', 'Hours per Day'], ['Work', 11 ], ['Eat', 2 ], ['Commute', 2 ], ['Sleep', 7 ], ['Repeat', 2 ], ] worksheet.write_row('A1', data[0]) worksheet.write_row('A2', data[1]) worksheet.write_row('A3', data[2]) worksheet.write_row('A4', data[3]) worksheet.write_row('A5', data[4]) worksheet.write_row('A6', data[5]) # Create a new chart object. chart = workbook.add_chart({'type': 'doughnut'}) # Add a series to the chart. chart.add_series({ 'name': 'Progress', 'categories': '=Sheet1!$A$2:$A$7', 'values': '=Sheet1!$B$2:$B$7', 'points': [ {'fill': {'color': '#4E79A7'}}, {'fill': {'color': '#F28E2B'}}, {'fill': {'color': '#E15759'}}, {'fill': {'color': '#76B7B2'}}, {'fill': {'color': '#59A14F'}}, {'fill': {'color': '#EDC948'}}, ], }) # Add a title. chart.set_title({'name': 'Doughnut Chart Example'}) # Add a legend. chart.set_legend({'position': 'right'}) # Insert the chart into the worksheet. worksheet.insert_chart('D2', chart) workbook.close() ``` -------------------------------- ### Implement Autofilter with Row Hiding Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/working_with_autofilters.md A complete example showing how to set an autofilter, apply criteria, and iterate through data to hide rows that do not meet the filter requirements. ```python worksheet.autofilter('A1:D51') worksheet.filter_column(0, 'Region == East') row = 1 for row_data in (data): region = row_data[0] if region == 'East': pass else: worksheet.set_row(row, options={'hidden': True}) worksheet.write_row(row, 0, row_data) row += 1 ``` -------------------------------- ### Creating and Configuring Format Objects Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/format.md Demonstrates how to instantiate a Format object using the workbook.add_format() method. Shows both the object-oriented setter approach and the dictionary-based property configuration. ```python cell_format1 = workbook.add_format() cell_format2 = workbook.add_format({'bold': True, 'font_color': 'red'}) cell_format = workbook.add_format() cell_format.set_bold() cell_format.set_font_color('red') ``` -------------------------------- ### Create an Excel file with XlsxWriter Source: https://github.com/jmcnamara/xlsxwriter/blob/main/README.rst This example demonstrates how to initialize a workbook, add a worksheet, apply formatting to cells, write various data types, and insert an image. It highlights the basic workflow of creating an XLSX file using the library. ```python import xlsxwriter # Create an new Excel file and add a worksheet. workbook = xlsxwriter.Workbook("demo.xlsx") worksheet = workbook.add_worksheet() # Widen the first column to make the text clearer. worksheet.set_column("A:A", 20) # Add a bold format to use to highlight cells. bold = workbook.add_format({"bold": True}) # Write some simple text. worksheet.write("A1", "Hello") # Text with formatting. worksheet.write("A2", "World", bold) # Write some numbers, with row/column notation. worksheet.write(2, 0, 123) worksheet.write(3, 0, 123.456) # Insert an image. worksheet.insert_image("B5", "logo.png") workbook.close() ``` -------------------------------- ### Configure Workbook Initialization Options Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/workbook.md Demonstrates how to initialize a Workbook with specific options like default date formats, timezone removal, ZIP64 support, and the 1904 date system. ```python xlsxwriter.Workbook(filename, {'default_date_format': 'dd/mm/yy'}) workbook = xlsxwriter.Workbook(filename, {'remove_timezone': True}) workbook = xlsxwriter.Workbook(filename, {'use_zip64': True}) workbook = xlsxwriter.Workbook(filename, {'date_1904': True}) ``` -------------------------------- ### Writing User Defined Types (UUID Example) Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/example_user_types1.md This example demonstrates how to add a custom handler for UUID data types to the XlsxWriter write() method using add_write_handler(). It shows how to convert a UUID object to a string before writing it to the Excel sheet, preventing potential TypeErrors. ```APIDOC ## Writing User Defined Types (UUID Example) ### Description This example demonstrates how to add a custom handler for UUID data types to the XlsxWriter `write()` method using `add_write_handler()`. It shows how to convert a UUID object to a string before writing it to the Excel sheet, preventing potential TypeErrors. ### Method Not Applicable (This is a Python script demonstrating library functionality) ### Endpoint Not Applicable ### Parameters Not Applicable ### Request Example ```python import uuid import xlsxwriter # Create a function that will behave like a worksheet write() method. def write_uuid(worksheet, row, col, token, cell_format=None): return worksheet.write_string(row, col, str(token), cell_format) # Set up the workbook as usual. workbook = xlsxwriter.Workbook("user_types1.xlsx") worksheet = workbook.add_worksheet() # Make the first column wider for clarity. worksheet.set_column("A:A", 40) # Add the write() handler/callback to the worksheet. worksheet.add_write_handler(uuid.UUID, write_uuid) # Create a UUID. my_uuid = uuid.uuid3(uuid.NAMESPACE_DNS, "python.org") # Write the UUID. This would raise a TypeError without the handler. worksheet.write("A1", my_uuid) workbook.close() ``` ### Response #### Success Response (File Creation) - The script creates an Excel file named `user_types1.xlsx`. - The file contains a single sheet with one column. - Cell A1 contains the string representation of the generated UUID. #### Response Example (No direct API response, but the created file `user_types1.xlsx` is the output.) ``` -------------------------------- ### Configure table with totals and formulas Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/working_with_tables.md Shows how to define a table with a total row, including specific total functions and pre-calculated total values for compatibility. ```python options = {'data': data, 'total_row': 1, 'columns': [{'total_string': 'Totals'}, {'total_function': 'sum', 'total_value': 150}, {'total_function': 'sum', 'total_value': 200}, {'total_function': 'sum', 'total_value': 333}, {'total_function': 'sum', 'total_value': 124}, {'formula': '=SUM(Table10[@[Quarter 1]:[Quarter 4]])', 'total_function': 'sum', 'total_value': 807}]} ``` -------------------------------- ### Add Cell Comments to Worksheet (Python) Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/example_comments1.md This Python code snippet shows how to add a basic comment to a cell in an Excel worksheet using the XlsxWriter library. It initializes a workbook, adds a worksheet, writes data to cell A1, and then adds a comment to the same cell. This is a simple example, with more advanced features available in other examples. ```python ############################################################################### # # An example of writing cell comments to a worksheet using Python and # XlsxWriter. # # For more advanced comment options see comments2.py. # # SPDX-License-Identifier: BSD-2-Clause # # Copyright (c) 2013-2025, John McNamara, jmcnamara@cpan.org # import xlsxwriter workbook = xlsxwriter.Workbook("comments1.xlsx") worksheet = workbook.add_worksheet() worksheet.write("A1", "Hello") worksheet.write_comment("A1", "This is a comment") workbook.close() ``` -------------------------------- ### Configure Column Options and Outlines Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/worksheet.md Explains how to use the options dictionary to hide columns, set outline levels for grouping, and manage collapsed outline states. ```python worksheet.set_column('D:D', 20, cell_format, {'hidden': 1}) worksheet.set_column('B:G', None, None, {'level': 1}) worksheet.set_column('H:H', None, None, {'collapsed': 1}) ``` -------------------------------- ### Example 3: Table without Autofilter Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/example_tables.md Creates a table with the default autofilter disabled. ```APIDOC ## Example 3: Table without Autofilter ### Description This example demonstrates how to create an Excel table while disabling the default autofilter functionality using the `autofilter` option. ### Method `worksheet.add_table(range, options)` ### Endpoint N/A ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body - **range** (string) - Required - The cell range for the table (e.g., "B3:F7"). - **options** (dict) - Optional - Configuration options for the table. - **autofilter** (int) - Set to 0 to disable the autofilter. ### Request Example ```python import xlsxwriter workbook = xlsxwriter.Workbook("tables.xlsx") worksheet3 = workbook.add_worksheet() # Some sample data for the table. data = [ ["Apples", 10000, 5000, 8000, 6000], ["Pears", 2000, 3000, 4000, 5000], ["Bananas", 6000, 6000, 6500, 6000], ["Oranges", 500, 300, 200, 700], ] # Set the columns widths. worksheet3.set_column("B:G", 12) # Add a table to the worksheet without default autofilter. worksheet3.add_table("B3:F7", {"autofilter": 0}) # Table data can also be written separately. worksheet3.write_row("B4", data[0]) worksheet3.write_row("B5", data[1]) worksheet3.write_row("B6", data[2]) worksheet3.write_row("B7", data[3]) workbook.close() ``` ### Response #### Success Response (None) Modifies the worksheet object by adding a table without an autofilter. #### Response Example N/A ``` -------------------------------- ### Create and Write to a Worksheet with XlsxWriter Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/worksheet.md Demonstrates how to instantiate a Workbook, add worksheets, and perform basic data writing using the write() method. This is the standard pattern for initializing and populating an Excel file. ```python workbook = xlsxwriter.Workbook('filename.xlsx') worksheet1 = workbook.add_worksheet() worksheet2 = workbook.add_worksheet() worksheet1.write('A1', 123) workbook.close() ``` -------------------------------- ### Method: write_column Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/worksheet.md Writes a list or tuple of data vertically down a column starting from a specified cell. ```APIDOC ## write_column(row, col, data, cell_format=None) ### Description Writes a sequence of data to a column. This is a convenient method for writing database query results or lists vertically. ### Parameters #### Path Parameters - **row** (int/str) - Required - The cell row (zero indexed) or A1 notation. - **col** (int) - Required - The cell column (zero indexed). - **data** (list/tuple) - Required - The data to write. - **cell_format** (Format) - Optional - A Format object to apply to the cells. ### Request Example ```python worksheet.write_column('A1', ['Foo', 'Bar', 'Baz']) ``` ### Response #### Success Response (0) - **int** - Returns 0 on success. ``` -------------------------------- ### Example 6: Banded Columns Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/example_tables.md Applies banded formatting to columns, disabling default banded rows. ```APIDOC ## Example 6: Banded Columns ### Description This example demonstrates how to apply banded formatting to columns in an Excel table while disabling the default banded row formatting. ### Method `worksheet.add_table(range, options)` ### Endpoint N/A ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body - **range** (string) - Required - The cell range for the table (e.g., "B3:F7"). - **options** (dict) - Optional - Configuration options for the table. - **banded_rows** (int) - Set to 0 to disable banded rows. - **banded_columns** (int) - Set to 1 to enable banded columns. ### Request Example ```python import xlsxwriter workbook = xlsxwriter.Workbook("tables.xlsx") worksheet6 = workbook.add_worksheet() # Some sample data for the table. data = [ ["Apples", 10000, 5000, 8000, 6000], ["Pears", 2000, 3000, 4000, 5000], ["Bananas", 6000, 6000, 6500, 6000], ["Oranges", 500, 300, 200, 700], ] # Set the columns widths. worksheet6.set_column("B:G", 12) # Add a table with banded columns but without default banded rows. worksheet6.add_table("B3:F7", {"banded_rows": 0, "banded_columns": 1}) # Table data can also be written separately. worksheet6.write_row("B4", data[0]) worksheet6.write_row("B5", data[1]) worksheet6.write_row("B6", data[2]) worksheet6.write_row("B7", data[3]) workbook.close() ``` ### Response #### Success Response (None) Modifies the worksheet object by adding a table with banded columns. #### Response Example N/A ``` -------------------------------- ### Create Format Object Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/workbook.md Shows how to instantiate a format object for cell styling. Properties can be passed as a dictionary during creation or set later. ```python format1 = workbook.add_format(props) # Set properties at creation. format2 = workbook.add_format() # Set properties later. ``` -------------------------------- ### Example 4: Table without Header Row Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/example_tables.md Creates a table where the default header row is suppressed. ```APIDOC ## Example 4: Table without Header Row ### Description This example demonstrates creating an Excel table without the default header row by setting the `header_row` option to 0. ### Method `worksheet.add_table(range, options)` ### Endpoint N/A ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body - **range** (string) - Required - The cell range for the table (e.g., "B4:F7"). Note the starting row might change if no header is present. - **options** (dict) - Optional - Configuration options for the table. - **header_row** (int) - Set to 0 to disable the header row. ### Request Example ```python import xlsxwriter workbook = xlsxwriter.Workbook("tables.xlsx") worksheet4 = workbook.add_worksheet() # Some sample data for the table. data = [ ["Apples", 10000, 5000, 8000, 6000], ["Pears", 2000, 3000, 4000, 5000], ["Bananas", 6000, 6000, 6500, 6000], ["Oranges", 500, 300, 200, 700], ] # Set the columns widths. worksheet4.set_column("B:G", 12) # Add a table to the worksheet without a default header row. worksheet4.add_table("B4:F7", {"header_row": 0}) # Table data can also be written separately. worksheet4.write_row("B4", data[0]) worksheet4.write_row("B5", data[1]) worksheet4.write_row("B6", data[2]) worksheet4.write_row("B7", data[3]) workbook.close() ``` ### Response #### Success Response (None) Modifies the worksheet object by adding a table without a header row. #### Response Example N/A ``` -------------------------------- ### Format XML using xmllint Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/workbook.md Demonstrates how to use the `xmllint` command-line tool to format an XML file for better readability, particularly useful for inspecting `custom.xml` within unzipped Excel files. ```bash $ xmllint --format myfile/docProps/custom.xml true 2024-01-01T12:00:00Z Privileged Confidential cb46c030-1825-4e81-a295-151c039dbf02 88124cf5-1340-457d-90e1-0000a9427c99 ``` -------------------------------- ### Writing Formatted Data and Dates to Excel Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/tutorial03.md A complete example demonstrating how to initialize a workbook, apply custom formats for money and dates, and write structured data including strings, dates, and formulas to a worksheet. ```python from datetime import datetime import xlsxwriter workbook = xlsxwriter.Workbook('Expenses03.xlsx') worksheet = workbook.add_worksheet() bold = workbook.add_format({'bold': 1}) money_format = workbook.add_format({'num_format': '$#,##0'}) date_format = workbook.add_format({'num_format': 'mmmm d yyyy'}) worksheet.set_column(1, 1, 15) worksheet.write('A1', 'Item', bold) worksheet.write('B1', 'Date', bold) worksheet.write('C1', 'Cost', bold) expenses = ( ['Rent', '2013-01-13', 1000], ['Gas', '2013-01-14', 100], ['Food', '2013-01-16', 300], ['Gym', '2013-01-20', 50], ) row = 1 for item, date_str, cost in expenses: date = datetime.strptime(date_str, "%Y-%m-%d") worksheet.write_string(row, 0, item) worksheet.write_datetime(row, 1, date, date_format) worksheet.write_number(row, 2, cost, money_format) row += 1 worksheet.write(row, 0, 'Total', bold) worksheet.write(row, 2, '=SUM(C2:C5)', money_format) workbook.close() ``` -------------------------------- ### Method: write_row Source: https://github.com/jmcnamara/xlsxwriter/blob/main/dev/docs/source/worksheet.md Writes a list or tuple of data horizontally across a row starting from a specified cell. ```APIDOC ## write_row(row, col, data, cell_format=None) ### Description Writes a sequence of data to a row. This is a convenient method for writing database query results or lists. ### Parameters #### Path Parameters - **row** (int/str) - Required - The cell row (zero indexed) or A1 notation. - **col** (int) - Required - The cell column (zero indexed). - **data** (list/tuple) - Required - The data to write. - **cell_format** (Format) - Optional - A Format object to apply to the cells. ### Request Example ```python worksheet.write_row('A1', ['Foo', 'Bar', 'Baz']) ``` ### Response #### Success Response (0) - **int** - Returns 0 on success. ```