📖 Python 学习笔记
CSV 处理
引入csv模块
import csv
读取
按照下标获取列内容 csv.reader
打印第三列的内容
with open("test.csv","r",encoding="utf-8") as fw:
#可根据下表查询某列
data=csv.reader(fw)
for x in data:
print(x[2])
按照列名获取列内容 csv.DictReader
打印name列的内容
with open("test.csv","r",encoding="utf-8") as fw:
# 可根据列名查询某列
data = csv.DictReader(fw)
for x in data:
print(x['name'])
写入
按照列表或元组的形式写入csv
- writer.writerow 每次只能写入一条
- writer.writerows可以批量写入
#列头
header=["id","name","sex"]
#数据
data=[
("0", "小明", "man"),
("1", "小红", "woman"),
("2", "小不灵", "woman"),
]
with open("test.csv","w",encoding="utf-8",newline="") as fw:
writer=csv.writer(fw)
#先写入列头
writer.writerow(header)
#批量写入数据
writer.writerows(data)
按照字典的形式写入csv
#列头
header=["id","name","sex"]
#数据
dictdata=[
{"id": "5", "name": "小明","sex":"man"},
{"id": "6", "name": "小红", "sex": "woman"},
{"id": "7", "name": "小不灵", "sex": "woman"},
]
with open("test.csv", "w", encoding="utf-8", newline="") as fw:
#设置列头
writer = csv.DictWriter(fw,fieldnames =header)
#写入列头
writer.writeheader()
#批量写入数据
writer.writerows(dictdata)
Excel 处理
安装xlrd和xlwt并引入
pip install xlrd
pip install xlwt
import xlrd
import xlwt
打开和获取 Sheet
workbook =xlrd.open_workbook("C:\\Users\\i\\Desktop\\教育模块表结构设计.xlsx")
#获取全部sheet的名字
sheetnames = workbook.sheet_names(1)
根据索引获取sheet对象
sheet = workbook.sheet_by_index(1)
根据名字获取sheet对象
sheet2 = workbook.sheet_by_name("sheet1")
获取全部的sheet对象
sheets = workbook.sheets()
sheet.nrows获取sheet的行数,sheet.ncols获取sheet的列数
print({"rows":sheet.nrows,"cols":sheet.ncols})
Cell 的相关操作
获取指定行指定列的cell对象
cell = sheet.cell(0,0)
# 使用cell.value获取cell的内容
print(cell.value)
直接获取指定行指定列的单元格内容
cell_value = sheet.cell_value(0,0)
print(cell_value)
获取指定行的某几列值
# row_values(第几行,从第几列开始(包含此行),到第几列结束(不包含此行))
# 如下第2列 全部行信息
rowvalues = sheet.row_values(0,0,2)
for rowvalue in rowvalues : print("rowvalue-",rowvalue)
获取指定列的某几行值
# col_values(第几列,从第几行开始(包含此行),到第几行结束(不包含此行))
# 如下第2列 全部行信息
colvalues = sheet.col_values(0,0,2)
for colvalue in colvalues : print("colvalue-",colvalue)
获取指定行的某几列的单元格
# row_slice(第几行,从第几列开始(包含此列),到第几列结束(不包含此列))
# 如下第2行 第1-4列
cells = sheet.row_slice(1,0,4)
for cell in cells : print(cell.value)
获取指定列的某几行的单元格
# col_slice(第几列,从第几行开始(包含此行),到第几行结束(不包含此行))
# 如下第2列 全部行信息
cells = sheet.col_slice(1,0,sheet.nrows)
for cell in cells : print(cell.value)
将数据写入 Excel 文件
#创建一个工作簿 要注明编码格式 否则在add_sheet是可能报错缺少self参数
workbook = xlwt.Workbook(encoding="utf-8")
#创建一个sheet
sheet = workbook.add_sheet('测试表格')
#设置列头
headers = ['ID','NAME','AGE']
#写入列头
for index,header in enumerate(headers):
sheet.write(0,index,header)
#id信息
ids = [1,2,3,4,5]
#sheet.write(行,列,内容)
#循环写入id信息
for index,id in enumerate(ids):
sheet.write(index+1, 0, id)
#循环写入name信息
names = ['小明','小红','小刚']
for index,name in enumerate(names):
sheet.write(index+1, 1, name)
#循环写入age信息
ages = [1,2,3,4,5]
for index,age in enumerate(ages):
sheet.write(index+1, 2, age)
#保存excel
workbook.save("F:\\JSHYYH.xls")
编辑 Excel 文件
实现横向汇总和纵向求平均值
#读取excel
rwb = xlrd.open_workbook("F:\\JSHYYH.xls")
rsheet = rwb.sheet_by_index(0)
#添加合计列
rsheet.put_cell(0,rsheet.ncols,xlrd.XL_CELL_TEXT,"COUNT",None)
#行数据获取并做处理 每行得分求和
nrows = rsheet.nrows
for row in range(1,nrows):
grades = rsheet.row_values(row,2,rsheet.ncols-1)
#求和
total = sum(grades)
#写入最后一列
rsheet.put_cell(row, rsheet.ncols-1, xlrd.XL_CELL_NUMBER,total, None)
#列数据获取并做处理 每列得分求平均值
ncols = rsheet.ncols
nrows = rsheet.nrows
for col in range(2,ncols):
grades = rsheet.col_values(col,1,nrows)
avg = sum(grades)/len(grades)
rsheet.put_cell(nrows, col, xlrd.XL_CELL_NUMBER, avg, None)
#写入新的excel
wwb = xlwt.Workbook(encoding="utf-8")
wsheet = wwb.add_sheet("test")
ncols = rsheet.ncols
nrows = rsheet.nrows
#将rsheet中的cell数据写到wsheet中
for row in range(0,nrows):
for col in range(0,ncols):
wsheet.write(row,col,rsheet.cell_value(row,col))
#保存
wwb.save("F:\\SDSDS.xls")