前言：

Python操作Excel需要三方库的支持，如果要兼容Excel 2007以前的版本，也就是xls格式的Excel文件，可以使用三方库xlrd和xlwt，前者用于读Excel文件，后者用于写Excel文件。如果使用较新版本的Excel，即操作xlsx格式的Excel文件，可以使用openpyxl库，当然这个库不仅仅可以操作Excel，还可以操作其他基于Office Open XML的电子表格文件。openpyxl并不支持操作Office 2007以前版本的Excel文件。
xlsx格式是向下兼容的，可兼容xls格式。
xls 是一个特有的二进制格式，其核心结构是复合文档类型的结构，而 xlsx 的核心结构是 XML 类型的结构，采用的是基于 XML 的压缩方式，使其占用的空间更小。xlsx 中最后一个 x 的意义就在于此。
区别csv是文本文件,用记事本就能打开。
xlrd官方文档
openpyxl官方文档

注意：用WPS或者office打开对应xls或者xlsx文件的时候，python代码无法对其进行操作，

在使用python代码操作xls或者xlsx文件的时候，要先关闭软件当中打开的文件。

2007年之前版本【后缀为.xls】

需要导入的包：xlwt（写）/xlrd（读）/xlutils（工具模块）
- pip install xlwt
- pip install xlrd
- pip install xlutils
通过pip list查看是否安装成功
1.1、读Excel文件

# 先导入xlrd模块
import xlrd
# 1.1.1、使用xlrd模块的open_workbook函数打开指定Excel文件并获得Book对象（工作簿）
wb = xlrd.open_workbook('阿里巴巴2020年股票数据.xls')	#确保xls文件和py代码文件在同一个文件夹就可以用这种相对路径
# 1.1.2、通过Book对象的sheet_names方法可以获取所有表单名称
sheet_names = wb.sheet_names()
# print(sheet_names)  #['股票数据', '开房记录', 'test']

# 1.1.3、通过指定的表单名称获取Sheet对象（工作表）
sheet = wb.sheet_by_name(sheet_names[0])

# 1.1.4、通过Sheet对象的nrows和ncols属性获取表单的行数和列数
print(sheet.nrows,sheet.ncols)  #255 7

# 1.1.5、遍历获取每个单元格当中的值（在xls类型的py中，行和列的索引是从零开始的）
# for r in range(sheet.nrows):
#     for c in range(sheet.ncols):
#         # 通过Sheet对象的cell方法获取指定Cell对象（单元格）
#         # 通过Cell对象的value属性获取单元格中的值
#         va = sheet.cell(r,c).value
#         if r > 0:
#             # 第1列的xldate类型先转成元组再格式化为“年月日”的格式
#             # 其中0代表以1900-01-01为基准的日期，1代表以1904-01-01为基准的日期
#             if c == 0:
#                 va = xlrd.xldate_as_tuple(va, 0)
#                 va = f'{va[0]}年{va[1]:2d}月{va[2]:2d}日'
#             # 其他列number类型处理成小数点后保留两位有效数字
#             else:
#                 va = f'{va:.2f}'
#         print(va,end='\t')
#     print()
'''
打印结果：
Date	High	Low	Open	Close	Volume	Adj Close
2019年12月31日	213.64	210.73	212.00	212.10	6773600.00	212.10
2020年 1月 2日	219.98	216.54	216.60	219.77	15873500.00	219.77
……
'''
# 1.1.6、获取最后一个单元格的数据类型
# 0 - 空值，1 - 字符串，2 - 数字，3 - 日期，4 - 布尔，5 - 错误
last_cell_type = sheet.cell_type(sheet.nrows - 1, sheet.ncols - 1)
print(last_cell_type)   #2

# 1.1.7、获取第一行的值
print(sheet.row_values(0))  #['Date', 'High', 'Low', 'Open', 'Close', 'Volume', 'Adj Close']
print(sheet.row_values(0,0,2))  #['Date', 'High']

