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')