It only works after opening the file and saving it manually

[Walbers]

"Hello everyone,

I encountered an issue where after writing an Excel file and attempting to read it with pandas using formulas, the results come out empty. However, if I manually open and save the file, then try to read it again, the results appear correctly. Since I'm using this process in a Jenkins pipeline, I can't manually open and save the file.

I would greatly appreciate any assistance on this matter."

[James Johnson]

I have a similar issue where an xlsx file is being created automatically by some process and when I try to load it using openpyxl I get an error.  However, if I manually open and just save, no edits, and retry the openpy excel load then it works.  Below is the error:

''''
wb = load_workbook(filename=wb_path)
Traceback (most recent call last):

  File C:\Python\Python312\Lib\site-packages\openpyxl\descriptors\base.py:59 in _convert
    value = expected_type(value)

TypeError: 'datetime.date' object cannot be interpreted as an integer


During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  Cell In[13], line 1
    wb = load_workbook(filename=wb_path)

  File C:\Python\Python312\Lib\site-packages\openpyxl\reader\excel.py:346 in load_workbook
    reader.read()

  File C:\Python\Python312\Lib\site-packages\openpyxl\reader\excel.py:293 in read
    self.read_properties()

  File C:\Python\Python312\Lib\site-packages\openpyxl\reader\excel.py:176 in read_properties
    self.wb.properties = DocumentProperties.from_tree(src)

  File C:\Python\Python312\Lib\site-packages\openpyxl\descriptors\serialisable.py:103 in from_tree
    return cls(**attrib)

  File C:\Python\Python312\Lib\site-packages\openpyxl\packaging\core.py:106 in __init__
    self.modified = modified or now

  File C:\Python\Python312\Lib\site-packages\openpyxl\descriptors\base.py:272 in __set__
    super(DateTime, self).__set__(instance, value)

  File C:\Python\Python312\Lib\site-packages\openpyxl\descriptors\nested.py:35 in __set__
    super(Nested, self).__set__(instance, value)

  File C:\Python\Python312\Lib\site-packages\openpyxl\descriptors\base.py:71 in __set__
    value = _convert(self.expected_type, value)

  File C:\Python\Python312\Lib\site-packages\openpyxl\descriptors\base.py:61 in _convert
    raise TypeError('expected ' + str(expected_type))

TypeError: expected <class 'datetime.datetime'>

'''

[James Johnson]

I debugged through the read_properties function and found out the created/modified datetime values in the docProps/core.xml file had a space in it, i.e. "2024-05-06T 5:00:00...".  The space after the "T" caused the conversion to datetime.datetime to fail.  I updated my code to actually convert the xlsx to zip, fill that space with a "0", and then try to load_workbook() with success.  I'm sure there's a better way but this was the first that I found.

[Charlie Clark]

On 6 May 2024, at 23:10, James Johnson wrote:

> I debugged through the read_properties function and found out the
> created/modified datetime values in the docProps/core.xml file had a space
> in it, i.e. "2024-05-06T 5:00:00...". The space after the "T" caused the
> conversion to datetime.datetime to fail. I updated my code to actually
> convert the xlsx to zip, fill that space with a "0", and then try to
> load_workbook() with success. I'm sure there's a better way but this was
> the first that I found.

I'm pretty certain this is #2120 and fixed in 3.1, which you can get using a checkout.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226