一、JSON处理
JSON支持数据格式:
对象(字典) | 使用花括号{} |
数组(列表) | 使用方括号[] |
字符串类型 | 必须要用双引号,不能用单引号 |
整形、浮点型、布尔类型还有null类型
多个数据之间要用逗号隔开
- Python对象转换成JSON字符串
(1)dumps函数
import json
books = [
{
"name":"三国演义",
'price':"18.8"
},
{
'name':"水浒传",
"price":"19.9"
}
]
result = json.dumps(books)
#result = json.dumps(books, ensure_ascii=False)
print(result)
print(type(result))
(2)dump函数
fp = open("books.json", 'w', encoding = 'utf-8')
json.dump(books, fp, ensure_ascii=False)
dumps:把python对象转换成JSON格式的字符串
dump:把python对象转换成JSON格式的字符串,并且还可以接受一个fp参数,可以写到文件中
这两个方法都有一个ensure_ascii
参数,默认为True,也就是说转换后的JSON字符串只能存储ascii格式的,不能存储中文
如果想要存储成中文,可以将它设置成False
- JSON字符串转换成Python对象
(1)loads函数
import JSON
json_str = '[{"name":"三国演义","price":"18.8"},{"name":"水浒传","price":"19.9"}]'
result = json.loads(json_str)
print(result)
print(type(result))
(2)load函数
with open('books.json', 'r', encoding = 'utf-8') as fp:
result = json.load(fp)
print(result)
print(type(result))
二、CSV处理
- csv文件读取的两种方式:
(1)reader
这种方式读取到的每一条数据是一个列表,所以需要通过下标的方式获取具体某一个值
with open("stock.csv", 'r', encoding = 'gbk') as fp:
reader = csv.reader(fp)
for x in reader:
print(x[3])
(2)DictReader
这种方式读取到的每一条数据是一个字典,所以可以通过列名来获取数据
with open("stock.csv", 'r', encoding = 'gbk') as fp:
reader = csv.DictReader(fp)
for x in reader:
print(x['secShortName'])
- csv文件写入的两种方式:
(1)使用writerrow
headers = ('name', 'age', 'height')
students = [
("张三",18,180),
("李四",19,190),
("王五",17,170)
]
with open("students.csv",'w', encoding = 'utf-8', newline='') as fp:
writer = csv.writer(fp)
writer.writerow(headers)
writer.writerows(students)
(2)使用DictWriter
students = [
{"name":"张三","age":18,"height":180},
{"name":"李四","age":19,"height":190},
{"name":"王五","age":17,"height":170}
]
with open("students1.csv",'w', encoding = 'utf-8', newline='') as fp:
writer = csv.DictWriter(fp, headers)
# 虽然DictWriter创建的时候有一个headers,但想要写入数据进去还需调用writer.writeheader(),
# 否则表头数据写入不进去
writer.writeheader()
writer.writerows(students)
三、Excel处理
1.打开Excel和获取sheet
import xlrd
- 打开Excel文件
workbook = xlrd.open_workbook("成绩表.xls")
- 获取所有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})
2.获取cell及其属性
import xlrd
workbook = xlrd.open_workbook("成绩表.xls")
- 获取指定行和列的cell对象
# cell = sheet.cell(1,1)
# print(cell.value)
- 获取指定行的某几列cell对象
# cells = sheet.row_slice(1,1,4)
# for cell in cells:
# print(cell.value)
- 获取指定列的某几行cell对象
# cols = sheet.col_slice(0,1,sheet.ncols)
# for col in cols:
# print(col.value)
- 获取指定行和列的值
# cell_value = sheet.cell_value(0,1)
# print(cell_value)
- 获取指定行的某几列的值
# col_values = sheet.col_values(1,1,sheet.nrows)
# print(col_values)
- 获取指定列的某几行的值
# row_values = sheet.row_values(1,1,sheet.ncols)
# print(row_values)
3.cell的数据类型
import xlrd
workbook = xlrd.open_workbook("成绩表.xls")
sheet = workbook.sheet_by_index(0)
- 文本类型
# 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)
- 日期时间类型
# cell = sheet.cell(4,0)
# print(cell.ctype)
# print(xlrd.XL_CELL_DATE)
- 布尔类型
# cell = sheet.cell(4,1)
# print(cell.ctype)
# print(xlrd.XL_CELL_BOOLEAN)
- 空白数据类型
# cell = sheet.cell(4,2)
# print(cell.ctype)
# print(xlrd.XL_CELL_EMPTY)
4.将数据写入Excel文件
写入Excel步骤如下:
(1)导入xlwt模块
(2)创建一个Wrokbook对象
(3)创建一个sheet对象
(4)使用sheet.write把数据写入到指定行和列上
(5)保存成Excel文件
具体操作如下
import xlwt
import random
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) # 第0行为姓名..,因此需要从第1行开始
for row in range(1,4):
for col in range(1,4):
sheet.write(row,col,random.randint(1,100)) #随机生成分数
workbook.save("成绩表1.xls") #保存名为“成绩表1”
其中sheet.write的参数为(row,col,content)
效果如下图:
5.编辑Excel文件
编辑Excel文件步骤如下
(1)先读取原来的Excel文件
(2)然后在读取的sheet上面进行cell的修改,可以使用
sheet.put_cell(row,col,ctype,value,None)
方法来实现
(3)再重新创建一个新的Excel文件,然后把之前读取、修改的数据写入到新的Excel文件中
具体实现如下:
import xlrd
import xlwt
rwb = xlrd.open_workbook("成绩表.xls")
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)
# print(total)
rsheet.put_cell(row,4,xlrd.XL_CELL_NUMBER,total,None)
# 求每个科目的平均分
nrows = rsheet.nrows
ncols = rsheet.ncols
# 还需要求总分的平均分,所以col为(1,5)
for col in range(1,5):
grades = rsheet.col_values(col, 1, nrows)
# print(grades)
avg = sum(grades)/len(grades)
rsheet.put_cell(nrows,col,xlrd.XL_CELL_NUMBER,avg,None)
# 由于xlrd只能读取数据,修改的数据并没有写入到rwb中
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")
效果如图: