Python&Excel办公自动化

操作工作簿

01 新建一个excel工作簿

#2023-4-17
import xlwings as xw
# 启动 excel，但不新建工作簿  app是什么，app是excel程序本身
app = xw.App(visible=True,add_book=True)

#新建一个工作簿
workbook=app.books.add()

# 保存新建的工作簿
workbook.save('D:\python_data/test.xlsx')
workbook.close()

#关闭excel软件
app.quit()

02 批量新建多个工作簿

#2023-4-17
import xlwings as xw
# 启动 excel，但不新建工作簿  app是什么，app是excel程序本身
app = xw.App(visible=False,add_book=False)  #后台运行

for i in range(1,13): #左闭右开
    workbook=app.books.add()
    workbook.save(f'D:\\python_data\\excelbook\\{i}月报表.xlsx')
    workbook.close()


#关闭excel软件
app.quit()

03 打开已有的工作簿

import xlwings as xw
app = xw.App(visible=True,add_book=False)
app.books.open(f'D:\\python_data\\发票信息.xlsx')

04 打开文件夹下的所有工作簿

import xlwings as xw
from pathlib import Path
#打开excel软件
app=xwapp = xw.App(visible=True,add_book=False)
#需要一个excel文件的list
folder_path=Path('C:\\Users\\hank-02\\Desktop\\新建文件夹 (2)')  #这里用了相对路径，相对路径要双斜线\\
excel_file_list=folder_path.glob('*.xls*')  #  判断文件夹下文件的扩展名是否为xls，或者xlsx

for file in excel_file_list:
    app.books.open(file)

05 重命名多个工作簿

from pathlib import Path

#第一步获取符合条件的文件列表
file_path=Path('D:\\python_data\\excelbook')  #待重命名工作簿所在的文件夹
file_list=file_path.glob('*月.xlsx') #查找文件夹下所有与 月结尾的工作簿匹配的文件

#遍历上述列表，循环重命名文件

for old_file in file_list:
    old_file_name=old_file.name  #获取当前文件名
    new_file_name=old_file_name.replace('月','月报表') #通过替换字符串方法生成新的文件名
    new_file=old_file.with_name(new_file_name) #通过with_name 生成一个新的文件对象
    old_file.rename(new_file) #通过rename方法重命名文件

06 批量修改工作簿格式

#批量转换工作簿的格式，将所有的xlsx格式文件，另存为xls格式的文件
from pathlib import Path

import xlwings as xw
app = xw.App(visible=False,add_book=False)

folder_path=Path('D:\\python_data\\excelbook') #  给出工作簿所在的文件夹路径

file_list=folder_path.glob('*.xlsx')   #查找文件夹下所有xlsx的工作簿文件


for file in file_list:
    
    if '$' in file.name:   #如果文件包含临时文件，则跳过 
        continue;
        
    print('开始转换：'+file.name)
    new_file_path=str(file.with_suffix('.xls'))
    workbook=app.books.open(file)
    workbook.api.SaveAs(new_file_path,FileFormat=56)
    workbook.close()
    print('完成转换：'+ new_file_path)

#关闭excel软件
app.quit()

07 拆分一个工作簿变成多个新工作簿

#拆分一个工作簿变成多个新工作簿
from pathlib import Path

import xlwings as xw
app = xw.App(visible=False,add_book=False)

source_workbook=app.books.open('D:\\python_data\\excelbook\\拆分工作簿.xlsx')#打开需要拆分的工作簿

source_worksheets=source_workbook.sheets #获取拆分工作簿里面是所有sheet


for worksheet in source_worksheets:
    #1 新建一个目标工作簿workbook
    new_workbook=app.books.add()
    #2 创建第一个sheet,  选择目标工作簿中的第一表
    new_worksheet=new_workbook.sheets[0]
    #3 复制sheet内容到新sheet中
    worksheet.copy(before=new_worksheet)
    #4 保存并关闭
    new_workbook.save(f'D:\\python_data\\excelbook\\拆分后的\\{worksheet.name}.xlsx')
    new_workbook.close()

    
source_workbook.close()

#关闭excel软件
app.quit()

08 将多个工作簿合并为一个工作簿

from pathlib import Path
import pandas as pd

file_list =Path('C:\\Users\\hank-02\\Desktop\\测试数据').glob('*.xlsx')
frozen_file_list=[]
for i in file_list:
    frozen_file_list.append(i)

with pd.ExcelWriter('C:\\Users\\hank-02\\Desktop\\测试数据\\总表.xlsx') as workbook:
    for file in frozen_file_list:
        #读取单个文件，只读了数据本身，没有把格式一并读出来file.stem
        data=pd.read_excel(file,sheet_name=0)
        data.to_excel(workbook,sheet_name=file.stem,index=False)

09 按照扩展名分类工作簿

from pathlib import Path
folder_path=Path('C:\\Users\\hank-02\\Desktop\\测试数据')  #数据源
file_list=folder_path.glob('*.xls*')
#1、遍历文件列表
for i in file_list:
    suffix_name=i.suffix
    new_folder_path=Path('C:\\Users\\hank-02\\Desktop\\测试数据\\'+suffix_name.replace('.',''))#测试数据后面要加个\\,代表是在这个文件夹下进行分类
#2、判断当前文件扩展名，查看扩展名目录是否存在，不存在的话就创建一个新的目录
    if not new_folder_path.exists():
        new_folder_path.mkdir()
    
#3、移动文件
    i.replace(new_folder_path/i.name)

10 按照日期分类工作簿

from pathlib import Path
from time import localtime

