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

Source: Internet
Author: User
Tags python script

#!/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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.