Excel 转json 和json 转Excel

word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V',
        'W', 'X', 'Y', 'Z']

default_style = {
    'font': {},
    'valign': 'bottom'
}


def xls2json(xls):
    from openpyxl import load_workbook
    workbook = load_workbook(xls)
    booksheet = workbook.active
    rows = booksheet.rows
    merged_cells = booksheet.merged_cells.ranges
    raw_merges = [x.bounds for x in merged_cells]
    merges = ['{0}{1}:{2}{3}'.format(word[x[0] - 1], x[1], word[x[2] - 1], x[3]) for x in raw_merges]

    ocols = {}
    for i in range(booksheet.max_column):
        if not booksheet.column_dimensions[word[i]].width:
            ocols[i] = {'width': 100}
        else:
            ocols[i] = {'width': int(booksheet.column_dimensions[word[i]].width * 10)}
    orows = {}
    styles = []
    i = 0
    for row in rows:
        row_dict = {}
        j = 0
        for cell in row:
            if cell.value:
                align = cell.alignment.horizontal
                vertical = cell.alignment.vertical
                color = cell.font.color
                size = int(cell.font.sz)
                style = default_style.copy()
                if align:
                    style['align'] = align
                if vertical:
                    style['valign'] = vertical if vertical != 'center' else 'middle'
                style['font'] = {'size': size}
                if isinstance(color.rgb, str):
                    style['color'] = '#{0}'.format(color.rgb[2:])
                row_dict[j] = {
                    'text': cell.value
                }
                row_dict[j]['style'] = len(styles)
                styles.append(style)
                for rm in raw_merges:
                    if rm[0] == j + 1 and rm[1] == i + 1:
                        row_dict[j]['merge'] = [rm[3] - rm[1], rm[2] - rm[0]]
                        break
            j += 1
        if row_dict:
            if booksheet.row_dimensions[i + 1].height:
                orows[i] = {'cells': row_dict, 'height': int(booksheet.row_dimensions[i + 1].height) / 3 * 5}
            else:
                orows[i] = {'cells': row_dict, 'height': 23}
        i += 1
    return json_format(orows, ocols, merges, styles)

以上是excel转json完整代码

def json2xls(json_str):
    import openpyxl
    workbook = openpyxl.Workbook()
    worksheet = workbook.active
    data = json.loads(json_str)
    rows = data[0]["rows"]
    merges = data[0]["merges"]
    styles = data[0]["styles"]
    cols = data[0]["cols"]

    # 处理行属性
    for k, v in rows.items():
        if k.isdigit():
            raw_row = int(k) + 1
            # 处理单元格内容
            if 'cells' in v:
                for k1, v1 in v['cells'].items():
                    if int(k1) < 26:
                        raw_col = word[int(k1)]
                        cell = worksheet['{0}{1}'.format(raw_col, raw_row)]
                        if 'text' in v1:
                            cell.value = v1['text']
                        if 'style' in v1:
                            raw_style = styles[int(v1['style'])]

                            align = 'left'
                            vertical = 'bottom'
                            if 'align' in raw_style:
                                align = raw_style['align']
                            if 'valign' in raw_style:
                                vertical = raw_style['valign'] if raw_style['valign'] != 'middle' else 'center'
                            cell.alignment = openpyxl.styles.Alignment(horizontal=align, vertical=vertical)

                            size = 11
                            color = '000000'
                            if 'font' in raw_style:
                                if 'size' in raw_style['font']:
                                    size = int(raw_style['font']['size'])
                            if 'color' in raw_style:
                                color = '{0}'.format(raw_style['color'][1:].upper())
                            cell.font = openpyxl.styles.Font(size=size, color=color)
            # 处理行高
            if 'height' in v:
                worksheet.row_dimensions[raw_row].height = int(v['height']) / 5 * 3

    # 处理列属性
    for k, v in cols.items():
        if k.isdigit():
            if 'width' in v:
                worksheet.column_dimensions[word[int(k)]].width = int(v['width']) * 1.0 / 10

    # 处理单元格合并
    for m in merges:
        worksheet.merge_cells(m)

    output = 'report_{0}'.format(str(len(os.listdir('../Common/modules/ireport/excel'))))
    workbook.save('../Common/modules/ireport/excel/{0}.xlsx'.format(output))
    return '../Common/modules/ireport/excel/{0}.xlsx'.format(output)

　　以上 是json转excel 

附上 所用到的函数

def json_format(rows, cols, merges, styles):
    data = {
        'freeze': 'A1',
        'styles': styles,
        'merges': merges,
        'rows': rows,
        'cols': cols,
        "validations": [],
        "autofilter": {}
    }
    rows = [data]
    return json.dumps(rows)