Novice Learning Python (11) Read/modify/export Excel

1) Read Excel, use XLRD module

Import xlrd
Book =xlrd.open_workbook (' App_student.xls ')
Sheet=book.sheet_by_index (0)            #根据顺序获取的
# sheet2=book.sheet_by_name (' Sheet1 ')   #也可根据名字获取
# Print (Sheet.cell (0,0). Value)          #指定sheet页里面行和列获取数据
# print (sheet.row_values (0))            #获取到第几行的内容
# print (sheet.nrows)                    #获取到excel里面总共有多少行
For I in Range (sheet.nrows):            #循环获取到每行数据
    Print (Sheet.row_values (i))
Print (sheet.ncols)                      #总共多少列


2) modify Excel to use the Xrld module

Import xlrd
From xlutils import copy #导入xlutils模块里的copy方法

book=xlrd.open_workbook (' App_student.xls ') #先用xlrd模块, open an Excel
new_book=copy.copy (book) #通过xlutils这个模块里面的copy方法, copy an Excel
sheet=new_book.get_sheet (0) #获取sheet页
lis=[' number ', ' name ', ' gender ', ' age ', ' Address ', ' class ', ' Mobile number ', ' Gold coin ' , '
col=0
For i in LIS: #把list里的值写入excel的0行 (counting starting from 0)
sheet.write (0,col,i)
Col +=1
new_book.save (' App_student.xls ') #再把新excel命名为之前文档的名字


3) Generic export Excel, using XLWT module

Import PYMYSQL,XLWT
def export_excel (table_name):
host,user,passwd,db= ' xx.xx.xx.xx ', ' aaa ', ' 123456 ', ' AAA ' #pymysql的信息
coon = Pymysql.connect (
Host=host, User=user, PASSWD=PASSWD,
port=3306, db=db, charset= ' UTF8 ' # port must write int type, CharSet must write UTF8
    )

cur = coon.cursor () # Creating Cursors
sql= ' select * from%s; ' %table_name
cur.execute (SQL) #执行sql语句
Fileds=[filed[0] for filed in Cur.description] #表头所有的字段
print (fileds)
All_data=cur.fetchall () #获取数据返回的所有数据
BOOK=XLWT. Workbook () #创建一个excel
sheet=book.add_sheet (' Sheet1 ') #新增一个sheet

For col,filed in Enumerate (fileds): #把表头字段写入sheet的0行
sheet.write (0,col,filed)
#print (all_data)
row=1 #定义一个变量, control line
For data in All_data: #控制行
For Col, filed in Enumerate (data): #控制列, Enumerate automatically remove the label, plus 1
sheet.write (Row, col, filed) #行不变, transform columns, write data sequentially
Row +=1 #向下移动行数

book.save ('%s.xls '%table_name) #保存表格

export_excel (' app_student ') #导出表格

Novice Learning Python (11) Read/modify/export Excel