jmcnamara / xlsxwriter Goto Github PK

Firstly, thank you all for this great tool!

I'm using XlsxWriter and I have encountered a problem. I'd like to add comment to some data cells, but found that all the comments cannot display correctly in MS Excel.

I am using Python 2.7.5 and XlsxWriter 0.4.2, on Windows 8 x64 system. My MS Excel is Excel 2013.

Here is some code that demonstrates the problem:

import xlsxwriter

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

worksheet1 = workbook.add_worksheet()
worksheet1.write_comment('A1', 'this comment will be good')
worksheet1.set_column('B:B', 8.43)

worksheet2 = workbook.add_worksheet()
worksheet2.set_column('B:B', 8.43)
worksheet2.write_comment('A1', 'this comment cannot display correctly in Excel')

workbook.close()

When I open the output file try_comment.xlsx in MS Excel, the comment in the 1st sheet looks normal and good, but the comment in the 2nd sheet looks weird. See this screenshot:

Currently the write() method converts numbers stored as strings to numbers prior to writing the data to the Excel file.

This is to avoid an Excel warning message and (in this instance) to mimic Excel's behaviour.

It is suggested that this behaviour is made optional to preserve string formats when required.

Add support for writing to streams or StringIO to make it easier to work with Django.

This is an easy fix. It will be added after chart sheets.

Reported by Thijs Engels:

Getting an error when trying to use an integer format, using the 0.3.2
release on Python 3.3.1 (x86) on Windows 7 (x64).

-------------------------
from xlsxwriter.workbook import Workbook

workbook = Workbook('example.xlsx')
worksheet = workbook.add_worksheet()

integer_format = workbook.add_format({"num_format": "0"})
worksheet.write_number(2, 0, 3, integer_format)

workbook.close()
-------------------------

[...]
  File "C:\Python33\lib\site-packages\xlsxwriter\styles.py", line 542,
  in _write_xf
    if xf_format.num_format_index > 0:
TypeError: unorderable types: str() > int()

Suggested workaround for now is to use the number index 1 or 0x01.

I am using XlsxWriter version 0.3.2 with python 2.7. My worksheet has a name with diacritics, which is fine. When I try to insert a chart, the code gets confused if I use a formula for a series name. The formula gets encoded in chart._process_names() and that spoils the worksheet name in the formula. For it to work I have to decode the worksheet name. Thus, from one of your examples, take a look at the add_series below. If I remove the decode part, it will fail. You may perhaps consider removing the encoding for formulas in this case.

#######################################################################
# -*- coding: utf-8 -*-
#
# An example of creating Excel Line charts with Python and XlsxWriter.
#
# Copyright 2013, John McNamara, [email protected]
#
from xlsxwriter.workbook import Workbook

workbook = Workbook('chart_utf.xlsx')
worksheet = workbook.add_worksheet('Ñoño'.decode('utf8'))
bold = workbook.add_format({'bold': 1})

# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]

worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])

# Create a new chart object. In this case an embedded chart.
chart1 = workbook.add_chart({'type': 'line'})


# Configure second series. Note use of alternative syntax to define ranges.
# List is [ sheet_name, first_row, first_col, last_row, last_col ].
sheetname = worksheet.name
chart1.add_series({
'name':       '='+sheetname.decode('utf8')+'!$C$1',
'categories': [sheetname, 1, 0, 6, 0],
'values':     [sheetname, 1, 2, 6, 2],
})

# Add a chart title and some axis labels.
chart1.set_title ({'name': 'Results of sample analysis'})
chart1.set_x_axis({'name': 'Test number'})
chart1.set_y_axis({'name': 'Sample length (mm)'})

# Set an Excel chart style. Colors with white outline and shadow.
chart1.set_style(10)

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})


workbook.close()

Hi.

It seems that the source tarball is very big with the vast majority of the size being accounted to documentation (both built PDF and various images). Considering that it is not really required for the module and you have rtfd.org documentation, could you consider removing it from the source distribution.

Thanks

Hello Together,

