ActiveX Data Objects (ADO) 让我们能够用一个工具来操作SQL Server, Access DB, Worksheet

ADO有很多用法，首先介绍一个：把excel中的表用SQL语法来处理

使用ADO流程是

• 建立连接
• 写SQL语句
• 执行SQL语句

要使用ADO，首先要打开编辑VBA的界面，上面的菜单栏里面有一个是Tools，点击Tools, 里面找到reference。勾选上Microsoft ActiveX Data Objects 6.1 Library（如果没有6.1版本，也可以用2.8版本）

建立连接的基本语法如下

Dim Con as new ADODB.Connection
Con.Open "Connection string" 
        '运行SQL语句
Con.Close

这里的connection string需要根据不同的Excel版本确定

https://www.connectionstrings.com/ 这个网站提供了不同情况下需要用到的connection string

我的excel版本是2016，我用的connection string如下

strFileName=ThisWorkbook.Fullname
"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFileName & "; Extended Properties=""Excel 12.0 Macro;HDR=Yes"";"

就可以成功连接到目前用的工作表啦

大家都知道如何安装CRAN的R包，只要输入

install.packages("包名称")

就可以直接从CRAN下载包啦！
但是有的包CRAN中没有，只有Github版本。这个时候我们需要用一个叫做devtools的工具帮我们的忙啦！
一般的流程是

install.packages('devtools')

library(devtools)

devtools::install_github("你要下载的包的github网页路径")

但是我自己实际测试之中出现了如下的Bug：

Error: Failed to install 'unknown package' from GitHub:
schannel: failed to receive handshake, SSL/TLS connection failed

好叭。
只能手动从Github下载包辽。
下载好zip文件解压之后之后在R中输入如下指令：

install("解压后的文件路径")

大功告成！

第二步是运行SQL。首先讲讲SELECT要怎么用

• 创建新对象：Recordset，用于储存SELECT运行出来的结果

Dim rs As New ADODB.Recordset
'如果你想要运行SQL的表是一个命了名的区域
strSQL="SELECT * FROM range"
’如果要运行的表是一整个worksheet
‘如果数据超过65536行，由于一个excel bug，只能用下面这种方法引用表
strSQL="SELECT * FROM [Sheet1$\$$]"
'如果是一个未命名区域
strSQL="SELECT * FROM [Sheet1$\$$A1:O60]"
'con是刚刚用到的ADO connection
rs.Open strSQL, con
rs.Close

• 把运行出来的结果保存到一个新建的excel表

Dim ws As Worksheet
        Set ws = output 'Application.Sheets.Add
        ws.Cells.ClearContents
        'Write the header
        Dim i As Long
        For i = 0 To rs.Fields.Count - 1
            ws.Cells(1, i + 1).Value2 = rs.Fields(i).Name
        Next i
        ws.Range("A2").CopyFromRecordset rs

完整代码如下

Sub ADOBD_SELECT()
Dim strFileName As String
Dim ConnectionString As String
'Fullname means the folder path is included
strFileName = ThisWorkbook.FullName

'From website https://www.connectionstrings.com/
'HDR=Yes -> my data set has heading
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFileName & "; Extended Properties=""Excel 12.0 Macro;HDR=Yes;"";"

Dim conn As New ADODB.Connection
'Recordset: where the result of the query will be placed
Dim rs As New ADODB.Recordset
Dim strSQL As String
'Write the query
strSQL = Range("Query")
conn.Open ConnectionString
        rs.Open strSQL, conn
        Dim ws As Worksheet
        Set ws = output 'Application.Sheets.Add
        ws.Cells.ClearContents
        'Write the header
        Dim i As Long
        For i = 0 To rs.Fields.Count - 1
            ws.Cells(1, i + 1).Value= rs.Fields(i).Name
        Next i
        ws.Range("A2").CopyFromRecordset rs
        rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub

群里有小伙伴说如果有用R的话可以用xaringan这个包，比较方便，参考https://slides.yihui.org/xaringan/zh-CN.html#1

需求分析：
首先，我们可以把文本用json的格式表示出来，这样就省去了解析文本的工作。
然后我们可以找一些现成的PPT模板，用json的数据填补其中的内容。PPT里可以填补的内容在代码中被统一称为Placeholder。

思路清楚之后就好做了。附上Github代码链接PPT_automation_tool

群里有同学提到了这个需求：

用Python分析文本自动生成ppt的思路：

根据下面的这一段文本自动生成ppt：

page（1）= maintitle【场外期权介绍】name【XXX】

page（2） = catalog 【1场外期权的概念// 2场外期权的准入条件//3场外期权的种类及应用//4不同情况下的应用实例】

