Python script--generates Excel, connects to the database, and writes the specified data to Excel

#!/usr/bin/python
#-*-Coding:utf-8-*-
#!/usr/bin/env python

Import MySQLdb
Import Sys
Import Xlsxwriter

Reload (SYS)
Sys.setdefaultencoding (' Utf-8 ')

#创建一个EXCEL文件
Workbook = Xlsxwriter. Workbook (' name.xlsx ') #EXCEL的文件名

#创建一个工作表对象

worksheet_1 = Workbook.add_worksheet (' name_1 ') #sheet页面1的名字
worksheet_2 = Workbook.add_worksheet (' name_2 ') #sheet页面2的名字

#需要更多的sheet页面以此类推

Title_1 = [u ' xxx ', u ' xxx ', u ' xxx ', u ' xxx '] #定义数据表头列表
title_2 = [u ' xxx ', u ' xxx ', u ' xxx ', u ' xxx ']

format_1 = Workbook.add_format () #定义format格式对象
Format_1.set_border (1) #定义format对象单元格边框加粗 (1 pixels) format

Format_title_1 = Workbook.add_format () #定义format_title格式对象
Format_title_1.set_border (1) #定义format_title对象单元格边框加粗 (1 pixels) Format Object
Format_title_1.set_bg_color (' #cccccc ') #定义format_title对象单元格背景颜色为 the format of ' #cccccc '

format_title_1.set_align (' Center ') #定义format_title对象单元格居中对齐的格式
Format_title_1.set_bold () #定义format_title对象单元格内容加粗的格式

Worksheet_ 1.write_row (' A1 ', title_1,format_title_1)

Conn =  mysqldb.connect (host= "xxx", user= "xxx", passwd= "xxx", db= "xxx", port=3306,charset= ' UTF8 ') #连接数据库, Host: Database link, User: account, passwd: password, DB: Database name

cursor_1 = Conn.cursor ()
Cursor_1.execute ("Select xxx") #执行sql语句
Data_1 = Cursor_1.fetchall () #数据赋值给data_1

List_1=range (Len ( data_1)) #测出data_1长度, assign a value to list_1

in range list

#按照一定的格式和位置循环写入EXCEL表格, where you can try it yourself
For I in List_1:
For j in Range (4):
Worksheet_1.write (I+1,j,data_1[i][j]) #写入EXCEL表格
i + = 1

Cursor_1.close ()
Conn.close ()
Workbook.close ()

This article is from the "one Gourd soy sauce" blog, please be sure to keep this source http://zhushenghang.blog.51cto.com/11105203/1825409

Python script--generates Excel, connects to the database, and writes the specified data to Excel