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

环境 ： 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=utf8

import sys
import os
import shutil
import time
import xlrd
import xlwt
import json

def create_sheet(f,table_name,try_count):
    if try_count <= 1:
        return
    try:
        sheet1 = f.add_sheet(table_name + '-' + str(11 - try_count),cell_overwrite_ok=True)
        return sheet1
    except 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') #新建excel

    font = xlwt.Font()
    font.bold = True
    borders = xlwt.Borders()
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER  #水平方向
    alignment.vert = xlwt.Alignment.VERT_TOP
    style1 = xlwt.XFStyle()
    style1.font = font
    #style1.borders = borders
    style1.alignment = alignment
    
    style2 = 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 = 0
    column_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] = 150
            sheet1.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 += 1
    f.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)

效果展示：