需求
将某个目录下多个特定文本文件,合并到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()