字符串拼接方法
pathlib官方文檔
from pathlib2 import Path # 獲取當前目錄 current_path = Path.cwd() print(current_path) # 輸出如下： # /Users/Anders/Documents/ # 獲取Home目錄 home_path = Path.home() print(home_path) # 輸出如下： # /Users/Anders
from pathlib2 import Path # 獲取當前目錄 current_path = Path.cwd() # 獲取上級父目錄 print(current_path.parent) # 獲取上上級父目錄 print(current_path.parent.parent) # 獲取上上上級父目錄 print(current_path.parent.parent.parent) # 獲取上上上上級父目錄 print(current_path.parent.parent.parent.parent) # 獲取上上上上級父目錄 print(current_path.parent.parent.parent.parent.parent) # 輸出如下： # /Users/Anders/Documents/Jupyter # /Users/Anders/Documents # /Users/Anders # /Users # /
# 獲取當前目錄 from pathlib2 import Path current_path = Path.cwd() for p in current_path.parents: print(p) # 輸出如下： # /Users/Anders/Documents/Jupyter # /Users/Anders/Documents # /Users/Anders # /Users # /
:::info
name 文件名
suffix 文件的擴展名
suffixes 返回多個擴展名列表
stem 文件的主名（不包含擴展名）
with_name(name) 替換 文件名並返回一個新的路徑
with_suffix(suffix) 替換擴展名，返回新的路徑，擴展名存在則不變
:::
from pathlib2 import Path
# 直接傳進一個完整字符串
example_path1 = Path('/Users/Anders/Documents/powershell-2.jpg')
# 也可以傳進多個字符串
example_path2 = Path('/', 'Users', 'dongh', 'Documents', 'python_learn', 'file1.txt')
# 也可以利用Path.joinpath()
example_path3 = Path('/Users/Anders/Documents/').joinpath('python_learn')
# 利用 / 可以創建子路徑
example_path4 = Path('/Users/Anders/Documents')
example_path5 = example_path4 / 'python_learn/pic-2.jpg'from pathlib2 import Path
# 返回目錄中最後一個部分的擴展名
example_path = Path('/Users/Anders/Documents')
[path for path in example_path.iterdir()]
# 輸出如下：
# [PosixPath('/Users/Anders/Documents/abc.jpg'),
# PosixPath('/Users/Anders/Documents/book-master'),
# PosixPath('/Users/Anders/Documents/Database'),
# PosixPath('/Users/Anders/Documents/Git'),
# PosixPath('/Users/Anders/Documents/AppProjects')]操作語法是： open(mode=‘r’, bufferiong=-1, encoding=None, errors=None, newline=None)
from pathlib2 import Path
example_path = Path('/Users/Anders/Documents/information/JH.txt')
with example_path.open(encoding = 'GB2312') as f:
print(f.read())
# or
example_path = Path('/Users/Anders/Documents/information/JH.txt')
example_path.read_text(encoding='GB2312')對於簡單的文件讀寫，在pathlib庫中有幾個簡便的方法：
.read_text(): 以文本模式打開路徑並並以字符串形式返回內容。
.read_bytes(): 以二進制/字節模式打開路徑並以字節串的形式返回內容。
.write_text(): 打開路徑並向其寫入字符串數據。
.write_bytes(): 以二進制/字節模式打開路徑並向其寫入數據。
關於這裡的創建文件目錄mkdir方法接收兩個參數：
from pathlib2 import Path
example_path = Path('/Users/Anders/Documents/test1/test2/test3')
# 創建文件目錄，在這個例子中因為本身不存在test1,test2,test3，由於parents為True，所以都會被創建出來。
example_path.mkdir(parents = True, exist_ok = True)
# 刪除路徑對象目錄，如果要刪除的文件夾內包含文件就會報錯
example_path.rmdir()關於文件的判斷還有很多相關屬性
is_dir() 是否是目錄
is_file() 是否是普通文件
is_symlink() 是否是軟鏈接
is_socket() 是否是socket文件
is_block_device() 是否是塊設備
is_char_device() 是否是字符設備
is_absolute() 是否是絕對路徑
resolve() 返回一個新的路徑，這個新路徑就是當前Path對象的絕對路徑，如果是軟鏈接則直接被解析
absolute() 也可以獲取絕對路徑，但是推薦resolve()
exists() 該路徑是否指向現有的目錄或文件：
from pathlib2 import Path
example_path = Path('/Users/Anders/Documents/pic-2.jpg')
# 判斷對象是否存在
print(example_path.exists())
# 輸出如下：
# True
# 判斷對象是否是目錄
print(example_path.is_dir())
# 輸出如下：
# False
# 判斷對象是否是文件
print(example_path.is_file())
# 輸出如下：
# True只需要通過**.stat()**方法就可以返還指定路徑的文件信息
from pathlib2 import Path
example_path = Path('/Users/Anders/Documents/pic.jpg')
print(example_path.stat())
# 輸出如下：
# os.stat_result(st_mode=33188, st_ino=8598206944, st_dev=16777220, st_nlink=1, st_uid=501, st_gid=20, st_size=38054, st_atime=1549547190, st_mtime=1521009880, st_ctime=1521009883)
# 文件大小 最後訪問時間 最後修改時間 創建時間
print(example_path.stat().st_size)
# 輸出如下：
# 38054from pathlib import Path # pathlib模塊是python內置模塊
old_file_path = Path('F:\\python\\第2章\\員工檔案.xlsx') # 原路徑
new_file_path = Path('F:\\table\\員工信息表.xlsx') # 新路徑
old_file_path.rename(new_file_path) # 重命名 rename只能在同一個磁盤分區from pathlib import Path
file_path = Path('F:\\python\\第2章\\出庫表.xlsx')
path = file_path.parent # 文件路徑
file_name = file_path.name # 文件名
stem_name = file_path.stem # 文件主名
suf_name = file_path.suffix # 文件擴展名
print(path)
print(file_name)
print(stem_name)
print(suf_name)from pathlib import Path
folder_path = Path('F:\\python\\第2章\\工作信息表\\')
file_list = folder_path.glob('*.xls*') # glob用於查找符合指定規則的文件或文件夾
lists = []
for i in file_list:
file_name = i.name
lists.append(file_name)
print(lists)
#['供應商信息表.xlsx', '出庫表.xlsx', '同比增長情況表.xls', '員工檔案表.xlsx', '庫存表.xlsx']import xlwings as xw
app = xw.App(visible=False, add_book=False) # 啟動Excel程序
workbook = app.books.add() # 新建工作簿
workbook.save('F:\\test\\1月銷售表.xlsx') # 保存新建工作簿
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程序import xlwings as xw
app = xw.App(visible=False, add_book=False)
for i in range(1, 6):
workbook = app.books.add()
workbook.save(f'F:\\test\\銷售表{
i}.xlsx') # f-string 替換{}裡內容
workbook.close()
app.quit()import xlwings as xw app = xw.App(visible=True, add_book=False) file_path = '員工信息表.xlsx' app.books.open(file_path)
from pathlib import Path
import xlwings as xw
app = xw.App(visible=True, add_book=False)
folder_path = Path('F:\\python\\第2章\\工作信息表\\')
file_list = folder_path.glob('*.xls*')
for i in file_list:
app.books.open(i)from pathlib import Path
folder_path = Path('F:\\python\\第2章\\table\\')
file_list = folder_path.glob('*月.xlsx')
for i in file_list:
old_file_name = i.name
new_file_name = old_file_name.replace('月', '月銷售表')
# with_name是pathlib路徑對象函數，用於替換原路徑文件名
new_file_path = i.with_name(new_file_name) # 用新的文件名構造新的文件路徑
i.rename(new_file_path) # 執行重命名操作from pathlib import Path
import xlwings as xw
app = xw.App(visible=False, add_book=False)
folder_path = Path('F:\\python\\第2章\\工作信息表\\')
file_list = folder_path.glob('*.xlsx')
for i in file_list:
new_file_path = str(i.with_suffix('.xls')) # SaveAs不能識別路徑
workbook = app.books.open(i) # 打開要轉換文件格式的工作簿
workbook.api.SaveAs(new_file_path, FileFormat=56) # 56代表.xls. 51代表.xlsx
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
file_path = 'F:\\python\\第2章\\新能源汽車備案信息.xlsx'
workbook = app.books.open(file_path)
worksheet = workbook.sheets
for i in worksheet:
new_workbook = app.books.add()
new_worksheet = new_workbook.sheets[0]
i.copy(before=new_worksheet) # 將來源工作簿的當前工作表復制到新建工作簿的第一個工作表之前
new_workbook.save('F:\\python\\第2章\\汽車備案信息\\{}.xlsx'.format(i.name))
new_workbook.close()
app.quit()from pathlib import Path
import pandas as pd
folder_path = Path('F:\\python\\第2章\\上半年銷售統計\\')
file_list = folder_path.glob('*.xls*')
with pd.ExcelWriter('F:\\python\\第2章\\總表.xlsx') as workbook:
for i in file_list:
stem_name = i.stem
data = pd.read_excel(i, sheet_name=0)
data.to_excel(workbook, sheet_name=stem_name, index=False)from pathlib import Path
folder_path = Path('第2章\\工作文件\\') # 給出要分類的文件夾路徑
file_list = folder_path.glob('*.xls*')
for i in file_list:
suf_name = i.suffix
new_folder_path = folder_path / suf_name # 構造以擴展名命名的文件夾的完整路徑
if not new_folder_path.exists():
new_folder_path.mkdir()
i.replace(new_folder_path / i.name) # 將工作簿移動到以擴展名的文件下
# replace用於使用新路徑覆蓋原路徑from time import localtime
from pathlib import Path
folder_path = Path('F:\\python\\第2章\\工作文件\\')
file_list = folder_path.glob('*.xls*')
for i in file_list:
lm_time = i.stat().st_mtime # 獲取最後修改時間
year = localtime(lm_time).tm_year # 提取年份
month = localtime(lm_time).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)from pathlib import Path
folder_path = input('請輸入查找路徑（如C:\\）：')
file_name = input('請輸入要查找的工作簿名稱：')
folder_path = Path(folder_path)
file_list = folder_path.rglob(file_name) # rglob 用於指定文件夾及其子文件夾中查找名稱符合指定規則的文件或文件夾
for i in file_list:
print(i)
# 請輸入查找路徑（如C:\）：I:\
# 請輸入要查找的工作簿名稱：出庫表.xlsx
# I:\Projects\jupyter\Excel\第2章\出庫表.xlsx
# I:\Projects\jupyter\Excel\第2章\table\出庫表.xlsx
# I:\Projects\jupyter\Excel\第2章\工作信息表\出庫表.xlsxfrom pathlib import Path
folder_path = input('請輸入查找路徑（如C:\\）：')
keyword = input('請輸入關鍵詞：')
folder_path = Path(folder_path)
file_list = folder_path.rglob(f'*{
keyword}*.xls*')
for i in file_list:
print(i)
# 請輸入查找路徑（如C:\）：I:\
# 請輸入關鍵詞：供應商
# I:\Projects\jupyter\Excel\第2章\工作信息表\供應商信息表.xlsximport xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第2章\\辦公用品采購表.xlsx')
workbook.api.Protect(Password='123', Structure=True, Windows=True) # 密碼 工作簿結構不被修改 窗口不被修改
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第2章\\辦公用品采購表.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('F:\\python\\第2章\\工作信息表\\')
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()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽車備案信息.xlsx')
worksheet = workbook.sheets
lists = []
for i in worksheet:
sheet_name = i.name
lists.append(sheet_name)
print(lists)
workbook.close()
app.quit()import pandas as pd file_path = 'F:\\python\\第3章\\新能源汽車備案信息.xlsx' data = pd.read_excel(file_path, sheet_name=None) worksheet_name = list(data.keys()) print(worksheet_name) # 第三行代碼讀取工作簿中所有工作表中的數據後，生成一個字典，字典的鍵為工作表的名稱，值為對應的數據
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽車備案信息.xlsx')
worksheet = workbook.sheets
new_sheet_name = '產品信息表'
lists = []
for i in worksheet:
sheet_name = i.name
lists.append(sheet_name)
if new_sheet_name not in lists:
worksheet.add(name=new_sheet_name) # 新增工作表
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽車備案信息.xlsx')
worksheet = workbook.sheets
del_sheet_name = '汽車備案信息'
for i in worksheet:
sheet_name = i.name
if sheet_name == del_sheet_name:
i.delete() # 刪除當前工作表
break
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('F:\\python\\第3章\\上半年銷售統計\\')
file_list = folder_path.glob('*.xls*')
new_sheet_name = '產品信息表'
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
lists = []
for j in worksheet:
sheet_name = j.name
lists.append(sheet_name)
if new_sheet_name not in lists:
worksheet.add(name=new_sheet_name)
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('F:\\python\\第3章\\汽車信息\\')
file_list = folder_path.glob('*.xls*')
del_sheet_name = 'Sheet1'
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
for j in worksheet:
sheet_name = j.name
if sheet_name == del_sheet_name:
j.delete()
break
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽車備案信息.xlsx')
worksheet = workbook.sheets
for i in worksheet:
if i.name == '汽車備案信息':
i.name = '汽車信息'
break
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\上半年銷售統計.xlsx')
worksheet = workbook.sheets
for i in worksheet:
i.name = i.name.replace('銷售表', '')
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('F:\\python\\第3章\\銷售統計\\')
file_list = folder_path.glob('*.xls*')
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
for j in worksheet:
if j.name == '產品信息':
j.name = '配件信息'
break
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook1 = app.books.open('F:\\python\\第3章\\產品信息表.xlsx')
workbook2 = app.books.open('F:\\python\\第3章\\1月銷售表.xlsx')
worksheet1 = workbook1.sheets['配件信息']
worksheet2 = workbook2.sheets[0] # sheets[0] 表示第一個工作表
worksheet1.copy(before=worksheet2)
workbook2.save()
app.quit()from pathlib import Path
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook1 = app.books.open('F:\\python\\第3章\\產品信息表.xlsx')
worksheet1 = workbook1.sheets['配件信息']
folder_path = Path('F:\\python\\第3章\\上半年銷售統計\\')
file_list = folder_path.glob('*.xls*')
for i in file_list:
workbook2 = app.books.open(i)
worksheet2 = workbook2.sheets[0]
worksheet1.copy(before=worksheet2)
workbook2.save()
app.quit()import pandas as pd
file_path = 'F:\\python\\第3章\\銷售表.xlsx'
data = pd.read_excel(file_path, sheet_name='總表')
pro_data = data.groupby('產品名稱')
for i, j in pro_data: # 組名 數據
new_file_path = 'F:\\python\\第3章\\拆分\\' + i + '.xlsx'
j.to_excel(new_file_path, sheet_name=i, index=False)import pandas as pd
file_path = 'F:\\python\\第3章\\銷售表.xlsx'
data = pd.read_excel(file_path, sheet_name='總表')
pro_data = data.groupby('產品名稱')
with pd.ExcelWriter('F:\\python\\第3章\\各產品銷售表.xlsx') as workbook:
for i, j in pro_data:
j.to_excel(workbook, sheet_name=i, index=False)import pandas as pd
file_path = 'F:\\python\\第3章\\銷售數量統計.xlsx'
data = pd.read_excel(file_path, sheet_name='總表')
head_col = list(data.columns)
same_col = data[['配件編號', '配件名稱']]
with pd.ExcelWriter('F:\\python\\第3章\\各產品銷售表1.xlsx') as workbook:
for i in head_col[2:]:
dif_col = data[i]
sheet_data = pd.concat([same_col, dif_col], axis=1)
sheet_data.to_excel(workbook, sheet_name=i, index=False)import pandas as pd file_path = 'F:\\python\\第3章\\上半年銷售統計.xlsx' data = pd.read_excel(file_path, sheet_name=None) all_data = pd.concat(data, ignore_index=True) new_file_path = 'F:\\python\\第3章\\銷售統計.xlsx' all_data.to_excel(new_file_path, sheet_name='總表', index=False)
import pandas as pd file_path = 'F:\\python\\第3章\\產品各月銷售數量表.xlsx' data = pd.read_excel(file_path, sheet_name=None) all_data = data['1月'][['配件編號', '配件名稱']] for i in data: col = data[i].iloc[:, [2]] all_data = pd.concat([all_data, col], axis=1) new_file_path = 'F:\\python\\第3章\\合並表.xlsx' all_data.to_excel(new_file_path, sheet_name='總表', index=False)
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽車備案信息.xlsx')
worksheet = workbook.sheets
for i in worksheet:
if i.name == '汽車備案信息':
i.api.Tab.Color = 255
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽車備案信息.xlsx')
worksheet = workbook.sheets
for i in worksheet:
if i.name == '汽車備案信息':
i.visible = False
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('F:\\python\\第3章\\區域銷售統計\\')
file_list = folder_path.glob('*.xls*')
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
for j in worksheet:
if j.name == '供應商信息':
j.visible = False
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('F:\\python\\第3章\\區域銷售統計\\')
file_list = folder_path.glob('*.xls*')
lists = ['配件信息', '供應商信息']
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
for j in worksheet:
if j.name in lists:
j.visible = False
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\產品信息表.xlsx')
worksheet = workbook.sheets['配件信息']
worksheet.api.Protect(Password='123', Contents=True)
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook= app.books.open('新能源汽車備案信息.xlsx')
worksheet = workbook.sheets[0]
worksheet.autofit()
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('產品信息表.xlsx')
worksheet = workbook.sheets[0]
area = worksheet.range('A1').expand('table')
area.column_width = 15
area.row_height = 20
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('新能源汽車備案信息.xlsx')
worksheet = workbook.sheets
for i in worksheet:
i.autofit()
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('F:\\python\\第4章\\區域銷售統計\\')
file_list = folder_path.glob('*.xls*')
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
for j in worksheet:
j.autofit()
workbook.save()
workbook.close()
app.quit()from openpyxl import load_workbook # openpyxl 模塊可用於.xlsx格式讀寫和修改
workbook = load_workbook('工資表.xlsx')
worksheet = workbook['工資表']
worksheet.insert_rows(6, 1)
workbook.save('工資表1.xlsx')from openpyxl import load_workbook
workbook = load_workbook('工資表1.xlsx')
worksheet = workbook['工資表']
num = 2 # 設置插入空白行的數量
last_num = worksheet.max_row # 獲取工作表數據區域行數
for i in range(0, last_num):
worksheet.insert_rows(i * (num + 1) + 3, num) # 插入空白行
workbook.save('工資表2.xlsx')from openpyxl import load_workbook
workbook = load_workbook('新能源汽車備案信息.xlsx')
worksheet = workbook['汽車備案信息']
worksheet.insert_cols(5, 1)
workbook.save('新能源汽車備案信息1.xlsx')from openpyxl import load_workbook
workbook = load_workbook('新能源汽車備案信息.xlsx')
worksheet = workbook['汽車備案信息']
worksheet.delete_rows(5, 2)
workbook.save('新能源汽車備案信息1.xlsx')from openpyxl import load_workbook
workbook = load_workbook('新能源汽車備案信息.xlsx')
worksheet = workbook['汽車備案信息']
worksheet.delete_cols(5, 2)
workbook.save('新能源汽車備案信息1.xlsx')import pandas as pd
data = pd.read_excel('銷售表.xlsx', sheet_name=0)
data.drop(columns=['成本價', '產品成本'], inplace=True) # 刪除指定列
data.to_excel('銷售表1.xlsx', sheet_name='總表', index=False)import xlwings as xw
app = xw.App(visible=False, add_book=False)
new_data = [['8', '重慶**汽車有限公司', '孫**', '187****2245'], ['9', '四川**汽車有限公司', '肖**', '177****2245']]
workbook = app.books.open('產品信息表.xlsx')
worksheet = workbook.sheets['供應商信息']
data = worksheet.range('A1').expand('table')
num = data.shape[0]
worksheet.range(num + 1, 1).value = new_data
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('F:\\python\\第4章\\區域銷售統計\\')
file_list = folder_path.glob('*.xls*')
new_data = [['8', '重慶**汽車有限公司', '孫**', '187****2245'], ['9', '四川**汽車有限公司', '肖**', '177****2245']]
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets['供應商信息']
data = worksheet.range('A1').expand('table')
num = data.shape[0]
worksheet.range(num + 1, 1).value = new_data
workbook.save()
workbook.close()
app.quit()import pandas as pd
data = pd.read_excel('銷售表.xlsx', sheet_name=0)
max_data = data['利潤'].max()
level = [0, 5000, 10000, max_data]
level_names = ['差', '良', '優']
data['等級'] = pd.cut(data['利潤'], level, labels=level_names)
data.to_excel('銷售表1.xlsx', sheet_name='總表', index=False)import pandas as pd
data = pd.read_excel('銷售表.xlsx', sheet_name='總表')
row_data = data.iloc[0:10] # 提取前10行
col_data = data[['單號', '銷售日期', '產品名稱', '利潤']]
range_data = data.iloc[0:5][['單號', '銷售日期', '產品名稱', '利潤']]
row_data.to_excel('提取行數據.xlsx', sheet_name='前10行數據', index=False)
col_data.to_excel('提取列數據.xlsx', sheet_name='利潤表', index=False)
range_data.to_excel('提取數據.xlsx', sheet_name='Sheet1', index=False)import pandas as pd
data = pd.read_excel('辦公用品采購表.xlsx', sheet_name=None)
with pd.ExcelWriter('提取表.xlsx') as workbook:
for i, j in data.items(): # items()用於返回字典的鍵值對
row_data = j.iloc[0:5]
row_data.to_excel(workbook, sheet_name=i, index=False)import pandas as pd
data = pd.read_excel('銷售表.xlsx', sheet_name=0)
data = data.replace('離合器', '剎車片')
data.to_excel('銷售表1.xlsx', sheet_name='總表', index=False)import pandas as pd
data = pd.read_excel('辦公用品采購表.xlsx', sheet_name=None)
with pd.ExcelWriter('辦公用品采購表1.xlsx') as workbook:
for i, j in data.items():
data = j.replace('固體膠', '透明膠帶')
data.to_excel(workbook, sheet_name=i, index=False)import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('產品信息表1.xlsx')
worksheet = workbook.sheets['配件信息']
data = worksheet.range('A2').expand('table').value
for i, j in enumerate(data):
data[i][3] = float(j[3]) * (1 + 0.1)
worksheet.range('A2').expand('table').value = data
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('銷售表.xlsx')
worksheet = workbook.sheets['總表']
data = worksheet.range('A1').expand('table').value
for i, j in enumerate(data):
if (j[2] == '裡程表') and (j[3] == 850):
data[i][3] = 900
worksheet.range('A1').expand('table').value = data
workbook.save()
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('產品分析表.xlsx')
worksheet = workbook.sheets[0]
data = worksheet.range('A1').expand('table').options(transpose=True).value
worksheet.clear() # 清除工作表的內容和格式設置
worksheet.range('A1').expand().value = data
workbook.save('產品分析表1.xlsx')
workbook.close()
app.quit()from openpyxl import load_workbook
workbook = load_workbook('銷售表.xlsx')
worksheet = workbook['總表']
worksheet.freeze_panes = 'B2'
workbook.save('銷售表1.xlsx')import pandas as pd
data = pd.read_excel('產品規格表.xlsx', sheet_name=0)
data_col = data['產品規格'].str.split('*', expand=True)
data['長(cm)'] = data_col[0]
data['寬(cm)'] = data_col[1]
data['高(cm)'] = data_col[2]
data.drop(columns=['產品規格'], inplace=True)
data.to_excel('產品規格表1.xlsx', sheet_name='規格表', index=False)import pandas as pd
data = pd.read_excel('產品規格表1.xlsx', sheet_name='規格表')
data['產品規格'] = data['長(cm)'].astype(str) + '*' + data['寬(cm)'].astype(str) + '*' + data['高(cm)'].astype(str)
data.drop(columns=['長(cm)', '寬(cm)', '高(cm)'], inplace=True)
data.to_excel('產品規格表2.xlsx', sheet_name='Sheet1', index=False)from openpyxl import load_workbook
workbook = load_workbook('新能源汽車備案信息.xlsx')
worksheet = workbook['汽車備案信息']
worksheet.row_dimensions.group(2, 10, hidden=True)
workbook.save('新能源汽車備案信息1.xlsx')from openpyxl import load_workbook
workbook = load_workbook('新能源汽車備案信息.xlsx')
worksheet = workbook['汽車備案信息']
worksheet.column_dimensions.group('A', 'D', hidden=True)
workbook.save('新能源汽車備案信息1.xlsx')import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.add()
worksheet = workbook.sheets.add(name='銷售情況')
worksheet.range('A1').value = [['產品名稱', '銷售數量', '銷售單價', '銷售額'], ['大衣', 15, 400, 6000], ['羽絨服', 20, 500, 10000]]
workbook.save('產品表.xlsx')
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('訂單表.xlsx')
worksheet = workbook.sheets[0]
header = worksheet.range('A1:I1')
header.font.name = '微軟雅黑'
header.font.size = 10
header.font.bold = True
header.font.color = (255, 255, 255)
header.color = (0, 0, 0) # 單元格填充顏色
data = worksheet.range('A2').expand('table') # 選中數據行所在的單元格區域
data.font.name = '微軟雅黑'
data.font.size = 10
workbook.save('訂單表1.xlsx')
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('訂單表1.xlsx')
worksheet = workbook.sheets[0]
header = worksheet.range('A1:I1')
header.api.HorizontalAlignment = -4108
header.api.VerticalAlignment = -4108
data = worksheet.range('A2').expand('table')
data.api.HorizontalAlignment = -4152 # 設置數據行的水平對齊方式
data.api.VerticalAlignment = -4108 # 設置數據行的垂直對齊方式
workbook.save('訂單表2.xlsx')
workbook.close()
app.quit()HorizontalAlignment設置水平對齊方式
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('訂單表2.xlsx')
worksheet = workbook.sheets[0]
area = worksheet.range('A1').expand('table')
for i in area:
for j in range(7, 11):
i.api.Borders(j).LineStyle = 1
i.api.Borders(j).Weight = 2
i.api.Borders(j).Color = xw.utils.rgb_to_int((255, 0, 0)) # 設置邊框的顏色
workbook.save('訂單表3.xlsx')
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('訂單表3.xlsx')
worksheet = workbook.sheets[0]
row_num = worksheet.range('A1').expand('table').last_cell.row
worksheet.range(f'B2:B{
row_num}').number_format = 'yyyy年m月d日'
worksheet.range(f'D2:D{
row_num}').number_format = '￥#,##0'
worksheet.range(f'E2:E{
row_num}').number_format = '￥#,##0'
worksheet.range(f'G2:G{
row_num}').number_format = '￥#,##0.00'
worksheet.range(f'H2:H{
row_num}').number_format = '￥#,##0.00'
worksheet.range(f'I2:I{
row_num}').number_format = '￥#,##0.00'
workbook.save('訂單表4.xlsx')
workbook.close()
app.quit()import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('訂單表5.xlsx')
worksheet = workbook.sheets[0]
title = worksheet.range('A1:I1') # 指定要合並的單元格區域
title.merge() # 合並單元格
title.font.name = '微軟雅黑'
title.font.size = 18
title.font.bold = True
title.api.HorizontalAlignment = -4108
title.api.VerticalAlignment = -4108
title.row_height = 30
workbook.save('訂單表6.xlsx')
workbook.close()
app.quit()合並單元格制作表格標題（方法二）
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment
workbook = load_workbook('訂單表5.xlsx')
worksheet = workbook['總表']
worksheet.merge_cells('A1:I1') # 指定要合並的單元格區域
worksheet['A1'].font = Font(name='微軟雅黑', size=18, bold=True)
worksheet['A1'].alignment = Alignment(horizontal='center', vertical='center')
worksheet.row_dimensions[1].height = 30
workbook.save('訂單表6.xlsx')from openpyxl import load_workbook
workbook = load_workbook('訂單金額表.xlsx')
worksheet = workbook['Sheet1']
lists = []
num = 2 # 從第二行開始
while True: # 構造永久循環
datas = worksheet.cell(num, 1).value
if datas:
lists.append(datas)
else: # 如果讀取的數據為空
break # 則強制結束循環
num += 1
s = 0
e = 0
data = lists[0]
for m in range(len(lists)):
if lists[m] != data:
data = lists[m]
e = m - 1
if e >= s:
worksheet.merge_cells(f'A{
s + 2}:A{
e + 2}') # 合並A列相同內容的單元格
s = e + 1
if m == len(lists) - 1:
e = m
worksheet.merge_cells(f'A{
s + 2}:A{
e + 2}') # 合並A列相同內容的單元格
workbook.save('訂單金額表1.xlsx')import pandas as pd
data = pd.read_excel('銷售表.xlsx', sheet_name='總表')
data['銷售金額'].fillna(0, inplace=True)
data['利潤'].fillna(0, inplace=True)
data.to_excel('銷售表1.xlsx', sheet_name='總表', index=False)import pandas as pd
data = pd.read_excel('銷售表1.xlsx', sheet_name='總表')
data = data.drop_duplicates() # 刪除重復行
data.to_excel('銷售表2.xlsx', sheet_name='總表', index=False)import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('銷售表2.xlsx')
worksheet = workbook.sheets[0]
data = worksheet.range('A1').expand('table').value
worksheet.range('A1').expand('table').value = data
workbook.save('銷售表3.xlsx')
workbook.close()
app.quit()import pandas as pd
data = pd.read_excel('銷售表.xlsx', sheet_name='總表')
data = data.sort_values(by='利潤', ascending=False) # 降序
data.to_excel('銷售表1.xlsx', sheet_name='總表', index=False)import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('銷售表.xlsx')
worksheet = workbook.sheets['總表']
data = worksheet.range('A1').expand('table').options(pd.DataFrame).value
result = data.sort_values(by='利潤', ascending=False)
worksheet.range('A1').value = result
workbook.save('銷售表1.xlsx')
workbook.close()
app.quit()import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('各月銷售數量表.xlsx')
worksheet = workbook.sheets
for i in worksheet:
data = i.range('A1').expand('table').options(pd.DataFrame).value # 將數據轉化為DataFrame格式
result = data.sort_values(by='銷售數量', ascending=False)
i.range('A1').value = result
workbook.save('各月銷售數量表1.xlsx')
workbook.close()
app.quit()from pathlib import Path
import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
folder_path = Path('各地區銷售數量')
file_list = folder_path.glob('*.xls*')
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets['銷售數量']
data = worksheet.range('A1').expand('table').options(pd.DataFrame).value
result = data.sort_values(by='銷售數量', ascending=False)
worksheet.range('A1').value = result
workbook.save()
workbook.close()
app.quit()import pandas as pd
data = pd.read_excel('銷售表.xlsx', sheet_name='總表')
pro_data = data[data['產品名稱'] == '離合器']
num_data = data[data['銷售數量'] >= 100]
pro_data.to_excel('離合器.xlsx', sheet_name='離合器', index=False)
num_data.to_excel('銷售數量大於等於100的記錄.xlsx', sheet_name='銷售數量大於等於100的記錄', index=False)import pandas as pd
data = pd.read_excel('銷售表.xlsx', sheet_name='總表')
condition1 = (data['產品名稱'] == '轉速表') & (data['銷售數量'] >= 50)
condition2 = (data['產品名稱'] == '轉速表') | (data['銷售數量'] >= 50)
data1 = data[condition1]
data2 = data[condition2]
data1.to_excel('銷售表1.xlsx', sheet_name='與條件篩選', index=False)
data2.to_excel('銷售表2.xlsx', sheet_name='或條件篩選', index=False)import pandas as pd
all_data = pd.read_excel('辦公用品采購表.xlsx', sheet_name=None)
with pd.ExcelWriter('篩選表.xlsx') as workbook:
for i in all_data:
data = all_data[i]
filter_data = data[data['采購物品'] == '辦公桌']
filter_data.to_excel(workbook, sheet_name=i, index=False)import pandas as pd
all_data = pd.read_excel('辦公用品采購表.xlsx', sheet_name=None)
datas = pd.DataFrame()
for i in all_data:
data = all_data[i]
filter_data = data[data['采購物品'] == '辦公桌']
datas = pd.concat([datas, filter_data], axis=0)
datas.to_excel('辦公桌.xlsx', sheet_name='辦公桌', index=False)import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('銷售表.xlsx')
worksheet = workbook.sheets['總表']
data = worksheet.range('A1').expand('table').options(pd.DataFrame, dtype=float).value
result = data.groupby('產品名稱').sum()
worksheet1 = workbook.sheets.add(name='分類匯總')
worksheet1.range('A1').value = result[['銷售數量', '銷售金額']]
workbook.save('分類匯總表.xlsx')
workbook.close()
app.quit()import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('辦公用品采購表.xlsx')
worksheet = workbook.sheets['1月']
data = worksheet.range('A1').expand('table').options(pd.DataFrame).value
result = data['采購金額'].sum()
worksheet.range('B15').value = '合計'
worksheet.range('C15').value = result
workbook.save('求和表.xlsx')
workbook.close()
app.quit()import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('辦公用品采購表.xlsx')
worksheet = workbook.sheets
for i in worksheet:
data = i.range('A1').expand('table').options(pd.DataFrame).value
result = data['采購金額'].sum()
column = i.range('A1').expand('table').value[0].index('采購金額') + 1
row = i.range('A1').expand('table').shape[0]
i.range(row + 1, column - 1).value = '合計'
i.range(row + 1, column).value = result
workbook.save('求和表.xlsx')
workbook.close()
app.quit()import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('銷售表.xlsx')
worksheet = workbook.sheets['總表']
data = worksheet.range('A1').expand('table').options(pd.DataFrame, dtype=float).value
pivot = pd.pivot_table(data, values=['銷售數量', '銷售金額'], index=['產品名稱'], aggfunc={
'銷售數量': 'sum', '銷售金額': 'sum'}, fill_value=0, margins=True, margins_name='合計')
worksheet1 = workbook.sheets.add(name='數據透視表')
worksheet1.range('A1').value = pivot
workbook.save('數據透視表.xlsx')
workbook.close()
app.quit()import pandas as pd
data = pd.read_excel('銷售額統計表.xlsx', sheet_name=0, index_col='序號')
result = data.corr() # 計算任意兩個變量之間的相關系數
print(result)
import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw
data = pd.read_excel('員工銷售業績表.xlsx', sheet_name=0)
data_describe = data['銷售額（萬元）'].astype(float).describe()
data_cut = pd.cut(data['銷售額（萬元）'], 6)
data1 = pd.DataFrame()
data1['計數'] = data['銷售額（萬元）'].groupby(data_cut).count()
data2 = data1.reset_index()
data2['銷售額（萬元）'] = data2['銷售額（萬元）'].apply(lambda x:str(x))
figure = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
n, bins, patches = plt.hist(data['銷售額（萬元）'], bins=6, edgecolor='black', linewidth=1)
plt.xticks(bins)
plt.title('員工銷售業績頻率分析')
plt.xlabel('銷售額（萬元）')
plt.ylabel('頻數')
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('員工銷售業績表.xlsx')
worksheet = workbook.sheets[0]
worksheet.range('E1').value = data_describe
worksheet.range('H1').value = data2
worksheet.pictures.add(figure, name='圖片1', update=True, left=400, top=200)
worksheet.autofit()
workbook.save('描述統計.xlsx')
workbook.close()
app.quit()import pandas as pd
from sklearn import linear_model
df = pd.read_excel('各月銷售額與廣告費支出表.xlsx', sheet_name=0)
x = df[['視頻門戶廣告費（萬元）', '電視台廣告費（萬元）']]
y = df['銷售額（萬元）']
model = linear_model.LinearRegression()
model.fit(x, y)
R2 = model.score(x, y)
print(R2)import pandas as pd
from sklearn import linear_model
df = pd.read_excel('各月銷售額與廣告費支出表.xlsx', sheet_name=0)
x = df[['視頻門戶廣告費（萬元）', '電視台廣告費（萬元）']]
y = df['銷售額（萬元）']
model = linear_model.LinearRegression()
model.fit(x, y)
coef = model.coef_
model_intercept = model.intercept_
equation = f'y={
coef[0]}*x1+{
coef[1]}*x2{
model_intercept:+}'
print(equation)
x1 = 40
x2 = 30
y = coef[0] * x1 + coef[1] * x2 + model_intercept
print(y)
