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.

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

I have started a Rust port of XlsxWriter called rust_xlsxwriter. See the following for information:

• User Guide: Working with the rust_xlsxwriter library.
  • Getting started: A simple getting started guide on how to use rust_xlsxwriter in a project and write a Hello World example.
  • Tutorial: A larger example of using rust_xlsxwriter to write some expense data to a spreadsheet.
  • Cookbook Examples.
• The rust_xlsxwriter crate
• The rust_xlsxwriter API docs at docs.rs
• The rust_xlsxwriter repository
• Release Notes
• https://github.com/jmcnamara/rust_xlsxwriter/issues/1

Any feedback, early usage, or reviews welcome.

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