xlwings的使用

1.隐藏Excel

调用xw.App(visible=False)

import xlwings as xw
 
VISABLE = False  # 是否可视化
app = xw.App(visible=VISABLE, add_book=False)  # 界面设置
app.display_alerts = VISABLE  # 关闭提示信息
app.screen_updating = VISABLE  # 关闭显示更新
 
wb = app.books.add()  # 创建新的工作簿
sht = wb.sheets['Sheet1']  # 实例化工作表
sht.range('A1').value = 'Hello World!'
print(sht.range('A1').value)  # 读取
wb.close()

2.工作簿和工作表

连接工作簿（workbook）

import xlwings as xw
 
app = xw.App(visible=True, add_book=False)  # 隐藏界面
# app = xw.App(visible=False, add_book=False)  # 隐藏界面
app.display_alerts = False  # 关闭提示信息
app.screen_updating = False  # 关闭显示更新
 
wb = app.books.add()  # 创建新的工作簿
# wb = app.books.open('test.xlsx')  # 连接当前路径下的工作簿
# wb = app.books.active  # 获取当前活动的工作簿
# wb = xw.Book()  # 创建新的工作簿
# wb = xw.Book('test.xlsx')  # 连接当前路径下的工作簿
# wb = xw.Book(r'C:\test.xlsx')  # Windows下绝对路径连接
input('Enter to quit')
wb.close()

连接工作表（sheet）

import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets.active  # 连接工作表
# sht = wb.sheets[0]  # 连接工作表
# sht = wb.sheets['Sheet1']  # 连接工作表
# sht1 = wb.sheets.add()  # 连接工作表
sht1 = wb.sheets.add('新表', after=sht)  # 连接工作表
input('Enter to quit')
wb.close()

3.读写数据

指定位置是数据起始点，一行一行读写

写入单元格

import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets['Sheet1']  # 连接工作表
sht.range('A1').value = 'Foo 1'
print(sht.range('A1').value)

批量写入

import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets['Sheet1']
sht.range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]  # 批量写入
print(sht.range('A1').expand().value)  # 批量读取
wb.save('test.xlsx')  # 保存
wb.close()

按行列号读写（向下、向右数），如图

import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets.active
for i in range(1, 6):
    for j in range(1, 6):
        sht.range(i, j).value = '({}, {})'.format(i, j)
print(sht.range((1, 1), (5, 5)).expand().value)  # 批量读取
print(sht.range(1, 1).expand('right').value)  # 按行读
print(sht.range(1, 1).expand('down').value)  # 按列读
input('Enter to quit')
wb.close()

4.数据兼容

强大的转换器能处理绝大多数数据类型，如 字典、Numpy Array 和 Pandas DataFrame

字典

import numpy as np
import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets['Sheet1']
d = {'a': 1, 'b': 2}
sht.range('A1').value = d
print(sht.range('A1').options(np.array, expand='table').value)
# [['a' '1.0']
#  ['b' '2.0']]

Numpy数组

import numpy as np
import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets['Sheet1']
sht.range('A1').value = np.eye(3)  # 对角矩阵
print(sht.range('A1').options(np.array, expand='table').value)
# [[1. 0. 0.]
#  [0. 1. 0.]
#  [0. 0. 1.]]

Pandas数据表

import pandas as pd
import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets['Sheet1']
df = pd.DataFrame([[1, 2], [3, 4]], columns=['a', 'b'])
sht.range('A1').value = df  # 写入DataFrame
print(sht.range('A1').options(pd.DataFrame, expand='table').value)
#        a    b
# 0.0  1.0  2.0
# 1.0  3.0  4.0

5.填充颜色

调用Range.color

import xlwings as xw
from itertools import product
 
app = xw.App(visible=False)  # 隐藏Excel
wb = app.books.open('test.xlsx')  # 打开工作簿
sht = wb.sheets['Sheet1']  # 实例化工作表
for cell in list(map(''.join, product('ABCDEFGH', '1'))):  # A1 B1 C1 D1 E1 F1 G1 H1
    print(cell, sht.range(cell).color)  # 填充颜色
wb.close()

6.插入图片

Matplotlib图像

import xlwings as xw
import matplotlib.pyplot as plt
 