i will add some conditional format on some cells. What i was looking for was that i can extend the existing format of a cell. This i don't found. So i create 2 formats. one which is set to the cell if i write it and one for the conditional format.

In my short example i would set the of A1 to green if in cell B1 is standing "yes". This works without problems. But the align will be changed from "center" to "left". It seams that the option will not be used.

If i use the api on a wrong way please let it me know.

(sorry for my bad english)

The versions:

• $ python -c 'import xlsxwriter; print(xlsxwriter.version)': 0.4.3
• $ python --version: Python 2.7.4

import xlsxwriter

workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

format_c = workbook.add_format({'align': 'center'})
format_yes = workbook.add_format({'align': 'center', 'font_color': '#00FF00'})

worksheet.write("A1", "Test",format_c)
worksheet.conditional_format('A1:A1' , {'type': 'formula','criteria': '=B1="yes"', 'format' : format_yes})

workbook.close()

Instead of basically forcing

from xlsxwriter.workbook import Workbook
wb = Workbook('myfile.xlsx')

allow something like

import xlsxwriter
wb = xlsxwriter.Workbook('myfile.xlsx')

The main point is not to be shorter (though that's nice), but rather more intuitive. Besides Workbook, I think it would also be good to expose a version constant (akin to xlwt.__VERSION__. Other than that, I'd say best to keep the top-level namespace uncluttered.

Due to float(x) being "not a number" when x.upper() == 'NAN', if such an x exists in the data being written, the resulting workbook will pop up a warning when opened in Excel:

Excel found unreadable content in 'foo.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

Even assuming this warning isn't intimidating to the end user, and they go ahead and recover, the affected cell apparently becomes 'nan' (losing any capitalization).

Obviously this can be worked around by checking the data beforehand and using write_string(), but it's ugly and a pain, and it especially uglifies the calling code if the problem occurs in write_row().

Also, I don't know what the proper course of action is when a true numeric NaN shows up. (Perhaps somehow write #NUM! to the cell? That's the result of =0^0....)

The write_datetime() docs say the cell format is optional, but it's not:

Traceback (most recent call last):
  File "datetime.py", line 12, in <module>
    ws.write('A2', date(2003, 1, 2))
  File "C:\Python27\lib\site-packages\xlsxwriter\worksheet.py", line 60, in cell_wrapper
    return method(self, *new_args, **kwargs)
  File "C:\Python27\lib\site-packages\xlsxwriter\worksheet.py", line 342, in write
    return self.write_datetime(row, col, *args)
  File "C:\Python27\lib\site-packages\xlsxwriter\worksheet.py", line 55, in cell_wrapper
    return method(self, *args, **kwargs)
TypeError: write_datetime() takes exactly 5 arguments (4 given)

The easiest thing to do would be to change the docs so they match the API.

If changing the API to match the docs (which would be more user-friendly, especially when using write_row()), then some default format would need to be supplied. If a locale-appropriate default cannot be determined automatically, I would opt for something based on ISO.

As the example below shows, when a cell is given a format the 'default_date_format' is overwritten. I expect that the default would only be overwritten if a 'num_format' is given.

The example:

import xlsxwriter
import datetime

today = datetime.date.today()  # 2013-09-10

wb = xlsxwriter.Workbook('test.xlsx',
                         {'default_date_format': 'yyyy-mm-dd'})

ws = wb.add_worksheet()

the_format = wb.add_format({'bold': True})

ws.write(0, 0, today)  # Returns 2013-09-10
ws.write_datetime(1, 0, today)  # Returns 2013-09-10
ws.write(2, 0, today, the_format)  # Returns 41527
ws.write_datetime(3, 0, today, the_format)  # Returns 41527

ws.set_column(0, 0, 10)

wb.close()

examples/autofilter_data.txt
examples/python.png
examples/unicode_polish_utf8.txt
examples/unicode_shift_jis.txt

Currently XlsxWriter doesn't supply a default format for URLs written with write_url() or write().

This is the documented behaviour.

However, for consistency with the default date format added in version 0.3.6 there should probably be a default hyperlink format as well.

