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方法重命名文件

#批量轉換工作簿的格式，將所有的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()

#拆分一個工作簿變成多個新工作簿
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()

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)

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)

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

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

#在多個工作簿中批量刪除工作表
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()

行/列操作