```java
import xlrd
import pymysql
import datetime
filePath = input("文件地址:")
tabName = input("表名:")
isCreateTabe = input("是否建表(Y/N):")
file = xlrd.open_workbook(filePath) #打开Excel文件
sheet_1 = file.sheet_by_index(0) #根据sheet页的排序选取sheet
row_content = sheet_1.row_values(1) #获取指定行的数据,返回列表,排序自0开始
row_number = sheet_1.nrows #获取有数据的最大行数
col_number = sheet_1.ncols #获取有数据的最大列数
titles = sheet_1.row_values(0)
if(isCreateTabe == 'Y'):
sql0 = 'DROP TABLE IF EXISTS '+tabName+';'
sql1 = 'create table '+tabName+' ('
for c in range(col_number):
sql1 += str(titles[c])
ctype = sheet_1.cell(1,c).ctype
if(ctype == 3):
sql1 += ' datetime'
elif(ctype == 2):
sql1 += ' double'
else:
sql1 += ' varchar(255)'
if(c < col_number-1): sql1+=',\n'
sql1 += ');'
print('建表语句:')
print(sql1)
sql2 = 'insert into '+tabName+' values'
for r in range(1,row_number):
sql2 += '('
row = sheet_1.row_values(r)
for c in range(col_number):
val = str(row[c])
if(sheet_1.cell(r,c).ctype == 3):
date = xlrd.xldate_as_tuple(sheet_1.cell(r,c).value,0)
val = datetime.datetime(*date)
val = str(val)
sql2 += '\''+val+'\''
if(c < col_number-1): sql2+=','
sql2 += ')'
if(r < row_number-1): sql2+=',\n'
sql2 += ';'
print('数据语句:')
print(sql2)
db = pymysql.connect('127.0.0.1','root','12345678','dbgirl') #建立数据库连接
cusor = db.cursor() # 使用 cursor() 方法创建一个游标对象 cursor
try:
# 执行sql语句
if(isCreateTabe == 'Y'):
cusor.execute(sql0);
cusor.execute(sql1);
cusor.execute(sql2);
# 提交到数据库执行
db.commit()
except:
# 发生错误时回滚
print('repr(e):\t', repr(e))
db.rollback()
# 关闭数据库连接
db.close()
print('导入成功!')
```
网友评论