【python】将多个tsv文件合并到excel表中

需求

将某个目录下多个特定文本文件，合并到excel表中。并要求将文件名作为sheet名，每个sheet中第一行第一个单元格填写对应文件的所在路径，单元格格式使用Times New Roman。

使用方法：

# yourdir: 输入多个特定文件所在目录
# end_str: 以end_str结尾的特定文件（要进性合并的文件）
# outout_xlsx: 指定输出的excel文件名
# suffix: 为了获取sheet名，用于分割的字符(串)，然后去除该分割(串)符之后的字符
# prefix: 如果文件前缀有该字符(串)，将去除该字符(串)之前的字符
python merge_tsv2xlsx.py ${yourdir} ${end_str} ${outout_xlsx} ${suffix} ${prefix}

示例：

# 比如"/your/files/dir"目录下有如下文件：
$ ls /your/files/dir
a.1.spe.tsv
a.2.spe.tsv
a.3.spe.tsv
a.x.spe.txt
# 合并该目录下所有.spe.tsv文件，并将sheet名命名为1,2,3,4
python merge_tsv2xlsx.py /your/files/dir .tsv /your/files/dir/a.merge.spe.xlsx .spe.tsv a.

注：后两个参数用于去除不需要的字符，如果不对前缀和后缀去除，直接设定为""（空）。

合并后的excel示例：

实现

命名为merge_tsv2xlsx.py

import sys
import os
import openpyxl
import pandas as pd
from openpyxl.styles import Font
from openpyxl.cell import WriteOnlyCell
from openpyxl.comments import Comment

tsv_dir = sys.argv[1]
end_str = sys.argv[2]  # eg: .txt
xlsx_out = sys.argv[3]
split_str = sys.argv[4]  # sheet name from split filename
start_str = sys.argv[5]  # sheet name: rm start str of filename

def str2num(str_a):
    try:
        n = float(str_a)
    except ValueError as e:
        # print("NOTE: not num: ", str_a)
        n = str_a
    finally:
        pass
    return n


def write_tsv2xlsx(tsv_file, sheet_name, xlsx_file):
    wb = openpyxl.load_workbook(xlsx_file)
    ws = wb.create_sheet(title=sheet_name, index=0)
    cell = WriteOnlyCell(ws, value=tsv_file)
    cell.font = Font(name='Times New Roman', size=11, color="D3D3D3")
    cell.comment = Comment(text="FilePath", author="yourname")

    ws.append([cell])
    with open(tsv_file, 'r') as f_in:
        for line in f_in:
            line_info_tmp = line.strip().split('\t')
            # line_info = [str2num(s) for s in line_info_tmp]
            # ws.append(line_info)
            cells = []
            for s in line_info_tmp:
                cell = WriteOnlyCell(ws, value=s)
                cell.font = Font(name='Times New Roman', size=11)
                cells.append(cell)
            ws.append(cells)
    wb.save(xlsx_file)


def merge_all_tsv():
    for file_n in os.listdir(tsv_dir):
        if not file_n.endswith(end_str):
            continue
        tsv_file_path = os.path.join(tsv_dir, file_n)
        if split_str:
            sname = file_n.split(split_str)[0]
        else:
            sname = file_n
        if start_str:
            name = sname.split(start_str)[1]
        else:
            name = sname
        print("#NOTE:%s file is writed to xlsx %s!" % (tsv_file_path, xlsx_out))
        write_tsv2xlsx(tsv_file_path, name, xlsx_out)

        
if __name__ == '__main__':
    if os.path.isfile(xlsx_out):
        print("**ERROR**: %s exists! Please change .xlsx file name" % xlsx_out)
        sys.exit(1)
    df_xlsx = pd.DataFrame()
    df_xlsx.to_excel(xlsx_out)
    merge_all_tsv()