xbot.excel.WorkBook
概述
workbook接口主要是对工作簿的处理,如获取sheet页、激活sheet页、创建sheet页、保存为excel文件、另存为excel文件、执行宏、关闭工作簿等
详情
get_active_sheet
获取工作簿中激活的sheet页
get_active_sheet(self)
参数
无
返回值
- WorkSheet:sheet页对象
示例1
获取excel文件'D:\test.xlsx'中当前激活的sheet页
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
worksheet = workbook.get_active_sheet()
get_sheet_by_index
获取工作簿中指定位置的sheet页
get_sheet_by_index(self, index)
参数
- index:目标sheet页在工作簿中的索引位置,从1开始计数
返回值
- WorkSheet:sheet页对象
示例1
获取excel文件'D:\test.xlsx'中第一个sheet页
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
worksheet = workbook.get_sheet_by_index(1)
get_sheet_by_name
获取工作簿中指定名称的sheet页
get_sheet_by_name(self, name)
参数
- name:目标sheet页的名称
返回值
- WorkSheet:sheet页对象
示例1
获取excel文件'D:\test.xlsx'中名称为 'Sheet1' 的sheet页
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
worksheet = workbook.get_sheet_by_name('Sheet1')
create_sheet
在工作簿中创建新的sheet页
create_sheet(self, name, create_way)
参数
- name:sheet页名称
- create_way:添加方式
- 'first':作为第一个sheet页
- 'last':作为最后一个sheet页
返回值
- WorkSheet:sheet页对象
示例1
打开excel文件'D:\test.xlsx',并追加一个名称为 'SheetTest' 的sheet页
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
worksheet = workbook.create_sheet('SheetTest', 'last')
active_sheet_by_index
激活工作簿中指定位置的sheet页
active_sheet_by_index(self, index)
参数
- index:目标sheet页在工作簿中的索引位置,从1开始计数
返回值
无
示例1
激活excel文件'D:\test.xlsx'中第一个sheet页
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.active_sheet_by_index(1)
active_sheet_by_name
激活工作簿中指定名称的sheet页
active_sheet_by_name(self, name)
参数
- name:目标sheet页的名称
返回值
无
示例1
激活excel文件'D:\test.xlsx'中名称为 'Sheet1' 的sheet页
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.active_sheet_by_name('Sheet1')
save
将工作簿保存为excel文件
save(self)
参数
无
返回值
无
示例1
打开excel文件'D:\test.xlsx'后保存
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.save()
save_as
将工作簿另存为excel文件
save_as(self, filename)
参数
- filename:另存为路径
返回值
无
示例1
打开excel文件'D:\test.xlsx'后另存为'D:\test1.xlsx'
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.save_as('D:\\test1.xlsx')
close
关闭工作簿
close(self)
参数
无
返回值
无
示例1
打开excel文件'D:\test.xlsx'后关闭
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.close()
execute_macro
执行宏
execute_macro(self, macro)
参数
- macro:宏名称
返回值
无
示例1
打开excel文件'D:\test.xlsx'后执行名称为'macro1'的宏
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.execute_macro('macro1')
get_all_sheets
获取所有的Sheet页
get_all_sheets(self)
参数
无
返回值
- List[WorkSheet]:sheet页对象列表
示例1
获取excel文件'D:\test.xlsx'所有Sheet页对象
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.get_all_sheets()
delete_sheet
删除Sheet页
delete_sheet(self, name)
参数
- name:Sheet页名称
返回值
无
示例1
删除excel文件'D:\test.xlsx'的Sheet1页
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.delete_sheet('Sheet1')
copy_sheet
拷贝Sheet页
copy_sheet(self, name, new_name)
参数
- name:待拷贝的Sheet页名称
- new_name:新的Sheet页名称
返回值
无
示例1
拷贝excel文件'D:\test.xlsx'的Sheet1页到新的Sheet2页
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.copy_sheet('Sheet1', 'Sheet2')
copy_sheet_to_workbook
拷贝Sheet页到指定Workbook
copy_sheet_to_workbook(self, name, workbook, new_name)
参数
- name:待拷贝的Sheet页名称
- workbook:目标Workbook名称
- new_name:新的Sheet页名称
返回值
无
示例1
拷贝excel文件'D:\test.xlsx'的Sheet1页到excel文件'D:\test1.xlsx'的Sheet2页
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook1 = excel.open('D:\\test1.xlsx', kind = 'office', visible = True)
workbook.copy_sheet_to_workbook('Sheet1', workbook1, 'Sheet2')
get_selected_range
获取当前页的选中区域
get_selected_range(self)
参数
无
返回值
- 四元组:(起始单元格行号, 起始单元格列名, 终止单元格行号, 终止单元格列名)
示例1
获取excel文件'D:\test.xlsx'的当前页的选中区域
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.get_selected_range()
rename_sheet
重命名Sheet页
rename_sheet(self, name, new_name)
参数
- name:待修改的Sheet页名称
- new_name:修改后的Sheet页名称
返回值
无
示例1
修改excel文件'D:\test.xlsx'的Sheet1页名称为'测试页面1'
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.rename_sheet('Sheet1', '测试页面1')
refresh_data
刷新数据
refresh_data(self)
参数
无
返回值
无
示例1
刷新excel文件'D:\test.xlsx'数据
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.refresh_data()
create_pivot_table
创建数据透视表
create_pivot_table(self, setting, source)
参数
- setting:数据透视表配置
- source:数据源设置
返回值
无
示例1
在excel文件'D:\test.xlsx'中创建数据透视表
(注:pivot_setting是使用影刀Excel拾取工具从Excel文件中拾取数据透视表数据)
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
pivot_setting = '{"sourceType":1,"sheetName":"Sheet6","tableRange":"A1:B7","name":"数据透视表8","properties":[{"name":"ColumnGrand","value":true},{"name":"RowGrand","value":true},{"name":"SubtotalHiddenPageItems","value":false},{"name":"InGridDropZones","value":false},{"name":"LayoutRowDefault","value":2},{"name":"DisplayFieldCaptions","value":true},{"name":"ShowDrillIndicators","value":true},{"name":"DisplayContextTooltips","value":true},{"name":"SortUsingCustomLists","value":true},r{"name":"DisplayImmediateItems","value":false},{"name":"FieldListSortAscending","value":false},{"name":"ShowValuesRow","value":false}],"fields":[{"name":"职称","index":"1","formula":null,"customName":"职称","subtotals":[true,false,false,false,false,false,false,false,false,false,false,false],"props":[{"name":"Orientation","value":3},{"name":"ShowAllItems","value":false},{"name":"EnableMultiplePageItems","value":false}]},{"name":"姓名","index":"1","formula":null,"customName":"姓名","subtotals":[true,false,false,false,false,false,false,false,false,false,false,false],"props":[{"name":"Orientation","value":1},{"name":"ShowAllItems","value":false},{"name":"EnableItemSelection","value":true}]},{"name":"工号","index":"1","formula":"","customName":"求和项:工号","subtotals":[true,false,false,false,false,false,false,false,false,false,false,false],"props":[{"name":"Orientation","value":4},{"name":"ShowAllItems","value":false},{"name":"Calculation","value":-4143},{"name":"Function","value":-4157},{"name":"NumberFormat","value":"General"}]}]}'
workbook.create_pivot_table(pivot_setting, 'Sheet1!$C$1:$E$4')
refresh_pivot_table
刷新数据透视表
refresh_pivot_table(self, name_or_index, sheet_name=None)
参数
- name_or_index:数据透视表名称或位置(从1开始)
- sheet_name:透视表所在Sheet页名称, 默认为当前激活的Sheet页
返回值
无
示例1
在excel文件'D:\test.xlsx'中刷新数据透视表
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.refresh_pivot_table('1')
filter_pivot_table
筛选数据透视表
filter_pivot_table(self, sheet_name, name_or_index, field_name, select_type, filter_value_list)
参数
- sheet_name:透视表所在Sheet页名称, 默认为当前激活的Sheet页
- name_or_index:数据透视表名称或位置(从1开始)
- field_name:透视表筛选器字段名称
- select_type:选择方式,包括all,none和partial
- filter_value_list:透视表筛选器选择项内容
返回值
无
示例1
在excel文件'D:\test.xlsx'中Sheet1页刷新数据透视表
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.filter_pivot_table('Sheet1', '1', '职务', 'partial', ['经理', '文员'])
export_to_pdf
导出PDF文件
export_to_pdf(self, pdf_name, sheet_name=None, all_sheets=False, override=True)
参数
- pdf_name:透视表所在Sheet页名称, 默认为当前激活的Sheet页
- sheet_name:要导出的Sheet页,默认为当前激活的Sheet名称
- all_sheets:是否导出全部Sheet页
- override:若导出的PDF文件已存在,是否选择覆盖
返回值
无
示例1
将excel文件'D:\test.xlsx'中Sheet1页导出到'D:\test.pdf'
from xbot import excel
def main(args):
workbook = excel.open('D:\\test.xlsx', kind = 'office', visible = True)
workbook.export_to_pdf('D:\\test.pdf', 'Sheet1')