编辑一些Excel插件

1. Xlwings和VBA

→ 官方文档：https://docs.xlwings.org/zh_CN/latest/index.html

1.1 准备工作

• Xlwings安装/卸载

  pip install xlwings
  xlwings addin install #安装excel插件
  
  xlwings addin remove
  pip uninstall xlwings
  

• 打开excel宏和显示开发工具
  • Excel > 偏好设置 > 安全性 > 启用所有宏
  • Excel > 偏好设置 > 功能区和工具栏 > 自定义功能区 > 开发工具

1.2 如何使用

本质上是写一个 python脚本，对传入的excel对象进行一些写操作

a. 新建

import xlwings as xw

# 方法一：当前App下新建一个Book
app = xw.App(visible=True, add_book=False)
wb = app.books.add()
# visible-是否打开相应excel文件，即是否可视化当前工作簿；
# add_book=True，此时立即创建一个有sheet表的工作簿
# add_book=False，此时立即创建一个没有sheet表的工作簿
sheet = wb.sheets[0]
# 单元格插入值
sheet.range('a4:h4').value =['合约代码','开始交易日','最后交易日','最后交割日', '交易额（亿元）','持仓额（亿元）','收盘价','结算价']
# 保存到当前文件夹下
wb.save("./test.xlsx")
# 关闭此工作簿
wb.close()
app.quit()

# 方法二：创建一个新的App，并在新App中新建一个Book
wb = xw.Book()
wb.save('1.xlsx')
wb.close()

b. 打开和修改已有excel

wb = xw.Book("test.xlsx")
sheet = wb.sheets[0]
b2 = sheet.range("b2").value
sheet.range("a2:d3").clear()

def get_alive_info():
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    xw.Range('A5:H30').clear_contents()

    contract_code = sheet.range('A2').value


    # 单元格判断为空：is None
    if contract_code is None:
        sheet.range('A2').value = 'Please Enter Contract Name'
    # 可以直接赋值df
    result = com.AliveContractInfo(date = yesterday, sort_by_oi = sort_by_oi, curve= curve, savefig=curve, savepath=pic_path)
    sheet.range('A5').value = result.values


    # 两种插入图片方法：1. 保存并调用所在路径 2. 直接使用plt
    try:
        pic_path = Path(__file__).parent / 'PIC' / f"{ticker.split('.')[0]}_Contango_Curve.png"
        sheet.pictures.add(pic_path, name ='pic1', left = sheet.range('J4').left, top = sheet.range('J4').top, 
		width =350, height = 250,update = True)
    except:
        figure = plt.figure()
        sheet.pictures.add(figure, name='pic1', left=sheet.range('J4').left, top=sheet.range('J4').top, 
		width=0,height=0, update=True)

插入图片的一些详细操作：
https://www.yisu.com/zixun/376621.html

c. 改变格式

可以使用代码改变，也可以直接在excel改好保存，之后每次选择清空内容不清空格式即可

# 颜色
sht.range("b1").color = (135,206,250)

# 行高和列宽
width = sht.range("b1").column_width
height = sht.range("b1").row_height
sht.range("b1").column_width = 25
sht.range("b1").row_height = 18

# 插入公式
sht.range("a2").value = "=CONCATENATE(b1, c1)"
## 或者
sht.range("a3").formula = "=CONCATENATE(b1, c1)"

# 查看单元格的公式
sht.range("a3").formula_array

一些高级格式：
https://www.cnblogs.com/aziji/p/13916129.html
https://zhuanlan.zhihu.com/p/157346746

1.3 如何调试代码

• RunPython: 通过 RunPython 调用Python时，可以设置一个 mock_caller 以便可以在Excel和Python的函数调用之间自由切换。
• UDFs: 为了调试用户定义函数，xlwings提供了一个方便的调试服务器。

RunPython

# my_module.py
import os
import xlwings as xw

def my_macro():
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = 1

if __name__ == '__main__':
    # Expects the Excel file next to this source file, adjust accordingly.
    xw.Book('myfile.xlsm').set_mock_caller()
    my_macro()

UDF

仅适用于Windows系统：要调试UDF，只需要在xlwings的 Add-in & Settings 功能区的VBA模块上部选中 Debug UDFs , 然后再Python源文件的结尾加上下面几行，就可以进行测试了。根据调试时用的Python集成环境，可能需要使用调试模式来运行代码(比如，使用PyCharm或者PyDev时):

if __name__ == '__main__':
    xw.serve()

接下来，当你刷新工作表时(用 Ctrl-Alt-F9)，代码会停止你设置的断点处，或者会输出你设置过的打印信息。

1.4 调用方法

方法一：宏调用

1. 使用alt+F11调出宏
2. 写函数

   Sub get_info()
      RunPython "import excel_plugin; excel_plugin.get_series()"  #放在同一路径下
   End Sub
   

3. 开发工具-插入控件-右键指定宏

方法二：UDF

