Python—xlwings

书：Python让Excel飞起来

#批量处理xlwings
import xlwings as xw#导入xlwings模块
app = xw.App(visible=True,add_book=False)
#visible设置Excel程序窗口的可见性，True显示，False为隐藏；add_book启动Excel程序窗口后是否新建程序窗口，True表示创建
workbook=app.books.add()#新建一个工作薄，add()为books对象的函数
#保存
workbook.save('F:\\python_test\\分公.xlsx')
#保存路径及文件名称—绝对路径，也可以用save(r'F:\python_test\分公.xlsx')，里面r用来取消路径的反斜杠的转义功能
workbook.close()#关闭工作薄
app.quit()#退出Excel程序
#打开工作簿
import xlwings as xw
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'F:\python_test\分公.xlsx')#指定的工作薄必须存在，并不能处于打开的状态
workbook
#操控工作表和单元格
worksheet=workbook.sheets['Sheet1']#选中工作表里名字为Sheet1
worksheet.range('A1').value='编号'#在单元格输入内容
worksheet=workbook.sheets.add('产品统计表')#新增一个名为产品统计表的工作表

#模式一
import math
a=math.sqrt(16)
print(a)
#模式二 直接引用函数，不用加前缀
from math import sqrt #
a=sqrt(16)
print(a)
#\n 转义字符 换行
print('2021,\n 一起加油')
#打印文件路径是用\\ 代替\
print('F:\\python_test\分公司1.xlsx')
for i in range(len(title)): #等同于range(3)
    print(str(i+1)+' '+'.'+title[i])
from math import  sqrt #导入math模块中单个函数
from turtle import forwward,backward,right,left#导入turtle模块
import os #E盘下文件名称
path='E:\\'
file_list=os.listdir(path)
print(file_list)
#分离文件主名和扩展名
import os
path='F:\\python_test\分公司1.xlsx'
separate=os.path.splitext(path)
print(separate) #返回包含两个元素的元祖
#os模块重命名rename（src,dst） oldname.newname
import os
oldname='F:\\python_test\\分公司1.xlsx'
newname='F:\\python_test\\分公.xlsx'
os.rename(oldname,newname)
#rename可以修改文件路径，newname='F:\\分公.xlsx'

#Numpy数组的基础
import numpy as np
a=[1,2,3,4]
b=np.array([1,2,3,4]) #一维数组
c=a*2
d=b*2
print(c) #数据复制
print(d) #数据*2
#print(type(a))  print(type(b))
#数组存储多维数据
e=[[1,3],[2,6],[3,9]]
f=np.array([[1,3],[2,6],[3,9]]) #二维数组
print(f)
print(e)
#np.arange()
#1个参数：起点默认为0，参数值为终点，步长为1，左闭右开
#2个参数：第1个参数值起点，第2个参数值为终点，步长为1，左闭右开
#3个参数：第1个参数值起点，第2个参数值为终点，第3个参数步长，左闭右开
x=np.arange(5)
y=np.arange(1,5)
z=np.arange(1,10,5)
print(x)
print(y)
print(z)
#np.random #创建一维 
c=np.random.randn(3)
print(c)
#np.arange() 和reshape()#创建二维
d=np.arange(12).reshape(3,4)
print(d)
#创建二维其他方式
e=np.random.randint(0,10,(4,4))#np.random.randint创建随机整数，起始数，终止数，多行多列的二维数组
print(e)

#第4章
#案例1：批量新建并关闭工作簿、
#案例2：批量打开一个文件夹下的所有工作簿
import os
import xlwings as xw
file_path=f'F:/python_test'
file_list=os.listdir(file_path)
app=xw.App(visible=True,add_book=False)
for i in file_list:
    if os.path.splitext(i)[1]=='.xlsx':#判断文件夹下文件的扩展是否为.xlxs
        app.books.open(file_path+'/'+i)
#案例3 批量重命名工作表
import xlwings as xw
app=xw.App(visible=False,add_book=False)#启动excel程序
workbook=app.books.open(f'F:/python_test/tt.xlsx')
worksheets=workbook.sheets#获取工作薄中所有工作表
for i in range(len(worksheets)):#遍历取到的工作表
    worksheets[i].name=worksheets[i].name.replace('销售','')#重命名工作表
workbook.save(f'F:/python_test/ttt.xlsx')#另存
app.quit()
#案例3 批量重命名部分工作表 比如前3个sheet
import xlwings as xw
app=xw.App(visible=False,add_book=False)
workbook=app.books.open(f'F:/python_test/tt.xlsx')
worksheets=workbook.sheets
for i in range(len(worksheets))[:5]:
    worksheets[i].name=worksheets[i].name.replace('销售','')
