xlwings笔记

置顶 已于 2023-09-06 11:53:54 修改
阅读量2k 收藏 31
点赞数 2
分类专栏： DataFrame 文章标签： python matplotlib 画图
于 2021-11-22 01:12:16 首次发布
本文链接：https://blog.csdn.net/Caiqiudan/article/details/121463661
版权

文章目录

前言

对比了openxl…、pd.ExcelWriter和xlwings。发现xlwtings最好用了，既不会频频失效，也不需要一格一格地遍历单元格。

先记录一些。

新建excel
import xlwings as xs
## 方法一
wb = xs.Book()
sheet = wb.sheets[0]

## 方法二
app = xw.App(visible=False)
app.display_alerts = False           # 关闭一些提示信息，可以加快运行速度。 默认为 True。当合并单元格时，会一直提示是否合并
# app.screen_updating = True          # 更新显示工作表的内容。默认为 True。关闭它也可以提升运行速度。
wb = app.books[0]                    # 建立excel连接
sheet = wb.sheets[0]                 # 获取第一张表
读取excel
# 方法一，直接读取
wb = xw.Book('data/test (2).xlsx')
# 方法二，不打开excel
xw.App(visible=False, add_book=False).books.open(path)
# 方法二如果报错，可能要读取两次：
app = xw.App(visible=False, add_book=False)
try:
    wb = app.books.open('data/test (2).xlsx')
except:
    wb = app.books.open('data/test (2).xlsx')
获取所有行数和列数
sheet.used_range.last_cell.row
sheet.used_range.last_cell.column
如何合并单元格sheet.range().api.Merge()

https://www.cnblogs.com/Small-lucky/p/14485234.html
同一列中，合并内容相同的单元格

# 合并单元格
j = 0
value = df.iloc[0, 0]
for n, i in enumerate(df.iloc[:, 0]):
    if i != value:
        sheet[j+1:n+1, 0].api.Merge()
        j = n         # 更新初始合并单元格
        value = i

在这里插入图片描述

设置换行 sheet.range().api.WrapText = True
sheet[0:nrows, n].api.WrapText = True
不要科学计数法：

对于数字很长的，比如身份证字段，写入excel会显示科学计数法，导致后面的几位数全变成0了！！！丢失了信息。即使将dataframe字段、excel格式设置为文本都没有作用。这时需要对dataframe字段做如下操作：

df['身份证'] = df['身份证'].astype(str) + '\t'
冻结窗口
import xlwings as xs

wb = xs.Book()
wb.app.range("A2").select()                 # 1. 选中要冻结的单元格  # "A2"冻结首行
wb.app.api.ActiveWindow.FreezePanes = True  # 2. 进行冻结操作

方法参考VBA冻结窗口操作：https://zhidao.baidu.com/question/1435266045034157659.html

如果要冻结其他工作簿的窗口格，那么只需指定窗口格：

sheet = wb.sheets[n]
sheet.select()  # 选中该sheet
wb.app.range("A2").select()
wb.app.api.ActiveWindow.FreezePanes = True
添加所有框线
sheet2[0:nrows+2, 0:ncolumns].api.Borders(12).Weight = 3   # 内部单元格横线
sheet2[0:nrows+1, 0:ncolumns+1].api.Borders(11).Weight = 3   # 内部单元格竖线
窗口显示比例
wb.app.api.ActiveWindow.Zoom  = 70     # 窗口显示70%

在这里插入图片描述

添加数据条

https://www.cnblogs.com/dontbealarmedimwithyou/p/16034327.html

import xlwings as xw
from xlwings.utils import rgb_to_int

