使用python操作格式固定的excel文件进行汇总

Python 操作excel文件学习笔记

• 操作excel文件可能用到的库

1. xlrd (对excel文件内容读取)
2. xlwt (对excel文件的创建写入)
3. xlsxwriter（xlwt写入256列后无法继续写入，使用xlsxwriter库）
4. openpyxl （读写excel文件）

1.要求

1. 从指定的文件夹中读取全部Excel文件
2. 逐一打开Excel文件，读取数据
3. 将数据写入新的Excel文件中
4. 注意：Excel文件格式是固定的；读取每一个文件的数据后，写入新的Excel文件，成为一行记录

2.openpyxl简介

• openpyxl模块是一个读写Excel 2010文档的Python库，如果要处理更早格式的Excel文档，需要用到其它库，这是openpyxl比较其他模块的不足之处
• openpyxl模块不仅能够同时读取和修改Excel文档，而且可以对Excel文件内单元格进行详细设置，包括单元格样式等内容，甚至还支持图表插入、打印设置等内容，且可以处理数据量较大的Excel文件
• 这里只是提及一下openpyxl库，很方便，之后再做excel文件处理可以优先考虑

3.需要掌握的方法

• os.walk()方法

1. 是一个简单易用的文件、目录遍历器，可以帮助我们高效的处理文件、目录方面的事情。
2. 用于通过在目录树中游走输出在目录中的文件名，向上或者向下。

    for root, dirs, files in os.walk(file_dir):#os.walk()方法只能传入绝对路径的文件夹名
        print(root)  # 当前目录路径
        print(dirs)  # 文件夹中所有的目录的名字
        print(files)  # 当前路径下所有文件的文件名
        return files

• xlwt库创建文件、sheet表以及保存文件

1. 在add_sheet时, 参数cell_overwrite_ok=True, 可以覆盖原单元格中数据。
2. 在Python的string前面加上‘r’， 是为了告诉编译器这个string是个raw string，不要转意 ‘’ 。当一个字符串使用了正则表达式后，最好在前面加上’r’。
3. 使用xlwt库时要注意，如果你输入的列数大于256行则报错，你需要使用xlsxwriter库。

workbook = xlwt.Workbook()  # 创建文件，
sheet = workbook.add_sheet('sheet1', cell_overwrite_ok=True) 
workbook.save(r'director\汇总1.xls')  # 保存文件

• xlsxwriter库创建文件、sheet表以及保存文件

1. 创建.xlsx文件时，一定要加文件名参数
2. 保存文件使用.close()方法，没有的话报错

work_book1 = xlsxwriter.Workbook("汇总1.xlsx")
sheet1 = work_book1.add_worksheet("sheet1")
work_book1.close()

• 写入excel表格第一行

1. Python的len()方法返回对象（字符、列表、元组等）长度或项目个数
2. write()方法的三个参数：行、列、值（xlwt库还有第四个参数style）

row = [.....]
for k in range(len(row)):
    sheet.write(0, k, row[k])

• 设置一列的宽度

1. set_column(first_col, last_col, width=None) 常用的三个参数：起始列、终止列、宽度。

sheet1.set_column(0,0,30)

• 使用xlrd读取excel表的数据

1. 用open_workbook(‘excel文件的路径’)方法打开文件
2. f’‘格式化字符串：f’E:/PycharmProjects/untitled/director/{i}’
3. format()格式化字符串：‘E:/PycharmProjects/untitled/director/{}’.format(i)
4. re.search(pattern, string, flags=0)扫描整个字符串，并返回第一个成功的匹配。如果匹配失败，则返回None
5. 内置函数filter，用于过滤序列，过滤掉不符合条件的元素，可以从字符串中提取出数字，需要注意的是遇到float类型会把小数点提取掉，需要使用正则表达式
6. str.index(str, beg=0, end=len(string))，使用索引index()时，若列表里是相同的数据，则返回的结果是相同数据第一个的索引位置

directory = file_name("director")  # 获取文件

for i in directory:
    try:
        data = xlrd.open_workbook(f'E:/PycharmProjects/untitled/director/{i}')  # 打开xls文件
        table = data.sheets()[0]  # 打开第一张表
        # 获取需要信息
        info = [.....]
        # 将数据修改为实际需要的数据
        new_info = []
        # 提取出的数据在类型上的处理
        for d in info:
            if d == "√":
                d = "1"
            if d == "□":
                d = "0"
            if "A" in str(d):
                d = "A"
            if bool(re.search(r'\d', str(d))):
                d = ''.join(list(filter(str.isdigit, str(d))))# isdigit() 方法检测字符串是否只由数字组成
            new_info.append(d)
        # 将信息逐一写入
        for j in range(len(new_info)):
            if new_infos[n].isdigit():
                new_infos[n] = int(new_infos[n])
            sheet.write(directory.index(i) + 1, j + 1, new_info[j], style)
        sheet.write(directory.index(i) + 1, 0, i, style)  # 写入文件名
    except:
        pass

4.容错处理

• 因为excel表格中的数据有时会记录的不同，或者格式有错误，需要加入容错处理
• 每一个for循环后的变量保持不同，防止相同变量在循环完改变
• 熟练使用type()、bool()方法

        for h in range(7,17):
            if table.cell_value(h,6) in ["张","人","万元","台/件","种/类"]:
                if table.cell_value(h, 5) == "":
                    infos.append(0)
                else:
                    if type(table.cell_value(h, 5)) == float:
                        infos.append(int(table.cell_value(h, 5)))
                    else:
                        infos.append(table.cell_value(h, 5))
            else:
                if type(table.cell_value(h, 6)) == float:
                    infos.append(int(table.cell_value(h, 6)))
                else:
                    infos.append(table.cell_value(h, 6))