python对excel⽂件的处理
python处理excel⽂件有很多⽅法，最开始接触的是xlrd、xlsxwriter模块，分别⽤于excel⽂件的读、写。后来⼜学习了openpyxl模块，可以同时完成excel⽂件的读、写。再后来，接触了⼤⽜pandas，这是python中专门⽤于数据分析的模块，有更加强⼤的功能。
本⽂尝试梳理⼀下这⼏个⽅法，以实际案例来对⽐各种⽅法的优劣。
1. xlrd、xlsxwriter模块
1import xlrd #读取excel⽂件
2import xlsxwriter #写⼊excel⽂件
3 file_name = r'C:/2020/python-exer/excel_doc/time_fmt.xls' #存在⼀个excel⽂件，⽤于读
python怎么读取excel文件数据4 file_name1 = r'C:/2020/python-exer/excel_doc/time_fmt_output.xls' #新建⼀个excel⽂件，⽤于写
5# 读取excel⽂件，按⾏读取数据，每⾏数据对应⼀个列表元素
6def excel_lines():
7 wb = xlrd.open_workbook(file_name)
8# 打开Excel⽂件
9 sheet1 = wb.sheet_by_name('Sheet1') # 通过excel表格sheet名称获取⼯作表
10 dat = [] # 创建空list
11 Max_lines = ws # sheet1数据最⼤⾏数，即便每列元素不同。
12print(Max_lines)
13for a in range(Max_lines):
14 cells = w_values(a) # 每⾏数据赋值给cells
15 dat.append(cells)
16return dat
View Code
#>>>[['序号', '时间格式定义'], [1.0, '%a Locale’s abbreviated weekday name. '],
[2.0, '%A Locale’s full weekday name. '],
……
从输出内容看出，得到的是⼀个嵌套list，每⾏数据对应着⼀个list元素。
# 读取excel⽂件，按列读取数据，每列数据对应⼀个列表元素
1def excel_cols():
2 wb = xlrd.open_workbook(file_name)
3# 1 打开Excel⽂件，按照名字获取第⼀个⼯作表
4# sheet1 = wb.sheet_by_name('Sheet1') # 通过excel表格sheet名称获取⼯作表
5# 2 Excel的所有sheet是个列表，通过索引获取第⼀个⼯作表
6 sheet1 = wb.sheets()[0]
7# 3 通过索引获取第⼀个⼯作表，这种⽅法有明显优势，不需要知道excel的sheet名称。与#3⽅法相同
8# 最⼤的优势能⽤for循环，遍历所有的sheet。
9# sheet1 = wb.sheet_by_index(0)
10# sheet_2= wb.sheets()[1]
11# print(l_values(0))
12
13 dat = [] # 创建空list
14global Max_rows
15 Max_cols = ls # sheet1数据最⼤列数
16 Max_rows = ws # sheet1数据最⼤⾏数
17print("Max_rows:", Max_rows)
18print("Max_cols:", Max_cols)
19for a in range(Max_cols):
20 cells = l_values(a) # 每列数据赋值给cells
21 dat.append(cells) # 每列数据追加到列表dat，那么dat就是以列数据为元素的列表
22return dat
View Code
#>>> [['序号', 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0, 18.0, 19.0,
20.0, 21.0, 22.0, 23.0, 24.0, '', ''], ['时间格式定义', '%a Locale’s abbreviated weekday name. ', ……
从上⾯的输出结果看，按照excel⽂件的列读取数据，⽣成嵌套list，每⼀列对应⼀个list元素
#写⼊excel⽂件，新建sheet⽤于保存数据
1def write_excel():
2 a = excel_cols()
3 excel_cols = [] # 保存列值的列表
4 excel_cols_comment = [] # 保存列注释的列表
5
6for i in range(Max_rows):
7if i == 0:
8# square_col.append(a[1][0]) #专门取列的头，实际上为了⽅便以后的数据处理。
9print("列的名字：", a[1][0])
10else:
11 excel_cols.append(a[1][i][:3]) #:2只取该元素的前2位，去掉后⾯的注释。
12 excel_cols_comment.append(a[1][i][5:]) # 上⾯列的注释，取字符串5:
13
14print("获取格式化time参数： \n", excel_cols)
15print("获取time参数的说明： \n", excel_cols_comment)
16
17 workbook_w = xlsxwriter.Workbook(file_name1)
18 sheet2 = workbook_w.add_worksheet("output_sheet")
19for i in range(Max_rows): # 因列名单独处理了，所以真正的列元素数要⽐总数-1
20# strf_time = time.strftime(excel_cols[i]) # 调⽤时间模块函数，参数为每列的值
21# comment = excel_cols_comment[i]
22if i == 0: # 每个列的第⼀⾏，列名。i代表⾏号，如果是很多列，也可以再增加j循环，表⽰列号
23 sheet2.write(i, 0, f"格式化时间参数：time.strftime")
24 sheet2.write(i, 1, f"执⾏结果")
25 sheet2.write(i, 2, f"注释")
26else: # 每个列，从第⼆⾏开始循环写⼊
27##下⾯的i-1，原因在于i是⼈为的把列头编写输出。⽽对于列表元素来说，索引从0开始。
28 strf_time = time.strftime(excel_cols[i - 1]) # 调⽤时间模块函数，参数为每列的值
29 comment = excel_cols_comment[i - 1]
30 sheet2.write(i, 0, f"({repr(excel_cols[i - 1])})")
31# 注意这⾥的i-1，前⾯的i与excel表格相关，后⾯的i-1是因为列的元素还是从0开始。
32
33 sheet2.write(i, 1, f"{strf_time}")
34 sheet2.write(i, 2, f"{comment}")
35print("写⼊成功")
36 workbook_w.close()
View Code
以上的程序，实际的关键点在于sheet.write函数的参数处理，第⼀个参数是⾏，第⼆个参数是列，第三个参数是写⼊的数据。其他的语句都是针对数据的具体化处理。
2.openpyxl模块，既可以读、也可以写
import openpyxl
from openpyxl import load_workbook
# 1.载⼊已存在的Excel
filename = r'C:\2020\python-exer\excel_doc\test.xlsx'
wb = load_workbook(filename)
# 注意load_workbook只能打开已经存在的Excel，不能创建新的⼯作簿
# 2.根据名称获取⼯作表
# Workbook对象属性（⼯作簿操作）
# sheetnames：获取⼯作簿中的表（列表）
# active：获取当前活跃的Worksheet
# worksheets：以列表的形式返回所有的Worksheet(表格)
# read_only：判断是否以read_only模式打开Excel⽂档
# encoding：获取⽂档的字符集编码
# properties：获取⽂档的元数据，如标题，创建者，创建⽇期等
1def get_properties(): ##获取excel的sheet属性函数
2print(wb.sheetnames) # >>>['Sheet1', '2表单12']
3print(wb.active) # >>><Worksheet "2表单12">
4print(wb.worksheets) # >>>[<Worksheet "Sheet1">, <Worksheet "2表单12">]
ad_only) # >>>False
ding) # >>>utf-8
7print(wb.properties) # 获取⽂档的元数据，如标题，创建者，创建⽇期等
8print(ator, wb.properties.title) # >>>openpyxl None
9 wb.properties.title = 'test-openpyxl'# >>>修改属性中的title
10print(wb.properties.title)
11print(wb.properties) # 确实修改了titile。
12# 3.Worksheet，Cell对象（⼯作表操作，单元格）。获取execl的sheet⼀般信息的函数
13def get_sheet_info():
14global sheet
15 sheet = wb['Sheet1']
16# 获取⼯作表的名称
17print(sheet.title) # >>>Sheet1
18# 获取⼯作表中⾏和列的最值
19print(sheet.max_column) # >>>2
20print(sheet.max_row) # >>>27
21print(sheet.min_column) # >>>1
22print(sheet.min_row) # >>>1
23##修改表的名称
24 sheet.title = '时间参数'
25print(sheet.title) # >>>时间参数
26# 返回指定⾏指定列的单元格信息
ll(row=1, column=2).value) # >>>时间格式定义
28 cell = sheet['B1']
29print(cell) # >>><Cell '时间参数'.B1>。注意cell是对象，下⾯是具体的属性：
w, lumn, cell.value, dinate)
31# >>>1 2 时间格式定义 B1
32# sheet的属性,sheet是⼀个类：
33print("sheet:", sheet, type(sheet))
34# 4.访问单元格的所有信息,rows是sheet的⼀个属性。该sheet的所有⾏信息。
35def get_sheet_rows():
ws) ##是⼀个⽣成器
37##<generator object Worksheet._cells_by_row at 0x000001806C22D820>
38for row ws:
39# 循环遍历每⼀个单元格
40for cell in row:
41# 获取单元格的内容
42print(cell.value, end=',')
43print()
View Code
#>>>
序号，时间格式定义,
1,%a Locale’s abbreviated weekday name. ,
2,%A Locale’s full weekday name. ,
3,%b Locale’s abbreviated month name. ,
4,%B Locale’s full month name. ,……
通过以上输出，按照excel的每⾏输出内容。
#5openpyxl写⼊excel
1def save_to_excel(data, wbname, sheetname='Sheet1'):
2"""
3将以下信息保存到excel表中;
4 [[' BOOK', 50, 3], ['APPLE', 100, 1], ['BANANA', 200, 0.5]]
5"""
6print("写⼊Excel[%s]中......." % (wbname))
7# 打开excel表, 如果⽂件不存在，⾃⼰实例化⼀个WorkBook对象
8 wb = openpyxl.Workbook()
9# 获取当前⼯作表
10 sheet = wb.active
11# 修改⼯作表的名称
12 sheet.title = sheetname
13
14 data.insert(0,head_line) #重新插⼊表头。
15for row, item in enumerate(data): # 0 [' BOOK', 50, 3]
16##使⽤枚举函数的好处，不⽤求元素总数len了。
17for column, cellValue in enumerate(item): # 0 ' BOOK'
18 ll(row=row + 1, column=column + 1, value=cellValue)
19
20# ** 往单元格写⼊内容
21# ll['B1'].value = "value"
22# ll(row=1, column=2, value="value")
23
24# 保存写⼊的信息
25 wb.save(filename=wbname)
26print("写⼊成功!")
View Code
⼩结：通过对xlrd、xlswriter及openpyxl的应⽤案例，本质上都是把excel当做⼀个数据⽂件进⾏读写。只不过openpyxl既能读⼜能写罢了。⽽pandas是把excel当做数据块或者说是矩阵来处理。如同处理csv⼀样，读⼊的数据认为是dataframe，可以有更多的数据分析功能。
3.pandas对excel⽂件的处理，可以同时写⼊多个sheet数据。
1import pandas as pd
2from pandas import DataFrame
3 filename = r'C:\2020\python-exer\excel_doc\pandas_excel.xlsx'
4 write_filename = r'C:\2020\python-exer\excel_doc\pandas_excel_1.xlsx'
5 csv_file=r'C:\2020\python-exer\excel_doc\pandas_excel_1.csv'
6def pandas_write_excel():
7# 创建新的Excel⽂件。如果是已有的Excel⽂件，见下⾯的read函数。
8# 准备字典数据，之所以引⼊list_dict[]，为了实现同⼀个excel表同时写⼊多个sheet(可以是不同数据)
。
9# 字典的键会被当做列索引。⾏索引⾃动增加1个序号数字列。如果是1个嵌套列表，DF会⾃动加上序号⾏作为列的索引。也会增加序号列作为⾏的索引。
10 list_dict = []
11 dict1 = {'标题列1': ['张三', '李四'],
12'标题列2': [80, 90],
13'标题列3': [30, 40],
14'标题列4': [50, 70],
15 }
16 dict2 = {'姓名': ['张三', '李四', '王五'],
17'数学': [80, 90, 70],
18'语⽂': [30, 40, 89],
19'英语': [50, 70, 76],
20 }
21
22for i in range(5):
23 list_dict.append(dict1) # ⽣成1个⼤列表，每个元素都是1个dict。
24# df = DataFrame(dict1)
25# print(df)
26'''#>>>在原始数据前⾯加⼊序号列。
27标题列1 标题列2 标题列3 标题列4
280 张三 80 30 50
291 李四 90 40 70
30'''
31##1pandas的df就是⼀个数据矩阵，天⽣与excel同构。所以可以直接写⼊excel：
32# df.to_excel(filename, index=False) ##只写⼊1个sheet，不需要save、close。
33 writer = pd.ExcelWriter(filename)
34##2 如果需要同时写⼊多个sheet，引⼊writer。⽽#1只是写⼊⼀个sheet。
35for i in range(4):
36 df = DataFrame(list_dict[i])
37 df.to_excel(excel_writer=writer, sheet_name=f"班级{i}", index=False)
38 df = DataFrame(dict2)
39# DataFrame可以把dict转变为写⼊的格式，如果index=True增加了第⼀列序号。如果index为False写⼊的excel没有序号
40print(df)
41 df.to_excel(excel_writer=writer, sheet_name="⾼级班", index=True)
42# 按列写⼊字典，index为True，写⼊的excel有序号。
43 writer.save()
44# writer.close() #这个⽐较奇葩，打开excel根本⽆法写⼊。关闭状态下这条语句会有警告。到底是否需要关闭？
45print('写⼊成功！')
46# 写⼊单个sheet函数，当打开⼀个⽂件的时候，如果有多个sheet，⽤这个函数只保留1个sheet。
47# 如果想同时写⼊多个sheet⽤前⾯的函数。
48def write_sheet(write_filename, Sheet_data, Sheet_name):
49 with pd.ExcelWriter(write_filename) as writer:
50 df = DataFrame(Sheet_data)
51# #这⾥data是⼀个列表，⽽之前的函数是写⼊字典，所以会有问题。⽽且data并不完全是原始数据。增加了序号列。
52 df.to_excel(excel_writer=writer, sheet_name=Sheet_name, index=False)
53# index为False不写⼊序号。否则，写⼊序号。
54 writer.save()
55print('写⼊成功！')
View Code
下⾯⽐较⼀下读写excel⽂件和csv⽂件的异同：
1def write_read_csv(data):
2##csv⽂件的写⼊、读取。感觉⽐excel简单，⾄少没有多个sheet的情况。
3#⽽且，csv⽂件打开的时候也是可以进⾏写操作的。⽽Excel⽂件不可以。
4 data_df = DataFrame(data)
5# print(data_df)
6 _csv(csv_file,index=False)
7#index=False，不写序号列。
8print("写⼊csv成功")
9 ad_csv(csv_file)
10print("读取csv⽂件数据:\n",data)
11#读出的结果与上⾯读取excel⼀致，原始数据前⾯加上了序号列。
12print("读取csv成功")
View Code
⼩结：对于pandas来说，有了DataFrame，写⼊Excel和写⼊CSV可以根据需求可以同时进⾏，只是对应pandas模块不同的读写函数⽽已。
总结上述，对于python来说，处理excel⽂件有很多的⽅法，但感觉pandas⽅法是更⽅便的，更接近数据处理，有更丰富的处理技巧。⽽其他模块都是取出excel的⾏或者列数据，再依照python的语⾔功能对这些数据进⾏进⼀步处理。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论