wb = xw.Book()
sht = wb.sheets['Sheet1']
fig = plt.figure()
plt.plot([1, 2, 3, 4, 5])
sht.pictures.add(fig, name='MyPlot', update=True)
wb.save('test.xlsx')
wb.close()

指定图片

import os
import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets['Sheet1']
# sht.pictures.add('1.jpg')  # 使用相对路径可能报错
sht.pictures.add(os.path.join(os.getcwd(), '1.jpg'))
wb.save('test.xlsx')
wb.close()

居中插入

import os
import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets['Sheet1']
rng = sht.range('A1')
fileName = os.path.join(os.getcwd(), '1.jpg')
width, height = 10, 10  # 指定图片大小
left = rng.left + (rng.width - width) / 2  # 居中
top = rng.top + (rng.height - height) / 2
sht.pictures.add(fileName, left=left, top=top, width=width, height=height)

7.设置字体 

下划线

下划线	值
普通	4或True
双下划线	5
粗双下划线	-4119

import xlwings as xw
from xlwings.utils import rgb_to_int
 
wb = xw.Book()
sht = wb.sheets['Sheet1']
 
sht.range('A1', 'XFD1048576').api.Font.Name = '微软雅黑'  # 全局字体
 
sht.range('A1').value = 'ABCDE'
sht.range('A1').api.Font.Size = 12  # 字号
sht.range('A1').api.Font.Bold = True  # 加粗
start_index = 3  # 下标从1开始
length_string = 1  # 修改长度
sht.range('A1').api.GetCharacters(start_index, length_string).Font.Color = rgb_to_int((255, 0, 0))  # 设为红色
 
sht.range('B1').value = 'ABCDE'
sht.range('B1').api.Font.Italic = True  # 斜体
sht.range('B1').api.Font.Strikethrough = True  # 删除线
 
sht.range('C1').value = 'ABCDE'
sht.range('C1').api.Font.Underline = True  # 下划线 4普通 5双下划线 -4119粗双下划线
 
sht.range('A2').value = 'a2'
sht.range('A2').api.GetCharacters(2, 1).Font.Superscript = True  # 上标
 
sht.range('B2').value = 'H2O'
sht.range('B2').api.GetCharacters(2, 1).Font.Subscript = True  # 下标
 
sht.autofit()  # 自动调整
input('Enter to quit')
wb.close()

设置列宽

# sht.autofit()  # 自动调整
sht.range("A1").column_width = 0.75  # 列宽
sht.range("B1").column_width = 25.89  # 列宽

 设置边框

Borders边框位置常量

位置	值
左上对角线	5
左下对角线	6
左	7
上	8
下	9
右	10
内部垂直线	11
内部水平线	12

 sht.range('A2', 'B3').api.Borders(5).LineStyle = 1  # 实线

LineStyle边框线型

线型	值
实线	1
虚线	-4115
点划相间线	4
划线后跟两个点	5
点式线	-4118
双线	-4119
无线	-4142
倾斜的划线	13

sht.range('C2', 'D3').api.Borders(6).LineStyle = -4119  # 双线

Weight边框粗细

粗细	值
细	1
细长	2
粗	4
中	-4138

sht.range('C2', 'D3').api.Borders(6).Weight = 2  # 细长

Color边框颜色和填充颜色不同，若指定RGB需要转换为HEX，调用rgb_to_int()，公式如下：Hex=R+G×256+B×65536

颜色	值
黑色	0x0
红色	0xFF
绿色	0xFF00
黄色	0xFFFF
蓝色	0xFF0000
洋红	0xFF00FF
蓝绿	0xFFFF00
白色	0xFFFFFF

import xlwings as xw
from xlwings.utils import rgb_to_int
 
