#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File : excleutil.py
# @Author: JinXudong
# @Date : 2017-12-1
# @Desc :
'''
导出mysql数据到excle
'''
importuuid
importMySQLdb
importxlrd
importxlwt
fromdbimportmysql
'''
导出mysql数据到excle
'''
defexport_excle(dbname, tablename, filepath):
conn = MySQLdb.connect('localhost','root','1', dbname,charset='utf8')
cursor = conn.cursor()
count= cursor.execute('select * from '+ tablename)
# 重置游标的位置
cursor.scroll(0,mode='absolute')
# 搜取所有结果
results = cursor.fetchall()
# 获取MYSQL里面的数据字段名称
fields = cursor.description
workbook = xlwt.Workbook();
sheet = workbook.add_sheet("sheet1",cell_overwrite_ok=True)
# 写上字段信息
foriinrange(0,len(fields)):
sheet.write(0, i,u'%s'% fields[i][0])
# 获取并写入数据信息
row=1
col=0
forrowinrange(1,len(results) +1):
forcolinrange(0,len(fields)):
sheet.write(row, col,u'%s'% results[row -1][col])
workbook.save(filepath)
print"导出"+ tablename +"完成........."
# demo
# export_excle("xdfstar_db","t_employee","F://t_employee.xls");
'''
读取excle文件写入到数据库中
'''
defread_excel():
# 打开文件 r'防止内容转义
workbook = xlrd.open_workbook(r'D:\test.xlsx')
# 获取所有sheet
printworkbook.sheet_names()
sheet2_name= workbook.sheet_names()[1]
# 根据sheet索引或者名称获取sheet内容
sheet2= workbook.sheet_by_index(1)# sheet索引从0开始
sheet2 = workbook.sheet_by_name('Sheet1')
# sheet的名称，行数，列数
print"sheet2name:"+ sheet2.name, sheet2.nrows, sheet2.ncols
# 获取整行和整列的值（数组）
rows= sheet2.row_values(0)# 获取第一行内容
cols= sheet2.col_values(1)# 获取第二列内容
rownum= sheet2.nrows
print"高级写法"*3
forrinrange(1, sheet2.nrows):# 从第一行到尾行
sql ="INSERT INTO employee VALUES (%s, %s, %s)"
uid= sheet2.cell(r,0).value
name = sheet2.cell(r,1).value
address = sheet2.cell(r,2).value
# values = (uuid.uuid1(),"1130","公司")
values = (uuid.uuid1(), name, address)
mysql.committomysql("test", sql, values)
# demo
# mysql.truncattable("test","delete from employee")