def add_bar(rng_dl, pcolor=[99, 142, 198], ncolor=[255, 0, 0]):
    '''
    # 增加数据条，设置数据条颜色
    params:
        rng_dl: sheet.range格式，如sheet[:, 1]  <Range [工作簿3]Sheet1!$A:$A>
        pcolor: list, 颜色RGB，当数据为正数时，数据条的颜色，默认为蓝色
        ncolor: list, 颜色RGB，当数据为负数时，数据条的颜色，默认为红色
    return:
        rng_dl: sheet.range格式，返回增加的数据条
    '''
    rng_dl.api.FormatConditions.Delete()
    # 添加数据条
    rng_dl.api.FormatConditions.AddDatabar()
    rng_dl.api.FormatConditions(rng_dl.api.FormatConditions.Count).SetFirstPriority()
    rng_dl.api.FormatConditions(1).MinPoint.Modify.newtype = xw.constants.ConditionValueTypes.xlConditionValueAutomaticMin
    rng_dl.api.FormatConditions(1).MaxPoint.Modify.newtype = xw.constants.ConditionValueTypes.xlConditionValueAutomaticMax
    # # 数据条颜色
    rng_dl.api.FormatConditions(1).BarColor.Color = rgb_to_int(pcolor)
    rng_dl.api.FormatConditions(1).BarColor.TintAndShade = 0
    # # 数据条颜色方向等设置
    rng_dl.api.FormatConditions(1).BarFillType = xw.constants.DataBarFillType.xlDataBarFillGradient     # 渐进填充 xlDataBarFillSolid 实心填充
    rng_dl.api.FormatConditions(1).Direction = xw.constants.Constants.xlContext
    rng_dl.api.FormatConditions(1).BarBorder.Type = xw.constants.DataBarBorderType.xlDataBarBorderSolid   # 颜色条是否有外框线
    rng_dl.api.FormatConditions(1).BarBorder.Color.Color = rgb_to_int([174, 171, 171])                    # 外框线设置为灰色
    rng_dl.api.FormatConditions(1).AxisPosition = xw.constants.DataBarAxisPosition.xlDataBarAxisAutomatic # 负数数据条反向

    # 当数据为负数时，反向填充红色
    rng_dl.api.FormatConditions(1).NegativeBarFormat.ColorType = xw.constants.DataBarNegativeColorType.xlDataBarColor
    rng_dl.api.FormatConditions(1).NegativeBarFormat.Color.Color = rgb_to_int(ncolor)
    rng_dl.api.FormatConditions(1).NegativeBarFormat.Color.TintAndShade = 0
    # rng_dl.api.FormatConditions(1).NegativeBarFormat.BorderColor.Color = rgb_to_int([0, 0, 0]) # 外框线设置为红色，不知道为什么报错

    # # 中轴线axis颜色 设置
    rng_dl.api.FormatConditions(1).AxisColor.Color = rgb_to_int([255,255,255])
    rng_dl.api.FormatConditions(1).AxisColor.TintAndShade = 0
自动设置列宽
sheet.autofit()   # 自适应列宽('c')
sheet.autofit('r') # 自适应高度('r')
2021.12.01更新
# 新版，将数据写入excel，并设置格式
# 2021.12.01更新：增加合并单元格/自定义列宽/设置统一行高/合并连续且重复的单元格/自定义日期后缀格式/自动换行/冻结窗口格
def to_excel(df, path, prefix=None, suffix=None, today_format='%Y%m%d%H%M', formats={}, column_width={}, row_height=None,
            wrap_text=[], merge=[], freeze='A2'):
    '''
    将dataframe存入excel
    参数：
        df: DateFrame,要写入excel的DataFrame
        path：str,文件路径，如：../../data/test.xlsx
        prefix：str,文件前缀，如"test_"，后缀自动用今天的日期补齐，如"20211122"
        suffix：str,文件后最，如"_test"，前缀自动用今天的日期补齐，如"20211122"
        today_format：文件的日期格式，默认精确到时分
        formats: Dict,自定义列格式，{'列名':'excel格式'},eg:{'列1':'@', '列2':'0.00', ...}
        column_width：Dict,自定义列宽，{'列名':列宽},eg:{'列1':10, '列2':50,...}
        row_height：float, 默认为None，一键设置每一行的行高
        wrap_text：List，默认为[]，需要自动换行的列  ['列1', ...]      # Dict，需要/不需要自动换行的列, {'列1':True, '列n':False}
        merge：List, 默认为[]，需要合并单元格的列，将该列中连续重复的单元格合并 ['列1', ...]
        freeze：冻结窗口格，默认为'A2'冻结首行，False/None即不冻结
        
    返回:
        将数据存入指定路径下的excel
    '''
    import xlwings as xw
    import time
    from os.path import join as pathJoin
    
    # 1. 设置文件名称  前缀/后缀 + 日期(精确到时分)
    today_date = time.strftime(today_format, time.localtime(time.time()))
    if prefix:                # 如果有前缀
        path = pathJoin(path, prefix+today_date+'.xlsx')
    elif suffix:
        path = pathJoin(path, today_date+suffix+'.xlsx')
    
    app = xw.App(visible=False)
    app.display_alerts = False           # 关闭一些提示信息，可以加快运行速度。 默认为 True。当合并单元格时，会一直提示是否合并
