xlwings库的常规操作
程序员文章站
2022-03-11 11:51:46
...
xlwings的使用，必须先导入
import xlwings
在程序中使用xlwings，必须首先创建一个app，在这个app下才能继续创建一个或多个work_book，在work_book下才能创建一个或多个work_sheet，这是xlwings的基本操作逻辑。
相对于openpyxl，xlwings多了一步即需要创建一个app。
“”“创建app”""
import xlwings
xlwings_app = xlwings.App(visible=False, add_book=False)
这里有两个参数：
1、visible，表示xlwings在操作excel文件时，用户是否在屏幕上可以看见，此处设置False是为了加快程序运行速度
2、add_book，表示在创建excel文件时，是否创建第二个excel文件，建议设置为False，加快程序运行速度
“”“创建工作本”""
import xlwings
xlwings_app = xlwings.App(visible=False, add_book=False)
创建excel范例文件的文件名，此处创建了两个文件名，一个是“.xls”文件，一个是“.xlsx”文件
excel_file_name_xlsx = 'xlwings_范例_xlsx.xlsx'
excel_file_name_xls = 'xlwings_范例_xlsx.xls'
创建excel文件，使用add()函数
work_book_xlwings = xlwings_app.books.add()
work_book_xlwings.save(excel_file_name_xlsx) # 创建并保存为.xlsx文件
work_book_xlwings.save(excel_file_name_xls # 创建并保存为.xls文件
work_book_xls.close() # excel文件使用完毕要退出，释放内存
work_book_xlsx.close() # excel文件使用完毕要退出，释放内存
xlwings_app.quit() # app使用完毕要退出，释放内存
“”“打开工作簿”""
import xlwings
xlwings_app = xlwings.APP(visible=False, add_book=False)
创建excel范例文件的文件名，此处创建了两个文件名，一个是“.xls”文件，一个是“.xlsx”文件
excel_file_name_xlsx = 'xlwings_范例_xlsx.xlsx'
excel_file_name_xls = 'xlwings_范例_xlsx.xls'
用.book.open()函数来打开excel文件
work_book_xlsx = xlwings_app.books.open(excel_file_name_xlsx) # 此处打开了一个.xlsx文件
work_book_xls = xlwings_app.books.open(excel_file_name_xls) # 此处打开了一个.xls文件
“”“在工作簿中创建、添加表”""
在excel文件中创建、添加工作表
第一种方法，在默认位置添加一张表，默认位置即在第一张表的前面新增添加一张表：
sheet_name = '新表'
work_sheet_xlsx = work_book_xlsx.sheets.add(sheet_name)
work_sheet_xls = work_book_xls.sheets.add(sheet_name)
第二种方法，在指定位置添加一张表：
sheet_name_1 = 'before_sheet1'
sheet_name_2 = 'after_sheet1'
work_sheet_xlsx = work_book_xlsx.sheets.add(sheet_name_1, before='Sheet1') # 在 .xlsx文件的 Sheet1表 的 前面 增加一张表
work_sheet_xlsx = work_book_xlsx.sheets.add(sheet_name_2, after='Sheet1') # 在 .xlsx文件的 Sheet1表 的 后面 增加一张表
work_sheet_xls = work_book_xlsx.sheets.add(sheet_name_1, before='Sheet1') # 在 .xls文件的 Sheet1表 的 前面 增加一张表
work_sheet_xls = work_book_xlsx.sheets.add(sheet_name_2, after='Sheet1') # 在 .xls文件的 Sheet1表 的 后面 增加一张表
保存文件，否则都是空谈
work_book_xlsx.save(excel_file_name_xlsx)
work_book_xls.save(excel_file_name_xls)
关闭文件，释放内存
work_book_xlsx.close()
work_book_xls.close()
释放app
xlwings_app.quit()
“”“打开工作簿中的工作表”""
第一种方法，根据工作表的名称，打开工作表
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xls = work_book_xls.sheets('Sheet1')
第二种方法，根据工作表的索引值，打开工作表（注意：此处有一个很大的坑）
work_sheet_xlsx = work_book_xlsx.sheets(1)
work_sheet_xls = work_book_xls.sheets(0)
注意，注意，注意，这里有一个很大的坑，xlwings对.xlsx文件中的工作表索引值是从“1”开始的，而对.xls文件中的工作表索引值是从“0”开始的！！！
“”“获取工作簿中所有工作表的名称”""
xlwings对这个功能的支持不是特别好，或者说不是特别方便
sheets_names_from_xlsx = work_book_xlsx.sheets # 注意，这里的.sheets 不带()
sheets_names_from_xls = work_book_xls.sheets # 注意，这里的.sheets 不带()
这里，sheets_names_from_xlsx或sheets_names_from_xls的返回值是一个xlwings.sheets类，需要通过代码才能转换为我们需要的类型，比如将所有工作簿的名字转换为一个字符串列表：
new_sheets_names_from_xlsx_list = []
sheets_names_from_xlsx_list = list(sheets_names_from_xlsx)
for sheet_name in sheets_names_from_xlsx_list:
sheet_name_str = str(sheet_name)
new_name_list = sheet_name_str.split(']')
sheet_name_str = new_name_list[-1][:-1] # 利用字符串的切片，将工作簿的名字剪切出来
new_sheets_names_from_xlsx_list.append(sheet_name_str)
这样，new_sheets_names_from_xlsx_list列表中，存储的就是这个工作簿内所有工作表的名称了。
“”“获取当前工作表的名称”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xls = work_book_xls.sheets('Sheet1')
work_sheet_name_from_xlsx = work_sheet_xlsx.name
print(work_sheet_name_from_xlsx)
work_sheet_name_from_xls = work_sheet_xls.name
print(work_sheet_name_from_xls)
“”“清空工作表内所有内容”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xlsx.clear_contents() # 删除表内的所有内容数据，但保留表格的格式，例如单元格颜色、已合并的单元格
work_sheet_xlsx.clear() # 删除表内的所有内容数据，包括单元格颜色、已合并的单元格将被差分开，但保留了单元格的行高和列宽
“”“删除工作表”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xlsx.delete() # 工作表将被彻底删除掉
“”“读取单元格的内容”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xls = work_book_xls.sheets('Sheet1')
读取一个单元格内的数值
cell_value_from_xlsx = work_sheet_xlsx.range(2, 3).value # 读取work_sheet_xlsx表中，第2行第3列 的单元格的值
cell_value_from_xls = work_sheet_xls.range(2, 3).value # 读取work_sheet_xls表中，第2行第3列 的单元格的值
读取连续单元格内的数值
cells_value_from_xlsx = work_sheet_xlsx.range((2, 2), (3, 3)).value # 读取work_sheet_xlsx表中，第2行第2列至第3行第3列 的单元格的值
“”“写入单元格”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xls = work_book_xls.sheets('Sheet1')
在一个单元格中写入数据
work_sheet_xlsx.range(2, 3).value= 'abcd' # 在 第2行第3列 中写入‘abcd’
在多个单元格中写入数据
test_value = [[1, 2, 3], ['a', 'b', 'c'], ('AA', 'BB', 'CC')]
work_sheet_xlsx.range('B5').value = test_value # 从‘B5’单元格开始横着写入数据，‘B5’单元格是1，‘C5’单元格是2，‘D5’单元格是3， ‘B6’单元格是a，以此类推
work_sheet_xlsx.range(10, 2).value = test_value # 从 第10行第2列 开始横着写入数据，单元格（10,2）是1，单元格（10,3）是2，单元格（10,4）是3，单元格（11,2）是a
“”“获取最大的行数和列数”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xls = work_book_xls.sheets('Sheet1')
cell = work_sheet_xlsx.used_range.last_cell
rows = cell.row # 返回该单元格的 行号
columns = cell.column # 返回该单元格的 列号
这里的cell可以是表格中非连续行数据的单元格，也就说，行与行之间可以存在空白行，列与列之间也可以存在空白列
“”“插入公式”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xls = work_book_xls.sheets('Sheet1')
work_sheet_xlsx.range(19, 2).formula = '=B3' # 在 第19行第2列 的单元格中写入公式‘=B3’
work_sheet_xlsx.range(19, 2).value = '=B3' # 在 第19行第2列 的单元格中写入公式‘=B3’
以上两种方式写入公式的效果是一样的
“”“版本转换，xls格式转为xlsx格式”""
xls_to_xlsx_file_name = 'xls_to_xlsx.xls'
new_name = xls_to_xlsx_file_name + 'x'
第一种方法（不推荐）
work_book_xls_to_xlsx = xlwings_app.books.open(xls_to_xlsx_file_name) # 打开 .xls 文件
work_book_xls_to_xlsx.api.SaveAs(new_name, 51) # 参数 51 为xlsx格式，56为 Excel 97-2003的xls版本
xlwings_app.quit() # 关闭 app
这种方法虽然能够将.xls文件转换为.xlsx文件，但是转换后的.xlsx文件被保存到了‘我的电脑’---->‘文档’这个路径下面了，所以不推荐使用
第二种方法，直接保存（推荐）
work_book_xls_to_xlsx = xlwings_app.books.open(xls_to_xlsx_file_name) # 打开 .xls 文件
work_book_xls_to_xlsx.save(new_name) # 直接在原.xls文件路径下，另存为一个新的.xlsx文件
“”“版本转换，xlsx格式转为xls格式”""
xlsx_to_xls_file_name = 'xlsx_to_xls.xls'
new_name = xlsx_to_xls_file_name[:-1]
方法同上，不推荐使用.api.SaveAs的方法，原因同上
推荐使用save方法，直接转换
“”“设置单元格大小”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xlsx.autofit() # 设置整张表为 自动调整单元格大小
work_sheet_xlsx.range(2, 2).row_height = 25 # 设置第2行的行高为25
work_sheet_xlsx.range(15, 6).column_width = 40 # 设置第6列的列宽为40
“”“设置单元格 字体格式”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
test_cell = work_sheet_xlsx.range(2, 3)
test_cell.color = 255, 200, 255 # 设置单元格的填充色为紫红色
test_cell = work_sheet_xlsx.range(12, 2)
test_cell.api.Font.ColorIndex = 3 # 设置字体的颜色
"""
字体颜色索引见下方：
无色 = -4142, 自动 = -4105, 黑色 = 1,
白色 = 2 ， 红色 = 3, 鲜绿 = 4,
蓝色 = 5 , 黄色 = 6, 粉红 = 7,
青绿 = 8 , 深红 = 9, 绿色 = 10,
深蓝 = 11, 深黄 = 12 , 紫罗兰 = 13,
青色 = 14, 灰色25 = 15, 褐色 = 53,
橄榄 = 52, 深绿 = 51, 深青 = 49,
靛蓝 = 55, 灰色80 = 56, 橙色 = 46,
蓝灰 = 47, 灰色50 = 16, 浅橙色 = 45,
酸橙色 = 43, 海绿 = 50, 水绿色 = 42,
浅蓝 = 41, 灰色40 = 48, 金色 = 44,
天蓝 = 33, 梅红 = 54, 玫瑰红 = 38,
茶色 = 40, 浅黄 = 36, 浅绿 = 35,
浅青绿 = 34, 淡蓝 = 37, 淡紫 = 39,
"""
test_cell.api.Font.Size = 15 # 设置字体的大小为15号
test_cell.api.Font.Bold = True # 设置为粗体
test_cell.api.HorizontalAlignment = -4152 # -4108 水平居中，-4131 靠左，-4152 靠右。
test_cell.api.VerticalAlignment = -4108 # -4108 水平居中（默认），-4160 靠上，-4107 靠下， -4130 自动换行对齐。
test_cell.api.NumberFormat = "@" # @ 为文本格式, 0.00 为小数格式，yyyy-mm-dd 为"-"连接的日期格式，0.00% 为百分数
“”“设置边框”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
test_cell = work_sheet_xlsx.range(2, 3)
test_cell.api.Borders(9).LineStyle = 1 # Borders(9) 底部边框，LineStyle = 1 直线
test_cell.api.Borders(9).Weight = 3 # Weight = 3 为边框的粗细
test_cell.api.Borders(7).LineStyle = 2 # Borders(7) 左边框，LineStyle = 2 虚线
test_cell.api.Borders(8).LineStyle = 5 # Borders(8) 顶部框，LineStyle = 5 双点划线
test_cell.api.Borders(10).LineStyle = 4 # Borders(10) 右边框，LineStyle = 4 点划线
test_cell.api.Borders(5).LineStyle = 1 # Borders(5) 单元格内从左上角 到 右下角。
test_cell.api.Borders(6).LineStyle = 1 # Borders(5) 单元格内从左下角 到 右上角。
“”“获取连续的整行数据”""
work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
cell_value = work_sheet_xlsx.range(2, 2).expand('right').value # 从第2行第2列单元格开始 向右 所有单元格的数据，注意，中间不能有空白单元格
cell_value = work_sheet_xlsx.range(2, 2).expand('down').value # 从第2行第2列单元格开始 向下 所有单元格的数据，注意，中间不能有空白单元格
获取某个单元格向下、向左，分别有多少个连续的行数和列数（单元格自身为起点，计数为1）
row_column_num = work_sheet_xlsx.range(3, 2).expand().shape
row_column_num 是一个元组（行数，列数）
print(row_column_num)
“”“插入、删除一行”""
work_sheet_xlsx.range('a3').api.EntireRow.Delete() # 会删除 ’a3‘ 单元格所在的行。
work_sheet_xlsx.api.Rows(3).Insert() # 会在第3行插入一行，原来的第3行下移。
“”“插入 、删除 一列”""
sht1.range('c2').api.EntireColumn.Delete() # 会删除 ’c2‘ 单元格所在的列。
sht1.api.Columns(3).Insert() # 会在第3列插入一列，原来的第3列右移。(也可以用列的字母表示)