利用 openpyxl 写EXCEL

openpyxl

import datetime

import time

from openpyxl import Workbook

wb = Workbook() #创建文件对象

# grab the active worksheet

ws = wb.active #获取第一个sheet

# Data can be assigned directly to cells

ws[‘A1’] = 42 #写入数字

ws[‘B1’] = “你好”+“吃饭了没” #写入中文

# Rows can also be appended

ws.append([1, 2, 3]) #写入多个单元格

# 写入一个当前时间

ws[‘A2’] = datetime.datetime.now()

#写入一个自定义的时间格式

# ws[‘A3’] =time.strftime("%Y年%m月%d日 %H时%M分%S秒",time.localtime())

# Save the file

wb.save(“d:\sample6.xlsx”)

from openpyxl import Workbook

wb = Workbook()

ws1 = wb.create_sheet(“Mysheet”) # 创建一个sheet

ws1.title = “表1” # 设定一个sheet的名字

ws2 = wb.create_sheet(“Mysheet”, 1) # 设定sheet的插入位置 默认插在后面

ws2.title = “表2” # 设定一个sheet的名字 必须是Unicode

ws1.sheet_properties.tabColor = “1072BA” # 设定sheet的标签的背景颜色

# 复制一个sheet

wb[“表2”][“A1”] = “zeke”

source = wb[“表2”]

target = wb.copy_worksheet(source)

# w3 = wb.copy_worksheet(wb[‘new title’])

# ws3.title = ‘new2’

# wb.copy_worksheet(wb[‘new title’]).title = ‘hello’

# Save the file

wb.save(“e:\sample1.xlsx”)

指定单元格写值

from openpyxl import Workbook

wb = Workbook()

ws1 = wb.create_sheet(“Mysheet”) #创建一个sheet

ws1[“A1”]=123.11

ws1[“B2”]=“你好”

d = ws1.cell(row=4, column=2, value=10)

# Save the file

wb.save(“e:\sample2.xlsx”)

from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook(‘e:\sample2.xlsx’)
wb.guess_types = True #猜测格式类型
ws=wb.active
ws[“D1”]=“12%”

Save the file

wb.save(“e:\sample4.xlsx”)