python对excel操作简书_python操作Excel

写入Excel

import xlwt

book = xlwt.Workbook()

sheet = book.add_sheet('xiangxin')

# sheet.write(0,0,'name') #行,列,内容

title = ['姓名', '班级', '住址', '手机号']

shuzu = [

['bred', 'class1', 'mingdong', 188109],

['shade', 'class2', 'gugong', 3332],

['dd', 'class3', 'changcheng', 6666]

]

#写入表头

i = 0

for j in title:

sheet.write(0,i,j)

i+=1

#写入表内容

l = 1

for d in shuzu:

c = 0

for dd in d:

sheet.write(l, c, dd)

c+=1

l+=1

#保存

book.save('嵌套循环.xls')

举个栗子:读取数据库数据写入Excel

#导入封装好的数据库类

from tools import conMysql

import xlwt

con = conMysql(***)

res = con.executeSelectSql('select * from tiantian_v2.tt_channel_company;')

print(len(res))

book = xlwt.Workbook()

sheet = book.add_sheet('sheet1')

title = ['id', 'company_name', 'name', 'status', 'password', 'view_permisson', 'permisson', 'h5_register', 'loan_amount', 'order_number', 'loan_number', 'create_time', 'update_time']

i = 0

for t in title:

sheet.write(0,i,t)

i+=1

for d in range(len(res)):

for j in range(len(res[d])):

sheet.write(d+1,j,res[d][j])

book.save('test.xls')

读取Excel

import xlrd

book = xlrd.open_workbook('test.xls')#打开要读取的Excel

sheet = book.sheet_by_name('sheet1')#打开sheet页

rows = sheet.nrows #sheet页里面的行数

columns = sheet.ncols #sheet页里面的列数

print(sheet.cell(1,2).value)#通过制定行和列去获取到单元格里的内容

row_data = sheet.row_values(2)#获取第一行的内容

print(row_data)

for i in range(rows):

print(sheet.row_values(i))#获取第几行的数据

修改Excel

from xlutils.copy import copy

import xlrd

import os

#打开需要修改的Excel

book = xlrd.open_workbook('test.xls')

#复制一份并在新Excel里写入要修改的数据

new_book = copy(book)

sheet = new_book.get_sheet(0)

sheet.write(1,1,'xiangshang')

#保存新表

new_book.save('newtest.xls')

#导入系统OS模块,删除原来的Excel

os.remove('test.xls')

#把新的Excel的表名改为原来的表名

os.rename('newtest.xls', 'test.xls')