Python_操作excel

python中与excel操作相关的模块：

• xlrd库：从excel中读取数据，支持xls、xlsx
• xlwt库：对excel进行修改操作，不支持对xlsx格式的修改
• xlutils库：在xlw和xlrd中，对一个已存在的文件进行修改。
• openpyxl：主要针对xlsx格式的excel进行读取和编辑。

安装方式：pip install openpyxl

 

import xlwt

book = xlwt.Workbook()
sheet = book.add_sheet("sheet1")
sheet.write(0,0,"id")
sheet.write(1,0,"1")
book.save("test.xls") #结尾用xlsx的话，用office打开有问题，wps打开没问题

title = ["id","name","sex","city"]
students = [
    [1,"xiaohei","男","北京"],
    [2,"xiaolan","男","北京"],
    [3,"candy","女","深圳"],
    [4,"lucy","女","北京"],
]
students.insert(0,title)

 row = 0 #行号
 for student in students: #控制行
     col = 0  # 列号
     for value in student: #控制列
         sheet.write(row,col,value)
         col+=1
     row+=1

for row,student in enumerate(students):
    for col,value in enumerate(student):
        sheet.write(row,col,value)

book.save("test.xls")

 

data = xlrd.open_workbook(table_name)
table = data.sheets()[0]
nrows,ncols = table.nrows,table.ncols
for i in range(1, nrows):
　　value1= table.cell(i, 1).value    获取第i行，第1列单元格数据
　　print(value1)

 

 

import xlrd
book = xlrd.open_workbook("test.xls")
sheet  = book.sheet_by_name("sheet1")
sheet  = book.sheet_by_index(0)
book.sheets()      获取所有sheet页
sheet.cell(0,0).value    获取某一个单元格的内容
sheet.row_values(0)　　获取整行的数据
sheet.col_values(0)　　获取整列的数据
sheet.nrows　　总共多少行
sheet.ncols　　总共多少列

import xlwt
book = xlwt.Workbook()
xlwt.Workbook()
sheet = book.add_sheet("sheet1")
for i in range(65538):
    sheet.write(i,0,i)
book.save("a.xlsx")

# 65536


修改

import xlrd
from xlutils import copy
book = xlrd.open_workbook("test.xls")
new_book = copy.copy(book)
sheet = new_book.get_sheet(0)
sheet.write(0,0,"编号")
sheet.write(0,1,"名字")
new_book.save("test.xls")

 

多行操作

 

import openpyxl
book = openpyxl.load_workbook("K1_0601.xlsx")
sheet = book["sheet1"]
print(book.sheetnames) #所有sheet页名
print(sheet.rows)
print(sheet.max_row)

print(sheet["A1"].value)
print(sheet["A2"].value)
sheet["A1"] = "编号"
book.save("K1_0601.xlsx")
print(sheet["A1"].value)