# 1.1.8、获取指定行指定列范围的数据（列表）
print(sheet.row_slice(0,0,5))   #[text:'Date', text:'High', text:'Low', text:'Open', text:'Close']
print(sheet.row_slice(1,0,5))   #[xldate:43830.0, number:213.6399993896484, number:210.7299957275391, number:212.0, number:212.1000061035156]

1.2、写Excel文件流程：
1、创建工作簿（work book）对象
2、添加工作表
3、写入数据
4、将工作簿保存到文件中

import random,xlwt
# 1.2.1、创建工作簿对象
wb = xlwt.Workbook()
# 1.2.2、创建工作表对象
sheet1 = wb.add_sheet('成绩表')	#工作簿当中对应表格名称会被修改
# 1.2.3、写入数据
# 定义数据
student_name = ['张三','李四','王五','赵六']
# 列表生成式，生成成绩，三门课成绩【语文 数学 英语】
scores = [[random.randrange(40,96) for _ in range(3)] for _ in range(4)]
print(scores)
# 创建表头【元组】
titles = ('姓名','语文','数学','英语')
# 将表头写入工作簿当中
for index,t in enumerate(titles):
    # write的第一个参数：行，第二个参数：列 第三个参数：内容 第四个参数：样式
    sheet1.write(0,index,t)
# 将学生姓名和成绩写入到工作表中
for r in range(len(scores)):
    sheet1.write(r+1,0,student_name[r])
    for c in range(len(scores[r])):
        sheet1.write(r+1,c+1,scores[r][c])
# 1.2.4、将工作簿保存到文件中
wb.save(r'考试成绩.xls')	#会将该工作簿文件和py文件保存在同一个文件夹当中

1.3、调整单元格样式【了解即可】
- 1、主要包括字体（Font）、对齐方式（Alignment）、
  边框（Border）和背景（Background）的设置
- 2、要设置单元格样式需要首先创建一个XFStyle对象，
  再通过该对象的属性对字体、对齐方式、边框等进行设定

2007年之后版本【后缀为.xlsx】

1、需要导入的包：openpyxl
2、openpyxl操作的便捷性上是优于xlwt和xlrd的【xlsx文件性能优于xls文件】
3、openpyxl还支持数据透视和插入图表等操作
4、openpyxl不支持2007年以前版本的Excel文件【xls文件】
5、说明：
Workbook:代表一个Excel 工作薄
Worksheet:代表一个Excel 工作表中的一页（sheet）
Cell:代表最简单的一个单元格
6、workbook对象涉及的属性
active: 获取当前活跃的Worksheet
worksheets: 以列表的形式返回所有Worksheet
read_only: 判断是否以read_only 模式打开excel 文档
encoding：获取文档的字符集编码
properties: 获取文档的元数据，如标题、创建者、创建日期等
7、workbook对象涉及的方法
get_sheet_names:获取所有表格的名称(该方法已经被废弃，推荐使用:通过Workbook 的sheetnames 属性即可获取)
get_sheet_by_name:通过表格名称获取WorkSheet对象(该方法已经被废弃，推荐使用:通过Worksheet[‘表名’]获取)
get_active_sheet: 获取活跃的表格
remove_sheet:删除一个表格
create_sheet：创建一个表格
copy_worksheet：在Workbook 内复制表格
8、Worksheet 对象涉及属性
title：表格的标题
dimensions:表示表格的大小，这里的大小是指数据的表格大小，即，左上角的坐标和右下角的坐标
max_row:表格最大行数
min_row:表格最小行数
max_column：表格最大列数
min_column：表格最小列数
rows:按行获取单元格
columns:按列获取单元格
freeze_panes:冻结窗口
values:按行获取表格内容
9、Worksheet 对象涉及相关方法
iter_rows:按行获取所有单元格，内置属性有:min_row、max_row、min_col和max_col
iter_columns：按列获取所有单元格
append：在表格末尾添加数据
merged_cells：合并多个单元格
unmerged_cells：移除合并的单元格
10、Cell 对象涉及相关属性
row: 单元格所在的行
column：单元格所在的列
value: 单元格的值
coordinate:单元格的坐标

