A Python module for creating Excel XLSX files.

XlsxWriter

XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format.

XlsxWriter can be used to write text, numbers, formulas and hyperlinks to multiple worksheets and 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/BMP/WMF/EMF images.
  • Rich multi-format strings.
  • Cell comments.
  • Integration with Pandas.
  • Textboxes.
  • Support for adding Macros.
  • Memory optimization mode for writing large files.

It supports Python 2.7, 3.4+ and PyPy and uses standard libraries only.

Here is a simple example:

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()

https://raw.github.com/jmcnamara/XlsxWriter/master/dev/docs/source/_images/demo.png

See the full documentation at: https://xlsxwriter.readthedocs.io

Release notes: https://xlsxwriter.readthedocs.io/changes.html

Owner
John McNamara
anyone lived in a pretty how town with up so floating many bells down
John McNamara
Comments
  • Feature request: Separate data and formatting

    Feature request: Separate data and formatting

    It is a planned feature to allow cell data and formatting to be applied separately.

    This would also allow format copying and merging of formats to be applied to a range of cells.

    However, it is non-trivial to implement correctly and there are no implied timelines on when or if this feature will be available.

    John

  • xlsxwriter/workbook.py causes ValueError('ZIP does not support timestamps before 1980')

    xlsxwriter/workbook.py causes ValueError('ZIP does not support timestamps before 1980')

    Hi,

    I am using XlsxWriter to simply execute the documented basic creation of an xlsx as per: http://xlsxwriter.readthedocs.io/example_pandas_simple.html

    but it reports an error:

    python3 test.py
    Traceback (most recent call last):
      File "test.py", line 22, in <module>
        writer.save()
      File "/usr/local/lib/python3.6/site-packages/pandas/io/excel.py", line 1732, in save
        return self.book.close()
      File "/usr/local/lib/python3.6/site-packages/xlsxwriter/workbook.py", line 311, in close
        self._store_workbook()
      File "/usr/local/lib/python3.6/site-packages/xlsxwriter/workbook.py", line 658, in _store_workbook
        xlsx_file.write(os_filename, xml_filename)
      File "/usr/local/lib/python3.6/zipfile.py", line 1594, in write
        zinfo = ZipInfo.from_file(filename, arcname)
      File "/usr/local/lib/python3.6/zipfile.py", line 496, in from_file
        zinfo = cls(arcname, date_time)
      File "/usr/local/lib/python3.6/zipfile.py", line 338, in __init__
        raise ValueError('ZIP does not support timestamps before 1980')
    ValueError: ZIP does not support timestamps before 1980
    
    

    I am using Python version 3.6.5 and XlsxWriter 1.0.5 under a docker image with debian jessie.

    The workaround I've managed is to edit the file https://github.com/jmcnamara/XlsxWriter/blob/master/xlsxwriter/workbook.py

    and change twice "1980" with "1990". Then there is no error and it works perfectly.

    Not the most elegant and not the root fix of the problem but it does the job. Reporting this out as ye would probably be able to figure out why and solve it for other users permanently.

    Thanks for the libraries John, they all are awesome!

  • Feature request: Add Value From Cells for series data_labels source

    Feature request: Add Value From Cells for series data_labels source

    Add Value From Cells for series data_labels source

    Currently, chart.add_series data_labels only supports:

    • value
    • category
    • series_name
    • position
    • leader_lines
    • percentage

    It would be great to select the source of the data, as is possible in Excel using "Value From Cells" in the label options formatting dialog.

    My particular need is for scatter charts.

    Thanks

  • Feature request: Support for copying the Format instance

    Feature request: Support for copying the Format instance

    Is it possible to add the subject? So that when we have a "general" format for the data processed in a loop, we could e.g. assign a separate Format instance to the cells that we want to write the hyperlinks into.

  • Issue adding images become squeezed

    Issue adding images become squeezed

    Hi,

    I am trying to insert images in spreadsheets. The code works fine, however the images are squeezed. When I open Excel / Format Shape / Size & Properties / Size, the scaling ratios seem rather insane:

    • 339%, 100% for image 1
    • 511%, 100% for image 2
    • 550%, 100% for image 3

    When I press the Reset button in that panel, the image go back to a reasonnable size.

    When I try to change the scale factor, as in the code below, I have to put unreasonnably small numbers for one of the axis, like 0.08, but even that does not work reliably and seems image dependent.

    I am at a lost as to what is happening.

    I am using Python 3.6.8 and XlsxWriter 1.2.7 and Excel 2019 version 2003 (Build 12624.20466).

    Here is some code that demonstrates the problem:

            # img = Image.open(path)
            # w, h = img.size
            # scale = min((240.0 / w, 240.0 / h))
    
            self.worksheet.insert_image(self.row, self.col, path, {
                # 'x_offset': 20, 'y_offset': 20,
                # 'x_scale': 0.8, 'y_scale': 0.08,
            })
    
  • Plans on Implementing Inserting Image in Footer?

    Plans on Implementing Inserting Image in Footer?

    In the documentation under the worksheet.insert_image() method, you mention that inserting an image into the worksheet footer is not supported. Are there any plans on implementing this? I know it would be useful, for example, for putting a company logo in the footer.

  • unable to close(); saves a corrupted workbook

    unable to close(); saves a corrupted workbook

    import xlsxwriter
    
    workbook = xlsxwriter.Workbook('hello.xlsx')
    worksheet = workbook.add_worksheet()
    
    worksheet.write('A1', 'Hello world')
    
    workbook.close()
    
    

    PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: 'C:\Users\DOUGHE~1.ROS\AppData\Local\Temp\tmpnpp39a_c'

    
    I was able to write excel files yesterday; today my day began with this issue and I was not able to resolve.  I was hoping someone here may be of assistance.  
    
    If I run workbook.close() again, I do not receive the error, but the .xlsx file does not open and says it is corrupted.
    
    I'm not sure what happened.  I may have have closed/exited the program incorrectly.  I've read of the importance of using workbook.close(), did I create a 'write to excel ghost' living in the purgatory of my temp folder?  This issue now extends to pandas .to_excel() as well.  I am able to write to csv.   
    
    I've restarted, reinstalled anaconda +packages, deleted all the tmp files in the temp folder, however I still receive this error.  Any help would be greatly appreciated. Thank you.
    
    
    
    windows 10
    python 2.7, 3.6, 3.7 (I had base install of anaconda py 2.7, with an additional environment of 3.6.  Reinstalled anaconda with python 3.7, 64bit.  Issue happened with all three)
  • Issue with protect sheet and sorting

    Issue with protect sheet and sorting

    Hi,

    I am using XlsxWriter to do protect sheet with sorting and autofliter options available, but it appears sorting is not working.

    I am using Python version 3.5.2 and XlsxWriter 1.0.3.

    
    import xlsxwriter
    import uuid
    
    workbook = xlsxwriter.Workbook('test.xlsx')
    worksheet = workbook.add_worksheet()
    
    for x in range(0,5):
    	worksheet.write(0, x, 'Header_' + str(x))
    
    for y in range(1,5):
    	for x in range(0,5):
    		worksheet.write(y, x, str(uuid.uuid4()))
    	
    worksheet.autofilter(0, 0, 4, 4)
    worksheet.protect('pwd', {'sort': 1,'autofilter': 1,})
    
    workbook.close()
    
    
  • Issue with stretched images

    Issue with stretched images

    When inseret image in cell (JPG, PNG all supported), that this image Stretched width. Example Code:

    from xlsxwriter.workbook import Workbook
    
    from PIL import Image
    import urllib
    
    url_img = u'https://www.filepicker.io/api/file/XUCKqJyLSXGbF6a6XyfL'
    img_name = 'test_name.jpg'
    urllib.urlretrieve(url_img, img_name) # Save image
    
    # Convert image
    img = Image.open(img_name)
    img = img.resize((115, 115), Image.ANTIALIAS)
    img.info["dpi"] = (96, 96)
    img_name = "{}_resize.{}".format('test_name_resize', img_name.split('.')[-1])
    img.save(img_name,)
    
    workbook = Workbook('out.xlsx')
    worksheet = workbook.add_worksheet()
    
    worksheet.set_row(2, 95)
    worksheet.set_column(2, 2, 12)
    
    worksheet.insert_image(2, 2, img_name, {'y_offset': 5, })
    workbook.close()
    

    Result: bug

    Correct Version correct

  • Deprecation notice for Python 2.7 (and 3.5) support. Target July 2021

    Deprecation notice for Python 2.7 (and 3.5) support. Target July 2021

    I have just added the following notice to the Changes page of the XlsxWriter docs:

    Deprecation Notice: Python 2.7 reached the end of its life on January 1st, 2020 and is no longer being supported in the community. XlsxWriter support for Python 2.7 will end by mid-year 2021 (probably in July 2021). No new features or fixes for Python 2.7 will be added to XlsxWriter after that date/release.

    If anyone has any concerns about this please raise them now.

  • Feature request: Allow identifying formats by name

    Feature request: Allow identifying formats by name

    Today, when dealing with complex an multiple formats, I use the following piece of code to be able to retrieve them at the time to write data :

    def register_formats(wb, d):
        return {k: wb.add_format(v) if v is not None else v for k, v in d.items()}
    
    #my_formats d parameter simplified for readability sake
    my_formats = register_formats(workbook, {
            'error':  {'bg_color': '#cc0000'},
            'warning': {'bg_color': '#ffff00'},
            'ok': None})
    
    # Much farther in the program ...
    
    ws = wb.add_worksheet()
    ws.write(0,0,"text", my_formats['error'])
    
    

    but then one has to pass this my_format dictionary around.

    I suggest to extend the workbook interface with an optional ref parameter, and then in the write functions be able to give the reference of the format rather than the format itself. It would result in something like :

    wb.add_format({'bg_color': '#cc0000'}, ref="error")
    
    #####
    
    ws.write(0,0,"text", format_ref="error")
    
    

    I can propose an implementation if that helps.

  • Added unique return codes

    Added unique return codes

    Added return codes as anticipated in https://github.com/jmcnamara/XlsxWriter/issues/884

    This pull request is the one including the new modifications to main introduced after PR #887

    The return codes are now an Enum inheriting from str, so an explicative message can be automatically printed without helper functions.

    The return codes are in file returncodes.py

    84 new dedicated tests are added to test/core/test_returncodes.py

    Documentation has been updated

    At the end I run make test, make test_flake8 and make docs. All tests succeeded and flake8 did not report anything. Development and tests were done with Python 3.9.2

  • Updated xl_col_to_name() method to add recursion

    Updated xl_col_to_name() method to add recursion

    This PR links to issue #881 which I opened recently – I propose that the xl_col_to_name() function (here) can be improved to a recursive function to bring it to O(log26 n) time complexity and O(1) auxiliary space.

    I created a GitHub Gist here to compare the times of the older and the newer functions I propose with %timeit. IPython defaults to n = 100 anyway. The updated function is faster.

    Writing tests

    I haven't written any unit tests to resolve the issue as of now – I'm not aware of how to do so, but I can learn.

  • feature request: color chart lines with gradients

    feature request: color chart lines with gradients

    Feature Request

    I would like to color the chart lines with gradients like other chart elements.

    Example: spectrumplot.add_series({ 'categories': '=Spektrum!$B$1:$CD$1', 'values': '=Spektrum!$B$2:$CD$2', 'gradient': { 'colors': ['#B000AE', '#FF00FC', '#0000FF', '#00FFFF', '#00FF00', '#FFFF00', '#FFA200', '#FF0000', '#000000'], 'positions': [0, 5, 17, 26, 37, 50, 56, 76, 100], } })

  • Request: option `invert_if_negative` should be able to define the

    Request: option `invert_if_negative` should be able to define the "Inverted Fill Colour" similar to the "Format Data Series" menu in Excel

    Current behavior

    Enabling option "invert if negative" in Excel (more info), shows an additional Colour picker to define the Inverted Fill Colour:

    Screenshot 2022-02-04 at 14 08 05

    This makes it useful to specify 2 colours; for example green for positive and red for negative values: Screenshot 2022-02-04 at 14 08 41 s

    XlsxWriter has the option invert_if_negative for chart.add_series(), but does not allow to specify the Inverted Fill Colour.

    Hence the negative bars are always coloured white (default), which makes property invert_if_negative not very useful: Screenshot 2022-02-04 at 14 13 49

    Expected behavior

    I expect besides property fill, a new property to define the inverted colour when invert_if_negative is set to True, e.g. inverted_fill.

    chart.add_series({
        'invert_if_negative': True,
        'fill' : {'color': "green"},
        'inverted_fill' : {'color': "red"}
    })
    

    Or instead of 2 parameters for the invert option, change data type of invert_if_negative from bool to dict; just specify a colour like fill:

    chart.add_series({
        'fill' : {'color': "green"},
        'invert_if_negative' : {'color': "red"}
    })
    

    This allows users to specify both the positive and negative bar colours.

    Sample code to reproduce

    import xlsxwriter
    
    SHEET_NAME_DAILY_RESULTS = "Daily Results"
    
    # Data, including negative values:
    day_results_df = pd.DataFrame({'Data': [10, 20, -30, 20, -15, 30, 45]})
    
    # Create a Pandas Excel writer using XlsxWriter as the engine.
    writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
    
    # Convert the dataframe to an XlsxWriter Excel object.
    day_results_df.to_excel(writer, sheet_name=SHEET_NAME_DAILY_RESULTS)
    
    # Get the xlsxwriter objects from the dataframe writer object.
    workbook  = writer.book
    worksheet = writer.sheets[SHEET_NAME_DAILY_RESULTS]
    
    # Create a chart object (see: https://xlsxwriter.readthedocs.io/chart.html):
    chart = workbook.add_chart({'type': 'column'})
    
    # Get the dimensions of the dataframe:
    (max_row, max_col) = day_results_df.shape
    
    # Configure the series of the chart from the dataframe data.
    chart.add_series({
        'categories': [SHEET_NAME_DAILY_RESULTS, 1, 1, max_row, 1],
        'values': [SHEET_NAME_DAILY_RESULTS, 1, max_col, max_row, max_col],
        'invert_if_negative': True, # MISSING option to define negative fill colour
        'fill' : {'color': "green"} 
    })
    
    # Insert the chart into the worksheet, 2 columns next to the last data column:
    worksheet.insert_chart(1, max_col+2, chart)
    
    # Close the Pandas Excel writer and output the Excel file.
    writer.save()
    

    Environment

    - XlsxWriter version: 3.0.2
    - Python version: 3.9.6
    - Excel version: 16.57 (22011101)
    - OS: macOS Monterey 12.1 (21C52)
    

    Any other information

    No response

    OpenOffice and LibreOffice users

    • [ ] I have tested the output file with Excel.
  • Merge already merged cells results in damaged file format

    Merge already merged cells results in damaged file format

    Current behavior

    I think if one try to merge cells with already merges cells it should either result in an error or the merged range should be expanded. But I got no errors while creating, but this on opening: image

    Expected behavior

    Raise an error, but not currupt the file.

    Sample code to reproduce

    import xlsxwriter
    
    # Create a workbook and add a worksheet.
    workbook = xlsxwriter.Workbook('test.xlsx')
    worksheet = workbook.add_worksheet()
    
    # Add a bold format to use to highlight cells.
    bold = workbook.add_format({'bold': True})
    
    
    # # Write some data headers.
    # worksheet.write('A1', 'Item', bold)
    # worksheet.write('B1', 'Cost', bold)
    
    # worksheet.write_row(1, 0, ['hello','this','is','a','row'], heading)
    
    worksheet.merge_range(1, 0, 2, 0, 'Merged Cells')
    worksheet.merge_range(2, 0, 3, 0, 'Merged Cells')
    worksheet.merge_range(3, 0, 4, 0, 'Merged Cells')
    
    
    
    workbook.close()
    

    Environment

    - XlsxWriter version: 2.0
    - Python version: 2.7
    - Excel version: 365
    - OS: Windows 10
    

    Any other information

    No response

    OpenOffice and LibreOffice users

    • [X] I have tested the output file with Excel.
  • feature request: Insert image to comment

    feature request: Insert image to comment

    Feature Request

    Hi,

    I'm working with project where outcome is bit complicated workbook. End users are viewing workbook with Excel. Workbook needs to contain also images (wave plots). Those are more additional information, so comment section would be most suitable place to add those.

    Manually it is possible to do, also via VBA code, but for me best solution is that python script inserts those images in the beginning.

    In other respects xlxswriter is amazing tool for this particular project!

    BR

