python自动化办公 excel操作

一、安装excel库

二、excel接口
读excel

导入库
import xlrd

#打开excel文件
book = xlrd.open_workbook('data.xlsx')
sheet1 = book.sheets()[0]
nrows = sheet1.nrows
print('表格总行数',nrows)
ncols = sheet1.ncols
print('表格总列数',ncols)
row3_values = sheet1.row_values(2)
print('第3行值',row3_values)
col3_values = sheet1.col_values(2)
print('第3列值',col3_values)
cell_3_3 = sheet1.cell(2,2).value
print('第3行第3列的单元格的值：',cell_3_3)

运行结果
表格总行数 5
表格总列数 5
第3行值 [‘3A’, ‘3B’, ‘3C’, ‘3D’, ‘3F’]
第3列值 [‘1C’, ‘2C’, ‘3C’, ‘4C’, ‘5C’]
第3行第3列的单元格的值： 3C

excel修改

import xlwt
import xlrd
import xlutils.copy
book_excel1 = xlrd.open_workbook("图书.xls")
        book_excel = xlutils.copy.copy(book_excel1)
        sheet1 = book_excel.get_sheet(0)
        sheet1.write(total_books, i, book[i])
        book_excel.save("图书.xls")

程序示例
excel写操作示例

import xlwt 

workbook = xlwt.Workbook()#新建excel
worksheet = workbook.add_sheet('test')#新建sheet表
worksheet.write(0,0,'A1data')#写'A1data'到第0行0列
workbook.save('excelwrite.xls')#保存

excel读操作示例
成绩单.xls

import xlwt 
import xlrd

book = xlrd.open_workbook('成绩单.xls')
sheet1 = book.sheets()[0]
nrows = sheet1.nrows
print('表格总行数',nrows)
ncols = sheet1.ncols
print('表格总列数',ncols)
row1_values = sheet1.row_values(1)
print('第3行值',row1_values)
col3_values = sheet1.col_values(2)
print('所有的数学成绩',col3_values)
cell_3_6 = sheet1.cell(3,6).value
print('王二的美术成绩：',cell_3_6)