workbook.save(f'F:/python_test/ttt2.xlsx')
app.quit()
#案例4 批量重命名工作薄 前提条条件名称有规律
import os
file_path='F:/python_test/t'
file_list=os.listdir(file_path)
old_book_name='销售'
new_book_name='xs'
for i in file_list:
    if i.startswith('~$'):#判断是否有~$开头的文件
        continue#如有，跳过
    new_file=i.replace(old_book_name,new_book_name)
    old_file_path=os.path.join(file_path,i)
    new_file_path=os.path.join(file_path,new_file)
    os.rename(old_file_path,new_file_path)
 #案例4 批量多个工作薄的同名工作表
import os
import xlwings as xw
file_path='F:\\python_test\\t'
file_list=os.listdir(file_path)
old_sheet_name='S'
new_sheet_name='gzb'
app=xw.App(visible=False,add_book=False)
for i in file_list:
    if i.startswith('~$'):
        continue
    old_file_path=os.path.join(file_path,i)
    workbook=app.books.open(old_file_path)
    for j in workbook.sheets:
        if j.name==old_sheet_name:
            j.name==new_sheet_name
    workbook.save()
app.quit()

#案例9 举一反三 将工作簿名称有规律的工作表合并到一个工作表
import os
import xlwings as xw
workbook_name='F:\\python_test\\t\\xin.xlsx'
sheet_names=[str(sheet)+'外访回款' for sheet in range (0,2)]
new_sheet_name='合并'#指定合并的新工作表名称
app=xw.App(visible=False,add_book=False)
header=None
all_data=[]
workbook=app.books.open(workbook_name)
for i in workbook.sheets:
    if new_sheet_name in i.name:
        i.delete()#如果已经存在删除
new_worksheet=workbook.sheets.add(new_sheet_name)#注意缩进
title_copyed=False
for j in workbook.sheets:
    if j.name in sheet_names:
        if title_copyed==False:
            j['A1'].api.EntireRow.Copy(Destination=new_worksheet['A1'].api)#将要合并的工作表列标题复制到新增工作表
            title.copyed=True
        row_num=new_worksheet['A1'].current_region.last_cell.row#列出新增工作表含有数据区域的最后一行
        j['A1'].current_region.offset(1,0).api.Copy(Destination=new_worksheet["A{}".format(row_num+1)].api)
        #在最后一行的下一行复制其他要合并工作表的数据，不复制标题
new_worksheet.autofit()
workbook.save()
app.quit()
Jupyter Notebook
EXCEL-PY 最后检查: 25 分钟前 (未保存改变) Current Kernel Logo 
Python 3
    File
    Edit
    View
    Insert
    Cell
    Kernel
    Widgets
    Help
import xlwings as xw
app=xw.App(visible=True,add_book=False)
for i in range(1,21):
    workbook=app.books.add()
    workbook.save(f'F://python_test//t//销售分公司{i}.xlsx')
    workbook.close()
app.quit()
#制定文件夹生成20个Excel工作薄
#三引号定义字符串 可以换行
print('''2020,
hello!
''')
2020,
hello!
#\n 转义字符 换行
print('2021,\n 一起加油')
#打印文件路径是用\\ 代替\
print('F:\\python_test\分公司1.xlsx')
2021,
 一起加油
F:\python_test\分公司1.xlsx
for i in range(5):
    if i ==1:
        print('安静')
    else:
        print('加油')
加油
安静
加油
加油
加油
title=['标题1','标题2','标题3']
print(len(title))
3
for i in range(len(title)): #等同于range(3)
    print(str(i+1)+' '+'.'+title[i])
