python:将json数据写入到excel

★★★ 个人博客导读首页—点击此处 ★★★

环境 ： Python2
封装一个函数，可以将json格式的数据，填入到excel中
函数使用示例: create_a_new_excel(s_name,“table1”,test_title, test_use_list)
s_name是excel的名称， table1是excel中表的名称，
test_title是一个列表，列出excel的标题
test_use_list是要填充的数据，是字典组成的列表

例如:test_title = ["title1","title2"] > > test_use_list = [{"title1":"(1,1)","title2":"1,2)"},{"title1":"(2,1)","title2":"2,2)"}]

源代码

# coding=utf8import sys import os import shutil import time import xlrd import xlwt import jsondef create_sheet(f,table_name,try_count):if try_count <= 1:returntry:sheet1 = f.add_sheet(table_name + '-' + str(11 - try_count),cell_overwrite_ok=True)return sheet1except Exception as e:return create_sheet(f,table_name,try_count-1)def create_a_new_excel(excel_path,table_name,title_name,use_lists):table1_invalid_start_x = 1;table1_invalid_start_y = 2;max_buf_len = []if os.path.exists(excel_path):os.remove(excel_path)f = xlwt.Workbook(encoding='utf-8') #新建excelfont = xlwt.Font()font.bold = Trueborders = xlwt.Borders()borders.left = xlwt.Borders.THINborders.right = xlwt.Borders.THINborders.top = xlwt.Borders.THINborders.bottom = xlwt.Borders.THINalignment = xlwt.Alignment()alignment.horz = xlwt.Alignment.HORZ_CENTER #水平方向alignment.vert = xlwt.Alignment.VERT_TOPstyle1 = xlwt.XFStyle()style1.font = font#style1.borders = bordersstyle1.alignment = alignmentstyle2 = xlwt.XFStyle()style2.alignment.wrap = 1 #自动换行try:sheet1 = f.add_sheet(table_name,cell_overwrite_ok=True)except Exception as e:sheet1 = create_sheet(f,table_name,10)for item in range(0, len(title_name)):sheet1.write(1, item+table1_invalid_start_x, title_name[item],style=style1)max_buf_len.append(len(title_name[item]))sheet1.col(1).width = 256 * (max_buf_len[item])item = 0column_len = len(title_name)for use_list in use_lists:for column_index in range(0, column_len):if len(use_list[title_name[column_index]]) > max_buf_len[column_index]:max_buf_len[column_index] = len(use_list[title_name[column_index]])if max_buf_len[column_index] > 150:max_buf_len[column_index] = 150sheet1.col(column_index+table1_invalid_start_x).width = 256 * (max_buf_len[column_index] + 3)sheet1.write(table1_invalid_start_y + item, column_index+table1_invalid_start_x, use_list[title_name[column_index]],style2)item += 1f.save(excel_path)if __name__ == '__main__':print(str(sys.argv[0]) + " enter")test_title = ["title1","title2"]test_use_list = [{"title1":"(1,1)","title2":"1,2)"},{"title1":"(2,1)","title2":"2,2)"}]s_name = "1.xlsx"print(test_use_list)create_a_new_excel(s_name,"table1",test_title, test_use_list)

效果展示：

总结

以上是生活随笔为你收集整理的python:将json数据写入到excel的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。