Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
27.3. Python 处理 Excel
上一页 第 27 章 办公自动化 下一页
知乎专栏

27.3. Python 处理 Excel

27.3.1. openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files

https://openpyxl.readthedocs.io/en/stable/

27.3.1.1. 创建空文档

			
from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
workbook.save('netkiller.xlsx')

27.3.1.2. 工作表

默认工作表

sheet = workbook.active 会创建一个工作表，默认名字是 Sheet 修改方法是 sheet.title = 'netkiller' 

				
from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
# 重命名工作表
sheet.title = 'netkiller'
workbook.save('openpyxl.xlsx')
创建新工作表
				
from openpyxl import Workbook

# 默认工作表
workbook = Workbook()
worksheet = workbook.active
worksheet.title = 'Windows'

# 创建新工作表，后面的参数0表示表示在工作表中的位置，0是第一位。
worksheet1 = workbook.create_sheet("MacOS", 0)
worksheet2 = workbook.create_sheet("Linux")
workbook.save('worksheet.xlsx')
遍历工作表
				
from openpyxl import Workbook, load_workbook

workbook = load_workbook('worksheet.xlsx')

print('输出文件所有工作表名：', workbook.sheetnames)

# 遍历查看当前 Excel 文档所有工作表名称
for sheet in workbook:
    print(sheet.title)
删除工作表
				
from openpyxl import Workbook, load_workbook

workbook = load_workbook('worksheet.xlsx')

print('列出当前文档所有工作表名：', workbook.sheetnames)
# 打开默认工作表
sheet = workbook.active
# 删除工作表
workbook.remove(sheet)
# 遍历查看当前 Excel 文档所有工作表名称
for sheet in workbook:
    print(sheet.title)

27.3.1.3. 单元格

单元格填充数据
				
from openpyxl import Workbook
workbook = Workbook()
worksheet = workbook.active
worksheet.title = '员工表'

# 向单元格写入数据
worksheet['A1'] = '姓名'
worksheet['B1'] = '性别'
worksheet.cell(row=2, column=1, value='景峯')
worksheet.cell(row=2, column=2, value='男')
worksheet.cell(row=3, column=1, value='小明')
worksheet.cell(row=3, column=2, value='男')
worksheet.cell(row=3, column=1, value='小梅')
worksheet.cell(row=3, column=2, value='女')

workbook.save('cell.xlsx')

追加数据

				
import datetime
import random
from openpyxl import Workbook
wb = Workbook()

# grab the active worksheet
ws = wb.active

# Data can be assigned directly to cells
ws['A1'] = '数据测试表'

# Rows can also be appended
for i in range(10):
    ws.append([random.randint(1, 10), random.randint(
        1, 10), random.randint(1, 10)])

# Python types will automatically be converted
ws['A12'] = "创建日期"
ws['B12'] = datetime.datetime.now()

# Save the file
wb.save("sample.xlsx")
获取工作表行数和列数
				
from openpyxl import Workbook
workbook = Workbook()
worksheet = workbook.active
worksheet.title = '员工表'

# 向单元格写入数据
worksheet['A1'] = '姓名'
worksheet['B1'] = '性别'
worksheet.cell(row=2, column=1, value='景峯')
worksheet.cell(row=2, column=2, value='男')
worksheet.cell(row=3, column=1, value='小明')
worksheet.cell(row=3, column=2, value='男')
worksheet.cell(row=3, column=1, value='小梅')
worksheet.cell(row=3, column=2, value='女')

workbook.save('cell.xlsx')

# 获取表有多少列
print('输出文件所有工作表名：', worksheet.max_column)

# 获取表中最多有多少行
print('输出文件所有工作表名：', worksheet.max_row)
读取单元格
				
# 读取
col0 = worksheet['A1']
col1 = worksheet.cell(row=1, column=2)
# print(c, c1)
print(col0.value, col1.value)

读取行列数据

				

import datetime
import random
from openpyxl import Workbook
wb = Workbook()

ws = wb.active

# Data can be assigned directly to cells
ws['A1'] = '序列号'
ws['B1'] = '随机数列A'
ws['C1'] = '随机数列B'
ws['D1'] = '随机数列C'
# Rows can also be appended
for i in range(10):
    ws.append([i+1, random.randint(1, 10), random.randint(
        1, 10), random.randint(1, 10)])

# Python types will automatically be converted
ws['A12'] = "合计"
ws['B12'] = "=SUM(B2:B11)"
ws['C12'] = "=SUM(C2:C11)"
ws['D12'] = "=SUM(D2:D11)"


