python-Excel自动化

分手后的思念是犯贱 2021-09-18 04:02 387阅读 0赞

# 基础操作 #

## 1.创建Excel ##

### 创建Excel步骤 ###

![18721752-da366153c46f6568.png][]

image.png

### code ###

#!/usr/bin/env python
    # _*_ coding: utf-8 _*_
    # @Time : 2021/2/13 16:53
    # @Author : pl
    # @Version：V 0.1
    # @File : Excel自动化基础操作.py
    # @desc :
    import xlwt
    
    # xlwt.Workbook() 先实例化一个操作excel的对象
    wb = xlwt.Workbook()
    # 添加sheet
    sh1 = wb.add_sheet('成绩')
    # 写入操作  write(行，列，数据)
    sh1.write(0, 0, '姓名')
    sh1.write(0, 1, '成绩')
    sh1.write(1, 0, '张三')
    sh1.write(1, 1, 88)
    sh1.write(2, 0, '李四')
    sh1.write(2, 1, 99)
    #保存
    wb.save('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\test.xls')

## 2.读取Excel数据 ##

## 第三方模块 ##

xlrd

## code ##

import xlrd
    """
    读取excel文件
    """
    wb1 = xlrd.open_workbook('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\test.xls')
    print(wb1.nsheets)
    print(wb1.sheet_names())
    #读取每个sheet中每行的数据    
    for sh in wb1.sheets():
        for r in range(sh.nrows):
            print(sh.row(r))

输出

![18721752-fef56881b1608b83.png][]

image.png

## 3.copy Excel ##

### 第三方模块 ###

xlutils

### code ###

#只引入copy函数即可不用所有函数都引入
    from xlutils.copy import copy
    
    """
    复制
    """
    readData = xlrd.open_workbook('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\test.xls')
    wb2 = copy(readData)
    sh1 = wb2.get_sheet(0)
    sh1.write(3, 0, '王二')
    sh1.write(3, 1, 99)
    wb2.save('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\test2.xls')

![18721752-86621e88c236bc67.png][]

image.png

## 4.数据格式化 ##

### 第三方模块及函数 ###

xlwt.easyxf()

### code ###

"""
    格式化
    """
    # font表示字体  字体加粗 bold on
    styleBR = xlwt.easyxf('font:name Times New Roman, color-index red,bold on')
    styleDate = xlwt.easyxf(num_format_str='YYYY-MM-DD')
    wb = xlwt.Workbook()
    sh1 = wb.add_sheet('成绩')
    sh1.write(0, 0, '姓名', styleBR)
    sh1.write(0, 1, '日期', styleBR)
    sh1.write(0, 2, '成绩', styleBR)
    
    sh1.write(1, 0, '张三')
    sh1.write(1, 1, '2021-02-13', styleDate)
    sh1.write(1, 2, 99)
    sh1.write(2, 0, '李四')
    sh1.write(2, 1, '2021-02-13', styleDate)
    sh1.write(2, 2, 100)
    wb.save('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\test3.xls')

![18721752-d6086667f3c29818.png][]

image.png

## 5.Vlookup ##

Excel中通过Vlookup函数实现在正确的范围内找到响应的数据匹配值  
而在python中匹配更加简单

### 第三方模块 ###

pandas

### code ###

import pandas as pd
    """
     VlookUp  将两个excel关联，存储到一个新的excel中
    """
    df1 = pd.DataFrame(pd.read_excel('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\test3.xls'))
    df2 = pd.DataFrame(pd.read_excel('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\test4.xls'))
    #左关联    
    result = pd.merge(df1, df2.loc[:, ['学号', '班级']], how='left', on='学号')
    writer = pd.ExcelWriter('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\test5.xls')
    result.to_excel(writer, index=False)
    writer.save()

test3

![18721752-e2d6dcaf0c295b95.png][]

image.png

test4

![18721752-620d93e681850f4b.png][]

image.png

test5

![18721752-708437447b8b5839.png][]

image.png

# 进阶操作 #

## 1. 拆分 ##

### 拆分步骤 ###

*  读取文件
 *  拆分数据
 *  生成不同的Excel表

## code ##