# 2.1、读Excel文件
import openpyxl,random
# 加载工作薄
wb = openpyxl.load_workbook(r'阿里巴巴2020年股票数据.xlsx')
# 获取工作簿当中工作表【sheet】的名字
# print(wb.sheetnames)  #['股票数据', 'Sheet2', 'Sheet3']
sheet2 = wb.worksheets[0]
# 获得单元格的范围
# print(sheet2.dimensions)    #A1:G255
# 获取最大行数和最大列数
# print(sheet2.max_row,sheet2.max_column) #255 7

# 获取指定单元格的值
print(sheet2.cell(3, 3).value)  #216.5399932861328
print(sheet2['A1'].value)   #Date
print(sheet2['G255'].value) #232.7299957275391
# 获取多个单元格（嵌套元组）
print(sheet2['A2:B3'])  #((<Cell '股票数据'.A2>, <Cell '股票数据'.B2>), (<Cell '股票数据'.A3>, <Cell '股票数据'.B3>))
# # 读取所有单元格的数据
# for row_ch in range(2, sheet2.max_row + 1):
#     for col_ch in 'ABCDEFG':
#         value = sheet2[f'{col_ch}{row_ch}'].value    #注意访问形式
#         print(value, end='\t')
#     print()
print(sheet2['A2:B3'])  #((<Cell '股票数据'.A2>, <Cell '股票数据'.B2>), (<Cell '股票数据'.A3>, <Cell '股票数据'.B3>))
wb.save(r'阿里巴巴2020年股票数据.xlsx')

总结：
- 1、openpyxl获取指定的单元格有两种方式，一种是通过cell方法，需要注意，该方法的行索引和列索引都是从1开始的，这是为了照顾用惯了Excel的人的习惯；另一种是通过索引运算，通过指定单元格的坐标，例如C3、G255，也可以取得对应的单元格，再通过单元格对象的value属性，就可以获取到单元格的值。
- 2、可以通过类似sheet[‘A2:C5’]或sheet[‘A2’:‘C5’]这样的切片操作获取多个单元格，两者效果相同

# 2.2、写Excel文件
# 创建工作簿
wb3 = openpyxl.Workbook()
# 添加工作表
sheet3 = wb3.active
# 设置工作表名称
sheet3.title = '期末成绩'
titles = ('姓名','化学','物理','生物')
# 给第一行写入
for c,t in enumerate(titles):
    # cell函数形参：第一个是行索引、第二个是列索引、第三个是单元格内容
    sheet3.cell(1,c+1,t)
nub = ('小明','小花','小李','娃哈哈')
# 写入内容
for r,n in enumerate(nub):
    sheet3.cell(r+2,1,n)
    for c in range(2,5):
        sheet3.cell(r+2,c,random.randrange(50,101))
# 保存工作簿
wb3.save('理科成绩.xlsx')

# 2.3、调整样式和公式计算
from openpyxl.styles import Font,Alignment,Border,Side
# 对齐方式
alignment = Alignment(horizontal='center',vertical='center')
# 边框线条
side = Side(color='ff4650',style='mediumDashed')

# 打开工作簿和工作表
wb4 = openpyxl.load_workbook(r'理科成绩.xlsx')
sheet4 = wb4.worksheets[0]
# 调整行高和列宽
sheet4.row_dimensions[1].height = 30
sheet4.column_dimensions['E'].width = 30
sheet4['E1'] = '平均分'
#设置具体一格的字体
sheet4.cell(1,5).font = Font(size=18,bold=True,color='4d1327',name='宋体')
#设置具体一格的对齐方式
sheet4.cell(1,5).alignment = alignment
#设置具体一格的边框
sheet4.cell(1,5).border = Border(left=side,top=side,right=side,bottom=side)
# 计算每个学生的平均分
for i in range(2,6):
    sheet4[f'E{i}'] = f'=average(B{i}:D{i})'
