利用xlwings库，实现文件名与excel表格内容的极大似然匹

1.背景

• 上一篇利用xlwings库，实现文件名与excel表格内容的极大似然匹配标注（一）文章（以下简称文章一）的末尾提到存在的不足与改进的方向。

• 先来简单说明一下任务：当前文件夹中存放着目标表格（hotels.xlsx），表格中内容是收集上来各个酒店的详细信息，包括全称以及其他信息，表格如下图所示：

  
  部分表格.png

  目前征集上来收取的各个宾馆的承诺书存放在当前目录下“住宿行业承诺书”这个文件夹。我们需要根据收取到的承诺书，在hotels.xlsx上面宾馆名称一列标出颜色，从而筛选出没有交的企业。

  
  当前目录结构 住宿行业承诺书文件夹内部

• 文章一的不足在于，承诺书的文件名称需要与表格中的完全匹配，一个字符都不能差。然而现实工作中这样的要求是很苛刻的，一般收取上来的承诺书有宾馆的简称就已经很不错了。

• 带着问题，博主咨询了在字节、快手互联网大厂工作的三位大佬@江风不眠晚来雾@黄_01fd@麻辣小王子。三位大佬各自提出了切实有效的解决方法。经过电话连线讨论，博主最终决定采用如下的方法：计算每个文件名与表格每个宾馆名称文本的相似度，文本相似度最高的，确定为极大似然匹配，之后在表格中标注。

2.实现

• 需要用到jieba分词以及gensim等工具。
• 思路如下：

1.在hotels.xlsx单元表格中获取所有宾馆的名字全称并存至列表生成文本集hotel_names。在住宿行业承诺书文件夹获取所有宾馆简称，生成关键字keywords库；

2.用jieba分词对hotel_names处理，生成分词列表hotel_names；

3.基于分词列表hotel_names建立词典dictionary，并获得词典特征数num_features；

4.基于词典dictionary，将分词列表集hotel_names转换成稀疏向量集corpus，称作语料库corpus；

5.用词典dictionary将keywords库中的每一个keyword转换成稀疏向量；

6.创建TF-IDF模型，传入语料库corpus训练；

7.用TF-IDF模型处理语料库，得到sparse_matrix；

8.对于每一个keyword，用TF-IDF模型处理，得到相似度。获取相似度最大的那一个的索引，在表格上找出标黄。

• 代码贴上：

'''
Descripttion: 利用xlwings库，实现文件名与excel表格内容的极大似然匹配标注
version: V2.0
Author: HK
Date: 2022-04-11 22:25:44
LastEditors: HK
LastEditTime: 2022-04-13 23:36:30
'''

import os
from sklearn.decomposition import sparse_encode
import xlwings as xw
from jieba import lcut
from gensim.similarities import SparseMatrixSimilarity
from gensim.corpora import Dictionary
from gensim.models import TfidfModel
import numpy as np

hotel_names = []

hotel_file_path = "./hotels.xlsx"

hotel_names_path = "./住宿行业承诺书/"
keywords = os.listdir(hotel_names_path)

for i in range(len(keywords)):
    index = keywords[i].rfind('.')
    keywords[i] = keywords[i][0:index]

try:
    app = xw.App(visible=False,add_book=False)
    wb = app.books.open(hotel_file_path)
    sht = wb.sheets("Sheet1")

    info = sht.used_range
    nrows = info.last_cell.row

    for i in range(3,nrows + 1):
        rng = "C%d" % i
        item = sht.range(rng)
        hotel_names.append(item.value)

    hotel_names = [lcut(hotel_name) for hotel_name in hotel_names]

    dictionary = Dictionary(hotel_names)
    num_features = len(dictionary.token2id)

    corpus = [dictionary.doc2bow(hotel_name) for hotel_name in hotel_names]

    kw_vectors = [dictionary.doc2bow(lcut(keyword)) for keyword in keywords]

    tfidf = TfidfModel(corpus)

    tf_texts = tfidf[corpus]
    sparse_matrix = SparseMatrixSimilarity(tf_texts,num_features)

    for kw_vector in kw_vectors:
        tf_kw = tfidf[kw_vector]
        similarities = sparse_matrix.get_similarities(tf_kw)
        index = np.argmax(similarities)
        item = sht.range(f'C{index + 3}')
        item.color = (255,255,0)

finally:
    wb.save()
    wb.close()
    app.quit()

3.总结

• 跑了二十多个测试用例，结果还不错，后面还需要增加测试用例，进行准确率分析。感谢三位大佬的建议，让博主减少了很多工作量，也恳请各位看官批评指正。

参考文献：

1.Python+gensim-文本相似度分析
2.使用gensim计算文本相似度