使用pandas对excel数据做块状提取
1、代码:
# coding=utf-8
'''
Function : excel to hrl
Author : wgn
Created time : 2020-8-13
Other :python=2.7.11,pandas=0.24.2
'''
import xlrd
import pandas as pd
import sys
#panda块需要
reload(sys)
sys.setdefaultencoding('utf-8')
#打开文件,获取excel文件的workbook(工作簿)对象
workbook=xlrd.open_workbook("test.xlsx") #文件路径hrl/XXX.xls
names=workbook.sheet_names()
print(names) #以列表形式,输出所有的表名
print(len(names))#备注:需要去重的话用:set1=set(name)
for i in range(1, len(names)): # 加【1,】跳过第一个sheet页【summary】
# 使用pandas模块读取数据, 默认头行为列表名,去掉【,header=None】不要第一行数据
df = pd.read_excel('test.xlsx', sheet_name=names[i])
print('\n'+names[i]+'.hrl'+'开始写入...')
# 写入井号分隔sep#
df.to_csv('./hrl/'+names[i]+'.hrl', header=None, sep='#', index=False)
# 根据文档需求,插入3行数据头
with open('./hrl/'+names[i]+'.hrl','r+') as file:
content = file.read()
file.seek(0,0)
file.write('#head;hrl;2.0;utf8\n'+
'#ref#speechfile#speaker#gender#reference word sequence#topic#;slotname#;slotvalue\n'+
'head\n'+content)
print(names[i]+'.hrl'+'完成!')
2、运行结果: