Python操作Excel
xlrd 只能读
xlwt 只能写
xlutils 能修改,copy后再进行修改
但是不能超过65536行
openpyxl 这个模块只能针对xlsx格式的,不限制长度
xlwt模块,写入Excel
import xlwt
title = ["id", "name", "sex", "city"]
students = [
[1, "哈哈哈", "男", "北京"],
[2, "啦啦啦", "男", "北京"],
[3, "哦哦", "女", "深圳"],
[4, "呵呵", "女", "北京"],
]
# 实例化写入的Excel对象
book = xlwt.Workbook()
# 新增一个sheet页
sheet = book.add_sheet('sheet1')
for i in range(0, len(title)):
sheet.write(0, i, title[i])
for j in range(0, len(students[0])):
# 写入到单元格里
sheet.write(i + 1, j, students[i][j])
book.save('test1.xls')
xlrd模块,读Excel
import xlrd
book = xlrd.open_workbook('test.xlsx')
sheet = book.sheet_by_index(0)
sheet1 = book.sheet_by_name('sheet1')
# book.sheets() # 获取所有的sheet页
print(sheet.cell(0, 0).value) # 获取单元格的内容
print(sheet.row_values(0)) # 获取整行数据
print(sheet.col_values(0)) # 获取整列数据
print(sheet.nrows) # 总共多少行
print(sheet.ncols) # 总共多少列
xlutils模块,修改Excel
import xlrd
from xlutils import copy
book = xlrd.open_workbook('test.xlsx')
new_book = copy.copy(book) # 拷贝一份新的用于修改
sheet = new_book.get_sheet(0)
sheet.write(0, 0, '编号') # 写入就覆盖了原来的,修改成新的
new_book.save('test2.xls')
openpyxl模块,操作多行Excel
import openpyxl
# openpyxl 这个模块只能针对xlsx格式的,不限制长度
book = openpyxl.load_workbook('test111.xlsx')
sheet = book['Sheet1']
print(book.sheetnames) # 所有sheet的名字
print(dir(sheet)) # 查看sheet下的所有的方法
print(list(sheet.rows))
# 获取每个单元格数据
for row in sheet.rows:
for col in row:
print(col.value)
for row in sheet.rows: # 获取每行的数据
row_data = [col.value for col in row]
print(row_data)
print(sheet.max_row) # 一共多少行
print(sheet.max_column) # 一共多少列
sheet['A1'] = '编号'
print(sheet['A1'].value)
print(sheet['A2'].value)
print(sheet.cell(1, 1).value) # 取出单元格的内容,但是下标是从0开始的
sheet.cell(1, 1).value = '编号' # 修改单元格的内容
print(sheet.cell(1, 1).value)
print(sheet['B1'].value)