openpyxl单元格输出异常排查：A2单元格显示###而非计算结果43的原因

Let's break down your issue and fix it step by step:

The Problem #

You're testing openpyxl for your business scenario, and after setting a formula in cell A2 (after first assigning a datetime value), the cell displays ### instead of the expected sum result 43. Your code is:

from openpyxl import Workbook
# Note: Missing import for datetime here, which will cause a runtime error
wb = Workbook()
ws = wb.active
ws['A1'] = 42
ws['A2'] = datetime.datetime.now()
ws['A2'] = "=SUM(A1, 1)"
wb.save("sample.xlsx")

Root Cause #

Your guess is spot-on! The issue stems from cell data type and format persistence. When you first assign a datetime to A2, openpyxl configures Excel to apply a date format to that cell. Later, when you overwrite the cell with a formula string, openpyxl doesn't automatically reset the cell's format—it keeps the date formatting intact.

The formula result 43 is a numeric value, but when displayed with a date format, it translates to a date way outside Excel's valid date range (Excel dates start around 1900), so Excel shows ### to indicate an invalid date value.

Fix #

To resolve this, you need to explicitly reset the cell's number format to General after setting the formula, so the numeric result displays correctly. Here's the corrected code:

from openpyxl import Workbook
import datetime  # Added missing import

wb = Workbook()
ws = wb.active
ws['A1'] = 42
ws['A2'] = datetime.datetime.now()
ws['A2'] = "=SUM(A1, 1)"
# Reset format to General to display the numeric formula result
ws['A2'].number_format = 'General'
wb.save("sample.xlsx")

Quick Note #

Don't forget the import datetime line in your original code—it's missing and will throw an error when you try to run it!

内容的提问来源于stack exchange，提问作者Mayank Pant