[python]（4）-----excel操作

excel表格操作

1. 登陆（加密，锁定账户）
2. 图书查看
3. 添加图书信息
4. 修改
5. 删除（软删除）

import xlrd
def login(username,password):
    #加密
    #验证
    book = xlrd.open_workbook("e://person.xls")
    # sheet1 = book.sheet_by_index(0)
    sheet1 = book.sheet_by_name("person")
    # a = sheet1.cell(2,3).value
    # 一共多少行
    r = sheet1.nrows
    # 一共多少列
    c = sheet1.ncols
    flag = False
    for i in range(1, r):
        if username == sheet1.cell(i,1).value and password == sheet1.cell(i,2).value:
            flag = True
            break
    return flag

import getpass
a = getpass.getpass("请输入密码：")
print(a)

import xlrd
book = xlrd.open_workbook("e://person.xls")
#sheet1 = book.sheet_by_index(0)
sheet1 = book.sheet_by_name("person")
#a = sheet1.cell(2,3).value
#一共多少行
r = sheet1.nrows
#一共多少列
c = sheet1.ncols
for i in range(1,r):
    for j in range(c):
        print(sheet1.cell(i,j).value,end=" ")
    print()

import xlrd,xlwt
from xlutils import copy

book = xlrd.open_workbook("e://person.xls")
book_copy = copy.copy(book)
sheet = book_copy.get_sheet(0)
sheet.write(2,1,"霍金旭")
book_copy.save("e://person.xls")

import xlrd,xlwt
from xlutils import copy

book = xlrd.open_workbook("e://person.xls")
r = book.sheet_by_name("person").nrows
c = book.sheet_by_name("person").ncols
book_copy = copy.copy(book)
sheet = book_copy.get_sheet(0)

a = ["3","张三","18","147","男","0"]
for i in range(c):
    sheet.write(r,i,a[i])

book_copy.save("e://person.xls")

import xlwt
#只能写不能读
stus = [['姓名', '年龄', '性别', '分数'],
         ['mary', 20, '女', 89.9],
         ['mary', 20, '女', 89.9],
         ['mary', 20, '女', 89.9],
         ['mary', 20, '女', 89.9]
         ]
book = xlwt.Workbook()#新建一个excel
sheet = book.add_sheet('case1_sheet')#添加一个sheet页
row = 0#控制行
for stu in stus:
     col = 0#控制列
     for s in stu:#再循环里面list的值，每一列
         sheet.write(row,col,s)
         col+=1
     row+=1
book.save('stu_1.xls')#保存到当前目录下

import xlwt
book = xlwt.Workbook()
sheet = book.add_sheet("user")
sheet.write(2,2,"nihao")
book.save("e://user.xls")