# 获取指定单元格数据
a3 = ws['A1']
print(a3.value)

print(("-" * 10) + "获取A列数据" + ("-" * 10))

# 获取指定一列数据
columns = ws['A']
for i in columns:
    print(i.value)

print(("-" * 10) + "获取第3行数据" + ("-" * 10))

# 获取一行数据
rows = ws[3]
for i in rows:
    print(i.value, sep=',')
print()
print(("-" * 10) + "iter_rows方法演示" + ("-" * 10))

# 获取数据库
for row in ws.iter_rows(min_row=1, max_col=2, max_row=ws.max_row-1):
    for cell in row:
        print(cell.value, end="\t")
    print()

print(("-" * 10) + "遍历工作表" + ("-" * 10))
for row in ws.values:
    for value in row:
        print(value, end="\t")
    print()


# Save the file
# wb.save("formulae.xlsx")
修改单元格
				
# 修改
worksheet['A1'] = '参加人'
worksheet.cell(2, 2).value = 'netkiller'
单元格合并/取消合并
				
from openpyxl.workbook import Workbook

wb = Workbook()
ws = wb.active

ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')

# or equivalently
ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
单元格格式化

日期格式化

				
import datetime
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# set date using a Python datetime
ws['A1'] = datetime.datetime(2010, 7, 21)

print(ws['A1'].number_format)
'yyyy-mm-dd h:mm:ss'
使用公式
				
import datetime
import random
from openpyxl import Workbook
wb = Workbook()

ws = wb.active

# Data can be assigned directly to cells
ws['A1'] = '序列号'
ws['B1'] = '随机数列A'
ws['C1'] = '随机数列B'
ws['D1'] = '随机数列C'
# Rows can also be appended
for i in range(10):
    ws.append([i+1, random.randint(1, 10), random.randint(
        1, 10), random.randint(1, 10)])

# Python types will automatically be converted
ws['A12'] = "合计"
ws['B12'] = "=SUM(B2:B11)"
ws['C12'] = "=SUM(C2:C11)"
ws['D12'] = "=SUM(D2:D11)"

# Save the file
wb.save("formulae.xlsx")
插入图片
				
from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active
ws['A1'] = 'You should see three logos below'
# create an image
img = Image('logo.png')
# add to worksheet and anchor next to cells
ws.add_image(img, 'A1')
wb.save('logo.xlsx')
行高和列宽
				
# 将整个表的行高设置为 50，列宽设置为 30；
sheet.row_dimensions.height = 50 
sheet.column_dimensions.width = 30 
				
				

				
# 设置第 1 行的高度
sheet.row_dimensions[1].height = 50
# 设置 B 列的宽度
sheet.column_dimensions["B"].width = 20
行列隐藏
				
import openpyxl
wb = openpyxl.Workbook()
ws = wb.create_sheet()
ws.column_dimensions.group('A','D', hidden=True)
ws.row_dimensions.group(1,10, hidden=True)
wb.save('group.xlsx')

27.3.1.4. 样式设置

sheet选项卡背景色

修改sheet选项卡背景色，默认为白色

				
ws.sheet_properties.tabColor = "FFA500"
字体
				
"""
name:		字体名称
color:		颜色通常是RGB或aRGB十六进制值
b(bold):	加粗（bool）
i(italic):	倾斜(bool)
shadow：		阴影（bool）
underline：	下划线（‘doubleAccounting’, ‘single’, ‘double’, ‘singleAccounting’）
charset:	字符集(int)
strike:		删除线(bool)
"""				
				
				
				
from openpyxl import Workbook
from openpyxl.styles import Font

workbook = Workbook()
worksheet = workbook.active

fontStyle = Font(size="18")
worksheet.cell(row=1, column=1, value='《Netkiller Python 手札》').font = fontStyle
worksheet.merge_cells('A1:E1')

worksheet.cell(row=2, column=1, value='作者：netkiller').font = Font(
    name='黑体', color='FF4433', size=15, b=False)

workbook.save('font.xlsx')
单元格背景色
				

from openpyxl.styles import PatternFill, GradientFill
from openpyxl import Workbook

workbook = Workbook()
worksheet = workbook.active

# 填充单元格颜色
worksheet["A1"].fill = PatternFill(fill_type="solid", fgColor="99ccff")
worksheet.merge_cells('A1:E1')

# 填充单元格渐变色
worksheet["A3"].fill = GradientFill(stop=("FFFFFF", "99ccff", "000000"))
worksheet.merge_cells('A3:E3')