1 .标题1
2 .标题2
3 .标题3
#模式一
import math
a=math.sqrt(16)
print(a)
4.0
#模式二 直接引用函数，不用加前缀
from math import sqrt #
a=sqrt(16)
print(a)
4.0
from math import  sqrt #导入math模块中单个函数
from turtle import forwward,backward,right,left#导入turtle模块
import os
path=os.getcwd()
print(path)
C:\Users\Administrator
import os #E盘下文件名称
path='E:\\'
file_list=os.listdir(path)
print(file_list)
['$RECYCLE.BIN', 'Anaconda', 'BaiduNetdisk', 'BaiduNetdiskDownload', 'Program Files (x86)', 'System Volume Information', 'WeChat', 'write', '工作—报表', '工作报表', '蜜蜂', '记录']
#分离文件主名和扩展名
import os
path='F:\\python_test\分公司1.xlsx'
separate=os.path.splitext(path)
print(separate) #返回包含两个元素的元祖
('F:\\python_test\\分公司1', '.xlsx')
#os模块重命名rename（src,dst） oldname.newname
import os
oldname='F:\\python_test\\分公司1.xlsx'
newname='F:\\python_test\\分公.xlsx'
os.rename(oldname,newname)
#rename可以修改文件路径，newname='F:\\分公.xlsx'
#批量处理xlwings
import xlwings as xw#导入xlwings模块
app = xw.App(visible=True,add_book=False)
#visible设置Excel程序窗口的可见性，True显示，False为隐藏；add_book启动Excel程序窗口后是否新建程序窗口，True表示创建
workbook=app.books.add()#新建一个工作薄，add()为books对象的函数
#保存
workbook.save('F:\\python_test\\分公.xlsx')
#保存路径及文件名称—绝对路径，也可以用save(r'F:\python_test\分公.xlsx')，里面r用来取消路径的反斜杠的转义功能
workbook.close()#关闭工作薄
app.quit()#退出Excel程序
#打开工作簿
import xlwings as xw
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'F:\python_test\分公.xlsx')#指定的工作薄必须存在，并不能处于打开的状态
workbook
<Book [分公.xlsx]>
#操控工作表和单元格
worksheet=workbook.sheets['Sheet1']#选中工作表里名字为Sheet1
worksheet.range('A1').value='编号'#在单元格输入内容
worksheet=workbook.sheets.add('产品统计表')#新增一个名为产品统计表的工作表
#Numpy数组的基础
import numpy as np
a=[1,2,3,4]
b=np.array([1,2,3,4]) #一维数组
c=a*2
d=b*2
print(c) #数据复制
print(d) #数据*2
#print(type(a))  print(type(b))
[1, 2, 3, 4, 1, 2, 3, 4]
[2 4 6 8]
#数组存储多维数据
e=[[1,3],[2,6],[3,9]]
f=np.array([[1,3],[2,6],[3,9]]) #二维数组
print(f)
print(e)
[[1 3]
 [2 6]
 [3 9]]
[[1, 3], [2, 6], [3, 9]]
#np.arange()
#1个参数：起点默认为0，参数值为终点，步长为1，左闭右开
#2个参数：第1个参数值起点，第2个参数值为终点，步长为1，左闭右开
#3个参数：第1个参数值起点，第2个参数值为终点，第3个参数步长，左闭右开
x=np.arange(5)
y=np.arange(1,5)
z=np.arange(1,10,5)
print(x)
print(y)
print(z)
[0 1 2 3 4]
[1 2 3 4]
[1 6]
#np.random #创建一维 
c=np.random.randn(3)
print(c)
#np.arange() 和reshape()#创建二维
d=np.arange(12).reshape(3,4)
print(d)
#创建二维其他方式
e=np.random.randint(0,10,(4,4))#np.random.randint创建随机整数，起始数，终止数，多行多列的二维数组
print(e)
[-0.99569137  0.41325471 -0.70682801]
[[ 0  1  2  3]
 [ 4  5  6  7]
 [ 8  9 10 11]]
[[1 8 5 0]
 [6 5 9 3]
 [8 2 9 7]
 [7 0 2 4]]
#pandas 数据导入和整理模块 
import pandas as pd
a=pd.DataFrame()#创建一个空DataFrame
date=[1,3,5]
score=[2,4,6]
a['data']=date#注意保证date和score的长度一致，否则会报错
a['score']=score
print(a)
   data  score
0     1      2
1     3      4
2     5      6
import pandas as pd
data=pd.DataFrame([[1,3],[2,6]],columns=['A','B'])
data.to_excel(r'F:\python_test\test1.xlsx')
data=pd.DataFrame(np.arange(1,10).reshape(3,3),index=['r1','r2','r3'],columns=['c1','c2','c3'])
data
    c1     c2     c3
r1     1     2     3
r2     4     5     6
r3     7     8     9
#数据的选取，筛选，排序，运算和删除
a=data['c1']
a
r1    1
r2    4
r3    7
Name: c1, dtype: int32
b=data.iloc[1:3]
b
    c1     c2     c3
r2     4     5     6
r3     7     8     9
b=data[1:3]
b
    c1     c2     c3