PowerShell module to import/export Excel spreadsheets, without Excel
PowerShell module to import/export Excel spreadsheets, without Excel

PowerShell + Excel = Better Together Automate Excel via PowerShell without having Excel installed. Runs on Windows, Linux and MAC. Creating Tables, Pi

Sep 21, 2022
According to the received excel file (.xlsx,.xlsm,.xltx,.xltm), it converts to word format with a given table structure and formatting

According to the received excel file (.xlsx,.xlsm,.xltx,.xltm), it converts to word format with a given table structure and formatting

Feb 18, 2022
xlwings is a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa. It works with Microsoft Excel on Windows and macOS. Sign up for the newsletter or follow us on twitter via

xlwings - Make Excel fly with Python! xlwings CE xlwings CE is a BSD-licensed Python library that makes it easy to call Python from Excel and vice ver

Sep 25, 2022
Excel-report-evaluator - A simple Python GUI application to aid with bulk evaluation of Microsoft Excel reports.
Excel-report-evaluator - A simple Python GUI application to aid with bulk evaluation of Microsoft Excel reports.

Excel Report Evaluator Simple Python GUI with Tkinter for evaluating Microsoft Excel reports (.xlsx-Files). Usage Start main.py and choose one of the

Dec 29, 2021
Single API for reading, manipulating and writing data in csv, ods, xls, xlsx and xlsm files