#!/usr/bin/env python
    # _*_ coding: utf-8 _*_
    # @Time : 2021/2/13 22:01
    # @Author : pl
    # @Version：V 0.1
    # @File : Excel进阶操作.py
    # @desc :
    # font表示字体  字体加粗 bold on
    import xlwt
    import pandas as pd
    
    styleBR = xlwt.easyxf('font:name Times New Roman, color-index red,bold on')
    styleDate = xlwt.easyxf(num_format_str='YYYY-MM-DD')
    wb = xlwt.Workbook()
    sh1 = wb.add_sheet('成绩')
    
    sh1.write(0, 0, '学号', styleBR)
    sh1.write(0, 1, '姓名', styleBR)
    sh1.write(0, 2, '日期', styleBR)
    sh1.write(0, 3, '成绩', styleBR)
    
    for index in range(1,100):
        sh1.write(index, 0, index)
        sh1.write(index, 1, '张%d' % index)
        sh1.write(index, 2, '2021-02-13', styleDate)
        sh1.write(index, 3, index+10)
    
    wb.save('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\test6.xls')
    # 将excel转换成数组
    df_source = pd.read_excel('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\test6.xls')
    # 获取数组前5个数据
    print(df_source.head())
    # 获取几行几列  输出为元组（，）
    print(df_source.shape)
    total_row_count = df_source.shape[0]
    user_names = ['MIKE', 'JANE', 'BOB', 'KEVIN', 'MAY', 'JAMES']
    split_size = total_row_count//len(user_names)
    # 存储拆分后的数据
    df_subs = []
    # enumerate() 函数用于将一个可遍历的数据对象(如列表、元组或字符串)组合为一个索引序列，同时列出数据和数据下标，一般用在 for 循环当中。  所以才有index
    for index, user_name in enumerate(user_names):
        begin = index*split_size
        end = begin+split_size
        # 数组切割
        df_sub = df_source.iloc[begin:end]
        # 一个有索引的列表 元素为 对象数据
        df_subs.append((index, user_name, df_sub))
    # 变量 对应此时 元素的数据结构 索引，名字，数据
    for idx, user_name, df_sub in df_subs:
        file_name = f'D:\\Study\\python\\B站\\自动化excel\\testData\\file\\{idx}_{user_name}.xls'
        df_sub.to_excel(file_name, index=False)

![18721752-3430d7997efbe1b4.png][]

image.png

### notice: ###

df\_subs列表的元素数据结构

![18721752-5ee5e5892d896865.png][]

image.png

## 2.批量合并Excel文件 ##

### 合并步骤 ###

![18721752-75ad230644ab1e45.png][]

image.png

### code ###

#!/usr/bin/env python
    # _*_ coding: utf-8 _*_
    # @Time : 2021/2/13 22:01
    # @Author : pl
    # @Version：V 0.1
    # @File : Excel进阶操作.py
    # @desc :
    
    import os
    import pandas as pd
    # 存储待合并的文件
    excel_names = []
    for excel_name in os.listdir('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\merge'):
        excel_names.append(excel_name)
    print(excel_names)
    # 存储待合并文件的数据列表
    df_list = []
    for excel_name in excel_names:
        excel_path = f'D:\\Study\\python\\B站\\自动化excel\\testData\\file\\merge\\{excel_name}'
        df_split = pd.read_excel(excel_path)
        df_list.append(df_split)
    #数组合并
    df_merge = pd.concat(df_list)
    df_merge.to_excel('D:\\Study\\python\\B站\\自动化excel\\testData\\file\\merge\\new.xls')

### notice: ###

Pandas 是基于 NumPy 的一个开源 Python 库，它被广泛用于快速分析数据，以及数据清洗和准备等工作

[18721752-da366153c46f6568.png]: /images/20210918/60950d219bb44f6bad07b9d3faef4500.png
[18721752-fef56881b1608b83.png]: /images/20210918/f4977ed539674169a9b7aae399e0c6c9.png
[18721752-86621e88c236bc67.png]: /images/20210918/f83c9775877c44349e59e01c5d1a46ad.png
[18721752-d6086667f3c29818.png]: /images/20210918/67d6b4748f704103839c6aa995723cd9.png
[18721752-e2d6dcaf0c295b95.png]: /images/20210918/b23fb0bf1d8049e5b16a1851b24933b8.png
[18721752-620d93e681850f4b.png]: /images/20210918/a5c85de90d52491fb13b496a28f15484.png
[18721752-708437447b8b5839.png]: /images/20210918/32ab8a8f08da41cbbeed494943f3c1a1.png
[18721752-3430d7997efbe1b4.png]: /images/20210918/0c90d6fd13cf421d92f413978f6ab0ac.png
[18721752-5ee5e5892d896865.png]: /images/20210918/ac3b0f1d5a964a1ea4951f312017189c.png
[18721752-75ad230644ab1e45.png]: /images/20210918/5f5f482a2e234d0c89b8c994002c6bea.png