r2     4     5     6
r3     7     8     9
c=data.iloc[-1] ##d=data[-1]报错，-1被认为是列名
c
c1    7
c2    8
c3    9
Name: r3, dtype: int32
e=data.loc[['r2','r3']] #选取行
e
    c1     c2     c3
r2     4     5     6
r3     7     8     9
#按区块选取数据 iloc通常先取行再取列 loc方法使用字符串作为索引，iloc使用数字作为索引 无ix方法
b=data.iloc[0:2][['c1','c2']]
c=data.loc[['r1','r2']][['c1','c2']]
c
    c1     c2
r1     1     2
r2     4     5
#筛选
a=data[data['c1']>1]
b=data[(data['c1']>1)&(data['c2']==5)]#多个条件筛选需用用&，并且需要用小括号筛选条件括起来
c=data[(data['c1']<5)|(data['c2']==5)]
a
    c1     c2     c3
r2     4     5     6
r3     7     8     9
b
    c1     c2     c3
r2     4     5     6
c
    c1     c2     c3
r1     1     2     3
r2     4     5     6
#数据的排序sort_values
a=data.sort_values(by='c2',ascending=False)#by用于指定按哪列排序，ascending=True为默认值，升序排列
b=a.sort_index()
    c1     c2     c3
r3     7     8     9
r2     4     5     6
r1     1     2     3
b=a.sort_index()#按索引排序
b
    c1     c2     c3
r1     1     2     3
r2     4     5     6
r3     7     8     9
#数据运算
data['c4']=data['c3']-data['c1']
data
    c1     c2     c3     c4
r1     1     2     3     2
r2     4     5     6     2
r3     7     8     9     2
#数据删除
b=data.drop(columns='c1')#1列
b
    c2     c3     c4
r1     2     3     2
r2     5     6     2
r3     8     9     2
c=data.drop(columns=['c1','c2'])#2列
c
    c3     c4
r1     3     2
r2     6     2
r3     9     2
d=data.drop(index=['r1','r2'])#2行
d
    c1     c2     c3     c4
r3     7     8     9     2
#上述演示的将数据删除后的新DataFrame赋给新的变量，不会改变原有结构，如想改原有结构，设置参数inplace=True
data.drop(index=['r1','r2'],inplace=True)
data
    c1     c2     c3     c4
r3     7     8     9     2
#数据的拼接merge(),concat(),append()
import pandas as pd
df1=pd.DataFrame({'公司':['恒盛','创锐','快学'],'分数':[90,80,85]})
df2=pd.DataFrame({'公司':['恒盛','创锐','京西'],'股价':[20,180,30]})
df1
    公司     分数
0     恒盛     90
1     创锐     80
2     快学     85
df2
    公司     股价
0     恒盛     20
1     创锐     180
2     京西     30
#merge()
df3=pd.merge(df1,df2)#默认为内连接
df3
    公司     分数     股价
0     恒盛     90     20
1     创锐     80     180
#merge()
df4=pd.merge(df1,df2,on='公司',how='left')#默认为内连接 设置参数how='outer'或是left，right
df4
    公司     分数     股价
0     恒盛     90     20.0
1     创锐     80     180.0
2     快学     85     NaN
#merge()按行索引合并
df5=pd.merge(df1,df2,left_index=True,right_index=True)
df5
    公司_x     分数     公司_y     股价
0     恒盛     90     恒盛     20
1     创锐     80     创锐     180
2     快学     85     京西     30
#concat() 类似union all，用axis指定连接轴向
df6=pd.concat([df1,df2],ignore_index=True)#等同于pd.concat([df1,df2],axis=0);ignore_index=True生成新索引
df6
    公司     分数     股价
0     恒盛     90.0     NaN
1     创锐     80.0     NaN
2     快学     85.0     NaN
3     恒盛     NaN     20.0
4     创锐     NaN     180.0
5     京西     NaN     30.0
df7=pd.concat([df1,df2],axis=1)
df7
    公司     分数     公司     股价
0     恒盛     90     恒盛     20
1     创锐     80     创锐     180
2     快学     85     京西     30
#append()
df8=df1.append(df2) #类似pd.concat([df1,df2],axis=0)
df8
    公司     分数     股价
0     恒盛     90.0     NaN
1     创锐     80.0     NaN
2     快学     85.0     NaN
0     恒盛     NaN     20.0
1     创锐     NaN     180.0
2     京西     NaN     30.0
df9=df1.append({'公司':'腾飞','分数':90},ignore_index=True)
df9
    公司     分数
