python操作excel

pip  install  xlwt  写模块，将原来的内容覆盖

pip install  xlrd  读模块

pip install xlutils  修改excel模块

#操作写excel


import xlwt #写excel

import xlrd #读excel

import xlutils  #操作excel



'''
1、新建一个excel
2、在excel里面添加一个sheet add_sheet
3、在sheet里面写东西，操作行、列


'''
book=xlwt.Workbook()

sheet = book.add_sheet('sheet2')

# sheet.write(0,0,'ID')
# sheet.write(0,1,'username')
# sheet.write(0,2,'password')
#
# sheet.write(1,0,'1')
# sheet.write(1,1,'tanailing')
# sheet.write(1,2,'123456')




stus =[
    [1,'njf1','12345'],
    [2,'njf2','12346'],
    [3,'njf3','12347'],
    [4,'njf4','12348'],
    [5,'njf5','12349']
]




heard =['id','uname','pwd']
rowline = 0
colline = 0
for  one  in heard:
    sheet.write(rowline,colline,one)
    colline += 1




line = 1
for  i  in stus:
    col_nume = 0
    for j  in  i :
        sheet.write(line,col_nume,j)
        col_nume += 1
    line += 1


book.save('学习操作写excel33.xls')

import xlrd



''''

1、打开一个excel
2、获取sheet页，book.sheet_by_index,sheet_by_name


'''

book =xlrd.open_workbook('学习操作写excel.xls') #打开一个excel

sheet = book.sheet_by_index(0)#打开excel的sheet，通过下标获取
# sheet = book.sheet_by_name('sheet1') #通过sheet页的名称获取

# print(sheet.nrows)  #打印有多少行
#
# print(sheet.ncols) #打印有多少列
#
# print(sheet.cell(0,0).value)  #获取指定单元格的内容  行  列   的值
#
# print(sheet.row_values(0))  #获取第1行的整行的内容
#
# print(sheet.col_values(0)) #获取第1列的整列的内容


#根据已知的行数，找到每一行的内容
'''
for  i  in  range(sheet.nrows):#得到sheet里面有多少行
    print(sheet.row_values(i)) #将每一行的内容打印出来
'''

'''   
['ID', 'username', 'password']
['1', 'tanailing', '123456']
'''
#根据已知的列数，找到每一列的内容

for col  in  range(sheet.ncols):
    print(sheet.col_values(col))

'''
['ID', '1']
['username', 'tanailing']
['password', '123456']
'''

import xlutils
import xlrd
'''excle的修改，和xlrd配合一起使用'''

from  xlutils import copy   #从xlutils中导入copy

book=xlrd.open_workbook('学习操作写excel.xls')


'''
1、先用xlrd打开一个excel
2、然后用xlutils将打开的book复制
3、在对复制的book进行操作

'''


book=xlrd.open_workbook('学习操作写excel.xls')

new_book=copy.copy(book) #将打开的excel复制成心的excel,然后对新的这个excel进行操作

sheet=new_book.get_sheet(0)  #获取sheet

sheet.write(0,1,'谭艾玲')
sheet.write(1,1,'hahahah')


new_book.save('学习操作写excel.xls')