#     app.screen_updating = True          # 更新显示工作表的内容。默认为 True。关闭它也可以提升运行速度。
    wb = app.books[0]                    # 建立excel连接
    sheet = wb.sheets[0]                 # 获取第一张表         

    # 获取行数列数
    nrows = df.shape[0] + 2              # 最大行数
    ncolumns = df.shape[1]               # 最大列字母
    
    # 设置列宽
    def get_len(x):
        try:
            length = len(str(x).encode('gbk'))   # 计算每列宽度
            return length 
        except:
            return None

    # 遍历每一列，计算每一列宽度
    # 优先考虑参数column_width的设置
    for n, c in enumerate(df.columns):
        # 如果该列在参数column_width中有指示，那么设置为指定的格式
        if c in column_width.keys():
            sheet[0, n].column_width = column_width[c]
            
        else:
            length = df[~df[c].isna()][c].apply(get_len).mean()               # 非空值的平均宽度
            # 空列的宽度赋值为3
            if str(length) == 'nan':
                length = 3                          # 计算列名本身的宽度
            col_length = len(str(c).encode('gbk'))                            # 计算列名本身的宽度

            # 如果列名比列平均宽度宽，则列宽设为列名的宽度
            if length > col_length:
                if ('时间' in c) or ('日期' in c):                                                              # 对于日期列，已经够宽了，不用+4的宽度
                    sheet[0, n].column_width = length                                 # 添加列宽为评价宽度
                else:
                    sheet[0, n].column_width = length + 4                          # 添加列宽为评价宽度
            else:
                sheet[0, n].column_width = col_length + 0.5                     # 设置列宽为列名

    # 遍历每一列，设置字体格式
    # 优先考虑参数formats的设置
    for n, (c, d) in enumerate(df.dtypes.items()):
        
        # 如果该列在format中有指示,那么设置为指定的格式
        if c in formats.keys():
            sheet[0:nrows, n].api.NumberFormat = formats[c]
        
        elif ('int' in str(d)) or ('联系方式' in str(c)) or ('号' in str(c)) or ('手机' in str(c)) or ('电话' in str(c)) or ('id' in str(c)):
            # 需在外部添加'\t'，防止转为科学计数法（仅仅是转为文本没用的，需要添加一列\t，才能保证导入excel时为文本格式而不是科学计数法格式）
            #df.loc[:, c] = (df.loc[:, c].astype(str) + '\t').values             
            sheet[0:nrows, n].api.NumberFormat = "@"
            print('【%s】调整为文本格式'%c)
        elif ('码' in str(c)):
            sheet[0:nrows, n].api.NumberFormat = "0"
            print('【%s】调整为整数格式'%c)

        # 如果是计算天数，则只需取整
        elif ('（天）' in str(c)):
            sheet[0:nrows, n].api.NumberFormat = '0'
            print('【%s】调整为整数'%c)
            
        # 修改时间格式为'yyy-mm-dd h:mm
        elif sheet[0:nrows, n].number_format == 'yyyy/m/d h:mm':
            sheet[0:nrows, n].api.NumberFormat = 'yyyy-mm-dd hh:mm'
            print('【%s】调整为日期格式：'%c, d)

        elif ('datetime' in str(d)) or ('时间' in str(c)):
            sheet[0:nrows, n].api.NumberFormat = 'yyyy-mm-dd hh:mm'
            print('【%s】调整为日期格式'%c)

        # 否则将列名有“占比”的格式调整为保留四位小数
        elif ('占比' in str(c)) or ('百分比' in str(c)) or ('比例' in str(c)) or ('比' in str(c)):
            sheet[0:nrows, n].api.NumberFormat = '0.00%'
            print('【%s】调整为百分比格式'%c)

        # 否则将float格式调整为保留两位小数
        elif 'float' in str(d):
            sheet[0:nrows, n].api.NumberFormat = '0.00'
            print('【%s】调整为小数格式（两位数）'%c)
    
        # 自动换行，如果列在参数wrap_text，那么自动换行
        if c in wrap_text:
            print('【%s】自动换行'%c)
            sheet[0:nrows, n].api.WrapText = True

    
    # 写入数据
    sheet['A1'].value = df.columns.values
    sheet['A2'].value = df.values
            
    # 合并单元格
    for c in merge:                              # 遍历每个需要合并单元格的列
        ind = list(df.columns).index(c)          # 找到列名在excel的列索引
        j = 0                                    # 初始化第一个单元格的行索引
        value = df.iloc[0, 0]                    # 初始化第一个单元格的取值
        for n, i in enumerate(df.iloc[:, 0]):
            if i != value:                       # 当遇到了不相同的取值
                sheet[j+1:n+1, ind].api.Merge()  # 合并取值相同且连续的单元格
                j = n                            # 更新下一个即将合并的单元格索引
                value = i                        # 更新下一个即将合并的单元格取值
        sheet[j+1:n+2, ind].api.Merge()          # 记得合并尾部的单元格
        print('【%s】合并单元格'%c)
    
    # 冻结窗口格
    if freeze:
        wb.app.range(freeze).select()                 # "A2"冻结首行
        wb.app.api.ActiveWindow.FreezePanes = True
    
    
    ## 其他设置
    # 设置字体
    sheet[0:nrows, 0:ncolumns].api.Font.Name = '微软雅黑'
    # 首行加粗
    sheet[0,0:ncolumns].api.Font.Bold = True
    # 居中
    sheet[0:nrows, 0:ncolumns].api.HorizontalAlignment = -4108    # -4108 水平居中。 -4131 靠左，-4152 靠右。
    # 首行背景颜色
    sheet[0, 0:ncolumns].color = [128, 128, 128]
    # 首行字体颜色, 2表示白色(详见https://blog.csdn.net/weixin_42146296/article/details/103647940)
    sheet[0, 0:ncolumns].api.Font.ColorIndex = 2
    # 首行添加单元格线条
    sheet[0, 0:ncolumns].api.Borders(11).Weight = 2
    # 设置行高
    if row_height:
        sheet[0:nrows, 0].api.RowHeight = row_height
    
    wb.save(path)
    print('文件保存再%s路径下'%path)
    wb.close()

