编程语言python对excel文件的处理

python处理excel文件有很多方法，最开始接触的是xlrd、xlsxwriter模块，分别用于excel文件的读、写。后来又学习了openpyxl模块，可以同时完成excel文件的读、写。再后来，接触了大牛pandas，这是python中专门用于数据分析的模块，有更加强大的功能。

本文尝试梳理一下这几个方法，以实际案例来对比各种方法的优劣。

1.xlrd、xlsxwriter模块

 1 import xlrd  #读取excel文件
 2 import xlsxwriter　　#写入excel文件
 3 file_name = r'C:/2020/python-exer/excel_doc/time_fmt.xls'　　#存在一个excel文件，用于读
 4 file_name1 = r'C:/2020/python-exer/excel_doc/time_fmt_output.xls'　　#新建一个excel文件，用于写
 5 # 读取excel文件，按行读取数据，每行数据对应一个列表元素
 6 def 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 = sheet1.nrows  # sheet1数据最大行数,即便每列元素不同。
12    print(Max_lines)
13    for a in range(Max_lines):
14       cells = sheet1.row_values(a)  # 每行数据赋值给cells
15       dat.append(cells)
16    return dat

View Code

#>>>[['序号', '时间格式定义'], [1.0, '%a  Locale’s abbreviated weekday name.  '], 
[2.0, '%A  Locale’s full weekday name.  '], 
……
从输出内容看出，得到的是一个嵌套list，每行数据对应着一个list元素。

# 读取excel文件，按列读取数据，每列数据对应一个列表元素

 1 def 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(sheet_2.col_values(0))
12 
13    dat = []  # 创建空list
14    global Max_rows
15    Max_cols = sheet1.ncols  # sheet1数据最大列数
16    Max_rows = sheet1.nrows  # sheet1数据最大行数
17    print("Max_rows:", Max_rows)
18    print("Max_cols:", Max_cols)
19    for a in range(Max_cols):
20       cells = sheet1.col_values(a)  # 每列数据赋值给cells
21       dat.append(cells)  # 每列数据追加到列表dat,那么dat就是以列数据为元素的列表
22    return 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用于保存数据

 1 def write_excel():
 2    a = excel_cols()
 3    excel_cols = []  # 保存列值的列表
 4    excel_cols_comment = []  # 保存列注释的列表
 5 
 6    for i in range(Max_rows):
 7       if i == 0:
 8          # square_col.append(a[1][0])      #专门取列的头，实际上为了方便以后的数据处理。
 9          print("列的名字：", a[1][0])
10       else:
11          excel_cols.append(a[1][i][:3])  #:2只取该元素的前2位，去掉后面的注释。
12          excel_cols_comment.append(a[1][i][5:])  # 上面列的注释，取字符串5:
13 
14    print("获取格式化time参数： \n", excel_cols)
15    print("获取time参数的说明： \n", excel_cols_comment)
16 
17    workbook_w = xlsxwriter.Workbook(file_name1)
18    sheet2 = workbook_w.add_worksheet("output_sheet")
19    for i in range(Max_rows):  # 因列名单独处理了，所以真正的列元素数要比总数-1
20       # strf_time = time.strftime(excel_cols[i])  # 调用时间模块函数,参数为每列的值
21       # comment = excel_cols_comment[i]
22       if i == 0:  # 每个列的第一行，列名。i代表行号，如果是很多列，也可以再增加j循环，表示列号
23          sheet2.write(i, 0, f"格式化时间参数：time.strftime")
24          sheet2.write(i, 1, f"执行结果") 
25          sheet2.write(i, 2, f"注释")
26       else:  # 每个列，从第二行开始循环写入
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}")
35    print("写入成功")
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：获取文档的元数据，如标题，创建者，创建日期等

 1 def get_properties():　　##获取excel的sheet属性函数
 2    print(wb.sheetnames)  # >>>['Sheet1', '2表单12']
 3    print(wb.active)  # >>><Worksheet "2表单12">
 4    print(wb.worksheets)  # >>>[<Worksheet "Sheet1">, <Worksheet "2表单12">]
 5    print(wb.read_only)  # >>>False
 6    print(wb.encoding)  # >>>utf-8
 7    print(wb.properties)  # 获取文档的元数据，如标题，创建者，创建日期等
 8    print(wb.properties.creator, wb.properties.title)  # >>>openpyxl None
 9    wb.properties.title = 'test-openpyxl'  # >>>修改属性中的title
