import pymysql
import xlrd
import math
import xlsxwriter
def sqltoxlsx(): ##############导出xlsX格式
###数据库连接
conn = pymysql.connect(
host=ip,
user='root',
passwd='passwd',
db='db'
)
# 获得游标
cur = conn.cursor()
sql='SELECT * from sheet' ##数据库查询
cur.execute(sql)
results = cur.fetchall() #所有结果
fields = cur.description ##获取字段
workbook = xlsxwriter.Workbook(r'E:\all.xlsx')
sheet = workbook.add_worksheet('table')
# 写上字段信息
for field in range(0, len(fields)):
sheet.write(0, field, fields[field][0])
for row in range(1, len(results) + 1):
for col in range(0, len(fields)):
sheet.write(row, col, results[row - 1][col])
contentRow = len(results) + 1 #列表元素个数 = 内容行数
#从content获取要写入的第一列的内容,存入列表
first_col = [] for i in range(contentRow):
first_col.append(results[i][1]) print("first_col",first_col) #去掉列表中重复元素,并且顺序不变
nfirst_col = list(set(first_col)) nfirst_col.sort(key=first_col.index) # sort排序与原顺序一致
print("nfirst_col",nfirst_col) row = 1
for i in nfirst_col:
count = first_col.count(i) # 计算元素的重复个数,比如测试 :3
uprange = row+count-1 #合并范围后的上行数
xlsheet.write_merge(row,uprange,0,0,i) #合并单元格写入内容
row = uprange+1 #从下一行开始写入
workbook.close()