参考文献：
xlwings官网_文档
https://blog.csdn.net/weixin_42146296/article/details/103647940
https://blog.csdn.net/qq_42374697/article/details/121327756
VBA代码–官方网站

mohana48833985
关注
专栏目录
xlwings - 报表自动换算、汇总
Fergus的博客
06-12 1560
利用xlwings实现excel报表自动化，并使用PyInstaller封装为.exe文件降低使用门坎
Excel Python之Xlwings学习笔记
神会找你的博客
04-03 2813
Xlwings学习笔记Python基本延时函数代码换行反斜杠 \ ——不影响输出结果的代码换行三引号 ''' ''' —— 令字符串的打印结果换行Xlwings导入xlwings模块打开、保存、关闭Excel文档在多个工作簿或sheet之间工作工作簿/sheet/单元格 引用读、写单元格值 Python基本 延时函数 import time time.sleep(10) #延时10秒 代码换行 反斜杠 \ ——不影响输出结果的代码换行 同时适用于字符串和代码 a = b + c + d + \ e +
使用xlwings获取excel表的行和列以及指定单元格的值
kxltsuperr的专栏
11-09 2558
【代码】使用xlwings获取excel表的行和列。
python——xlwings（2）
weixin_49816076的博客
09-11 217
excel表格的读取与读写。
xlwings|如何获取有数据的所有行数和列数
恰排骨芸豆包嘛的博客
05-06 1万+
https://mp.weixin.qq.com/s/-EGrdXeA2J8YfudRI5DoNg
pythonexcelweb交互插件_Python与Excel交互——Xlwings
weixin_39905500的博客
12-09 397
我建了个 Python 新手群供大家学习交流，要入群的请私聊我或者点开我头像加我-------------------------------------手打分割线----------------------------------------------Xlwings是我认为的Python最强大的处理Excel的库，主要原因如下：1 Windows，Mac都能用 (Excel，WPS也都能用)2...
xlwings的使用
weixin_50170972的博客
05-09 995
Color边框颜色和填充颜色不同，若指定RGB需要转换为HEX，调用rgb_to_int()，公式如下：Hex=R+G×256+B×65536。强大的转换器能处理绝大多数数据类型，如 字典、Numpy Array 和 Pandas DataFrame。文件 → 选项 → 信任中心 → 信任中心设置 → 信任对 VBA 工程对象模型的访问。指定位置是数据起始点，一行一行读写。按行列号读写（向下、向右数），如图。此宏运行的逻辑与同名.py的一致。LineStyle边框线型。Matplotlib图像。
xlwings库的基本使用笔记
Alfie的博客
08-22 5682
xlwings的安装； 在cmd命令窗口下pip下xlwings库 具体在cmd下输入命令行：pip install xlwings 安装失败，根据报错自行百度。 导入xlwings库 import xlwings as xw 总结操纵打开excel的方式： 1.利用xlwings中的Book filepath=r'C:\Users\gztsalfiel\Desktop\...
Python xlwings库_学习笔记_2
weixin_44409210的博客
08-13 1119
xlwings库操作Excel表格 本周工作需要用Python新建或者打开Excel表格，要求新建多个sheet，并且插入数据 在Python中可以操作Excel的库有很多： xlwings：xlwings pandas：pandas openpyxl：openpyxl xlsxwriter：xlsxwriter 等等还有很多，每个库都有自己的优点，根据工作需要不同，我选择了xlwings xlwings：可以结合 VBA 实现对 Excel 编程，强大的数据输入分析能力，同时拥有丰富的接口，还可以结合 p
python笔记——xlwings（App.books.open/Book方式打开文件）
热门推荐
wang_zuel的博客
06-29 2万+
python3 xlwings（App/books/Book）笔记打开Excel文件xlwings.App.books.open()xw.Book新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式...
My_Learning-Python:学习Python-Excel，Pandas，Matplotlib，Xlwings
05-02
My_learning-Python 学习Python： 面向对象程序设计（OOP），功能程序设计（FP） 数据科学 机器学习 Web开发 聊天机器人 安装 我怎么用？ 编辑器（带建议）：Sublime Text 3（带有安装程序的破解版）， ...
【xlwings api语言参考】Range.NumberFormat 属性
DataLab的博客
05-14 855
功能： 返回或设置指定单元格区域的数字格式。 语法： rng.NumberFormat rng是一个表示 Range 对象的变量。 备注： 如果指定区域中的所有单元格的数字格式不一致，则此属性将返回空。 示例： 本例设置单元格C3的数字格式为保留两位小数。 import xlwings as xw app=xw.App() bk=app.books.active sht=bk.sheets.active rng=sht.api.Range('C3') rng.Value=10 rng.NumberForma
python xlwings vba api
qq_42206623的博客
09-17 660
常用api # sht对象： # 搜索 range_obj = sht.api.Cells.Find(What='Tofind_str', After=sht.api.Cells(1, 1), LookAt=xlwings.constants.LookAt.xlWhole, LookIn=xlwings.constants.FindLookIn.xlFormulas,
xlwings时间格式单元格更改数据
weixin_41989712的博客
10-26 1338
需求: 每天把当天日期的九点钟放到单元格里 首先time.localtime获取当天日期 import time localtime = time.localtime(time.time()) 然后用datetime把格式转变一下填入单元格，这样单元格的显示格式也不会变，如果原先是年月日还是会显示年月日的格式 sht.range('L30').value = datetime.datetime(localtime.tm_year, localtime.tm_mon, localtime.tm_mday,9
使用Python操作excel单元格——冻结窗格操作
最新发布
xll_007的博客
05-11 770
通过使用Python的openpyxl库，来操作excel单元格，达到冻结窗格的目的。在excel上操作，是选中相应的行和列，或者单元格，然后点击冻结窗格来实现的，通过Python来操作。
xlwings合并单元格_xlwings最全操作；10秒搞定Xlwings全套操作
weixin_42305163的博客
01-21 1万+
import xlwings as xw app = xw.App(visible=True, add_book=False) app.display_alerts = False # 关闭一些提示信息，可以加快运行速度。 默认为 True。 app.screen_updating = True # 更新显示工作表的内容。默认为 True。关闭它也可以提升运行速度。 wb = app...
python xlwings操作excel调试代码
fqfq123456的专栏
11-25 706
import xlwings as xw # pip install xlwings app = xw.App(visible=True, add_book=False) app.display_alerts = False # 关闭一些提示信息，可以加快运行速度。 默认为 True。 app.screen_updating = True # 更新显示工作表的内容。默认为 True。关闭它也可以提升运行速度。 wb = app.books.add() sht = wb.sheets.act.
【xlwings api语言参考】Range.WrapText 属性
DataLab的博客
05-24 1331
功能： 返回或设置一个布尔值，它指明 Microsoft Excel 是否为对象中的文本自动换行。 语法： rng.WrapText rng是一个表示 Range 对象的变量。 备注： 如果指定单元格区域内所有单元格中的文本都自动换行，此属性将返回 True；如果指定区域内所有单元格中的文本都不自动换行，则返回 False；如果指定区域内有些单元格中的文本自动换行，而另一些单元格中的文本不自动换行，则返回空。 Microsoft Excel 会在必要的时候改变区域的行高以容纳其中的文字。 示例： 此示例对
python用xlwings 隐藏Excel某行或某列
weixin_48160417的博客
08-21 1812
隐藏行或列 python xlwings
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值