10    print(wb.properties.title)
11    print(wb.properties)  # 确实修改了titile。
12 # 3.Worksheet，Cell对象（工作表操作，单元格）。获取execl的sheet一般信息的函数
13 def get_sheet_info():　　
14    global sheet
15    sheet = wb['Sheet1']
16    # 获取工作表的名称
17    print(sheet.title)  # >>>Sheet1
18    # 获取工作表中行和列的最值
19    print(sheet.max_column)  # >>>2
20    print(sheet.max_row)  # >>>27
21    print(sheet.min_column)  # >>>1
22    print(sheet.min_row)  # >>>1
23    ##修改表的名称
24    sheet.title = '时间参数'
25    print(sheet.title)  # >>>时间参数
26    # 返回指定行指定列的单元格信息
27    print(sheet.cell(row=1, column=2).value)  # >>>时间格式定义
28    cell = sheet['B1']
29    print(cell)  # >>><Cell '时间参数'.B1>。注意cell是对象，下面是具体的属性：
30    print(cell.row, cell.column, cell.value, cell.coordinate)
31    # >>>1 2 时间格式定义 B1
32    # sheet的属性,sheet是一个类：
33    print("sheet:", sheet, type(sheet))
34 # 4.访问单元格的所有信息,rows是sheet的一个属性。该sheet的所有行信息。
35 def get_sheet_rows():
36    print(sheet.rows)  ##是一个生成器
37    ##<generator object Worksheet._cells_by_row at 0x000001806C22D820>
38    for row in sheet.rows:
39       # 循环遍历每一个单元格
40       for cell in row:
41          # 获取单元格的内容
42          print(cell.value, end=',')
43       print()

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

 1 def save_to_excel(data, wbname, sheetname='Sheet1'):
 2    """
 3    将以下信息保存到excel表中;
 4    [[' BOOK', 50, 3], ['APPLE', 100, 1], ['BANANA', 200, 0.5]]
 5    """
 6    print("写入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)        #重新插入表头。
15    for row, item in enumerate(data):  # 0 [' BOOK', 50, 3]
16       ##使用枚举函数的好处，不用求元素总数len了。
17       for column, cellValue in enumerate(item):  # 0 ' BOOK'
18          sheet.cell(row=row + 1, column=column + 1, value=cellValue)
19 
20    # ** 往单元格写入内容
21    # sheet.cell['B1'].value = "value"
22    # sheet.cell(row=1, column=2, value="value")
23 
24    # 保存写入的信息
25    wb.save(filename=wbname)
26    print("写入成功!")

View Code

小结：通过对xlrd、xlswriter及openpyxl的应用案例，本质上都是把excel当做一个数据文件进行读写。只不过openpyxl既能读又能写罢了。而pandas是把excel当做
数据块或者说是矩阵来处理。如同处理csv一样，读入的数据认为是dataframe，可以有更多的数据分析功能。

3.pandas对excel文件的处理，可以同时写入多个sheet数据。

 1 import pandas as pd
 2 from 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'
 6 def 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 
22    for 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
28 0   张三    80        30    50
29 1   李四    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。
35    for 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没有序号
40    print(df)
41    df.to_excel(excel_writer=writer, sheet_name="高级班", index=True)
42    # 按列写入字典，index为True，写入的excel有序号。
43    writer.save()
44    # writer.close()      #这个比较奇葩，打开excel根本无法写入。关闭状态下这条语句会有警告。到底是否需要关闭？
45    print('写入成功！')
46 # 写入单个sheet函数，当打开一个文件的时候，如果有多个sheet，用这个函数只保留1个sheet。
47 # 如果想同时写入多个sheet用前面的函数。
48 def 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()
55       print('写入成功！')

View Code

下面比较一下读写excel文件和csv文件的异同：

 1 def write_read_csv(data):
 2    ##csv文件的写入、读取。感觉比excel简单，至少没有多个sheet的情况。
 3    #而且，csv文件打开的时候也是可以进行写操作的。而Excel文件不可以。
 4    data_df = DataFrame(data)
 5    # print(data_df)
 6    data_df.to_csv(csv_file,index=False)
 7    #index=False,不写序号列。
 8    print("写入csv成功")
 9    data=pd.read_csv(csv_file)
10    print("读取csv文件数据:\n",data)
11    #读出的结果与上面读取excel一致，原始数据前面加上了序号列。
12    print("读取csv成功")

View Code

小结：对于pandas来说，有了DataFrame，写入Excel和写入CSV可以根据需求可以同时进行，只是对应pandas模块不同的读写函数而已。

总结上述，对于python来说，处理excel文件有很多的方法，但感觉pandas方法是更方便的，更接近数据处理，有更丰富的处理技巧。而其他模块都是取出excel的行或者列数据，
再依照python的语言功能对这些数据进行进一步处理。

文章来源：https://www.cnblogs.com/dingzy1972/p/14802068.html