python与word交互_python将数据写入excel代码，python与office交互

# -*- coding: utf-8 -*-

from smartframe.header import *

import pymysql

import json

import importlib,sys

importlib.reload(sys)

import re

import xlwt

from xlwt import Workbook, Formula

import xlrd

##连接数据库

conn =pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='XXXX',charset='utf8')

# 创建游标# # 游标设置为字典类型

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 执行sql，并返回受到的影响行数#####

#sql='select * from ceshi'

cursor.execute(sql)

# 获取所有数据########

result = cursor.fetchall()

#获取获取第一条数据#####

#result = cursor.fetchone()

#print(result)

#print (len(result))

datanew = {}

for item in result:

#print(item)

datanew[item['user_id']] = list(eval(item['answer']))

#print(datanew)

book = Workbook()

sheet1 = book.add_sheet('Sheet 1')

#字典数据

#循环字典key 的值

num = [a for a in datanew]

#num1=[]

#for a in datax:

# print (a)

#if a not in num1:

#num1.append(a)

#num.sort()

#print(num)

lennum=len(num)

#print(lennum)

for a in range(lennum):

#print(num[a])

lena=len(datanew[num[a]]);

#print(lena)

lena1=datanew[num[a]];

lena1.sort()

#print(lena1)

a1=num[a];

sheet1.write(a+1,0,a1)

#print(lenanew)

b1x=[]

for b in range(lena):

#print(b)

#print (lena1[b]);

#print((lena1[b])[0])

#print((lena1[b])[1])

b1=(lena1[b])[0]

b2=(lena1[b])[1]

b1x.append(b1);

#print(b)

sheet1.write(a+1,b+1,b2)

#print(b2)

if a==0:

#print(b1x)

for y in range (len(b1x)):

bx=b1x[y]

#print(b1x[y])

#print(y)

sheet1.write(0,y+1,bx)

book.save('C:/Users/Administrator/Desktop/ceshi0_1.xls')

conn.commit()

#获取自增id

new_id = cursor.lastrowid

print (new_id)

# 关闭游标

cursor.close()

# 关闭连接

conn.close()