0     恒盛     90
1     创锐     80
2     快学     85
3     腾飞     90
#Matplotlib
import matplotlib.pyplot as plt
#plt.plot()
#plt.bar()
#plt.pie()
#折线图
import matplotlib.pyplot as plt
x=[1,2,3,4,5]
y=[2,4,6,8,10]
plt.plot(x,y)
plt.show()
#柱形图
x=[1,2,3,4,5,6]
y=[6,5,4,3,2,1]
plt.bar(x,y)
plt.show()
#模块的交互
#案例1  与pandas
import xlwings as xw
import pandas as pd
app=xw.App(visible=False)
workbook=app.books.add()
worksheet=workbook.sheets.add('新工作表')
df=pd.DataFrame([[1,2],[3,4]],columns=['a','b'])
worksheet.range('A1').value=df
workbook.save(f'F://python_test//tt.xlsx')
workbook.close()
app.quit()
#案例2 xlwings与matplotlib
import xlwings as xw
import matplotlib.pyplot as plt
figure=plt.figure()
x=[1,2,3,4,5]
y=[2,4,6,8,10]
plt.plot(x,y)
app=xw.App(visible=False)
workbook=app.books.add()
worksheet=workbook.sheets.add('新工作表')
worksheet.pictures.add(figure,name='图片1',update=True,left=100)
workbook.save(r'F:\python_test\tt3.xlsx')
workbook.close()
app.quit()
#第4章
#案例1：批量新建并关闭工作簿、
#案例2：批量打开一个文件夹下的所有工作簿
import os
import xlwings as xw
file_path=f'F:/python_test'
file_list=os.listdir(file_path)
app=xw.App(visible=True,add_book=False)
for i in file_list:
    if os.path.splitext(i)[1]=='.xlsx':#判断文件夹下文件的扩展是否为.xlxs
        app.books.open(file_path+'/'+i)
#案例3 批量重命名工作表
import xlwings as xw
app=xw.App(visible=False,add_book=False)#启动excel程序
workbook=app.books.open(f'F:/python_test/tt.xlsx')
worksheets=workbook.sheets#获取工作薄中所有工作表
for i in range(len(worksheets)):#遍历取到的工作表
    worksheets[i].name=worksheets[i].name.replace('销售','')#重命名工作表
workbook.save(f'F:/python_test/ttt.xlsx')#另存
app.quit()
#案例3 批量重命名部分工作表 比如前3个sheet
import xlwings as xw
app=xw.App(visible=False,add_book=False)
workbook=app.books.open(f'F:/python_test/tt.xlsx')
worksheets=workbook.sheets
for i in range(len(worksheets))[:5]:
    worksheets[i].name=worksheets[i].name.replace('销售','')
workbook.save(f'F:/python_test/ttt2.xlsx')
app.quit()
#案例4 批量重命名工作薄 前提条条件名称有规律
import os
file_path='F:/python_test/t'
file_list=os.listdir(file_path)
old_book_name='销售'
new_book_name='xs'
for i in file_list:
    if i.startswith('~$'):#判断是否有~$开头的文件
        continue#如有，跳过
    new_file=i.replace(old_book_name,new_book_name)
    old_file_path=os.path.join(file_path,i)
    new_file_path=os.path.join(file_path,new_file)
    os.rename(old_file_path,new_file_path)
#案例4 批量重命名多个工作薄的同名工作表
import os
import xlwings as xw
file_path='F:\\python_test\\t'
file_list=os.listdir(file_path)
old_sheet_name='Sheet1'
new_sheet_name='gzb'
app=xw.App(visible=True,add_book=False)
for i in file_list:
    if i.startswith('~$'):
        continue
    old_file_path=os.path.join(file_path,i)
    workbook=app.books.open(old_file_path)
    for j in workbook.sheets:
        if j.name==old_sheet_name: #判断
            j.name=new_sheet_name  #赋值
    workbook.save()
