# 03. 为所有员工按姓名拼音顺序编号并填入表格

Evzen / 2023-08-31 / 原文

03.为所有员工按姓名拼音顺序编号并填入表格

python的excel文件类型转换
python的拼音排序
python的修改excel单元格值

文件批量转换.py

由于python库的限制，在进行文件的遍历时，需要将所有【xls文件】转换为【xlsx文件】

'''
Author: Evzen
Date: 2023-08-30 12:04:36
Description: Moidfy here please
FilePath: \undefinedc:\Users\20952\Desktop\文件批量转换.py
'''
# %%
import os
import pandas as pd
from openpyxl import load_workbook
import win32com.client as win32
import pandas as pd
from itertools import chain
from pypinyin import pinyin, Style


# %%
def save_as_xlsx(fname):
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    wb = excel.Workbooks.Open(fname)
    
    wb.SaveAs(fname+"x", FileFormat = 51)    #FileFormat = 51 is for .xlsx extension
    wb.Close()                               #FileFormat = 56 is for .xls extension
    excel.Application.Quit()


if __name__ == "__main__":
    package = "C:\\Users\\20952\\Desktop\\汇总\\"
    files = os.listdir(package)
    for fname in files:
        if fname.endswith(".xls"):
            save_as_xlsx(package + fname)
            print(fname + "格式转换完成")
        else:
            print("跳过非xls文件："+fname)

编号与贴纸.py

将文件按照文件名拼音排序，并通过排序所得的顺序对每一位员工进行格式为【编号：00x】的编号。
将编号填入每一个表格的C2单元格内。
一共有382位员工。
将上述获得的编号与姓名、性别一一对应整理成册，用于贴纸打印。

'''
Author: Evzen
Date: 2023-08-30 12:08:24
Description: Moidfy here please
FilePath: \Desktop\编号与贴纸.py
'''
# %%
import os
import pandas as pd
from openpyxl import load_workbook
import win32com.client as win32
import pandas as pd
from itertools import chain
from pypinyin import pinyin, Style

# %%
g = os.walk(r"汇总")  

def to_pinyin(s):
    # '''转拼音

    # :param s: 字符串或列表
    # :type s: str or list
    # :return: 拼音字符串
    # >>> to_pinyin('你好吗')
    # 'ni3hao3ma'
    # >>> to_pinyin(['你好', '吗'])
    # 'ni3hao3ma'
    # '''
    return ''.join(chain.from_iterable(pinyin(s, style=Style.TONE3)))

i=0
# 遍历data文件夹 获取excel文件名称
if __name__ == "__main__":
    package = "C:\\Users\\20952\\Desktop\\汇总\\"
    files = os.listdir(package)
    #将获得的文件名列表按照中文拼音顺序进行排序
    #这里需要自己借用pypinyin库，因为python本身不支持拼音排序，它读取文件的排序是乱的
    files=sorted(files, key=to_pinyin)
    for fname in files: 
        #获取文件路径
        #pd按照路径读取文件
        i=i+1
        num='编号：'+str(i).rjust(3,'0')
        print(fname)
        workbook = load_workbook(filename=package+fname)
        sheet = workbook.active
        # 直接修改源文件
        sheet["C2"] = num
        workbook.save(filename=package+fname)

print(i)

# %%
# pd读取姓名-性别表
xingbie=pd.read_excel('XINGBI.xls')


# %%
j=0
number=[]
pname=[]
sex=[]
for fname in files:
    j=j+1
    number.append(str(j).rjust(3,'0'))
    print(fname)
    name=fname.split(".",1)[0]
    # 如果在性别对照表中可以找到员工 则获取该员工的性别
    if(len(xingbie[xingbie['姓名'].isin([name])]['性别'])):
        index = xingbie[xingbie.姓名 == name].index.tolist()[0]  
        value = xingbie.iloc[index,1]
    else:
        # 如果找不到 则填充为空值（空格）
        value=' '
    sex.append(value)
    pname.append(name)

output=pd.DataFrame([number,pname,sex])
output.to_excel('bianhao.xlsx')

可能遇到的报错情况

报错：ValueError: Unable to read workbook: could not read stylesheet from C:\Users\20952\Desktop\汇总\陈小荣.xlsx. This is most probably because the workbook source files contain some invalid XML. Please see the exception for more details.

解决办法：是openpyxl版本的问题,不安装3.1.2,用pip install openpyxl==3.0.9,更换版本