Python xlwings模块简单使用
xlwings 安装
xlwings excel-book-打开-新建
xlwings sheet-api
xlwings 操作数据
xlwings 读取数据
xlwings 安装
操作excel模块的比较
xlwings官方文档
基本操作
安装
pip install xlwings
xlwings 打开-新建
打开excel文件
多个excel操作
import time import xlwings as xw """ visible Ture：可见excel False：不可见excel add_book True:打开excel并且新建工作簿 False：不新建工作簿 """ app = xw . App ( visible = True , add_book = False ) wb = app . books . open ( './a.xlsx' ) wb2 = app . books . open ( "./b.xlsx" ) # 打印当前活跃的books- print ( app . books . active ) # 打印当前的book-Books([,]) print ( app . books ) wb . save ( ) # 关闭a.xlsx文件-指定操作的excel # wb.close() app . books [ 0 ] . close ( ) time . sleep ( 3 ) app . quit ( )
新建excel文件
import xlwings as xw app = xw . App ( visible = True , add_book = False ) # 添加一个新的工作薄 wb = app . books . add ( ) # 保存文件 wb . save ( './a2.xlsx' ) wb . close ( ) app . quit ( )
xlwings sheet-api
新建sheet
wb.sheets.add("sheet2")
删除sheet
wb . sheets [ "sheet2" ] . delete ( )
查看当前表格名
wb . sheets [ 1 ] . name
重命名表格sheet
wb . sheets [ 1 ] . name = “abc”
清空整张表格的内容和格式
wb . sheets [ 1 ] . clear ( )
查看当前活跃的sheet
wb . sheets . active sh = wb . sheets . active sh . range ( "A1" ) . value = "A1"
激活指定的sheet
wb . sheets [ "sheet1" ] . activate ( )
xlwings 操作数据
引用单元格
import xlwings as xw import time app = xw . App ( visible = True , add_book = False ) wb = app . books . open ( "./a.xlsx" ) sht = wb . sheets [ "sheet1" ] # TODO 待输入-见下面示例 # A1 单元格 wb . sheets [ "sheet1" ] . range ( "A1" ) # A2 单元格 sht . range ( "A2" ) # A3单元格-xw-当前活跃的app-book-sheet xw . Range ( "A3" ) # A4单元格 rng_a4 = sht [ "A4" ] # A1:B3 单元格 rng_a1_b3 = sht [ "A1:B3" ] rng_a1_b3_2 = sht . range ( "A1:B3" ) xw . Range ( ( 1 , 1 ) , ( 3 , 2 ) ) # C1单元格 rng_c1 = sht [ 0 , 2 ] time . sleep ( 3 ) wb . save ( ) wb . close ( ) app . quit ( )
写入数据
单个单元格输入
sht . range ( "A1" ) . value = "A1"
输入行-- 在A2输入1,B2输入2
sht . range ( "A2" ) . value = [ 1 , 2 ]
输入行-A3-A4-A5赋值
sht . range ( "A3" ) . options ( transpose = True ) . value = [ "行" , "行" , "行" ]
输入表格
sht . range ( 'A4' ) . options ( expand = 'table' ) . value = [ [ 1 , 2 ] , [ 3 , 4 ] ] sht . range ( "A4:B5" ) . value = [ [ 1 , 2 ] , [ 3 , 4 ] ]
xlwings 读取数据
读取
sht . range ( "A1" ) . value sht . range ( "A1:A3" ) . value sht . range ( "A1:B3" ) . value xw . Range ( "A1" ) . value
