基础操作

1.创建Excel

创建Excel步骤

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))

输出

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')

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')

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

image.png

test4

image.png
test5
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)

image.png

notice:

df_subs列表的元素数据结构

image.png

2.批量合并Excel文件

合并步骤

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 库，它被广泛用于快速分析数据，以及数据清洗和准备等工作