Python 写Excel实战：json存Excel

案例一：

 

import xlwt

data_json={
  "1":["小花",99,100,98.5],
  "2":["小王",90,30.5,95],
  "3":["小明",67.5,49.6,88]
}
title = ["编号","姓名","语文成绩","数学成绩","英语成绩","平均分","总分"]

#1、循环一下，把字典的key插入到字典的value里面，同时计算平均分、总分
all_data = []
for k,v in data_json.items():
  sum_score = round(sum(v[1:]),2)
  avg_score = round(sum_score / len(v[1:]),2)
  v.insert(0,k) #学号放在最前面
  v.append(sum_score)
  v.append(avg_score)
  all_data.append(v)
all_data.insert(0,title)

book = xlwt.Workbook()
sheet = book.add_sheet("sheet")

for row,i in enumerate(all_data): #[1,2,3,4] [4,5,6,10]
    for col,j in enumerate(i): #1 2 3 4 4 5 6 10
        sheet.write(row,col,j)

book.save("data.xls")


案例二：

import json
import xlwt

# 将json转成字典
str_json = '''{"1":["小花",99,100,98.5],"2":["小王",90,30.5,95],"3":["小明",67.5,49.6,88]}'''
dic = json.loads(str_json)  # json转为字典

# 将字典整理成方便遍历的list
value_list = []
for num, value in dic.items():  # 把编号和姓名成绩合为一个列表，方便后面写入
    value.insert(0, num)  # 编号并入姓名成绩的list
    total_score = round(sum(value[2:]), 2)  # 对当前行所有成绩求和
    ave_score = round(total_score / len(value[2:]), 2)  # 对当前行所有成绩求平均值
    value.append(total_score)   # 总分插入当前行list
    value.append(ave_score)     # 平均分插入当前行list
    value_list.append(value)  # 整理好的行list放入大list

# 标题加入list
title = ['编号', '姓名', '语文成绩', '数学成绩', '英语成绩', '总分', '平均分']
value_list.insert(0, title)

# 写Excel
book = xlwt.Workbook()  # 初始化Excel
sheet = book.add_sheet('student')  # 新建sheet

for row, i in enumerate(value_list):  # 把整理好的二维数组写入Excel
    for col, y in enumerate(i):
        sheet.write(row, col, y)
book.save('my_student2.xls')  # 保存Excel