Hi,

I am looking into using XlsxWriter from Jython, which only offers Python 2.5.

It seems there are only a few places that are not Python 2.5 compatible. I got a basic version working with <10 lines of change (b prefix for strings, removed checks via isnan()/isinf()), which runs the chart_area and chart_bar examples.

Is anyone interested in making the upstream version compatible to python 2.5?

Chart support for XlsxWriter is in progress.

This is a tracker issue/defect so that interested parties can get updates and give feedback.

There is a known speed degradation in all versions of XlsxWriter <= 0.2.2.

The cause has been identified and a fix is on the way.

XlsxWriter: Bug Reports and Pull Requests

The following is from Contibuting.md.

Reporting Bugs

Here are some tips on reporting bugs in XlsxWriter.

Upgrade to the latest version of the module

The bug you are reporting may already be fixed in the latest version of the
module. You can check which version of xlsxwriter that you are using as follows:

python -c 'import xlsxwriter; print(xlsxwriter.__version__)'

The Changes file lists what has changed in the latest versions.

Read the documentation

Read or search the XlsxWriter documentation to see if the issue you are encountering is already explained.

Look at the example programs

There are several example programs in the distribution. Try to identify an example program that corresponds to your query and adapt it to use as a bug report.

Pointers for submitting a bug report

1. Describe the problem as clearly and as concisely as possible.
2. Include a sample program. This is probably the most important step. It is generally easier to describe a problem in code than in written prose.
3. The sample program should be as small as possible to demonstrate the problem. Don't copy and paste large non-relevant sections of your program.

Sample Bug Report

A sample bug report is shown below. This format helps to analyse and respond to the bug report more quickly.

Issue with SOMETHING

I am using XlsxWriter to do SOMETHING but it appears to do SOMETHING ELSE.

I am using Python version X.Y.Z and XlsxWriter x.y.z.

Here is some code that demonstrates the problem::

  import xlsxwriter

  workbook = xlsxwriter.Workbook('hello.xlsx')
  worksheet = workbook.add_worksheet()

  worksheet.write('A1', 'Hello world')

  workbook.close()

Use the XlsxWriter GitHub issue tracker

Submit the bug report using the XlsxWriter issue tracker.

Pull Requests and Contributing to XlsxWriter

All patches and pull requests are welcome but must start with an issue tracker.

Getting Started

1. Pull requests and new feature proposals must start with an issue tracker. This serves as the focal point for the design discussion.
2. Describe what you plan to do. If there are API changes add some code example to demonstrate them.
3. Fork the repository.
4. Run all the tests to make sure the current code work on your system using make test.
5. Create a feature branch for your new feature.
6. Enable Travis CI on your fork, see below.

Enabling Travis CI via your GitHub account

Travis CI is a free Continuous Integration service that will test any code you push to GitHub with Python 2.5, 2.6, 2.7, 3.2, 3.3, 3.4 and PyPy.

See the Travis CI Getting Started instructions.

Note there is already a .travis.yml file in the XlsxWriter repo so that doesn't need to be created.

Writing Tests

This is the most important step. XlsxWriter has approximately 1000 tests and a 2:1 test to code ratio. Patches and pull requests for anything other than minor fixes or typos will not be merged without tests.

Use the existing tests in XlsxWriter/xlsxwriter/test/ as examples.

Ideally, new features should be accompanied by tests that compare XlsxWriter output against actual Excel 2007 files. See the XlsxWriter/xlsxwriter/test/comparison test files for examples. If you don't have access to Excel 2007 I can help you create input files for test cases.

Tests should use the standard unittest Python module.

Code Style

Follow the general style of the surrounding code and format it to the PEP8 coding standards.

Tests should conform to PEP8 but can ignore E501 for long lines to allow the inclusion of Excel XML in tests.

There is a make target that will verify the source and test files:

make pep8

Running tests

As a minimum tests should be run using Python 2.7 and Python 3.3.

make test
# or
py.test

I use pythonbrew and Tox to test with a variety of Python versions. See the Makefile for example test targets. A tox.ini file is already configured.

