操作Excel

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)