workbook.save(filename="fill.xlsx")
设置单元格样式
				
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
workbook = Workbook()
worksheet = workbook.active
worksheet.title = '员工表'

# 在第一行插入新的一行作为表头
worksheet.insert_rows(1)
# 设置文本标题
worksheet['A1'] = '《Netkiller Python 手札》'
# 水平跨列合并单元格
worksheet.merge_cells('A1:E1')
# 设置样式
style = worksheet['A1']
# 设置字体
font = Font(name='黑体', color='555555', size=15, b=True)

style.font = font

# 设置文本对齐
align = Alignment(horizontal='center', vertical='center')
"""
horizontal:水平对齐('centerContinuous', 'general', 'distributed',
                    'left', 'fill', 'center', 'justify', 'right')
vertical:垂直对齐（'distributed', 'top', 'center', 'justify', 'bottom'）
"""
style.alignment = align

# 设置图案填充
fill = PatternFill('solid', fgColor='FFAABB')
style.fill = fill
workbook.save('style.xlsx')
综合应用

				
from openpyxl.styles import Side, Border, Alignment, Font, PatternFill, NamedStyle, colors
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
import random

workbook = Workbook()
worksheet = workbook.active

worksheet['A1'] = '数据测试表'
worksheet.merge_cells('A1:E1')


worksheet['A2'] = '序号'
worksheet['B2'] = 'A列'
worksheet['C2'] = 'B列'
worksheet['D2'] = 'C列'
worksheet['E2'] = '合计'

# Rows can also be appended
for i in range(1, 10):
    worksheet.append([i, random.randint(1, 10), random.randint(
        1, 10), random.randint(1, 10), "=SUM(B{}:D{})".format(i+2, i+2)])

# Python types will automatically be converted
worksheet['A12'] = "合计"
worksheet['B12'] = "=SUM(B2:B11)"
worksheet['C12'] = "=SUM(C2:C11)"
worksheet['D12'] = "=SUM(D2:D11)"
worksheet['E12'] = "=SUM(E2:E11)"
# worksheet['B12'] = datetime.datetime.now()


# 字体
fontTitle = Font(name='黑体', size=18, b=True)
fontHeader = Font(name='黑体', size=11, b=True)

# 边框
thin = Side(style='thin', color='000000')  # 细边框
medium = Side(style='medium', color=colors.BLACK)  # 粗边框

titleBorder = Border(top=medium, bottom=medium, left=medium, right=medium)
headerBorder = Border(top=medium, bottom=thin, left=thin, right=thin)
bodyBorder = Border(top=thin, bottom=thin, left=thin, right=thin)

# 最外边粗边框
tableTopBorder = Border(top=medium)
tableRightBorder = Border(right=medium)
tableBottomBorder = Border(bottom=medium)
tableLeftBorder = Border(left=medium)

# 单元格填充颜色
titlePatternFill = PatternFill('solid', fgColor='CFCFCF')
headerPatternFill = PatternFill('solid', fgColor='EEEEEE')

# 对齐
alignment = Alignment(horizontal='center', vertical='center')

# 将样式打包命名
titleNamedStyle = NamedStyle(name='titleNamedStyle', font=fontTitle, fill=titlePatternFill,
                             alignment=alignment)  # border=titleBorder,
# 表头样式
headerNamedStyle = NamedStyle(
    name='headerNamedStyle', font=fontHeader, fill=headerPatternFill, alignment=alignment, border=headerBorder)
headerLeftNamedStyle = NamedStyle(
    name='headerLeftNamedStyle', font=fontHeader, fill=headerPatternFill, alignment=alignment, border=tableLeftBorder+tableTopBorder)
headerRightNamedStyle = NamedStyle(
    name='headerRightNamedStyle', font=fontHeader, fill=headerPatternFill, alignment=alignment, border=tableRightBorder+tableTopBorder)

bodyNamedStyle = NamedStyle(
    name='bodyNamedStyle', font=fontHeader, border=bodyBorder, alignment=alignment)

# 读取数据表格范围
rows = worksheet.max_row
cols = worksheet.max_column

worksheet['A1'].style = titleNamedStyle

# for cell in worksheet[1]:
# cell.border = tableTopBorder

n = 0
for cell in worksheet[2]:
    if n == 0:
        cell.style = headerLeftNamedStyle
    elif n == cols-1:
        cell.style = headerRightNamedStyle
    else:
        cell.style = headerNamedStyle
    n += 1