When you push your changes they will also be tested using Travis CI as explained above.

Documentation

If your feature requires it then write some RST documentation in Sphinx format or add to the existing documentation.

The docs, in dev/docs/source can be built in Html format using:

make docs

Example programs

If applicable add an example program to the examples directory.

Copyright and License

Copyright remains with the original author. Do not include additional copyright claims or Licensing requirements. GitHub and the git repository will record your contribution an it will be acknowledged it in the Changes file.

Submitting the Pull Request

If your change involves several incremental git commits then rebase or squash them onto another branch so that the Pull Request is a single commit or a small number of logical commits.

Push your changes to GitHub and submit the Pull Request with a hash link to the to the Issue tracker that was opened above.

Do you have any plan to support Excel pivot table and pivot chart? I didn't find it in the document.

In workbook.py, there is currently only support for the full datetime type. It would be beneficial to include support for datetime.time and datetime.date as well.

A simple implementation would be:

if isinstance(date, datetime.date):  # separate handling for partial date
    # date in excel is number of days since Jan1 1900
    d = datetime.datetime.fromordinal( date.toordinal() ) - datetime.datetime( 1900, 1, 1 )
    number = d.days
elif isinstance(date, datetime.time):  # separate handling for partial time
    # time in excel is float between 0 and 1. Use time in seconds from 00:00, divided by seconds in a day
    number = (date.hour_60_60.0 + date.minute_60.0 + date.second + 1.0_date.microsecond/1e6)/(24_60_60.0)
else:  # existing version for full datetime
    number = self._convert_date_time(date)


Thanks for your efforts!

Eduardo

XlsxWriter doesn't currently work with Google App Engine because it uses a temporary directory to assemble the XLSX sub-component files prior to creating an output file.

Invalid string formatting in line 958 of workbook._add_chart_data():

if not sheetname in worksheets:
warn("Unknown worksheet reference '%s' in range "
"'%d' passed to add_series()" % (sheetname, c_range))

Reproducing the issue:

workbook = ...
worksheet = workbook.add_worksheet("sheet1")
chart = workbook.add_chart({'type': 'column'})
chart.add_series({'values': '=unknownsheet!$A$1:$A$5'})
worksheet.insert_chart('A7', chart)
workbook.close()

File "build\bdist.win32\egg\xlsxwriter\workbook.py", line 958, in _add_chart_data
"'%d' passed to add_series()" % (sheetname, c_range))
TypeError: %d format: a number is required, not str

FIX is trivial: replace '%d' with '%s'

Version 0.3.2

Python 2.7.5, XlsxWriter 0.4.3, Windows 8 x64, Excel 2013.

In this case I created two tables in a worksheet, TableRef and TableData. And I wanted to add some formulas in TableData, to reference some other value provided by TableRef. Most formulas work well, but when using VLOOKUP function, Microsoft Excel reports #Name? error.

Here is the code:

import xlsxwriter

book = xlsxwriter.Workbook('output.xlsx')
sheet1 = book.add_worksheet('Data')

ref_data = [
    ['a', 10],
    ['b', 7],
    ['c', 25],
    ['d', 9],
]
ref_columns = [
    {'header': 'Name'},
    {'header': 'Value'},
]
sheet1.add_table('A1:B5', {'name': 'TableRef', 'data': ref_data, 'columns': ref_columns})

data = [
    ['a', 'lol'],
    ['b', 'foo'],
    ['c', 'blah'],
    ['d', 'bar'],
]
columns = [
    {'header': 'Name'},
    {'header': 'Note'},
    {'header': 'V1', 'formula': '=IF(ISBLANK(TableRef[@Value]),123,TableRef[@Value])'},
    {'header': 'V2', 'formula': '=VLOOKUP([@Name],TableRef,2,FALSE)'},
]
sheet1.add_table('D1:G5', {'name': 'TableData', 'data': data, 'columns': columns})

book.close()

And the unexpected result:

You can double click any cell in TableData V2 column (for example the cell G2), then press Enter key, the correct value will appear. Shown as the below figure, but you can see that the formula is exact the same with above.