app.quit()
#案例5 在多个工作薄新增工作表
import os
import xlwings as xw
file_path='F:\\python_test\\t'
file_list=os.listdir(file_path)
sheet_name='xinzeng'
app=xw.App(visible=False,add_book=False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workbook=app.books.open(file_paths)
    sheet_names=[j.name for j in workbook.sheets]
    if sheet_name not in sheet_names:
        workbook.sheets.add(sheet_name)
        workbook.save()
app.quit()
#案例5 举一反三 批量删除工作表
import os
import xlwings as xw
file_path='F:\\python_test\\t'
file_list=os.listdir(file_path)
sheet_name='xinzeng'
app=xw.App(visible=False,add_book=False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workbook=app.books.open(file_paths)
    for j in workbook.sheets:
        if j.name==sheet_name:
            j.delete()
        break
    workbook.save()
app.quit()
#案例6 批量打印工作薄 PrintOut()
import os
import xlwings as xw
file_path='F:\\python_test\\t'
file_list=os.listdir(file_path)
app=xw.App(visible=False,add_book=False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workbook=app.books.open(file_paths)
    workbook.api.PrintOut()#打印
app.quit()
#案例6 批量打印工作薄指定工作表
import os
import xlwings as xw
file_path='F:\\python_test\\t'
file_list=os.listdir(file_path)
sheet_name='gzb'
app=xw.App(visible=False,add_book=False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workbook=app.books.open(file_paths)
    for j in workbook.sheets:
        if j.name==sheet_name:
            j.api.PrintOut()#打印
app.quit()
#案例7 将一个工作薄批量复制到其他工作薄
import os
import xlwings as xw
app=xw.App(visible=True,add_book=False)
file_path='F:\\python_test\\t'
file_list=os.listdir(file_path)
workbook=app.books.open('F:\\python_test\\资管外访日报20210512.xlsx')
worksheet=workbook.sheets
for i in file_list:
    if os.path.splitext(i)[1]=='.xlsx':
        workbooks=app.books.open(file_path+'\\'+i)
        for j in worksheet:
            contents=j.range('A1').expand('table').value
            name=j.name
            workbooks.sheets.add(name=name,after=len(workbooks.sheets))
            workbooks.sheets[name].range('A1').value=contents
        workbooks.save()
app.quit()
#案例7  举一反三 将某个工作表的数据批量复制到其他工作簿指定工作表中
import os
import xlwings as xw
app=xw.App(visible=True,add_book=False)
file_path='F:\\python_test\\t'
file_list=os.listdir(file_path)
workbook=app.books.open('F:\\python_test\\资管外访日报20210512.xlsx')
worksheet=workbook.sheets['外访回款0']#选择工作薄里面的工作表
value=worksheet.range('A1').expand('table')#读取工作表里面数据
start_cell=(2,1)
end_cell=(value.shape[0],value.shape[1])
cell_area=worksheet.range(start_cell,end_cell).value#根据前面设定的区域选取复制的数据
for i in file_list:
    if os.path.splitext(i)[1]=='.xlsx':
        try:
            workbooks=xw.Book(file_path+'\\'+i)
            sheet=workbooks.sheets['gzb']#选择要粘贴数据的工作表
            scope=sheet.range('A1').expand()#选中要粘贴数据的区域
            sheet.range(scope.shape[0]+1,1).value=cell_area#粘贴数据
            workbooks.save()
        finally:
            workbooks.close()
workbook.close()
app.quit()
#案例8 按条件将一个工作表拆分为多个工作簿 ************
import xlwings as xw
app=xw.App(visible=True,add_book=True)
file_path='F:\\python_test\\资管外访日报20210512.xlsx'
sheet_name='外访回款'
workbook=app.books.open(file_path)
worksheet=workbook.sheets[sheet_name]
value=worksheet.range('A2').expand('table').value#读取拆分工作表的所有是数据
data=dict()
for i in range(len(value)):
    product_name=value[i][10]#获取当前行的产品名称，名称不要带/,识别不了报错
    if product_name not in data:
        data[product_name]=[]
    data[product_name].append(value[i])
for key,value in data.items():
    new_workbook=xw.books.add()
    new_worksheet=new_workbook.sheets.add(key)
    new_worksheet['A1'].value=worksheet['A1:Y1'].value#将拆分的的工作表列标题复制到新表中
    new_worksheet['A2'].value=value
    new_workbook.save('{}.xlsx'.format(key))#以当前产品名作为文件名称
    new_workbook.save()
workbook.close()
app.quit()
#案例8 举一反三 按条件将一个工作表拆分为多个工作表 默认保存地址为C:\Users\Administrator\Documents
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open('F:\\python_test\\日报20210512.xlsx')
worksheet=workbook.sheets['外访回款0']
value=worksheet.range('A1').options(pd.DataFrame,header=1,index=False,expand='table').value
data=value.groupby('产品')
for idx,group in data:
    new_worksheet=workbook.sheets.add(idx)#数据新增到工作表
    new_worksheet['A1'].options(index=False).value=group#数据新增到工作表
workbook.save()
workbook.close()
app.quit()
#案例8 举一反三 按条件将一个工作簿里多个工作表拆分为多个工作薄
import xlwings as xw
workbook_name='F:\\python_test\\资管外访日报20210512.xlsx'
app=xw.App(visible=True,add_book=False)
header=None
all_data=[]
workbook=app.books.open(workbook_name)
for i in workbook.sheets:
    workbook_split=app.books.add()#新建目标工作簿
    sheet_split=workbook_split.sheets[0]#选择目标工作簿的第一个工作表
    i.api.Copy(Before=sheet_split.api)#将来源工作簿中当前的工作表复制到目前工作簿的第一个工作表之前
    workbook_split.save('{}'.format(i.name))#以当前工作表名称作为文件名保存目标工作薄
app.quit()
#案例9 批量合并多个工作薄的同名工作表
import os
import xlwings as xw
file_path='F:\\python_test\\t'
file_list=os.listdir(file_path)
sheet_name='外访回款0'#合并的同名工作表名称
app=xw.App(visible=False,add_book=False)
header=None#定义变量，初始值为空对象，后续用于存放要合并数据的列标题
all_data=[]
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workbook=app.books.open(file_paths)
    for j in workbook.sheets:
        if j.name==sheet_name:
            if header==None:
                header=j['A1:Y1'].value
            values=j['A2'].expand('table').value
            all_data=all_data+values
new_workbook=xw.Book()#新建工作薄
new_worksheet=new_workbook.sheets.add(sheet_name)#新建工作表，表名为外访回款0
new_worksheet['A1'].value=header
new_worksheet['A2'].value=all_data
new_worksheet.autofit()#根据合并后的互数据自动调整新增工作表的行高和列宽
new_workbook.save('F:\\python_test\\t\\xin.xlsx')#保存
app.quit()
#案例9 举一反三 将工作簿名称有规律的工作表合并到一个工作表
import os
import xlwings as xw
workbook_name='F:\\python_test\\t\\xin.xlsx'
sheet_names=[str(sheet)+'外访回款' for sheet in range (0,2)]
new_sheet_name='合并'#指定合并的新工作表名称
app=xw.App(visible=False,add_book=False)
header=None
all_data=[]
workbook=app.books.open(workbook_name)
for i in workbook.sheets:
    if new_sheet_name in i.name:
        i.delete()#如果已经存在删除
new_worksheet=workbook.sheets.add(new_sheet_name)#注意缩进
title_copyed=False
for j in workbook.sheets:
    if j.name in sheet_names:
        if title_copyed==False:
            j['A1'].api.EntireRow.Copy(Destination=new_worksheet['A1'].api)#将要合并的工作表列标题复制到新增工作表
            title.copyed=True
        row_num=new_worksheet['A1'].current_region.last_cell.row#列出新增工作表含有数据区域的最后一行
        j['A1'].current_region.offset(1,0).api.Copy(Destination=new_worksheet["A{}".format(row_num+1)].api)
        #在最后一行的下一行复制其他要合并工作表的数据，不复制标题
new_worksheet.autofit()
workbook.save()
app.quit()

day3

#第5章 python 批量处理行、列和单元格 os pandas xlwings  
#案例01 精确调整多个工作薄的行高和列宽 xlwings de column_width,row_height
import os
import xlwings as xw
file_path='F:\\python_test'#注意该路径下不要存在其他类型文件，识别不了会报错
file_list=os.listdir(file_path)
app=xw.App(visible=True,add_book=True)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)#多个参数
    workbook=app.books.open(file_paths)
    for j in workbook.sheets:
        value=j.range('A1').expand('table')
        value.column_width=12
        value.row_height=20
    workbook.save()
    workbook.close()
app.quit()
#案例01 举一反三  精确调整1个工作薄的行高和列宽
import xlwings as xw
app=xw.App(visible=True,add_book=False)
workbook=app.books.open('F:\\python_test\\资管外访日报20210512.xlsx')
for i in workbook.sheets:
    value=i.range('A1').expand('table')
    value.column_width=12
    value.row_height=20
workbook.save()
app.quit()
#案例02 批量修改多个工作薄的数据格式 ********************
import os
import xlwings as xw
file_path='F:\\python_test'
file_list=os.listdir(file_path)
app=xw.App(visible=True,add_book=True)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workboook=app.books.open(file_paths)
    for j in workbook.sheets:
        row_num=j['A1'].current_region.last_cell.row
        j['A2:A{}'.format(row_num)].number_format='m/d'
        j['H2:H{}'.format(row_num)].number_format='¥#,##0.00'
    workbook.save()
    workbook.close()
app.quit()
#案例02 举一反三  批量更改多个工作薄外观格式
import os 
import xlwings as xw
file_path='F:\\python_test'
file_list=os.listdir(file_path)
app=xw.App(visible=True,add_book=False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workbook=app.books.open(file_paths)
    for j in workbook.sheets:
        j['A1:N1'].api.Font.Name='宋体'
        j['A1:N1'].api.Font.Size=10
        j['A1:N1'].api.Font.Bold=True
        j['A1:N1'].api.Font.Color=xw.utils.rgb_to_int((255,255,255))
        j['A1:N1'].Color=xw.utils.rgb_to_int((0,0,0))
        j['A1:N1'].api.HorizontalAlignment=xw.constants.HAlign.xlHAlignCenter
        #j['A1:N1'].api.VerticalAlignment=xw.constants.VAlign.xlHAlignCenter #代码有问题报错xlHAlignCenter
        j['A2'].expand('table').api.Font.Name='宋体'
        j['A2'].expand('table').api.Font.Size=10
        j['A2'].expand('table').api.HorizontalAlignment=xw.constants.HAlign.xlHAlignLeft
        #j['A2'].expand('table').api.VerticalAlignment=xw.constants.VAlign.xlHAlignCenter
        for cell in j['A1'].expand('table'):
            for b in range(7,12):
                cell.api.Borders(b).LineStyle=1
                cell.api.Borders(b).Weight=2
    workbook.save()
    workbook.close()
app.quit()     
#案例03 批量替换多个工作薄的行数据
import os 
import xlwings as xw
file_path='F:\\python_test'
file_list=os.listdir(file_path)
app=xw.App(visible=True,add_book=False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workbook=app.books.open(file_paths)
    for j in workbook.sheets:
        value=j['A2'].expand('table').value
        for index,val in enumerate(value):#enumerate两个参数，第1个参数可以是列表，元祖和字符串；第2个参数省略默认为0
            if val==['背包','12','36']:
                value[index]=['2背包','32','96']
        j['A2'].expand('table').value=value
    workbook.save()
    workbook.close()
app.quit()     
#案例04 批量提取一个工作薄一个工作表的特定数据 
import xlwings as xw
import pandas as pd
app=xw.App(visible=False,add_book=False)
workbook=app.books.open('F:\\python_test\\资管外访日报20210512.xlsx')
worksheet=workbook.sheets
data=[]
for i in worksheet:
    values=i.range('A1').expand().options(pd.DataFrame).value
    filtered=values[values['产品']=='城市信贷']
    if not filtered.empty:
        data.append(filtered)
new_workbook=xw.books.add()
new_worksheet=new_workbook.sheets.add('城市信贷')
new_worksheet.range('A1').value=pd.concat(data,ignore_index=False)
new_workbook.save()
workbook.close()
app.quit()
#案例05 对多个工作薄工指定工作表数据分列
import os
import xlwings as xw
import pandas as pd
file_path='F:\\python_test\\'
file_list=os.listdir(file_path)
app=xw.App(visible=True,add_book=True)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workbook=app.books.open(file_paths)
    worksheet=workbook.sheets['外访回款']
    values=worksheet.range('A1').options(pd.DataFrame,header=1,index=False,expand='table').value
    new_values=values['拆分'].str.split('*',expand=True)
    values['1']=new_values[0]
    values['2']=new_values[1]
    values['3']=new_values[2]
    values.drop(columns=['拆分'],inplace=True)
    worksheet['A1'].options(index=False).value=values
    worksheet.autofit()
    workbook.save()
    workbook.close()
app.quit()
#案例06 批量提取一个工作簿的所有工作表的唯一值
import xlwings as xw
app=xw.App(visible=False,add_book=True)
workbook=app.books.open('F:\\python_test\\报表.xlsx')
data=[]
for i,worksheet in enumerate(workbook.sheets):#遍历工作薄的工作表
    values=worksheet['B2'].expand('down').value#提取所需的列
    data=data+values
data=list(set(data))#set()去重后，用list转为列表，以便于用insert()添加元素
data.insert(0,'name')#增加标题
new_workbook=xw.books.add()#新建book
new_worksheet=new_workbook.sheets.add('name')#新建sheet，名name
new_worksheet['A1'].options(transpose=True).value=data#转置
new_worksheet.autofit()
new_workbook.save()
new_workbook.close()
app.quit()