python操作excel

windows需要安装xlrd库，
安装命令 pip install xlrd
MAC安装http://www.mamicode.com/info-detail-1758614.html(如果安装报错可使用sudo模式)
如果还没有，可以这么安装：

搜索想要的插件安装就可以了。

 

import xlrd, xlwt, xlutils
############ 读excel ###############
book = xlrd.open_workbook('C:\\Users\lingyul\Desktop\students.xlsx')#打开excel
print(book.sheet_names())#获取所有sheet页的名字
sheet1 = book.sheet_by_index(0)##根据sheet页的下标获取sheet页
sheet2 = book.sheet_by_name('students')#根据sheet页的名字获取sheet页
print(sheet1,sheet2)
print('row_all',sheet1.nrows)#获取所有行数
print('ncols_all',sheet1.ncols)#获取所有列数
print(sheet1.row_values(0))#根据行号打印某一行
sheet1.row_values(0)[0]#第0行的第0个元素
print(sheet1.col_values(0))#根据列号打印某一列
sheet1.col_values(0)[0]#第0列的第0个元素
print('####',sheet1.cell(2,2).value)#不加value的运行结果为：text:'小明'。打印第二行第二列的数据，即获取单元格数据
lis=[]
sheet1 = book.sheet_by_index(0)
ncols_all = sheet1.ncols
for row in range(1,sheet1.nrows):
 students = {}
 id = sheet1.cell(row, 0).value
 name = sheet1.cell(row, 1).value
 sex = sheet1.cell(row, 2).value
 students['id']=id
 students['name']=name
 students['sex']=sex
 lis.append(students)
print(lis)

############ 写excel ###############
import xlwt
book = xlwt.Workbook()#新建一个excel对象
sheet = book.add_sheet('stu')# 新建一个sheet
# sheet.write(0,0,'编号')
# book.save('stu.xls')
## 写 excel，时候，保存的文件必须以xls结尾
title = ['编号', '姓名', '性别']
stus = [['mary',20,'女',89.9],['mary',20,'女',89.9],['mary',20,'女',89.9],['mary',20,'女',89.9]]
for i in range(len(title)):
 sheet.write(0,i,title[i])
for row in range(len(lis)):
 new_row=row+1
 id = lis[row]['id']
 name = lis[row]['name']
 sex = lis[row]['sex']
 sheet.write(new_row,0,id)
 sheet.write(new_row, 1, name)
 sheet.write(new_row, 2, sex)
book.save('stu.xls')

import xlrd
from xlutils.copy import copy
def copy():
 book = xlrd.open_workbook('new_stu.xls')
 new_book = copy(book)#通过xlutils里面的copy复制一个excel对象
 sheet = new_book.get_sheet(0)#获取sheet页
 sheet.write(0,0,'id')
 new_book.save('new_stu_1.xls')

json_list= {
"1":["小花",99,100,98.5],
"2":["小王",90,30.5,95],
"3":["小明",67.5,49.6,88]
}
title = ['学号','姓名','语文成绩','数学成绩','英语成绩','总分','平均分']

book = xlrd.open_workbook('new_stu.xls')
new_book = copy(book) # 通过xlutils里面的copy复制一个excel对象
sheet = new_book.get_sheet(0) # 获取sheet页
sheet.write(0, 0, 'id')
new_book.save('new_stu_1.xls')