Python openpyxl单元格写入详解：多种写入方式与数据类型教程

# 坐标写入
ws['A1'] = 'Hello'

# cell()方法写入
ws.cell(row=1, column=1, value='Hello')

# 追加行写入
ws.append([1, 2, 3])

# 公式写入
ws['A3'] = '=SUM(A1:A2)'

from openpyxl import Workbook
from datetime import datetime

wb = Workbook()
ws = wb.active

# 坐标写入
ws['A1'] = '文本数据'
ws['B1'] = 100
ws['C1'] = 3.14
ws['D1'] = True
ws['E1'] = datetime(2024, 6, 15)

# cell()方法写入
ws.cell(row=2, column=1, value='第二行')
ws.cell(row=2, column=2, value=200)
ws.cell(row=2, column=3, value=6.28)

# 查看写入结果
for col in range(1, 6):
    cell = ws.cell(row=1, column=col)
    print(f"{cell.coordinate}: {cell.value} (类型: {type(cell.value).__name__})")

wb.save('write_basic.xlsx')
print("\n基本写入文件已保存")

A1: 文本数据 (类型: str)
B1: 100 (类型: int)
C1: 3.14 (类型: float)
D1: True (类型: bool)
E1: 2024-06-15 00:00:00 (类型: datetime)

基本写入文件已保存

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 表头
ws.append(['产品', '单价', '数量', '金额'])

# 追加数据行
products = [
    ['苹果', 5.5, 10],
    ['香蕉', 3.2, 20],
    ['橙子', 4.8, 15],
    ['葡萄', 8.0, 8],
]
for product in products:
    ws.append(product)

# 写入公式（金额 = 单价 * 数量）
for row in range(2, 6):
    ws.cell(row=row, column=4).value = f'=B{row}*C{row}'

# 合计行
ws.append(['合计', '', '', '=SUM(D2:D5)'])

# 查看结果
for row in ws.iter_rows(values_only=True):
    print(f"  {row}")

wb.save('write_formula.xlsx')
print("\n公式写入文件已保存")

  ('产品', '单价', '数量', '金额')
  ['苹果', 5.5, 10, '=B2*C2']
  ['香蕉', 3.2, 20, '=B3*C3']
  ['橙子', 4.8, 15, '=B4*C4']
  ['葡萄', 8.0, 8, '=B5*C5']
  ['合计', '', '', '=SUM(D2:D5)']

公式写入文件已保存

from openpyxl import Workbook
from datetime import datetime, timedelta

wb = Workbook()
ws = wb.active
ws.title = '考勤表'

# 写入表头
headers = ['日期', '姓名', '上班时间', '下班时间', '状态']
ws.append(headers)

# 循环写入日期数据
start_date = datetime(2024, 1, 1)
for i in range(5):
    date = start_date + timedelta(days=i)
    ws.cell(row=i+2, column=1, value=date)
    ws.cell(row=i+2, column=2, value=f'员工{i+1}')
    ws.cell(row=i+2, column=3, value=datetime(2024, 1, 1, 9, 0))
    ws.cell(row=i+2, column=4, value=datetime(2024, 1, 1, 18, 0))
    ws.cell(row=i+2, column=5, value='正常')

# 设置日期格式
from openpyxl.styles import numbers
for row in range(2, 7):
    ws.cell(row=row, column=1).number_format = 'YYYY-MM-DD'
    ws.cell(row=row, column=3).number_format = 'HH:MM'
    ws.cell(row=row, column=4).number_format = 'HH:MM'

# 查看结果
for row in ws.iter_rows(min_row=1, max_row=3, values_only=True):
    print(f"  {row}")

wb.save('write_date.xlsx')
print("\n日期写入文件已保存")

  ('日期', '姓名', '上班时间', '下班时间', '状态')
  (datetime(2024, 1, 1, 0, 0), '员工1', datetime(2024, 1, 1, 9, 0), datetime(2024, 1, 1, 18, 0), '正常')
  (datetime(2024, 1, 2, 0, 0), '员工2', datetime(2024, 1, 1, 9, 0), datetime(2024, 1, 1, 18, 0), '正常')

日期写入文件已保存