import xlrd
import xlwt
import random
#读取Excel文件
workbook = xlrd.open_workbook("成绩表.xlsx")
#获取所有的sheet名字
# print(workbook.sheet_names())
#根据索引获得指定的sheet对象
# sheet = workbook.sheet_by_index(0)
# print(sheet.name)
#根据名称获取指定的sheet对象
# sheet = workbook.sheet_by_name('2班')
# print(sheet.name)
#获取所有的sheet对象
# sheets = workbook.sheets()
# for sheet in sheets:
# print(sheet.name)
#获取指定sheet的行数和列数
# sheet = workbook.sheet_by_index(0)
# print({"rows":sheet.nrows,"cols":sheet.ncols})
############Cell相关的操作#############
sheet = workbook.sheet_by_index(0)
# cell = sheet.cell(1,1)
# print(cell.value)
# cells = sheet.row_slice(1,1,4)
# for cell in cells:
# print(cell.value)
# cells = sheet.col_slice(0,1,sheet.nrows)
# for cell in cells:
# print(cell.value)
# cell_value = sheet.cell_value(0,1)
# print(cell_value)
# cell_values = sheet.col_values(1,1,sheet.nrows)
# print(cell_values)
# cell_values = sheet.row_values(1,1,sheet.ncols)
# print(cell_values)
############CELL的类型#############
# xlrd.XL_CELL_TEXT(Text):文本类型
# xlrd.XL_CELL_NUNBER(Number):数值类型
# xlrd.XL_CELL_DATE(Date):日期时间类型
# xlrd.XL_CELL_BOOLEAN(Bool):布尔类型
# xlrd.XL_CELL_EMPTY:空白数据类型
# cell = sheet.cell(0,0)
# print(cell.ctype)
# print(xlrd.XL_CELL_TEXT)
# cell = sheet.cell(1,1)
# print(cell.ctype)
# print(xlrd.XL_CELL_NUMBER)
#写入Excel文件
#写入Excel步骤如下
#1.导入xlwt模块
#2.创建一个Workbook对象
#3.创建一个Sheet对象
#4.使用sheet.write方法把数据写入到Sheet下指定行和列中。如果想要在原来workbook对象上
#添加新的cell,那么需要调用put_cell来添加
#5.保存成Excel文件
workbook = xlwt.Workbook()
sheet = workbook.add_sheet("sheet1")
headers = ["姓名","语文","英语","数学"]
for index,header in enumerate(headers):
sheet.write(0,index,header)
names = ["张三","李四","王五"]
for index,name in enumerate(names):
sheet.write(index+1,0,name)
for row in range(1,4):
for col in range(1,4):
sheet.write(row,col,random.randint(1,100))
workbook.save("成绩表.xls")
#编辑Excel文件
rwb = xlrd.open_workbook("成绩表.xlsx")
rsheet = rwb.sheet_by_index(0)
#求所有学生成绩的总分
rsheet.put_cell(0,4,xlrd.XL_CELL_TEXT,"总分",None)
nrows = rsheet.nrows
for row in range(1,nrows):
grades = rsheet.row_values(row,1,4)
total = sum(grades)
rsheet.put_cell(row,4,xlrd.XL_CELL_NUMBER,total,None)
#求每个科目的平均分
nrows = rsheet.nrows
ncols = rsheet.ncols
for col in range(1,5):
grades = rsheet.col_values(col,1,nrows)
avg = sum(grades)/len(grades)
rsheet.put_cell(nrows,col,xlrd.XL_CELL_NUMBER,avg,None)
wwb = xlwt.Workbook()
wsheet = wwb.add_sheet("sheet1")
nrows = rsheet.nrows
ncols = rsheet.ncols
for row in range(0,nrows):
for col in range(0,ncols):
wsheet.write(row,col,rsheet.cell_value(row,col))
wwb.save("abc.xls")
数据存储:Excel文件操作
最新推荐文章于 2023-11-24 13:23:51 发布