Excel 日常收集公式

:::tip{title="生成100万行不重复ID"}
使用RandomDataCTE 产生100万行不重复的数值
:::



```
	-- 使用 CTE 生成随机数据
WITH RandomDataCTE AS (
    SELECT TOP 1000000
      id =  ABS((CHECKSUM(NEWID())+cast((RAND()*(50-10)+10)*1000000 as bigint))%900000000000)+100000000000
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
)

-- 查询随机数据
SELECT * FROM RandomDataCTE

 
```

SqlServer 生成不重复ID

:::info{title="电信超级管理员密码"}
电信超级管理员密码
:::

### 账号：telecomadmin
### 密码：nE7jA%5m

电信猫的超级管理员

:::tip{title="使用python脚本实现将图片表格转换为excel文档"}
使用python脚本实现将图片表格转换为excel文档
:::

![image.png](/static/img/3d6029b1501fcba07c3942d01688fada.image.webp)



# 步骤一，开通腾讯云的【通用文字识别服务】

![image.png](/static/img/935656505d281ca7681039ea04374c1f.image.webp)
# 步骤二、申请一个apiKey

![image.png](/static/img/7fe3f65ea1ebcfc40a4c9bbb30a8ec2f.image.webp)
# 步骤三 安装腾讯的 python库
```
pip3 install tencentcloud_sdk_python
```

![image.png](/static/img/2a32913a7249462e4fd23b54327fa493.image.webp)
# 步骤四 建立脚本
`imgToExcel.py`
```python
#coding=utf-8
import sys
import ocr
import yaml

def get_yaml_data(yaml_file):
    # 打开yaml文件
    file = open(yaml_file, 'r', encoding="utf-8")
    file_data = file.read()
    file.close()
    # 将字符串转化为字典或列表
    data = yaml.load(file_data, Loader=yaml.FullLoader)
    return data

def imageToExcel(pic_path):

    config = get_yaml_data("config.yml")

    # 使用ocr进行转换
    trans = ocr.OCR()
    path_excel = trans.img_to_excel(
        pic_path,
        image_path=pic_path,
        secret_id=config['secret_id'],
        secret_key=config['secret_key'],
    )

if __name__ == '__main__':
    pic_path = sys.argv[1]
    imageToExcel(pic_path)
```
`ocr.py`
```python
#coding=utf-8
from tencentcloud.common import credential
from tencentcloud.common.profile.client_profile import ClientProfile
from tencentcloud.common.profile.http_profile import HttpProfile
from tencentcloud.ocr.v20181119 import ocr_client, models
import base64

# OCR识别封装
class OCR(object):

    def img_to_excel(self,
                     output_file_name,
                     image_path,
                     secret_id,
                     secret_key):
        # 实例化一个认证对象，入参需要传入腾讯云账户secretId，secretKey
        cred = credential.Credential(
            secret_id,
            secret_key
        )
        # 实例化client对象
        httpProfile = HttpProfile()
        httpProfile.endpoint = "ocr.tencentcloudapi.com"
        clientProfile = ClientProfile()
        clientProfile.httpProfile = httpProfile
        clientProfile.signMethod = "TC3-HMAC-SHA256"
        client = ocr_client.OcrClient(cred, "ap-shanghai", clientProfile)
        # 实例化一个请求对象
        #req = models.GeneralFastOCRRequest()
        req = models.GeneralFastOCRRequest()
        # 读取图片数据，使用Base64编码
        with open(image_path, 'rb') as f:
            image = f.read()
            image_base64 = str(base64.b64encode(image), encoding='utf-8')
        req.ImageBase64 = image_base64
        # 通过client对象调用访问接口，传入请求对象
        resp = client.RecognizeTableOCR(req)
        # 获取返回数据（Data为Base64编码后的Excel数据）
        data = resp.Data
        # 转换为Excel
        output_file_name = str(output_file_name)
        path_excel = output_file_name + ".xlsx"
        with open(path_excel, 'wb') as f:
            f.write(base64.b64decode(data))
        return path_excel
```
`config.yml` 替换你自己的apikey信息
```
#coding=utf-8
secret_id: a
secret_key: a
```
# 步骤五 执行脚本见证奇迹
```python
python3 imgToExcel.py 111.png
```
![image.png](/static/img/3d6029b1501fcba07c3942d01688fada.image.webp)

图片表格转excel文档

:::tip{title="iTerm2"}
安装完毕iterm后，之前在终端中的命令无法运行
:::




现在终端中已经安装了nvm 和npm 还有npm下的一些其他服务但是在iterm2中发现这些命令均无法运行，此时修改下iterm2的配置文件即可

```
# 打开配置文件
# 找到 plugins 选项，添加命令项并保存
# 我的改动：'plugins=(git)' -> 'plugins=(git node nvm npm)'
open ~/.zshrc
 
# 启用修改
source ~/.zshrc
 
# 重启 iTerm
```