wb4.save(r'理科成绩.xlsx')

# 2.4、生成统计图表
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook(write_only=True)
sheet = wb.create_sheet()

rows = [
    ('水果', '销售A组', '销售B组', '销售C组'),
    ('苹果', 40, 30,80),
    ('香蕉', 50, 60,79),
    ('梨', 80, 70,90),
    ('火龙果', 20, 10,110),
    ('橘子', 70, 90,150),
]

# 向表单中添加行
for row in rows:
    sheet.append(row)

# 创建图表对象
chart = BarChart()
chart.type = 'col'
chart.style = 1 #影响显示配色
#=========作图============
# 设置图表的标题
chart.title = '销售统计图'
# 设置图表纵轴的标题
chart.y_axis.title = '销量'
# 设置图表横轴的标题
chart.x_axis.title = '水果类别'
# 设置数据的范围【包含第一行数据归属】
data = Reference(sheet, min_col=2, min_row=1, max_row=6, max_col=4)
# 设置分类的范围【分类数据所在位置】
cats = Reference(sheet, min_col=1, min_row=2, max_row=6)
# 给图表添加数据
chart.add_data(data, titles_from_data=True)
# 给图表设置分类
chart.set_categories(cats)
# 将图表添加到表单指定的单元格中
sheet.add_chart(chart, 'A10')
wb.save('demo.xlsx')

小任务：将多个内容一致的Excel文件合并到一个Excel文件中

三个分表，格式一样：
- 小宝剑大药房（高新店）2018年销售数据.xlsx
- 小宝剑大药房（犀浦店）2018年销售数据.xlsx
- 小宝剑大药房（新津店）2018年销售数据.xlsx

import openpyxl
from openpyxl.styles import Font,Alignment,Border,Side
#1、建立总表表头
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = '总表'
titles = ('购药时间','社保卡号','商品编码','商品名称','销售数量','应收金额','实收金额')
for indext,n in enumerate(titles):
    sheet.cell(1,indext+1,n)
    sheet.cell(1,indext+1).font = Font(size=10,bold=True,name='宋体')
wb.save('总表.xlsx')
#2、制作添加分表函数【形参表：原表地址，添加分表地址，添加分表的第一个表的起始行，添加分表的指定列】
def add_sheet(f_address,f_add_address,row,column_list):
    #以下是被添加的sheet0
    wb0 = openpyxl.load_workbook(f_address)
    sheet0 = wb0.worksheets[0]
    s0_max_row = sheet0.max_row + 1
    print(f"s0_max_row:{s0_max_row}")
    #以下是用来添加的sheet
    wb = openpyxl.load_workbook(f_add_address)
    sheet = wb.worksheets[0]
    for i in range(row, sheet.max_row + 1):
        for j, c in enumerate(column_list):
            if sheet[f'{c}{i}'].value != None:
                sheet0.cell(s0_max_row, j + 1, sheet[f'{c}{i}'].value)
        s0_max_row += 1
    #保存总表
    wb0.save('总表.xlsx')
#3、调用函数
add_sheet("总表.xlsx",'小宝剑大药房（新津店）2018年销售数据.xlsx',3,'ABCDEFG')
add_sheet("总表.xlsx",'小宝剑大药房（犀浦店）2018年销售数据.xlsx',3,'ABCDEFG')
add_sheet("总表.xlsx",'小宝剑大药房（高新店）2018年销售数据.xlsx',3,'ABCDEFG')

结果展示

文章出处登录后可见！

已经登录？立即刷新

python操作Excel

前言：

注意：用WPS或者office打开对应xls或者xlsx文件的时候，python代码无法对其进行操作，

2007年之前版本【后缀为.xls】

2007年之后版本【后缀为.xlsx】

小任务：将多个内容一致的Excel文件合并到一个Excel文件中

相关推荐