pyexcel - Let you focus on data, instead of file formats Support the project If your company has embedded pyexcel and its components into a revenue ge

Sep 17, 2022
Library to create spreadsheet files compatible with MS Excel 97/2000/XP/2003 XLS files, on any platform.

xlwt This is a library for developers to use to generate spreadsheet files compatible with Microsoft Excel versions 95 to 2003. The package itself is

Sep 22, 2022
Transpiler for Excel formula like language to Python. Support script and module mode

Transpiler for Excel formula like language to Python. Support script and module mode (formulas are functions).

Dec 7, 2021
Sep 15, 2022
ExcelPeek is a tool designed to help investigate potentially malicious Microsoft Excel files.

ExcelPeek is a tool designed to help investigate potentially malicious Microsoft Excel files.

Apr 16, 2022
Reads Data from given Excel File and exports Single PDFs and a complete PDF grouped by Gateway
Reads Data from given Excel File and exports Single PDFs and a complete PDF grouped by Gateway

E-Shelter Excel2QR Reads Data from given Excel File and exports Single PDFs and a complete PDF grouped by Gateway Features Reads Excel 2021 Export Sin

Nov 13, 2021
Upload an Excel/CSV file ( < 200 MB) and produce a short summary of the data.
Upload an Excel/CSV file ( < 200 MB) and produce a short summary of the data.