folder_path=Path('C:\\Users\\hank-02\\Desktop\\测试数据')
file_list=folder_path.glob('*.xls*')

#1、遍历文件列表
for i in file_list:
    #2、根据文件最好修改日期的年份和月份，组成目录
    lm_date=i.stat().st_mtime
    year=localtime(lm_date).tm_year
    month=localtime(lm_date).tm_mon
    new_folder_path=folder_path/str(year)/str(month)
    if not new_folder_path.exists():
        new_folder_path.mkdir(parents=True)
    i.replace(new_folder_path/i.name)

11 精确查找工作簿

from pathlib import Path
folder_path_str = input('请输入查找的路径：')
file_name_str= input('请输入要查找的工作簿名称:')
folder_path = Path(folder_path_str)
file_list = folder_path.rglob(file_name_str) # rglob 用于指定文件夹及其子文件夹中查找名称符合指定规则的文件或文件
for i in file_list:
    print(i)

12 按关键字查找工作簿

from pathlib import Path
folder_path_str = input('请输入查找的路径：')
key_word= input('请输入关键字:')
folder_path = Path(folder_path_str)
file_list = folder_path.rglob(f'*{key_word}*.xls*')  #rglob 用于指定文件夹及其子文件夹中查找名称符合指定规则的文件或文件
for i in file_list:
    print(i)

13 保护一个工作簿的结构

import xlwings as xw
app=xw.App(visible=False,add_book=False)
workbook=app.books.open('C:\\Users\\hank-02\\Desktop\\工作\\交接文档\\南山固定资产.xlsx')

workbook.api.Protect(Password='123',Structure=True,Windows=False)
workbook.save()
workbook.close()
app.quit()

14 加密保护一个工作簿

import xlwings as xw
app=xw.App(visible=False,add_book=False)
workbook=app.books.open('C:\\Users\\hank-02\\Desktop\\工作\\交接文档\\南山固定资产.xlsx')

workbook.api.Password='123'
workbook.save()
workbook.close()
app.quit()

15 加密保护多个工作簿

from pathlib import Path
import xlwings as xw
app=xw.App(visible=False,add_book=False)
folder_path=Path('C:\\Users\\hank-02\\Desktop\\测试数据\\2023\\5')
file_list=folder_path.glob('*.xls*')

for i in file_list:
    workbook=app.books.open(i)
    workbook.api.Password='123'
    workbook.save()
    workbook.close()

app.quit()

操作工作表

16 提取一个工作簿中所有工作表的名称

#提取一个工作簿中所有工作表的名称（方法一：）
import xlwings as xw
app=xw.App(visible=False,add_book=False)
workbook=app.books.open('C:\\Users\\hank-02\\Desktop\\2023-3-31南山固定资产（重要）.xlsx')
worksheets=workbook.sheets
sheet_name_list=[]
for i in worksheets:
    sheet_name_list.append(i.name)

workbook.close()
app.quit()
print(sheet_name_list)



#提取一个工作簿中所有工作表的名称（方法二：）
import pandas as pd

file_path='C:\\Users\\hank-02\\Desktop\\2023-3-31南山固定资产（重要）.xlsx'

data=pd.read_excel(file_path,sheet_name=None)

#print(type(data))#data是字典类型

worksheet_name=list(data.keys())
print(worksheet_name)

17 在一个工作薄中新增一个工作表

#在一个工作薄中新增一个工作表
import xlwings as xw
app=xw.App(visible=False,add_book=False)
workbook=app.books.open('C:\\Users\\hank-02\\Desktop\\2023-3-31南山固定资产（重要）.xlsx')
worksheets=workbook.sheets
new_sheet_name='测试'

sheet_name_list=[]
for i in worksheets:
    sheet_name_list.append(i.name)
if new_sheet_name not in sheet_name_list:
    worksheets.add(name=new_sheet_name)

workbook.save()
workbook.close()
app.quit()

18 在一个工作簿中删除一个工作表

#在一个工作簿中删除一个工作表
import xlwings as xw
app=xw.App(visible=False,add_book=False)
workbook=app.books.open('C:\\Users\\hank-02\\Desktop\\2023-3-31南山固定资产（重要）.xlsx')
worksheets=workbook.sheets
del_sheet_name='测试'

for i in worksheets:
    if i.name==del_sheet_name:
        i.delete()
        break

workbook.save()
workbook.close()
app.quit()

19 在多个工作簿中批量新增工作表

#在多个工作簿中批量新增工作表
import xlwings as xw
from pathlib import Path

#1、获取文件列表

file_list=Path('C:\\Users\\hank-02\\Desktop\\新建文件夹2').glob('*.xls*')

new_sheet_name='测试表'

app=xw.App(visible=False,add_book=False)

for file in file_list:
    workbook=app.books.open(file)
    worksheets=workbook.sheets
    sheet_name_list=[]
    for i in worksheets:
        sheet_name_list.append(i.name)
    if new_sheet_name not in sheet_name_list:
        worksheets.add(name=new_sheet_name)

    workbook.save()
    workbook.close()
    
app.quit()

 20 在多个工作簿中批量删除工作表

#在多个工作簿中批量删除工作表
import xlwings as xw
from pathlib import Path

#1、获取文件列表

file_list=Path('C:\\Users\\hank-02\\Desktop\\新建文件夹2').glob('*.xls*')

del_sheet_name='测试表'

app=xw.App(visible=False,add_book=False)

for file in file_list:
    workbook=app.books.open(file)
    worksheets=workbook.sheets
    
    for i in worksheets:
        if i.name==del_sheet_name:
            i.delete()
            break

    workbook.save()
    workbook.close()
    
app.quit()

行/列操作