for r in range(3, rows+1):
    for c in range(1, cols+1):
        if c == 1:
            worksheet.cell(r, c).border = tableLeftBorder + bodyBorder
        elif c == worksheet.max_column:
            worksheet.cell(r, c).border = tableRightBorder + bodyBorder
        else:
            worksheet.cell(r, c).style = bodyNamedStyle

n = 0
for cell in worksheet[rows]:
    if n == 0:
        cell.border = tableLeftBorder + tableBottomBorder + bodyBorder
    elif n == cols-1:
        cell.border = tableRightBorder + tableBottomBorder + bodyBorder
    else:
        cell.border = tableBottomBorder + bodyBorder
    n += 1

# for cell in worksheet['A']:
#     cell.border = tableLeftBorder + bodyBorder
# for cell in worksheet[get_column_letter(worksheet.max_column)]:
#     cell.border = tableRightBorder + bodyBorder

workbook.save("borders.xlsx")

27.3.1.5. 工具

数字列转标签
				
from openpyxl.utils import get_column_letter

letter = column = get_column_letter(3)

print(letter)

第3列对应标签是C

				
neo@MacBook-Pro-Neo ~/workspace/python % python3.9 /Users/neo/workspace/python/office/openpyxl/utils.py
C

27.3.2. xlrd/xlwt/xlutils

https://github.com/python-excel

27.3.2.1. 读 Excel

安装 xlrd

			
neo@MacBook-Pro-Neo ~/workspace/python % pip install xlrd

演示程序

			
import xlrd

workbook = xlrd.open_workbook(filename='test.xls')  # 打开文件
print("获取所有工作表: {}".format(workbook.sheet_names()))              # 获取所有工作表
print("工作表数量 {0}".format(workbook.nsheets))

sheet1 = workbook.sheet_by_index(0)        # 通过索引获取表格
sheet2 = workbook.sheet_by_name('工资表')   # 通过名字获取表格
print(sheet1, sheet2)
print("工作表名 {}, 行数 {}, 列数 {}".format(sheet1.name, sheet1.nrows, sheet1.ncols))

rows = sheet1.row_values(2)                 # 获取行内容
cols = sheet1.col_values(3)                 # 获取列内容
print("第二行内容 {}".format(rows))
print("第三列内容 {}".format(cols))

# 获取表格里的内容，三种方式
print(sheet1.cell(1, 0).value)
print(sheet1.cell_value(1, 0))
print(sheet1.row(1)[0].value)

27.3.2.2. 写 Excel

		
neo@MacBook-Pro-Neo ~/workspace/python % pip install xlwt
添加工作表
		
import xlwt
# 创建workbook对象
workbook = xlwt.Workbook()
# 创建工作表 sheet
sheet1 = workbook.add_sheet('sheet1', cell_overwrite_ok=True)
sheet2 = workbook.add_sheet('sheet2', cell_overwrite_ok=True)

# 向sheet工作表中写入数据
sheet1.write(0, 0, '姓名')
sheet1.write(0, 1, '性别')
sheet1.write(0, 2, '年龄')

sheet1.write(1, 0, '景峯')
sheet1.write(1, 1, '男')
sheet1.write(1, 2, '35')

# 第二张工作表
sheet2.write(0, 0, '姓名')
sheet2.write(0, 1, '性别')
sheet2.write(0, 2, '年龄')

sheet2.write(1, 0, '景峯')
sheet2.write(1, 1, '男')
sheet2.write(1, 2, '35')

# 保存该excel文件,文件同名会被覆盖
workbook.save('class.xlsx')

设置编码

			
excel = xlwt.Workbook(encoding='utf-8')

设置列宽

			
sheet.col(0).width = 25 * 256

设置行高

			
sheet.row(0).height_mismatch = True
sheet.row(0).height = 20 * 20
合并单元格
				
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
# 参数详解： write_merge(列, 行, 合并列数, 合并行数, '文本'，样式)
worksheet.write_merge(0, 0, 0, 3, '《Netkiller Python 手札》')
worksheet.write_merge(1, 2, 0, 3, '作者：netkiller')
workbook.save('netkiller.xls')				
				
				
			
import xlwt
# 创建workbook对象
workbook = xlwt.Workbook()
# 创建工作表 sheet
sheet1 = workbook.add_sheet('sheet1', cell_overwrite_ok=True)

# 水平合并行单元格
sheet1.write_merge(0, 0, 0, 3, '班级学生名单')

