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