json list to excel

每次从数据库导出数据交付产品的时候常需要把json拍成excel

"""
    json_to_excel.py
    ~~~~~~~

    json文件转换为excel文件(xlsx)

    注:最多容纳1048576行数据
"""

import os
import sys
import json
import openpyxl
from openpyxl.styles import NamedStyle, Font, Alignment, Border, Side
from openpyxl.styles.colors import BLACK
from openpyxl.utils import get_column_letter

font = Font(
    name='宋体',
    color=BLACK,
    outline=True,
    size=9,
)
title_font = Font(
    name='宋体',
    color=BLACK,
    outline=True,
    size=9,
    b=True
)
alignment = Alignment(
    horizontal='left',
    vertical='center',
)
border = Border(
    left=Side(border_style='thin'),
    right=Side(border_style='thin'),
    top=Side(border_style='thin'),
    bottom=Side(border_style='thin'),
)

# 表格样式
default_style = NamedStyle(name='default', font=font, alignment=alignment, border=border)
title_style = NamedStyle(name='title', font=title_font, alignment=alignment, border=border)

filter_fields = ['ossUrl', 'oss_url']

def tran_to_excel(src, dst=''):
    if not os.path.exists(src):
        print('file(%s) not fount' % src)
        return

    if not dst:
        dst = src + '.xlsx'

    sheet_name = src.split('/')[-1].split('.')[0]
    workbook = openpyxl.Workbook()

    ws = workbook.active

    title_list = []
    # 首行下标为1
    row = 1
    max_len = {}

    with open(src, 'r', encoding='utf-8') as fin:
        for line in fin:
            if row == 1048576:
                break
            # 第二行开始写
            row += 1
            # if len(line) >= 32767:  # 如果行字长超过excel单元格限制长度,结束本次循环
            #     continue
            try:
                doc = json.loads(line)
            except:
                print(line)
                continue

            # # 仅保留某些字段
            # keys = {"name", "nameId"}
            # if keys:
            # 	old_doc = doc
            # 	doc = {}
            # 	for k, v in old_doc.items():
            # 		if k in keys:
            # 			doc[k] = v

            for key, value in doc.items():
                # if key in filter_fields:
                #     continue
                if isinstance(value, (list, dict)):
                    value = json.dumps(value, ensure_ascii=False)
                elif not isinstance(value, str):
                    value = repr(value)
                if key not in title_list:
                    title_list.append(key)
                    max_len[key] = len(key)
                column = title_list.index(key) + 1
                if max_len.get(key, 0) < len(value):
                    max_len[key] = len(value)
                try:
                    default_cell = ws.cell(row, column, value)
                    default_cell.style = default_style
                except:
                    pass
            ws.row_dimensions[row].height = 17

    # 添加标题行
    for column, key in enumerate(title_list):
        title_cell = ws.cell(1, column + 1, key)
        title_cell.style = title_style
    # ws.row_dimensions[row].height = 18

    # 调整列宽
    for key, _max in max_len.items():
        column_name = get_column_letter(title_list.index(key) + 1)
        ws.column_dimensions[column_name].width = min(int(max_len[key]), 50)

    workbook.save(dst)
    return dst


if __name__ == '__main__':
    filename = sys.argv[1]
    if os.path.isdir(filename):
        for f in os.listdir(filename):
            fn = os.path.join(filename, f)
            print(fn)
            dst = tran_to_excel(fn)
            print(dst)
    else:
        tran_to_excel(filename)