作者:乔山办公网日期:
返回目录:excel表格制作
读取mysql数据,填写数据到excel
from pyexcel_xls import save_data
from pyexcel_xls import get_data
from collections import OrderedDict
import mysql.connector
#和数据库建立连接
cnx =mysql.connector.connect(user='root', password='',
host='127.0.0.1',
database='test')
#查询语句
sql = "select my_name,my_value from tbl_members "
#执行查询
cursor.execute(sql)
#获得查询结果
result = cursor.fetchall()
cursor.close()
cnx.close()
#打开预定义表头文件
xls_header= get_data("d:/xh.xls")
#获得表头数据
xh = xls_header.pop("Sheet1")
#拼接整表数7a64e58685e5aeb9333据
xd = OrderedDict()
xd.update({"Sheet 1":xh+result})
#保存到另一个文件中
save_data("d:/xd.xls",xd
import xlrd#open the .xls filexlsname="test.xls"book = xlrd.open_workbook(xlsname)#build a dictionary of the names->sheets of the booksd={}for s in book.sheets(): sd[s.name]=s#obtain Sheet "Foglio 1" from sheet names dictionarysheet=sd["Foglio 1"]#print value of the cell J141print sheet.cell(142,9)print sheet.cell(142,9)可以获得zd142行第9列那专个单元格属的值