page（3） = title【场外期权介绍】subtitle【1.1场外期权概述】
text【期权：是指一种合约，该合约赋予持有人在某一特定日期或该日之前的任何时间以固定价格购进或售出一种资产的权利。
场外期权：场外期权（一般简称为OTC ）是指，在非集中性的交易场所进行的非标准化的金融期权合约的交易。场内期权与场外期权的区别最主要就表现在期权合约是否标准化。
场外期权业务描述：证券公司为机构客户提供场外期权报价交易服务，交易要素根据客户需求灵活定制，支持日间询报价、即时达成交易，并提供交易策略定制、热点题材策略定制等产品服务，满足客户风险对冲、股票投资需求。】

page(1)是指生成第一页ppt，等号后面的都是这一页里面的内容
遍历这一段文本，读到maititle之后就自动将【】里面的内容放到大标题，读到name之后就将【】里面的内容放到下面的文本框里，如下图

page(2)是指生成第二页ppt，遍历这一段文本，读到catalog之后就将【】里面的内容放到目录里，//是换行符，如下图所示

page(3)是指生成第三页ppt，遍历这一段文本，读到title之后就把【】里面的内容放到顶上的标题里，读到subtitle之后就把【】里面的内容放到方框后的标题里，读到text之后就把【】里面的内容放到文本框里

封装完成之后，我发现如果把一个程序都封装到一个file里面，会导致程序启动很慢。
然后我在网上找到了这个答案

如果把程序封装到一个文件，在文件执行的时候还需要有一个unpack的操作。
因此，用 --onedir 把程序封装到一个文件夹里之后可以有效加快运行

pyinstaller --onedir -w pydocument.py

tqdm用法非常简单，只需在平常循环的对象上套上tqdm函数，就可以看到运行进度啦！

from tqdm import tqdm

for i in tqdm(range(100)):
    i  = i * 2

如果你用的是Jupyter notebook，建议用这个notebook.tqdm函数，或者auto.tqdm

from tqdm.notebook import tqdm
# from tqdm.auto import tqdm
for i in tqdm(range(100)):
    i  = i * 2

这个函数画出的Progress Bar更好看

如果你用的是Pandas apply，也可以用tqdm包显示运行进度哦

代码来源：https://stackoverflow.com/questions/18603270/progress-indicator-during-pandas-operations

import pandas as pd
import numpy as np
from tqdm import tqdm
# from tqdm.auto import tqdm  # for notebooks

df = pd.DataFrame(np.random.randint(0, int(1e8), (10000, 1000)))

# Create and register a new `tqdm` instance with `pandas`
# (can use tqdm_gui, optional kwargs, etc.)
tqdm.pandas()

# Now you can use `progress_apply` instead of `apply`
df.groupby(0).progress_apply(lambda x: x**2)

主要介绍一个包 tqdm。可以显示loop 运行的进度

tqdm documentation

Error handling:

On error resume next

定义变量

Dim strName As String 

Dim intX As Integer, intY As Integer, intZ As Integer 
'在下面的语句中，intX 和 intY 都声明为 **** Variant 类型；只有 intZ 声明为 Integer 类型。! 注意!
Dim intX, intY, intZ As Integer

循环: Do...While

Do While [Condition]
        [Statement~~strikethrough text~~]
        Loop

加快代码运行

Application.Calculation = xlManual
Application.ScreenUpdating = False

[Code]

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

核心代码如下：

# platform是数据集的名字
# 查看缺失值，如果有，需要填充
platform.isna().sum()

# 简单处理缺失值
platform.fillna(0, inplace = True)

# 拟合数据
from lifelines import CoxPHFitter
cph = CoxPHFitter()
cph.fit(platform, duration_col='Survival_years', event_col='Survival',formula = "factor1 + factor2")

# 查看结果
cph.print_summary()

Cox模型简单介绍

Cox模型是用来做生存分析的。但是也可以用来分析企业的生存状况，以及任何一种有终止状态的过程。

Cox模型的数据准备

数据需要有一列代表观测时间，还有一列代表观测时的状态（一般用0和1表示）。
以人的生存状态举例：观测500个人，如果观测的人是活着的，可以记录观测时人的年龄；如果观测的人已经死去，可以记录死亡时间。

Lifelines的CoxPHFitter包还要求不能有缺失值，所以需要对缺失值进行处理。

研究数据集列介绍

Survival 代表观测时的状态的变量
factor1 可能和死亡有关的因素1
factor2 可能和死亡有关的因素2
Survival_days 观测时死亡/已存活时间

Bokeh的效果图：