python自动合并excel_python-Excel自动化

基础操作

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