像下面这样找Python源文件：

• 在工作簿所在的相同目录下
• 和工作簿同名，但是文件后缀是 .py 而不是 .xlsm 。
• 另外一种方法是在xlwings功能区通过 UDF Modules 来指明是哪一个文件。

假设你有一个工作簿 myproject.xlsm ，可以把下列代码输入 myproject.py:

import xlwings as xw

@xw.func
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

在xlwings工具功能区点击 Import Python UDFs ，把在 myproject.py 中修改的内容同步进来。
在一个单元格里输入公式 =double_sum(1, 2) ，可以看到正确结果

• 引入的函数甚至可以在vba-模块里调用，参见：https://cloud.tencent.com/developer/article/1871204
• 可以使用quickstart快速获得模版：https://zhuanlan.zhihu.com/p/366118096

2. VBA运行Bat

2.1 bat文件怎么搞

• 是啥
  批处理(Batch)是对某对象进行批量的处理,通常被认为是一种简化的脚本语言。bat文件是WINDOWS里的批处理命令，简单来说就是一堆在cmd中可以运行的命令的集合，被按照文件中的顺序执行。

  .sh文件是Linux中使用的批处理。

• 干啥
  系统会调用cmd.exe按照该文件中各个命令出现的顺序来逐个运行它们，因此可以批量运行一些命令，以及令脚本命令可复用

• 怎么用呢
  调用方法：1. 直接双击运行 2. 直接在cmd相应路径下里输入文件名
  文件格式：后缀名为.bat或.cmd，可以直接使用文本文稿编辑

2.1.1一些常见的使用

a. 变量的定义和引用

set 命令用于设置变量，=等号前后不能有空格

set CHAR=ncy

%% 用来引用变量

set SENTENCE=hi %CHAR%
echo %SENTENCE%

b. echo

echo类似于print，可以输出内容及控制输出

# 输出内容
echo hi ncy
pause
>> C:\bat> echo hi ncy
>hi ncy
>>  C:\bat>pause
>请按任意键继续
# 关闭命令的回显，在命令前加上@即可（即不显示正在执行的批处理命令）
@echo hi ncy
pause

>hi ncy
>>  C:\bat>pause
>请按任意键继续

# 打开/关闭整体的echo功能：echo on| echo off
@echo off(即连echo off也不显示)
echo hi ncy
pause

>hi ncy
>请按任意键继续

# 空行（.紧跟着echo）
echo.

# 新建文件或内容
@ echo 123>myfile.txt
@ echo. >myfile.txt (建空文件)

# 传入命令的参数
echo Y|rd \d c:\myfile.txt (对于是否确定删除文件的询问输入Y)

c. 如何向bat传入参数

bat文件中可以引用的参数为%0 - %9，其中%0是指批处理文件的本身，最多可以有9个参数。此时再运行bat就无法用双击的方法（因为无法传入参数）。

test.bat:
	@echo hi %1

>> test.bat ncy
> hi ncy

结合一下python文件的参数传入(python传参也是从1开始)：

test.py:
	    if sys.argv[1]=='name':
        	print('hi ncy')
test.bat:
	python test.py %1

>> test.bat name
> hi ncy

d. 其他一些常见的操作

md：创建文件夹
del：删除
copy：复制
ren：重命名文件(夹)
rem: 注释后面的内容 rem ehco hi

2.1.2 运行完怎么让cmd停一下：

众所周知，bat文件运行完cmd就嗖的一下没有了，根本没法看全都输出了些什么，那要怎么才能让cmd停住呢，基本就是在结尾加一些东西

1. pause
   在结尾加上pause，程序执行完会暂停住，需要按下任意键才会继续（关闭cmd窗口）

   echo hello
   pause
   

   

2. timeout
   可以实现和sleep函数相同的效果，程序会等待给定的时长再执行下一个命令（如果后面无命令a.k.a就是自动关闭）

   echo hello
   timeout /nobreak /t 3
   nobreak: 不允许用户打断时间，反之如果没有这个参数，那么在倒计时期间用户随便按一个按键，则脚本就继续向下进行
   3： 延迟的秒数
   

   

3. cmd \k
   \k 参数，执行完后保留窗口
   \c 参数，执行完后关闭窗口

   echo hello
   cmd \k
   

   

2.2 怎么在VBA里调用bat

Shell(PathName[,WindowStyle])

• PathName :要执行的程序名，包括目录或文件夹
• WindowStyle :程序运行时窗口的样式
  

---

步骤：

1. 使用alt+F11调出宏
2. 使用shell函数

   Sub get_info()
      Path = ThinsWorkbook.Path #bat文件和excel放在同一路径下
      ChDir(Path)
      a = Shell("Func1.bat sheet2", 1) #一定要赋值给变量，因为有返回值
   End Sub
   

3. 开发工具-插入控件-右键指定宏

Ref

1. https://blog.csdn.net/as604049322/article/details/124417405
2. 一些函数查询