DOCSEARCHbyAlgolia
Submit Docs

XlsxWriter Documentation

repository·main·Indexed 26 days ago

https://github.com/jmcnamara/xlsxwriter

A Python module for creating Excel 2007+ XLSX files. It provides extensive support for formatting, charts, images, and data validation, and integrates with data science tools like Pandas and Polars. The library includes comprehensive features for creating various chart types (area, bar, column, line, pie, doughnut, scatter, stock, radar), configuring axes, and managing chart legends and titles.

Tokens
73.5K
Snippets
201
Records
462
Agent score
85%

What's inside XlsxWriter

  1. Overview of XlsxWriter capabilities

    main

    XlsxWriter is a Python module used to create Excel 2007+ XLSX files. It allows you to write text, numbers, formulas, and hyperlinks to multiple worksheets.

    Key features include:

    • 100% compatible Excel XLSX files.
    • Full formatting, merged cells, and defined names.
    • Charts, Autofilters, and Data validation (including drop down lists).
    • Conditional formatting.
    • Support for various image formats (PNG, JPEG, GIF, BMP, WMF, EMF).
    • Rich multi-format strings and cell comments.
    • Textboxes and Sparklines.
    • Integration with Pandas and Polars.
    • Memory optimization mode for writing large files.

  2. Overview of XlsxWriter

    main

    XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format. It is 100% compatible with Excel XLSX files and uses standard libraries only. It supports Python 3.8+ and PyPy3.

    Key features include:

    • Full formatting, merged cells, and defined names.
    • Charts, Autofilters, and Data validation (including drop down lists).
    • Conditional formatting.
    • Worksheet images (PNG, JPEG, GIF, BMP, WMF, EMF).
    • Rich multi-format strings and cell comments.
    • Integration with Pandas and Polars.
    • Textboxes and support for adding Macros.
    • Memory optimization mode for writing large files.

  3. Compare XlsxWriter with alternative Excel modules

    main

    If XlsxWriter does not meet your requirements, consider these Python alternatives based on your specific needs:

    • OpenPyXL: Use this if you need to both read and write modern Excel files (.xlsx, .xlsm, .xltx, .xltm).
    • Xlwings: Use this for interactive data analysis and leveraging the Python scientific stack (Jupyter, NumPy, Pandas, etc.) to interact with Excel. It is available on Windows and Mac.
    • XLWT: Use this specifically for writing data and formatting to the older Excel .xls format.
    • XLRD: Use this specifically for reading data and formatting from the historical .xls format.

  4. Write Polars DataFrames to Excel

    main

    You can export a Polars DataFrame directly to an Excel file using the write_excel() method. This method automatically formats the data as an Excel Data Table.

    Note: The write_excel() API is part of the Polars library, which uses XlsxWriter internally for this integration.

    import polars as pl

df = pl.DataFrame({"Data": [10, 20, 30, 20, 15, 30, 45]})

df.write_excel(workbook="polars_simple.xlsx")

  5. Convert a Pandas DataFrame to an Excel Table

    main

    To group a DataFrame range into an Excel Table object, write the data without the index or header (using header=False and index=False), then use worksheet.add_table() with a list of column settings generated from the DataFrame columns.

    # Write the data without the index or header, and by starting 1 row forward to allow space for the table header.
df.to_excel(writer, sheet_name='Sheet1',
            startrow=1, header=False, index=False)

# Create a list of headers to use in add_table().
column_settings = [{'header': column} for column in df.columns]

# Add the Excel table structure.
(max_row, max_col) = df.shape
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

  6. Create a simple XLSX file with XlsxWriter

    main

    To create an Excel file, you must follow a specific lifecycle: import the module, create a Workbook object, add a worksheet, write your data, and finally call close() on the workbook.

    Important Limitations:

    • XlsxWriter can only create new files. It cannot read or modify existing files.
    import xlsxwriter

# 1. Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()

# 2. Data to write
expenses = (
    ['Rent', 1000],
    ['Gas',   100],
    ['Food',  300],
    ['Gym',    50],
)

# 3. Write data (rows and columns are zero-indexed)
row = 0
col = 0
for item, cost in expenses:
    worksheet.write(row, col,     item)
    worksheet.write(row, col + 1, cost)
    row += 1

# 4. Write a formula
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

# 5. Close the workbook to save the file
workbook.close()

  7. Handle timezones in datetimes

    main

    Excel does not support timezones. When passing timezone-aware Python datetime objects to write_datetime(), you have two options:

    1. Manual Conversion: Convert the datetime to a timezone-adjusted time and manually remove the tzinfo using .replace(tzinfo=None).
    2. Automatic Removal: Use the remove_timezone option in the Workbook constructor. When set to True, XlsxWriter will automatically strip the timezone from datetime values passed to write_datetime().

    If using pandas.ExcelWriter with the xlsxwriter engine, pass the option through the options argument.

    # Option 1: Manual removal
naive_datetime = utc_datetime.replace(tzinfo=None)
worksheet.write_datetime(row, 0, naive_datetime, date_format)

# Option 2: Workbook constructor option
workbook = xlsxwriter.Workbook(filename, {'remove_timezone': True})

# Using with Pandas
import pandas as pd
writer = pd.ExcelWriter('pandas_example.xlsx', 
                        engine='xlsxwriter', 
                        options={'remove_timezone': True})

  9. Create a Worksheet using Workbook.add_worksheet()

    main

    A Worksheet object represents an Excel worksheet and cannot be instantiated directly. You must create a new worksheet by calling the add_worksheet() method on a Workbook instance.

    Excel worksheet limits supported by XlsxWriter are 1,048,576 rows by 16,384 columns.

    import xlsxwriter

workbook   = xlsxwriter.Workbook('filename.xlsx')

worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()

worksheet1.write('A1', 123)

workbook.close()

  11. Install XlsxWriter from a tarball

    main

    If you have downloaded a tarball of the XlsxWriter source, you can install it by extracting the archive and running the setup script.

    # For a specific version tarball:
$ tar -zxvf XlsxWriter-1.2.3.tar.gz
$ cd XlsxWriter-1.2.3
$ python setup.py install

# For the latest code from GitHub:
$ curl -O -L http://github.com/jmcnamara/XlsxWriter/archive/main.tar.gz
$ tar zxvf main.tar.gz
$ cd XlsxWriter-main/
$ python setup.py install