# 向sheet工作表中写入数据
sheet1.write(1, 0, '姓名')
sheet1.write(1, 1, '性别')
sheet1.write(1, 2, '年龄')
sheet1.write(1, 2, '疫苗接种')

sheet1.write(2, 0, '景峯')
sheet1.write(2, 1, '男')
sheet1.write(2, 2, '35')

sheet1.write(3, 0, '小明')
sheet1.write(3, 1, '男')
sheet1.write(3, 2, '35')

# 垂直合并列单元格
sheet1.write_merge(2, 3, 3, 3, '已接种')

# 保存该excel文件,文件同名会被覆盖
workbook.save('class.xlsx')
运行公式
			
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, 5) # Outputs 5
worksheet.write(0, 1, 2) # Outputs 2
worksheet.write(1, 0, xlwt.Formula('A1*B1')) 		# Should output "10"(A1[5] * A2[2])
worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) 	# Should output"7" (A1[5] + A2[2])
workbook.save('Workbook.xls')
超链接
			
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0,xlwt.Formula('HYPERLINK("http://www.netkiller.cn";"Netkiller Python 手札")'))
workbook.save('Excel.xls')
样式设置
单元格对齐
				
import xlwt
# 创建workbook对象
workbook = xlwt.Workbook()
# 创建工作表 sheet
sheet1 = workbook.add_sheet('sheet1', cell_overwrite_ok=True)

title = xlwt.XFStyle()
alignment = xlwt.Alignment()
# 垂直对齐
alignment.horz = alignment.HORZ_CENTER
# 水平对齐
alignment.vert = alignment.VERT_CENTER
# 换行
alignment.wrap = alignment.WRAP_AT_RIGHT
# 设置边框

title.alignment = alignment

# valign = xlwt.XFStyle()

# 水平合并行单元格
sheet1.write_merge(0, 0, 0, 3, '班级学生名单', title)

# 向sheet工作表中写入数据
sheet1.write(1, 0, '姓名')
sheet1.write(1, 1, '性别')
sheet1.write(1, 2, '年龄')
sheet1.write(1, 2, '疫苗接种')

sheet1.write(2, 0, '景峯')
sheet1.write(2, 1, '男')
sheet1.write(2, 2, '35')

sheet1.write(3, 0, '小明')
sheet1.write(3, 1, '男')
sheet1.write(3, 2, '35')

# 垂直合并列单元格
sheet1.write_merge(2, 3, 3, 3, '已接种')

# 保存该excel文件,文件同名会被覆盖
workbook.save('class.xlsx')
字体和颜色
				
import xlwt
# 创建workbook对象
workbook = xlwt.Workbook()
# 创建工作表 sheet
sheet1 = workbook.add_sheet('sheet1', cell_overwrite_ok=True)

title = xlwt.XFStyle()

font = xlwt.Font()
# 字体类型
font.name = 'name Times New Roman'
# 字体颜色
font.colour_index = 4
# 字体大小，11为字号，20为衡量单位
font.height = 20 * 11
# 字体加粗
font.bold = False
# 下划线
font.underline = True
# 斜体字
font.italic = True


alignment = xlwt.Alignment()
# 垂直对齐
alignment.horz = alignment.HORZ_CENTER
# 水平对齐
alignment.vert = alignment.VERT_CENTER
# 换行
alignment.wrap = alignment.WRAP_AT_RIGHT
# 设置边框

title.alignment = alignment
title.font = font

valign = xlwt.XFStyle()
va = xlwt.Alignment()
va.vert = alignment.VERT_CENTER
valign.alignment = va

# 水平合并行单元格
sheet1.write_merge(0, 0, 0, 3, '班级学生名单', title)

# 向sheet工作表中写入数据
sheet1.write(1, 0, '姓名')
sheet1.write(1, 1, '性别')
sheet1.write(1, 2, '年龄')
sheet1.write(1, 3, '疫苗接种')

sheet1.write(2, 0, '景峯')
sheet1.write(2, 1, '男')
sheet1.write(2, 2, '35')

sheet1.write(3, 0, '小明')
sheet1.write(3, 1, '男')
sheet1.write(3, 2, '35')

# 垂直合并列单元格
sheet1.write_merge(2, 3, 3, 3, '已接种', valign)

# 保存该excel文件,文件同名会被覆盖
workbook.save('class.xlsx')
设置边框
				
import xlwt
# 创建workbook对象
workbook = xlwt.Workbook()
# 创建工作表 sheet
sheet1 = workbook.add_sheet('sheet1', cell_overwrite_ok=True)

