Skip to content
GitLab
Projects Groups Topics Snippets
  • /
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Register
  • Sign in
  • O openpyxl
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributor statistics
    • Graph
    • Compare revisions
  • Issues 282
    • Issues 282
    • List
    • Boards
    • Service Desk
    • Milestones
  • Merge requests 7
    • Merge requests 7
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Packages and registries
    • Packages and registries
    • Container Registry
  • Monitor
    • Monitor
    • Incidents
  • Analytics
    • Analytics
    • Value stream
    • CI/CD
    • Repository
  • Snippets
    • Snippets
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar

Due to a massive storm of spam, registration on this instance now requires explicit administrator approval. Sorry for the added friction, we're also looking into automatic filtering options.

  • openpyxl
  • openpyxl
  • Issues
  • #1645
Closed
Open
Issue created Mar 17, 2021 by Jean-Francois Thuong@jfthuong

Timezone-aware datetimes raise an Exception in version 3.0.7

Update for dates done in PR!399 introduced a change that raise an exception for aware dates like datetime(2021, 1, 1).replace(tzinfo=timezone.utc).astimezone(tz=None):

  ...
  File "C:\Python37-32\lib\site-packages\openpyxl\cell\_writer.py", line 76, in lxml_write_cell
    value, attributes = _set_attributes(cell, styled)
  File "C:\Python37-32\lib\site-packages\openpyxl\cell\_writer.py", line 32, in _set_attributes
    value = to_excel(value, cell.parent.parent.epoch)
  File "C:\Python37-32\lib\site-packages\openpyxl\utils\datetime.py", line 97, in to_excel
    days = (dt - epoch).days
TypeError: can't subtract offset-naive and offset-aware datetimes

The following code can reproduce the issue:

from datetime import datetime, timezone, timedelta
import calendar

from openpyxl import Workbook


def utc_to_local(utc_dt):
    """Transform date to Local Timezone"""
    # get integer timestamp to avoid precision lost
    timestamp = calendar.timegm(utc_dt.timetuple())
    local_dt = datetime.fromtimestamp(timestamp)
    assert utc_dt.resolution >= timedelta(microseconds=1)
    return local_dt.replace(microsecond=utc_dt.microsecond)


def utc_to_local_simpler(utc_dt: datetime):
    """Transform date to Local Timezone"""
    return utc_dt.replace(tzinfo=timezone.utc).astimezone(tz=None)


wb = Workbook()
ws = wb.active
ws.column_dimensions["A"].width = 25

date = datetime(2021, 1, 1)
ws["A1"] = date  # OK
ws["A2"] = utc_to_local_simpler(date)  # OK
ws["A3"] = utc_to_local(date)  # Exception

dest_filename = "check_writing_dates.xlsx"
wb.save(filename=dest_filename)

Note: I have cell.parent.parent.iso_dates equals to False in my case, in condition of _writer.py:27.

Edited Mar 17, 2021 by nmartensen
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information
Assignee
Assign to
Time tracking