Python制作处理excel--openpyxl

做公司项目碰到要读写excel （xslx），暂定要实现的功能如下：

1. 从json文件里解析数据

2.制作excel模板（有三行需要一个个单元格分开制作）

3.复制sheet页

使用的库如下，功能一目了然，不做详解：

import json,openpyxl
import shutil,os
from openpyxl.styles import Border, Side, Font, GradientFill, Alignment

写Cell(合并)方法如下：

def write_cell(ws,value,cell_range, border=Border(), fill=None, font=None, alignment=None):
    my_cell = ws[cell_range.split(":")[0]]
    my_cell.value = value
    style_range(ws, cell_range, border=border, fill=fill, font=font,alignment=al)

其中style_range ，主要就是写单元格的样式以及合并单元格

def style_range(ws, cell_range, border=Border(), fill=None, font=None, alignment=None):
    top = Border(top=border.top)
    left = Border(left=border.left)
    right = Border(right=border.right)
    bottom = Border(bottom=border.bottom)
    first_cell = ws[cell_range.split(":")[0]]
    if alignment:
        ws.merge_cells(cell_range)
        first_cell.alignment = alignment
    rows = ws[cell_range]
    if font:
        first_cell.font = font
    for cell in rows[0]:
        cell.border = cell.border + top
    for cell in rows[-1]:
        cell.border = cell.border + bottom
    for row in rows:
        l = row[0]
        r = row[-1]
        l.border = l.border + left
        r.border = r.border + right
        if fill:
            for c in row:
                c.fill = fill

样式如下：

thin = Side(border_style="thin", color="000000")
border = Border(top=thin, left=thin, right=thin, bottom=thin)
fill = GradientFill(stop=("D8E4BC", "D8E4BC"))
font_b = Font(b=True, color="000000")
font_t = Font(b=False, color="000000")
al = Alignment(horizontal="center", vertical="center")

主方法：

def Json2Excel():
    with open('createxml.json', encoding='utf-8') as f:
        score_all = json.load(f)
    paththis = __file__.split('json2excel.py')[0]
    #mycopyfile(paththis+'tmp.xlsx',paththis+'target.xlsx')
    print("打开文件")
    book = openpyxl.load_workbook('target.xlsx')
    print("打开完成")
    mobansheet = book['范本']
    for sheetins in book.worksheets:
        for cell in sheetins.merged_cells:
            style_range(sheetins, cell.coord, border=border)
    for sheet in score_all['sheet_List']:
        sheetinstance = book.copy_worksheet(mobansheet)
        sheetinstance.title=sheet['SheetName']
        for cell in sheetinstance.merged_cells:
            style_range(sheetinstance, cell.coord, border=border)
        rowindex = 7
        for dt in sheet['dt_List']:
            rowindex += 1
            write_cell(sheetinstance, str(dt['dtName']).split(' ')[0], 'A' + str(rowindex) + ':D' + str(rowindex),
                       border=border, fill=fill, font=font_b, alignment=al)
            write_cell(sheetinstance, str(dt['dtName']).split(' ')[1], 'E' + str(rowindex) + ':J' + str(rowindex),
                       border=border,
                       font=font_t, alignment=al)
            rowindex += 1
            write_cell(sheetinstance, "序号", 'A' + str(rowindex) + ':A' + str(rowindex + 1), border=border, fill=fill,
                       font=font_t, alignment=al)
            write_cell(sheetinstance, "测试目的", 'B' + str(rowindex) + ':B' + str(rowindex + 1), border=border, fill=fill,
                       font=font_t, alignment=al)
            write_cell(sheetinstance, "操作步骤", 'C' + str(rowindex) + ':E' + str(rowindex), border=border, fill=fill,
                       font=font_t, alignment=al)
            write_cell(sheetinstance, "期望结果", 'F' + str(rowindex) + ':G' + str(rowindex), border=border, fill=fill,
                       font=font_t, alignment=al)
            write_cell(sheetinstance, "测试数据", 'H' + str(rowindex) + ':H' + str(rowindex + 1), border=border, fill=fill,
                       font=font_t, alignment=al)
            write_cell(sheetinstance, "判断结论", 'I' + str(rowindex) + ':I' + str(rowindex + 1), border=border, fill=fill,
                       font=font_t, alignment=al)
            write_cell(sheetinstance, "备注", 'J' + str(rowindex) + ':J' + str(rowindex + 1), border=border, fill=fill,
                       font=font_t, alignment=al)
            rowindex += 1
            write_cell(sheetinstance, "动作", 'C' + str(rowindex) + ':C' + str(rowindex), border=border, fill=fill,
                       font=font_t, alignment=al)
            write_cell(sheetinstance, "参数", 'D' + str(rowindex) + ':D' + str(rowindex), border=border, fill=fill,
                       font=font_t, alignment=al)
            write_cell(sheetinstance, "对象", 'E' + str(rowindex) + ':E' + str(rowindex), border=border, fill=fill,
                       font=font_t, alignment=al)
            write_cell(sheetinstance, "类型", 'F' + str(rowindex) + ':F' + str(rowindex), border=border, fill=fill,
                       font=font_t, alignment=al)
            write_cell(sheetinstance, "参数", 'G' + str(rowindex) + ':G' + str(rowindex), border=border, fill=fill,
                       font=font_t, alignment=al)
            rowindex += 1
            print("创建" + dt['dtName'])
            for row in dt['dt']:
                write_cell(sheetinstance, row['序号'], 'A' + str(rowindex) + ':A' + str(rowindex), border=border, font=font_t, alignment=al)
                write_cell(sheetinstance, row['测试目的'], 'B' + str(rowindex) + ':B' + str(rowindex), border=border, font=font_t, alignment=al)
                write_cell(sheetinstance, row['动作'], 'C' + str(rowindex) + ':C' + str(rowindex), border=border, font=font_t, alignment=al)
                write_cell(sheetinstance, row['参数'], 'D' + str(rowindex) + ':D' + str(rowindex), border=border, font=font_t, alignment=al)
                write_cell(sheetinstance, row['对象'], 'E' + str(rowindex) + ':E' + str(rowindex), border=border, font=font_t)
                write_cell(sheetinstance, row['类型'], 'F' + str(rowindex) + ':F' + str(rowindex), border=border, font=font_t, alignment=al)
                write_cell(sheetinstance, row['期望'], 'G' + str(rowindex) + ':G' + str(rowindex), border=border, font=font_t)
                write_cell(sheetinstance, row['测试数据'], 'H' + str(rowindex) + ':H' + str(rowindex), border=border, font=font_t)
                write_cell(sheetinstance, row['判断结论'], 'I' + str(rowindex) + ':I' + str(rowindex), border=border, font=font_t, alignment=al)
                write_cell(sheetinstance, row['备注'], 'J' + str(rowindex) + ':J' + str(rowindex), border=border, font=font_t)
                rowindex += 1
    book.remove(book['范本'])
    book.save('target.xlsx')

target.xlsx 是现有的，因为目的是在其后添加sheet页。  

范本是其中的一个sheet，里面有我想要的母版。复制范本后生成新的sheet页，返回添加数据

注意：

for sheetins in book.worksheets:
        for cell in sheetins.merged_cells:
            style_range(sheetins, cell.coord, border=border)

loadexcel  会无视合并单元的表格样式，所以需要遍历合并单元格 添加边框。

有新功能会持续更新。。。。。