Data-Analysis-Report Deployed App 1. What is this app? Upload an excel/csv file and produce a summary report of the data. 2. Where to upload? How to p

Feb 26, 2022
ObjTables: Tools for creating and reusing high-quality spreadsheets

ObjTables: Tools for creating and reusing high-quality spreadsheets ObjTables is a toolkit which makes it easy to use spreadsheets (e.g., XLSX workboo

Jun 14, 2021
Python Module for Tabular Datasets in XLS, CSV, JSON, YAML, &c.

Tablib: format-agnostic tabular dataset library _____ ______ ___________ ______ __ /_______ ____ /_ ___ /___(_)___ /_ _ __/_ __ `/__ _

Sep 22, 2022
Create Open XML PowerPoint documents in Python

python-pptx is a Python library for creating and updating PowerPoint (.pptx) files. A typical use would be generating a customized PowerPoint presenta

Sep 19, 2022
A set of Python scripts for finding threats in Office365
A set of Python scripts for finding threats in Office365

Py365 A collection of scripts for finding threats in Office365 Risky Rules A tool for finding risky or suspicious inbox rules - more detail in this po

May 18, 2022
A Python module for creating Excel XLSX files.
A Python module for creating Excel XLSX files.

XlsxWriter XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format. XlsxWriter can be used to write text, numbers, formula

Sep 22, 2022
Make creating Excel XLSX files fun again
Make creating Excel XLSX files fun again

Poi: Make creating Excel XLSX files fun again. Poi helps you write Excel sheet in a declarative way, ensuring you have a better Excel writing experien

Apr 1, 2022
PyExcelerate - Accelerated Excel XLSX Writing Library for Python 2/3

PyExcelerate Accelerated Excel XLSX writing library for Python master: dev: test coverage: Authors: Kevin Wang and Kevin Zhang Copyright 2015 Kevin Wa

Sep 20, 2022
PowerShell module to import/export Excel spreadsheets, without Excel
PowerShell module to import/export Excel spreadsheets, without Excel

PowerShell + Excel = Better Together Automate Excel via PowerShell without having Excel installed. Runs on Windows, Linux and MAC. Creating Tables, Pi

Sep 21, 2022
A flask extension using pyexcel to read, manipulate and write data in different excel formats: csv, ods, xls, xlsx and xlsm.

Flask-Excel - Let you focus on data, instead of file formats Support the project If your company has embedded pyexcel and its components into a revenu

Sep 24, 2022