borders = xlwt.Borders()

# 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
# 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
# borders.left_colour = 1
# borders.right_colour = 1
# borders.top_colour = 1
# borders.bottom_colour = 1

style_borders = xlwt.XFStyle()
style_borders.borders = borders

title = xlwt.XFStyle()
alignment = xlwt.Alignment()
# 垂直对齐
alignment.horz = alignment.HORZ_CENTER
# 水平对齐
alignment.vert = alignment.VERT_CENTER
# 换行
alignment.wrap = alignment.WRAP_AT_RIGHT
# 设置边框

title.alignment = alignment

valign = xlwt.XFStyle()
va = xlwt.Alignment()
va.vert = alignment.VERT_CENTER
valign.alignment = va
valign.borders = borders

# 水平合并行单元格
sheet1.write_merge(0, 0, 0, 3, '班级学生名单', title)

# 向sheet工作表中写入数据
sheet1.write(1, 0, '姓名', style_borders)
sheet1.write(1, 1, '性别', style_borders)
sheet1.write(1, 2, '年龄', style_borders)
sheet1.write(1, 3, '疫苗接种', style_borders)

sheet1.write(2, 0, '景峯', style_borders)
sheet1.write(2, 1, '男', style_borders)
sheet1.write(2, 2, '35', style_borders)

sheet1.write(3, 0, '小明', style_borders)
sheet1.write(3, 1, '男', style_borders)
sheet1.write(3, 2, '35', style_borders)

# 垂直合并列单元格
sheet1.write_merge(2, 3, 3, 3, '已接种', valign)

# 保存该excel文件,文件同名会被覆盖
workbook.save('class.xlsx')
设置背景色
				
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
pattern = xlwt.Pattern()  # Create the Pattern
# May be: NO_PATTERN,SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# May be: 8 through 63. 0 = Black,1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7= Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = DarkYellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = LightGray, 23 = Dark Gray, the list goes on...
pattern.pattern_fore_colour = 5

style = xlwt.XFStyle()  # Create Style
style.pattern = pattern  # Add Borders to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Workbook.xls')
单元格格式化
				
import xlwt
from datetime import datetime
workbook = xlwt.Workbook()
sheet1 = workbook.add_sheet('工资表', cell_overwrite_ok=True)
columes = ["姓名", "年龄", "出生日期", "工资", '报销']
datas = [
    ["张三", 13, '2019-02-12', 16800, 15779.24],
    ["李四", 12, '2018-03-15', 17200, -24.225]
]

format = xlwt.XFStyle()
format.num_format_str = 'yyyy/mm/dd'

number = xlwt.XFStyle()


sheet1.write_merge(0, 0, 0, 4, '工资表')  # 合并行单元格
# 写第一行
for i in range(0, len(columes)):
    sheet1.write(1, i, columes[i])

line = 2
# 写第一列
for data in datas:
    for col in range(len(data)):
        cell = data[col]
        if col == 2:
            date = datetime.strptime(cell, '%Y-%m-%d').date()  # %H:%M:%S
            # print(date)
            sheet1.write(line, col, date, format)
        elif col == 3:
            number.num_format_str = '#,##;[RED]-#,##'
            sheet1.write(line, col, cell, number)
        elif col == 4:
            number.num_format_str = '#,##0.00;[RED]-#,##0.00'
            sheet1.write(line, col, cell, number)
        else:
            sheet1.write(line, col, cell)
    line = line+1

workbook.save('test.xls')

27.3.2.3. xlutils

安装 xlutils

			
pip install xlutils
			
			
			
import xlrd
import xlutils.copy

# 打开一个xls文件
xls = xlrd.open_workbook('test.xls')
workbook = xlutils.copy.copy(xls)

# 添加新sheet表
workbook.add_sheet('sheet2', cell_overwrite_ok=True)

# 获取sheet对象，通过sheet_by_index()获取的sheet对象没有write()方法
sheet = workbook.get_sheet(0)

# 修改数据
sheet.write(2, 0, '王二小')

# 保存时同名会覆盖，达到修改excel文件的目的
workbook.save('test.xls')

27.3.3. xlwings

		
import matplotlib.pyplot as plt
import xlwings as xw

fig = plt.figure()
plt.plot([1, 2, 3])

sheet = xw.Book().sheets[0]
sheet.pictures.add(fig, name='Plot', update=True)
上一页 上一级 下一页
27.2. Word 文字处理 起始页 第 28 章 实用代码