首页 > 编程语言 > python

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

admin python 2021-05-25 09:22:45 文件 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

后台-系统设置-扩展变量-手机广告位-内容正文底部
版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
本文地址:https://www.jcdi.cn/pythonjc/30665.html

留言与评论(共有 0 条评论)
   
验证码:
后台-系统设置-扩展变量-手机广告位-评论底部广告位

教程弟

https://www.jcdi.cn/

统计代码 | 京ICP1234567-2号

Powered By 教程弟 教程弟

使用手机软件扫描微信二维码