python3 根据sql导出excel文件 支持xls和xlsx
代码简介：
sql_output_excel函数是用来导出excel文件的，其他两个函数是导出xls和xlsx格式的。
使用pymysql链接数据库，如果是使用sqlalchemy，可以直接打开注释的两行，把下面的注释掉
重要提示：xlrd2.0版本，不支持读写xlsx格式，1.2.0才可以支持
pip install xlrd==1.2.0
代码如下：
import xlwtimport openpyxlfrom openpyxl.styles import PatternFill, Font, Border, Sideimport xlrdimport pymysqlHOST = '192.168.0.11'PORT = 3306USER = 'root'PWD = '123456'DB = 'my_bill'def sql_output_excel(sql, filename, sheet_name, excel_format="xlsx", sum_title=None):"""输入sql，导出xls或者xlsx格式的表格:param sql::param filename::param sheet_name: sheet页的名字:param excel_format: xls 或者 xlsx:param sum_title: 求和字段:return: 无返回"""# title = db.session.execute(sql).keys()# data = db.session.execute(sql).fetchall()db = pymysql.connect(host=HOST, user=USER, password=PWD, database=DB, port=PORT)cursor = db.cursor(cursor=pymysql.cursors.DictCursor)cursor.execute(sql)cursor2 = db.cursor()cursor2.execute(sql)title = cursor.fetchone().keys()data = cursor2.fetchall()if excel_format == 'xls':output_excel_xls(title, data, filename, sheet_name, sum_title=sum_title)elif excel_format == 'xlsx':output_excel_xlsx(title, data, filename, sheet_name, sum_title=sum_title)def output_excel_xls(title, data, filename, sheet_name, sum_title=None):book = xlwt.Workbook(encoding='utf-8')# 设置颜色pattern = xlwt.Pattern()pattern.pattern = xlwt.Pattern.SOLID_PATTERNpattern.pattern_fore_colour = 22# 设置边框borders = xlwt.Borders()borders.right = xlwt.Borders.THINborders.top = xlwt.Borders.THINborders.bottom = xlwt.Borders.THINborders.left = xlwt.Borders.THINstyle = xlwt.XFStyle()style.pattern = patternstyle.borders = borderssheet1 = book.add_sheet(sheet_name)# 写入头部col_num = 0for single_data in title:sheet1.write(0, col_num, single_data, style)col_num += 1# 写数据row_num = 1for row_data in data:col_num = 0for single_data in row_data:if single_data is None:single_data = ""sheet1.write(row_num, col_num, str(single_data))col_num += 1row_num += 1book.save(filename)def output_excel_xlsx(title, data, filename, sheet_name, sum_title=None):book = openpyxl.Workbook()sheet1 = book.activesheet1.title = sheet_namedata.insert(0, title)# 写数据row_num = 1for row_data in data:col_num = 1print('-------')print(row_data)for single_data in row_data:sheet1.cell(row_num, col_num, single_data)col_num += 1row_num += 1max_row = sheet1.max_row + 1# 修改单元格格式fill = PatternFill(patternType='solid', start_color='38B0DE')font = Font(bold=True)thin = Side(border_style='thin', color='000000')border = Border(left=thin, right=thin, bottom=thin, top=thin)for i in sheet1[1]:i.fill = filli.font = fonti.border = border# 修改时间字段宽度for i in sheet1[1]:if '时间' in i.value or 'time' in i.value:sheet1.column_dimensions[i.column_letter].width = 20# 求和if sum_title and isinstance(sum_title, list):for i in sheet1[1]:if i.value in sum_title:sum_column = list(sheet1[i.column_letter])sum_column.pop(0)sum_num = 0for single_data in sum_column:if single_data.value:try:sum_num += int(single_data.value)except Exception as e:print(e)breakelse:sheet1.cell(max_row, i.column, sum_num)book.save(filename)def get_excel_data(excel_file_name):table = xlrd.open_workbook(excel_file_name)sheet1 = table.sheet_by_name("奖励明细数据")header = sheet1.row_values(0)data_list = []for i in range(1, sheet1.nrows, 1):info = dict()row_data = sheet1.row_values(i)for k in range(len(header)):if header[k] == "手机号":info[header[k]] = str(int(row_data[k]))else:info[header[k]] = row_data[k]data_list.append(info)result = dict()result["count"] = sheet1.nrowsresult["header"] = headerresult["data"] = data_listreturn resultif __name__ == '__main__':sql = "select * from source order by createTime"sql_output_excel(sql, "t.xls", "sheet1", excel_format="xls")
