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

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!

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

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.

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,
        })

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.

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)

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

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

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.

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.

Notes on the XlsxWriter implementation of autofit().

Version 3.0.6 of XlsxWriter added a worksheet.autofit() function.

The implementation is a technical compromise and I wanted to write some notes about it.

First off let's start with the text that was in the FAQ:

Q. Is there an "AutoFit" option for columns?

Unfortunately, there is no way to specify "AutoFit" for a column in the Excel file format. This feature is only available at runtime from within Excel. It is possible to simulate "AutoFit" in your application by tracking the maximum width of the data in the column as your write it and then adjusting the column width at the end.

This is still true. There is no "autofit" flag in the Excel XLSX format that will trigger the same autofit that you get from Excel at runtime.

As a workaround I implemented a pixel calculation based on defined widths for all the characters in the ASCII range 32-126. You can see that here and here.

Fidelity

This matches Excel for strings up to around 100 pixels (and there are a number of test_autofit??.py test cases that compare against Excel. After that Excel adds an additional pixel for every ~ 32 pixels additional length. For example in Excel the following strings have these pixel widths:

• "N" : 10
• "NNN": 30
• "NNNNN": 50
• "NNNNNNNN": 80
• "NNNNNNNNNN": 101

This may be due to some internal rounding (in Excel) or may be due to a conversion from character units to pixel widths.

Either way this isn't significant. The additional pixels added by Excel appear as extra padding and won't be substantively noticeable to the end user. At the same time the lack of these extra padding pixels from the XlsxWriter autofit shouldn't be noticeable either. I am mainly highlighting this so that hopefully someone won't submit a bug report about pixel difference between Excel and XlsxWriter.

Here is a visual example from the autofit.py XlsxWriter program:

And the same file with an Excel autofit:

The pixels widths for the columns are:

| Program | A | B | C | D | | ---------- | --- | --- | --- | --- | | XlsxWriter | 50 | 63 | 113 | 147 | | Excel | 50 | 63 | 114 | 150 |

Difference with Excel for macOS

Excel for Windows files appear differently in Excel for macOS. For example here is the same XlsxWriter file as above on the mac:

You will notice that the widths and padding are rendered differently. This is not an XlsxWriter issue. The same happens with any Excel file generated on Windows and rendered on the mac. For example:

| Program | A | B | C | D | | ---------------- | --- | --- | --- | --- | | Excel Win | 50 | 63 | 114 | 150 | | Excel Win on Mac | 43 | 54 | 98 | 129 | | Excel Mac | 39 | 55 | 83 | 111 |

This is quite a difference and if the Excel Mac autofit file is transferred back to Windows the columns no longer appear fitted.

So if you encounter this issue it isn't due to XlsxWriter.

Padding

Excel adds a 7 pixel padding to each cell. So a word like "Hello" has a width of 33 pixels but the column width (in Excel) will be 33+7=40 pixels.

Known Limitations

• Fonts and font sizes aren't taken into account.
• Non-ASCII characters are given a default width of 8 pixels.
• Dates are assumed to have a width/format of mm/dd/yyyy.
• Number formats aren't taken into account (and realistically won't be in the future).
• Autofilter dropdowns aren't taken into account.
• It is not supported in constant_memory mode.

Feature Request

We don't see a option for setting the width of columns automatically accordingly to column values. It will be good feature to have. I have seen a workaround for this in below post https://stackoverflow.com/questions/29463274/simulate-autofit-column-in-xslxwriter

Feature Request

options = {
    "data": data,
    "slicer": [
        {"column": "Column1", "location": "A1"},
        {"column": "Column2", "location": "C1"},
    ]
}

or 

options = {
    'data': data,
    'columns': [
        {'header': 'Product', 'slicer': 'A1'},
        {'header': 'Quarter 1'},
        {'header': 'Quarter 2'},
        {'header': 'Quarter 3'},
        {'header': 'Quarter 4'},
    ]
}
worksheet.add_table('B3:F7', {options})

In this way, you can initially realize the function of the pivot table.

At present, I think the method is to rewrite the Workbook, Worksheet for simple implementation, but I don't know how to add slicer1.xml file

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

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(log₂₆ 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

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], } })