- # 1、 批量提取一个工作簿中所有工作表的特定数据<br>'''<br>import xlwings as xw<br>import pandas as pd<br>app = xw.App(visible = False, add_book = False)<br>workbook = app.books.open('采购.xlsx') # 打开工作簿<br>worksheet = workbook.sheets # 列出工作簿中的所有工作表<br>data = [] # 创建一个空列表用于存放数据<br>for i in worksheet:<br> values = i.range('A1').expand().options(pd.DataFrame).value # 读取当前工作表的所有数据<br> filtered = values[values['采购物品'] == '复印纸'] # 提取采购物为复印纸的行数据<br> if not filtered.empty: # 判断提取行数据是否为空<br> data.append(filtered) # 提取数据追加到列表中<br>new_workbook = xw.books.add() # 新建工作簿<br>new_worksheet = new_workbook.sheets.add('复印纸') # 新建工作簿中新增名为复印纸的工作表<br>new_worksheet.range('A1').value = pd.concat(data, ignore_index = False) # 将提取的数据写入复印纸工作表中<br>new_workbook.save('复印纸.xlsx')<br>workbook.close()<br>app.quit()<br>'''<br>import os<br><br># ### concat(objs,axis=0,join='outer',join_axes=None,ignore_index=False,keys=None,levels=None,names=None,verify_integrity=False,copy=True)<br># objs:要拼接的数据对象<br># axis：拼接时所依据的轴，如果为0，则沿着行拼接，为1，则沿着列拼接<br># join：拼接的方式，默认outer<br># join_index:index对象列表<br># ignore_index：默认为FALSE，如为Ture，忽略原有索引，生成新的数字序列作为新索引<br># keys：序列，默认空。使用传递的键作为最外层构建层次索引，如果为多索引，应使用元组<br># levels：序列列表，默认值空，用于构建唯一值<br># names：列表，默认值为空，结果层次索引中的级别名称<br># verify_integrity:默认FALSE，用于检查新拼接的轴是否包含重复值<br># copy：默认TRUE，如果为FALSE，则不执行非必要的数据复制<br><br># 2、批量提取一个工作簿中所有工作表的列数据<br>'''<br>import xlwings as xw<br>import pandas as pd<br>app = xw.App(visible = False, add_book = False)<br>workbook = app.books.open('采购表.xlsx')<br>worksheet = workbook.sheets<br>column = ['采购日期', '采购金额'] # 指定要提取的列的标题<br>data = [] <br>for i in worksheet:<br> values = i.range('A1').expand().options(pd.DataFrame, index = False).value<br> filtered = values[column] # 根据前面指定的列标题提取数据<br> data.append(filtered)<br>new_workbook = xw.books.add()<br>new_worksheet = new_workbook.sheets.add('提取数据')<br>new_worksheet.range('A1').value = pd.concat(data, ignore_index = False).set_index(column[0])<br>new_workbook.save('提取表.xlsx')<br>workbook.close()<br>app.quit()<br>'''<br><br># 3、 在多个工作簿的指定工作表中批量追加行数据<br>'''<br>import os<br>import xlwings as xw<br>newContent = [['双肩包','64','110'],['腰包','23','58']] # 要追加的行数据<br>app = xw.apps.add()<br>file_path = '分部信息'<br>file_list = os.listdir(file_path)<br>for i in file_list:<br> if os.path.splitext(i)[1] == '.xlsx':<br> workbook = app.books.open(file_path + '\\' + i)<br> worksheet = workbook.sheets['产品分类表'] # 指定要追加行数据的工作表<br> values = worksheet.range('A1').expand() # 读取原有数据<br> number = values.shape[0] # 获取原有数据的行数<br> worksheet.range(number + 1, 1).value = newContent # 将前面指定的行数追加到原有数据的下方<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br># 4、 对多个工作簿中指定工作表的数据进行分列<br>'''<br>import os<br>import xlwings as xw<br>import pandas as pd<br>file_path = '产品记录表'<br>file_list = os.listdir(file_path)<br>app = xw.App(visible = False, add_book = False)<br>for i in file_list:<br> if i.startswith('~$'): # 判断是否有文件以~$开头<br> continue # 如果有，则跳过<br> file_paths = os.path.join(file_path, i) # 将文件夹路径和名称拼接成工作表的完整路径<br> workbook = app.books.open(file_paths) # 打开工作簿<br> worksheet = workbook.sheets['规格表'] # 指定要处理的工作表<br> values = worksheet.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table') # 读取指定工作表数据<br> new_values = values['规格'].str.split('*', expand = True) # 根据*拆分规格列<br> values['长(mm)'] = new_values[0]<br> values['宽(mm)'] = new_values[1]<br> values['高(mm)'] = new_values[2]<br> values.drop(columns=['规格'], inplace = True) # 删除规格列<br> worksheet['A1'].options(index = False).value = values # 用分列后的数据替换工作表中的原有数据<br> worksheet.autofit() # 根据数据内容自动调整工作表的行高列宽<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br># ### Series.str.split(pat=None,n=-1,expand=False)<br># pat:指定分隔符，默认为空格；<br># n：指定拆分的次数，1为在第1个分隔符拆分，2为1、2个分隔符拆分<br># expand:TRUE则为DataFrame，FALSE，则为Series<br># ### DataFrame.drop(labels=None,axis=0,index=None,columns=None,inplace=False)<br># labels:要删除的行、列的名称<br># axis：默认为0，表示删除列。如为1，则删除行<br># index：指定要删除的行<br># columns：指定要删除的列<br># inplace：默认False，表示删除不改变原DataFrame，返回执行删除操作后的新DataFrame。如为True，直接在原DataFrame上删除，无法恢复。<br><br># 5、批量合并多个工作簿中指定工作表中的列数据<br>'''<br>import os<br>import xlwings as xw<br>import pandas as pd<br>file_path = '产品记录表'<br>file_list = os.listdir(file_path)<br>app = xw.App(visible = False, add_book = False)<br>for i in file_list:<br> if i.startswith('~$'):<br> continue<br> file_paths = os.path.join(file_path, i)<br> workbook = app.books.open(file_paths)<br> worksheet = workbook.sheets['规格表']<br> values = worksheet.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table').value<br> # 合并列数据<br> values['规格'] = values['长(mm)'].astype('str') + values['宽(mm)'].astype('str') + '*' + values['高(mm)'].astype('str')<br> values.drop(columns=['长(mm)'], inplace= True) # 删除标题为长（mm）的列<br> values.drop(columns=['宽(mm)'], inplace= True)<br> values.drop(columns=['高(mm)'], inplace= True)<br> worksheet.clear() # 清楚工作表规格表中原有数据<br> worksheet['A1'].options(index = False).value = values # 将处理好的数据写入工作表<br> worksheet.autofit()<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br><br># 6、多个工作簿中指定工作表的列数据拆分成多行<br>'''<br>import os<br>import xlwings as xw<br>import pandas as pd<br>file_path = '产品记录表'<br>file_list = os.listdir(file_path)<br>app = xw.App(visible = False, add_book = False)<br>for i in file_list:<br> if i.startswith('~$'):<br> continue<br> file_paths = os.path.join(file_path,i)<br> workbook = app.books.open(file_paths)<br> worksheet = workbook.sheets['规格表']<br> values = worksheet.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table').value<br> new_values = values['规格'].str.split('*', expand = True)<br> values['长(mm)'] = new_values[0]<br> values['宽(mm)'] = new_values[1]<br> values['高(mm)'] = new_values[2]<br> values.drop(columns=['规格'], inplace= True)<br> values = values.T # 转换数据的行列<br> values.columns = values.iloc[0]<br> values.index.name = values.iloc[0].index.name<br> values.drop(values.iloc[0].index.name, inplace= True)<br> worksheet.clear()<br> worksheet['A1'].value = values<br> worksheet.autofit()<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br><br># 7、批量提取一个工作簿中所有工作表的唯一值<br>### 将这6个工作表中的书名提取出来，但是不能有重复的书名<br>'''<br>import xlwings as xw<br>app = xw.App(visible= True, add_book = False)<br>workbook = app.books.open('上半年销售统计表.xlsx')<br>data = [] #创建一个空列存放书名数据<br>for i, worksheet in enumerate(workbook.sheets): # 遍历工作簿中的工作表<br> values = worksheet['A2'].expand('down').value # 提取当前工作表中的书名数据<br> data = data + values<br>data = list(set(data)) # 对列表中的书名数据进行去重操作<br>data.insert(0, '书名') # 在去重后的书名数据前添加列标题“书名”<br>new_workbook = xw.books.add() # 新建工作簿<br>new_worksheet = new_workbook.sheets.add('书名') # 新工作簿中新增名为“书名”的工作表<br>new_worksheet['A1'].options(transpose = True).value = data # 将处理好的书名数据写入新工作表<br>new_worksheet.autofit()<br>new_workbook.save('书名.xlsx')<br>workbook.close()<br>app.quit()<br>'''<br>### insert(index,obj)<br># index:要插入元素的位置<br># obj：要插入的元素<br><br># 8、 批量提取一个工作簿中所有工作表的唯一值并汇总<br>'''<br>import xlwings as xw<br>app = xw.App(visible = True, add_book = False)<br>wb = app.books.open('销售统计表.xlsx')<br>data = list() # 创建空列表用于存放书名和销售明细<br>for i, sht in enumerate(wb.sheets):<br> values = sht['A2'].expand('table').value<br> data = data + values<br>sales = dict() # 创建空字典存放书名和销量汇总<br>for i in range(len(data)): #遍历书名和销量明细<br> name = data[i][0] # 获取书名<br> sale = data[i][1] # 获取销量<br> if name not in sales:<br> sales[name] = sale # 如果不存在，字典中添加此书名的销量记录<br> else:<br> sales[name] += sale # 如果存在，计算书名累加销量<br>dictlist = list()<br>for key, value in sales.items():<br> temp = [key, value] # 列出书名和累积销量<br> dictlist.append(temp)<br>dictlist.insert(0, ['书名','销量']) # 获取的数据前添加列标题书名和销量<br>new_workbook = xw.books.app()<br>new_worksheet = new_workbook.sheets.add('销量统计')<br>new_worksheet['A1'].value = dictlist<br>new_worksheet.autofit()<br>new_workbook.save('销售统计.xlsx')<br>wb.close<br>app.quit()<br>'''<br><br># 9、调整多个工作簿的行高列宽<br>'''<br>import os<br>import xlwings as xw<br>file_path = 'e:\\table\销售表'<br>file_list = os.listdir(file_path)<br>app = xw.App(visible = False, add_book = False)<br>for i in file_list:<br> if i.startswith('~$'):<br> continue<br> file_paths = os.path.join(file_path, i)<br> for j in workbook.sheets:<br> value = j.range('A1').expand('table') # 在工作表选择要调整行高列宽的区域<br> value.column_width = 12 # 列宽调整为12个字符宽度<br> value.row_height = 20 # 将行高设置为20<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br><br># 10、一个工作簿中所有表的行高列宽<br>'''<br>import xlwings as xw<br>app = xw.App(visible = False, add_book = False)<br>workbook = app.books.open('e:\\table\*.xlsx')<br>for i in workbook.sheets:<br> value = i.range('A1').expand('table')<br> value.column_width = 12<br> value.row_height = 20<br>workbook.save()<br>app.quit()<br>'''<br><br># 11、批量更改多个工作簿的数据格式<br>'''<br>import os<br>import xlwings as xw<br>file_path = '采购'<br>file_list = os.listdir(file_path)<br>app = xw.App(visible = False, add_book = False)<br>for i in file_list:<br> if i.startswith('~$'):<br> continue<br> file_paths = os.path.join(file_path, i)<br> workbook = app.books.open(file_paths)<br> for j in workbook.sheets:<br> row_num = j['A1'].current_region.last_cell.row # 获取工作表数据区域最后一行的行号<br> j['A2:A{}'.format(row_num)].number_format = 'm/d' # A列的采购如期全部更改为月/日格式<br> j['D2:D{}'.format(row_num)].number_format = '￥#，##0.00' # D列的采购金额更改为货币符号和两位小数格式<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br><br># 12、批量更改多个工作簿的外观格式<br>'''<br>import os<br>import xlwings as xw<br>file_path = '销售表'<br>file_list = os.listdir(file_path)<br>app = xw.App(visible = False, add_book = False)<br>for i in file_list:<br> if i.startswith('~$'):<br> continue<br> file_paths = os.path.join(file_path, i)<br> workbook = app.books.open(file_paths)<br> for j in workbook.sheets:<br> j['A1:H1'].api.Font.Name = '宋体' # 标题行为宋体<br> j['A1:H1'].api.Font.Size = 10<br> j['A1:H1'].api.Font.Bold = True # 字体加粗<br> j['A1:H1'].api.Font.Color = xw.utils.rgb_to_int((255,255,255)) # 字体颜色为白色<br> j['A1:H1'].color = xw.utils.rgb_to_int((0,0,0)) # 单元格填充颜色为黑色<br> j['A1:H1'].api.HorizontalAlignment = xw.constants.VAlign.xlHAlignCenter # 工作表标题行的水平对齐方式居中<br> j['A1:H1'].api.VerticalAlignment = xw.contants.VAlign.xlVAlignCenter # 工作表标题行垂直对齐方式居中<br> j['A2'].expand('table').api.Font.Name = '宋体'<br> j['A2'].expand('table').api.Font.Size = 10<br> j['A2'].expand('table').api.HorizontalAlignment = xw.constants.HAlign.xlHAlignLeft # 设置正文水平对齐方式靠左<br> j['A2'].expand('table').api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter # 设置正文垂直对齐方式居中<br> for cell in j['A1'].expand('table'):<br> for b in range(7,12):<br> cell.api.Borders(b).LineStyle =1 # 边框线型为1<br> cell.api.Borders(b).Weight = 2 # 单元格边框粗细2<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br><br># 13、批量替换多个工作簿的行数据<br>'''<br>import os<br>import xlwings as xw<br>file_path = '分部信息'<br>file_list = os.listdir(file_path)<br>app = xw.App(visible = False, add_book = False)<br>for i in file_list:<br> if i.startswith('~$'):<br> continue<br> file_paths = os.path.join(file_path, i)<br> workbook = app.books.open(file_paths)<br> for j in workbook.sheets:<br> value = j['A2'].expand('table').value<br> for index, val in enumerate(value): # 按行遍历工作表数据<br> if val == ['背包', 16, 65]:<br> value[index] = ['双肩包', 36, 46]<br> j['A2'].expand('table').value = value<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br><br># 14、批量升序工作簿中所有工作表<br>'''<br>import xlwings as xw<br>import pandas as pd<br>app = xw.App(visible = False, add_book = False)<br>workbook = app.books.open('销售表.xlsx')<br>worksheet = workbook.sheets<br>for i in worksheet:<br> values = i.range('A1').expand('table').options(pd.DataFrame).value # 读取当前工作表数据，并转换为DataFrame格式<br> result = values.sort_values(by = '销售利润') # 对销售利润进行升序排列<br> i.range('A1').value = result # 将排序结果写入当前工作表，替换原有数据<br>workbook.save()<br>workbook.close()<br>app.quit()<br>'''<br>### sort_values(by='##',axis=0,ascending=True,inplace=False,na_position='last')<br> # by:要排序的列名或索引值<br> # axis：省略或者0或者index，则按照by指定的列中的数据排序；如为1或columns，则按照by指定的索引的数据排序<br> # ascending：排序方式。省略或者TRUE，为升序；如果为false，则做降序排列<br> # inplace：省略或者false，不替换；如果TRUE，则用排序后的数据替换原来数据<br> # na_pasition：空值的显示位置，如果为first，将空值放在列的首位；如果为last，则放在列的末尾<br>'''<br>result = values.sort_values(by = '销售利润', ascending=False) # 对销售利润列进行降序排序<br>'''<br><br># 15、批量排序多个工作簿中的数据<br>'''<br>import xlwings as xw<br>import pandas as pd<br>import os<br>app = xw.App(visible = False, add_book = False)<br>file_path = '产品销售表'<br>file_list = os.listdir(file_path)<br>for i in file_list:<br> if os.path.splitext(i)[1] == 'xlsx':<br> workbook = app.books.open(file_path + '\\' + i)<br> worksheet = workbook.sheets<br> for j in worksheet:<br> values = j.range('A1').expand('table').options(pd.DataFrame).value<br> result = values.sort_values(by = '销售利润')<br> j.range('A1').value = result<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br><br># 16、筛选一个工作簿中所有工作表数据<br>'''<br>import xlwings as xw<br>import pandas as pd<br>app = xw.App(visible = False, add_book = False)<br>workbook = app.books.open('采购表.xlsx')<br>worksheet = workbook.sheets<br>table = pd.DataFrame() # 创建一个空DataFrame<br>for i,j in enumerate(worksheet):<br> # 读取当前工作表的数据<br> values = j.range('A1').options(pd.DataFrame, header = 1, index= False, expand= 'table').value<br> # 调整列的顺序<br> data = values.reindex(columns = ['采购物品', '采购日期', '采购数量', '采购金额'])<br> # 将调整列顺序后的数据合并到前面创建的DataFrame中<br> table = table.append(data, ignore_index = True)<br>table = table.groupby('采购物品') # 根据采购物品列筛选数据<br>new_workbook = xw.books.add() # 新建一个工作簿<br>for idx, group in table:<br> new_worksheet = new_workbook.sheets.add(idx) # 在新工作簿中新增工作表，以物品名称作为工作表名<br> new_worksheet['A1'].options(index = False).value = group # 新工作表中写入当前物品的所有明细<br> last_cell = new_worksheet['A1'].expand('table').last_cell # 获取当前工作表数据区域右下角的单元格<br> last_row = last_cell.row<br> last_column = last_cell.column<br> last_column_letter = chr(64 + last_column) # 将数据区域最后一列的数字转换为改列的列标字母<br> sum_cell_name = '{}{}'.format(last_column_letter, last_row+1) # 获取数据区域右下角单元格下方的单元格位置<br> sum_last_row_name = '{}{}'.format(last_column_letter, last_row) # 获取数据区域右下角单元格位置<br> formula = ' =SUM({}2:{})'.format(last_column_letter, sum_last_row_name) # 根据前面获取单元格位置构造Excel公式，对求购金额求和<br> new_worksheet[sum_cell_name].formula = formula # 将求和公式写入数据区域右下角单元格下方单元格中<br> new_worksheet.autofit() # 自动调整工作表的行高和列宽<br>new_worksheet.save('采购分类表.xlsx')<br>workbook.close()<br>app.quit()<br>'''<br># reindex(index = **, columns = **, fill_value=0)<br># index：要改变位置的行，**为列表<br># columns：要改变位置的列，**为列表<br># fill_value：可选参数，前面两个不存在时，可用该参数定义如何填充缺失值<br><br># chr(64+列号) 可以将列号转换为列表<br><br># 17、在一个工作簿中筛选单一类别数据<br>'''<br>import xlwings as xw<br>import pandas as pd<br>app = xw.App(visible = False, add_book = False)<br>workbook = app.books.open('采购表.xlsx')<br>worksheet = workbook.sheets<br>table = pd.DataFrame()<br>for i,j in enumerate(worksheet):<br> values = j.range('A1').options(pd.DataFrame, header=1, index = False, expand = 'table').value<br> data = values.reindex(columns = ['采购物品','采购日期','采购数量','采购金额'])<br> table = table.append(data, ignore_index = True) # 将多个工作表数据合并到一个DataFrame中<br>product = table[table['采购物品'] == '保险箱'] # 筛选采购物品是保险箱的数据<br>new_workbook = xw.books.add()<br>new_worksheet = new_workbook.sheets.add('保险箱')<br>new_worksheet['A1'].options(index = False).value = product # 将筛选出数据写入工作表（index=False为删除索引列）<br>new_worksheet.autofit()<br>new_workbook.save('保险箱.xlsx')<br>new_workbook.close()<br>app.quit()<br>'''<br><br># 18、对多个工作簿的工作表进行分类汇总<br>'''<br>import os<br>import xlwings as xw<br>import pandas as pd<br>app = xw.App(visible = False, add_book = False)<br>file_path = '销售表'<br>file_list = os.listdir(file_path)<br>for i in file_list:<br> if os.path.splitext(i)[1] == 'xlsx':<br> workbook = app.books.open(file_path + '\\' + i)<br> worksheet = workbook.sheets<br> for j in worksheet:<br> values = j.range('A1').expand('table').options(pd.DataFrame).value # 读取当前工作表的数据<br> values['销售利润'] = values['销售利润'].astype('float') # 转换销售利润数据类型<br> result = values.groupby('销售区域').sum() # 根据销售区域列对数据进行分类汇总，求和<br> j.range('J1').value = result['销售利润'] # 将各个销售区域销售利润汇总结果写入当前工作表<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br># groupby()函数后接的sum()函数用于进行求和汇总，还可以使用其他函数完成其他类型的汇总运算。常用的有：用mean()函数求平均值，<br># 用count()函数统计个数，用max()函数求最大值，用min()函数求最小值。<br><br># 19、批量分类汇总多个工作簿中的指定工作表<br>'''<br>import os<br>import xlwings as xw<br>import pandas as pd<br>app = xw.App(visible = False, add_book = False)<br>file_path = '销售表'<br>file_list = os.listdir(file_path)<br>for i in file_list:<br> if os.path.splitext(i)[1] == 'xlsx':<br> workbook = app.books.open(file_path + '\\' + i)<br> worksheet = workbook.sheets['销售记录表'] # 指定要分类汇总的工作表<br> values = worksheet.range('A1').expand('table').options(pd.DataFrame).value<br> values['销售利润'] = values['销售利润'].astype('float')<br> result = values.groupby('销售区域').sum()<br> worksheet.range('J1').value = result['销售利润']<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br><br># 20/ 将多个工作簿数据分类汇总到一个工作簿<br>'''<br>import os<br>import xlwings as xw<br>import pandas as pd<br>app = xw.App(visible = False, add_book = False)<br>file_path = '销售表'<br>file_list = os.listdir(file_path)<br>collection = []<br>for i in file_list:<br> if os.path.splitext(i)[1] == 'xlsx':<br> workbook = app.books.open(file_path + '\\' + i)<br> worksheet = workbook.sheets['销售记录表']<br> values = worksheet.range('A1').expand('table').options(pd.DataFrame).value<br> filtered = values[['销售区域', '销售利润']] # 只保留两列数据<br> collection.append(filtered)<br> workbook.close()<br>new_values = pd.concat(collection, ignore_index = False).set_index('销售区域')<br>new_values['销售利润'] = new_values['销售利润'].astype('float')<br>result = new_values.groupby('销售区域').sum()<br>new_workbook = app.books.add()<br>sheet = new_workbook.sheets(0)<br>sheet.range('A1').value = result<br>new_workbook.save('汇总.xslx')<br>app.quit()<br>'''<br><br># 21、对一个工作簿中所有表分别求和<br>'''<br>import xlwings as xw<br>import pandas as pd<br>app = xw.App(visible = False, add_book = False)<br>workbook = app.books.open('采购.xlsx')<br>worksheet = workbook.sheets<br>for i in worksheet:<br> values = i.range('A1').expand('table')<br> data = values.options(pd.DataFrame).value<br> sums = data['采购金额'].sum() # 创建的DataFrame中对采购金额进行求和<br> column = values.valuep[0].index('采购金额') + 1 # 获取采购金额列的序号<br> row = values.shape[0] # 获取数据区域最后一行行号<br> i.range(row+1, column).value = sums # 将求和结果写入采购金额列最后一个单元格下方的单元格<br>workbook.save()<br>workbook.close()<br>app.quit()<br>'''<br># index(obj,start,end)<br># obj:要查找的元素<br># start:查找的起始位置<br># end：可选，查找的结束位置<br><br># 22、将所有求和结果写入固定单元格<br>'''<br>import xlwings as xw<br>import pandas as pd<br>app = xw.App(visible =False, add_book = False)<br>workbook = app.books.open('采购表.xlsx')<br>worksheet = workbook.sheets<br>for i in worksheet:<br> values = i.range('A1').expand('table').options(pd.DataFrame)<br> sums = values['采购金额'].sum()<br> i.range('F1').value = sums # 将当前工作表中的数据求和结果写入当前工作表的单元件中<br>workbook.save()<br>workbook.close()<br>app.quit()<br>'''<br><br># 23、批量统计工作表的最大值最小值<br>'''<br>file_path = '产品销售统计表'<br>file_list = os.listdir(file_path)<br>for j in file_list:<br> if os.path.splitext(j)[1] == '.xslx':<br> workbook = app.books.open(file_path + '\\' + j)<br> worksheet = workbook.sheets<br> for i in worksheet:<br> values = i.range('A1').expand('table').options(pd.DataFrame).value<br> max = values['销售利润'].max()<br> min = values['销售利润'].min()<br> i.range('I1').value = '最大销售利润'<br> i.range('J1').value = max<br> i.range('I2').value = '最小销售利润'<br> i.range('J2').value = min<br> workbook.save()<br> workbook.close()<br>app.quit()<br>'''<br><br># 24、批量统计工作簿中所有表的最大最小值<br>'''<br>workbook = xw.Book('产品销售统计表.xlsx')<br>worksheet = workbook.sheets<br>for i in worksheet:<br> values = i.range('A1').expand('table').options(pd.DataFrame).value<br> max = values['销售利润'].max()<br> min = values['销售利润'].min()<br> i.range('I1').value = '最大利润'<br> i.range('J1').value = max<br> i.range('I2').value = '最小利润'<br> i.range('J2').value = min<br>workbook.save()<br>workbook.close()<br>app.quit()<br>'''
|
