#coding=utf-8
importxlrdimportsqlite3importosimportuuiddefinsert_data_to_db(path):
wb=xlrd.open_workbook(path)print(wb.sheet_names())
sheet=wb.sheets()[0]
nrows=sheet.nrows#获取任务行里索引
begin_index =0
end_index=begin_indexfor i inrange(nrows):
col_value= sheet.row(i)[1].valueif(begin_index == 0 and col_value == "责任人"):
begin_index= i + 1end_index=begin_indexif(begin_index != 0 and col_value == ""):
end_index=ibreak
print(begin_index)print(end_index)#检查创建数据库
db_path = "zb.db"is_db_exist=os.path.exists(db_path)
conn=sqlite3.connect(db_path)
cursor=conn.cursor()if notis_db_exist:
conn.execute('''CREATE TABLE IF NOT EXISTS Tasks(
Id varchar PRIMARY KEY,
Name varchar,
Task varchar,
PercentageTask varchar,
BeginTime datetime,
EndTime datetime,
Memo varchar,
FileName varchar)''')
conn.commit()#获取文件名
(_file_path, file_name) =os.path.split(path)
sql= '''INSERT OR REPLACE INTO Tasks
(Id,Name,Task,PercentageTask,BeginTime,EndTime,Memo,FileName) VALUES (?,?,?,?,?,?,?,?)'''
#遍历内容插入数据库
for i inrange(begin_index, end_index):
col_id=str(uuid.uuid1())
col_name= sheet.row(i)[1].value
col_task= sheet.row(i)[2].value
col_percentage_task= sheet.row(i)[3].value
col_begin_time= sheet.row(i)[4].value
col_end_time= sheet.row(i)[5].value
col_memo= sheet.row(i)[6].value
cursor.execute(sql, (col_id, col_name, col_task, col_percentage_task,
col_begin_time, col_end_time, col_memo, file_name))
conn.commit()defmain():
file_dir= "zb"
for _root, _dirs, files inos.walk(file_dir):for file infiles:
insert_data_to_db(file_dir+"/"+file)
main()