I myself now find a temporary fix is using TabelRef[#Data] instead, the code looks like this:

    {'header': 'V2', 'formula': '=VLOOKUP([@Name],TableRef[#Data],2,FALSE)'},

I'm not sure whether this is a XlsxWriter bug or not (probably not). Just for your information.

Add checks/exceptions for max string sizes in data validations.

It looks like that XlsxWriter is the best (API design, documentation, etc.) xlsx writer in Python.

By the way, do you have any plan to implement a reader? I am planning to develop an application that reads a template xslx file (with lots of borders, colors, number formats, etc.; the template is created manually using MS Excel or equivalent) and write new xlsx files with that template. I found that data other than cell values are generally incompatible across reader/writer libraries (different Python class, different enum values, ...), so I think XlsxReader would help a lot of people like me. I may be able to do something (eg, write some code or documents, ...) if needed.

Add support for Chart sheets, i.e, charts that occupy an entire worksheet in a workbook,

After capturing several images from videos as jpeg with ffmpeg the workbook raises the exception 'Unknown or unsupported file type.'

This happenes due to the fact that marker3 is neither 'JFIF' nor 'EXIF', but Lavc'.
After adding 'Lavc' as a valid option for JPEG markers in Workbook._get_image_properties everything works as expected.

Best regards,
Simon Breuss

Most of the data I come across is in the form of a numpy array, or maybe a 1D list/tuple. Can you add easy support for writing ranges of data from these datatypes?

I'm using Python 2.7.5 and XlsxWriter 0.4.3, on Windows 8 x64 system. My MS Excel is Excel 2013.

I want to create two sheets, then add two tables to them in a reversed order (add the 1st table to sheet2, which is created later than sheet1; and then add the 2nd table to sheet1). But the output excel file will have some problem to open: "Repaired Records: Table from /xl/tables/table1.xml part (Table)", reported by Microsoft Excel.

Here is the sample code.

import xlsxwriter

book = xlsxwriter.Workbook('output.xlsx')

sheet1 = book.add_worksheet('Sheet1')
sheet2 = book.add_worksheet('Sheet2')

sheet2.add_table('A1:D10')
sheet1.add_table('A1:F5')

book.close()

I can fix it by calling sheet1.add_table before sheet2.add_table. But in real-life scenarios, it's very hard to do the exchange.

Fig: Expected Result:

Fig: Actual Result:

Hello John,

First off, thanks again for your efforts.
As discussed, it would be great if default formats for dates and times could be added, so that the user can specify values to be written without making exceptions to provide a format. I find your idea of class methods providing the defaults, which could be overriden if needed. Any format from ISO 8601 would do, e.g., YYYY-MM-DD and hh:mm:ss

Thanks!

Eduardo

If the input to the below method is UTF-8 encoded non ascii string, it will fail.
With pyscopg we will get the data from postgres in UTF-8 format. If the data is in non ascii characters, it cannot handle it.

The exception need to be handled.

"""
if string is None or sys.version_info >= (3, 0):
    return string
else:
    return string.encode('utf-8')

Minor bug, but still a bug, and easily fixable: The width conversion in _write_col_info currently gets widths less than one grossly wrong (by percentage), and widths greater than one occasionally off by a pixel. Instead of

    # Convert column width from user units to character width.
    if width > 0:
        # For Calabri 11.
        max_digit_width = 7
        padding = 5
        width = int((float(width) * max_digit_width + padding)
                    / max_digit_width * 256.0) / 256.0

it should really just use the pixel calculation from _size_col and divide by 7.0. For example:

    # Convert column width from user units to character width.
    if width > 0:
        # For Calabri 11.
        max_digit_width = 7
        padding = 5

        if width < 1:
            pixels = int(width * 12 + 0.5)
        else:
            pixels = int(width * max_digit_width + 0.5) + padding
        width = float(pixels) / max_digit_width

(I would have gone for pixels / 7.0 myself. If you're serious about using your named values instead of magic numbers, maybe 12 should be replaced by (max_digit_width + padding).)

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:

Correct Version

From this StackOverflow question.

The following raises an error:

import xlsxwriter

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

worksheet = workbook.add_worksheet()
chart = workbook.add_chart({'type': 'scatter',
                            'subtype': 'straight'})

data = [
    [1, 2, 3, 4, 5],
    [2, 4, 6, 8, 10],
]

# Write the data for the chart.
worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])

chart.add_series({
    'categories': '=Sheet1!$A$1:$A$5',
    'values': '=Sheet1!$B$1:$B$5',
    'marker': {'type': 'automatic'},
})

worksheet.insert_chart('E9', chart)

workbook.close()

Traceback (most recent call last):
  File "so124_1.py", line 26, in <module>
    workbook.close()
  File "/opt/Python27/ib/site-packages/xlsxwriter/workbook.py", line 240, in close
    self._store_workbook()
  File "/opt/Python27/ib/site-packages/xlsxwriter/workbook.py", line 397, in _store_workbook
    packager._create_package()
  File "/opt/Python27/ib/site-packages/xlsxwriter/packager.py", line 122, in _create_package
    self._write_chart_files()
  File "/opt/Python27/ib/site-packages/xlsxwriter/packager.py", line 205, in _write_chart_files
    chart._assemble_xml_file()
  File "/opt/Python27/ib/site-packages/xlsxwriter/chart.py", line 529, in _assemble_xml_file
    self._write_chart()
  File "/opt/Python27/ib/site-packages/xlsxwriter/chart.py", line 1161, in _write_chart
    self._write_plot_area()
  File "/opt/Python27/ib/site-packages/xlsxwriter/chart_scatter.py", line 173, in _write_plot_area
    self._write_chart_type({'primary_axes': 1})
  File "/opt/Python27/ib/site-packages/xlsxwriter/chart_scatter.py", line 53, in _write_chart_type
    self._write_scatter_chart(args)
  File "/opt/Python27/ib/site-packages/xlsxwriter/chart_scatter.py", line 92, in _write_scatter_chart
    self._modify_series_formatting()
  File "/opt/Python27/ib/site-packages/xlsxwriter/chart_scatter.py", line 277, in _modify_series_formatting
    if series['marker'] is None or not series['marker']['defined']:
KeyError: 'defined'

In this case chart_scatter.py should be:

                # Set a marker type unless there is a user defined type.
                if series.get('marker') or not series['marker']['defined']:

The Worksheet.write_url methods, handle only links for text value.
https://github.com/jmcnamara/XlsxWriter/blob/master/xlsxwriter/worksheet.py#L629

However with excel, it's possible to have links on number or fomula.

Like on this screenshot:

Do you think, it would be possible to implement it, in xlsxwriter ?

feature_request

Hi Jmcnamara!

I posted this question on StackOverflow:
http://stackoverflow.com/questions/19365507/how-to-change-the-legend-font-size-using-xlsxwriter-python/19376946?noredirect=1#19376946
I would like to make a feature_request please.
being able to change the legend font-size so it can be visible on the excel chart report

Thanks!

By "inconsistent", I do mean the behavior changes each time I run the code.

I am working with Portable Python 2.7.3 and XlsxWriter 0.1.5.

Here is a sample, taken from an adapted defined_name.py:

from xlsxwriter.workbook import Workbook

workbook = Workbook('defined_name_bug.xlsx')
worksheet1 = workbook.add_worksheet('sheet One')

# Define some global/workbook names.
workbook.define_name('Exchange_rate', '=0.96')
workbook.define_name('Sales', "='Sheet One'!G1:H10")

# Write some text in the file and one of the defined names in a formula.
for worksheet in workbook.worksheets():
    worksheet.set_column('A:A', 45)
    worksheet.write('A1', 'This worksheet contains some defined names.')
    worksheet.write('A2', 'See Formulas -> Name Manager above.')
    worksheet.write('A3', 'Example formula in cell B3 ->')

    worksheet.write('B3', '=Exchange_rate')

workbook.close()

Results in Excel 2007's Name Manager after each run for "Sales"
='sheet One'!G1:H10
='sheet One'!G1:H10
='sheet One'!G5:H14
='sheet One'!G1:H10
='sheet One'!G1:H10

All I've done is run this file, open the Excel workbook, copied this from the name manager, closed the file, and repeat this process.

It doesn't appear to happen unless the sheet name has spaces and requires the quotation marks. I can run the straight sample several times and not encounter this issue.

The compat_collections.py module was added in version 0.3.8 to allow backward compatibility with Python 2.5.

However, this module isn't Python 3 compatible and generates the following (harmless) error during build/installation:

$ sudo /usr/local/bin/python3.3 setup.py install
running install
running build
running build_py
copying xlsxwriter/compat_collections.py -> build/lib/xlsxwriter
running install_lib
copying build/lib/xlsxwriter/compat_collections.py -> /usr/local/lib/python3.3/site-packages/xlsxwriter
byte-compiling /usr/local/lib/python3.3/site-packages/xlsxwriter/compat_collections.py to compat_collections.cpython-33.pyc
  File "/usr/local/lib/python3.3/site-packages/xlsxwriter/compat_collections.py", line 145
    print template
                 ^
SyntaxError: invalid syntax

Installation should still complete and this error doesn't affect the installed version since the module is only used in Python 2.5.

Will fix in the next release.

John

Hello John

I am currently having an issue with large tables.
I currently have a table with >20000 rows and around 30 columns
Memory consumption goes over 1 gigabyte, and the module crashes with a memory error message ("MemoryError").

I think this is because there is no partial flush of the "self.table" object.
I tried to change self.optimization = 1 in the constructor, but it made no difference.
So far I have been using only calls to worksheet.write().

I saw in the comparison with the perl counterpart, that the self.optimization support is on the roadmap, but I would like to ask you (since I don't have much time to test the perl module), if you have an estimate of the maximum memory consumption in case the optimization is enabled - so that in practice the number of rows to be written would not be an issue?

Thanks!

Eduardo

The write() method doesn't support keyword arguments as shown in the docs:

from xlsxwriter.workbook import Workbook

workbook = Workbook('file.xlsx')
worksheet = workbook.add_worksheet()

xf_format = workbook.add_format()

worksheet.write('A1', 'Hello, world!', cell_format=xf_format)

workbook.close()

Hi - I'm trying to impliment XlsxWriter in google appengine and i'm getting the error "NotImplementedError: Only tempfile.TemporaryFile is available for use"

I believe this is because xlsxwriter tries to write into the temp directory and GAE doesn't allow access to the disk.

Can you recommend a workaround?

Kind Regards,
Anthony

I understand and agree with the decision to remove PDF docs from the main distribution bundle. However, I personally like browsing a PDF more than I like browsing disjoint Web pages, no matter how well linked and indexed. So perhaps you could add PDF generation back into the build process for creating a release, but instead of including it in the main bundle, have it separately downloadable.

Currently, if you write a boolean type it falls under the numeric type and writes it as either a '1' or a '0'.

Perhaps a boolean should be explicitly checked for and written as either "TRUE" or "FALSE"

from xlsxwriter import Workbook

wb = Workbook("test.xlsx")
ws = wb.add_worksheet("My Worksheet")
ws.write("A1", True)

wb.close()

There are several functions in the utility.py module that could be exposed via an external API.

Some users are using these functions anyway so it is time to make them public.

They are already covered by test cases.

Hi,

I'm really happy to see the rapid growth of xlsxwriter. I imagine adding freeze pane support is on your radar since it's a feature of Excel::Writer::XLSX. At the moment that's the one thing that I'd like to have that isn't already in there (as of 0.2.1). Any chance it will appear within the next few releases?

Thanks,

-- Eric

Add a section to the docs to explain named and Html colours based on: https://xlsxwriter.readthedocs.org/en/latest/working_with_formats.html#format-colors

Many thanks for authoring and porting XlsxWriter.

I'm trying to name both series' in my scatter chart, and run into difficulties if I base the chart on a data table.

Here's my code:

from xlsxwriter.workbook import Workbook

book  = Workbook('series_name_issue.xlsx')
sheet = book.add_worksheet()

data = [[1, 10, 11],
        [2, 40, 41],
        [3, 50, 51]]
sheet.add_table(0, 0, 2, 2, {'data': data})

chart = book.add_chart({'type': 'scatter'})
chart.add_series({
    'name':         'MyHeader',
    'categories':   "='Sheet1'!A1:A6",
    'values':       "='Sheet1'!B1:B6"})
chart.add_series({
    'name':         'MyHeader2',
    'categories':   "='Sheet1'!A1:A6",
    'values':       "='Sheet1'!C1:C6"})
    # None of these work either...
    # 'name_formula': "=B2",
    # 'name_formula': "='Sheet1'!B2",
    # 'name_formula': "='Sheet1'!B2:B2",
    #'name':         '="{}"'.format('MyHeader2'),

sheet.insert_chart('G6', chart)
book.close()

Expected: Legend shows 'MyHeader1' and 'MyHeader2'
Actual: Legend shows 'Column1' and 'Column2'

The problem seems to be related to the index arguments given to sheet.add_table(). Specifically if I pass (0, 0, 2, 3) instead of (0, 0, 2, 2), then the names are displayed as expected. However, this causes an extra column to be shown in my table. This oddity originally lead me to believe that I was passing the wrong values for those indexes, but the documentation indicates that zero-based indexes for both rows and columns are correct:

worksheet.add_table('B3:F7')
# Same as:
worksheet.add_table(2, 1, 6, 5)

(http://xlsxwriter.readthedocs.org/en/latest/working_with_tables.html)

However, I looked at the code for the add_table method, and it tells a different tale:

        Add an Excel table to a worksheet.

        Args:
            first_row:    The first row of the cell range. (zero indexed).
            first_col:    The first column of the cell range.
            last_row:     The last row of the cell range. (zero indexed).
            last_col:     The last column of the cell range.

This indicates to me that the row args are zero indexed, but the column args are not. I assume this is an unrelated documentation error, but I thought I'd point it out.

Thanks!

The documentation should make it clearer that formulas used as a data source in conditional formats should be absolute cell values. Like $H$1 in the following:

worksheet.conditional_format('A1:F10', {'type':     'cell',
                                        'criteria': 'greater than',
                                        'value':    '$H$1',
                                        'format':   format1})

Link to absolute/relative ranges when added to the docs.

Add support for non-contiguous category/range values in chart data.

Add a clarification to the docs that formulas should be in English and use the commas operator.

See the following SO question

It seems that graph plotting is not working in the 0.4.0 release. I have some code that worked fine on the 0.3.6 release but on the 0.4.0 release I get the following error:

line 308, in generate_excel_report
book.close()
File "/usr/local/lib/python2.7/dist-packages/xlsxwriter/workbook.py", line 231 >, in close
self._store_workbook()
File "/usr/local/lib/python2.7/dist-packages/xlsxwriter/workbook.py", line 380 , in _store_workbook
self._prepare_drawings()
File "/usr/local/lib/python2.7/dist-packages/xlsxwriter/workbook.py", line 746 , in _prepare_drawings
sheet._prepare_chart(index, chart_ref_id, drawing_id)
File "/usr/local/lib/python2.7/dist-packages/xlsxwriter/worksheet.py", line 36 22, in _prepare_chart
width, height)
File "/usr/local/lib/python2.7/dist-packages/xlsxwriter/worksheet.py", line 36 63, in _position_object_emus
width, height)
File "/usr/local/lib/python2.7/dist-packages/xlsxwriter/worksheet.py", line 37 35, in _position_object_pixels
x_abs += self._size_col(col_id)
File "/usr/local/lib/python2.7/dist-packages/xlsxwriter/worksheet.py", line 38 05, in _size_col
pixels = int(width * (max_digit_width + padding) + 0.5)
TypeError: unsupported operand type(s) for *: 'NoneType' and 'float'