if __name__ == '__main__':
    wb = xw.Book()
    sht = wb.sheets['Sheet1']
 
    '''5左上角对角线'''
    sht.range('A2', 'B3').api.Borders(5).LineStyle = 1  # 实线
    sht.range('A2', 'B3').api.Borders(5).Weight = 1  # 细
    sht.range('A2', 'B3').api.Borders(5).Color = 0x0  # 黑色
 
    '''6左下角对角线'''
    sht.range('C2', 'D3').api.Borders(6).LineStyle = -4119  # 双线
    sht.range('C2', 'D3').api.Borders(6).Weight = 2  # 细长
    sht.range('C2', 'D3').api.Borders(6).Color = 0xFF  # 红色
 
    '''7 8 9 10 左上下右'''
    for i in [7, 8, 9, 10]:
        sht.range('E2', 'F3').api.Borders(i).LineStyle = 4  # 点划相间线
        sht.range('E2', 'F3').api.Borders(i).Weight = 4  # 粗
        sht.range('E2', 'F3').api.Borders(i).Color = 0xFF00  # 绿色
 
    '''11内部垂直线'''
    sht.range('G2', 'H3').api.Borders(11).LineStyle = 5  # 划线后跟两个点
    sht.range('G2', 'H3').api.Borders(11).Weight = -4138  # 中
    sht.range('G2', 'H3').api.Borders(11).Color = rgb_to_int((0, 128, 128))  # 紫色
 
    '''12内部水平线'''
    sht.range('I2', 'J3').api.Borders(12).LineStyle = -4115  # 虚线
    sht.range('I2', 'J3').api.Borders(12).Weight = 4  # 粗
    sht.range('I2', 'J3').api.Borders(12).Color = rgb_to_int((0, 0, 255))  # 蓝色
 
    input('任意输入保存')
    wb.save('result.xlsx')
    wb.close()

 8.合并拆分单元格

import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets.active
sht.range('A1:D5').merge()  # 合并单元格
input('Enter to unmerge')
sht.range('A1:D5').unmerge()  # 拆分单元格
input('Enter to quit')
wb.close()

 9.插入公式

import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets.active
sht.range('A1').value = [['语文', '数学', '总分'], [100, 100, None]]
sht.range('C2').formula = '=SUM(A2:B2)'
input('Enter to quit')
wb.close()

 全局单元格

_range = sht.range('A1', sht.used_range.last_cell)  # 用过的最后一个单元格
_range = sht.range('A1', 'XFD1048576')  # 全局单元格

10.宏

1.设置 Excel 信任对 VBA 工程对象模型的访问：

文件 → 选项 → 信任中心 → 信任中心设置 → 信任对 VBA 工程对象模型的访问

2. 安装加载项

xlwings addin install

3.命令行客户端

快速构建项目

xlwings quickstart project

4.打开project.xlsm

5.打开Visual Basic编辑器 

6.运行宏

 此宏运行的逻辑与同名.py的一致

import xlwings as xw
 
 
@xw.sub
def main():
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    if sheet['A1'].value == 'Hello xlwings!':
        sheet['A1'].value = 'Bye xlwings!'
    else:
        sheet['A1'].value = 'Hello xlwings!'

 7.dataframe格式写入

import xlwings as xw
 
ws = wb.sheets.add(zhengli_name)
ws.range('A2').expand('table').value = df_huizong.values.tolist()

11 对excel表格单元格内容针对索引进行加粗标红

import xlwings as xw
#path为excel路径，ranged为单元格如A6，start_range和end_range为开始索引和结束索引，color为颜色代码，blod为是否加粗
def char_red(path, ranged, start_range, end_range, color=(0, 0, 0), blod=False):
    app = xw.App(visible=False, add_book=False)
    wb = app.books.open(path)
    ws = wb.sheets[0]
    ws[ranged].characters[start_range:end_range].font.color = color
    ws[ranged].characters[start_range:end_range].font.Size = 11
    ws[ranged].characters[start_range:end_range].font.Name = "宋体"
    ws[ranged].characters[start_range:end_range].font.bold = blod
    wb.save(path)
    wb.close()
    app.quit()

12.xlwings设置excel表格单元格样式

#cell为单个单元格对象
def set_style(cell):
    # 设置单元格文字居右,垂直居中
    cell.api.HorizontalAlignment = xw.constants.HAlign.xlHAlignLeft
    cell.api.VerticalAlignment = xw.constants.HAlign.xlHAlignCenter
    # 设置单元格自动换行
    cell.api.WrapText = True
    # 设置字体和大小
    cell.api.Font.Size = 11
    cell.api.Font.Name = "宋体"
    cell.api.Font.Bold = False