Python + openpyxl 生成Excel并设置样式

这里说一下我命令行接收的参数

python pin_join.py -t template -d data.json

-t 模板路径， -d json文件路径

第三方库 openpyxl

直接上代码

import getopt
import sys
import json
import os
import time
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, Alignment, PatternFill


class Excel(object):
    def __init__(self):
        self.arg = self.get_args()
        self.thin = Side(border_style="thin", color="000000")
        self.border = Border(left=self.thin, right=self.thin,top=self.thin, bottom=self.thin)
        self.align = Alignment(horizontal='center',vertical='center', wrap_text=True)
        self.fill1 = PatternFill(start_color="cde6c7",end_color="cde6c7", fill_type="solid")
        self.fill2 = PatternFill(start_color="feeeed",end_color="feeeed", fill_type="solid")
        self.border_h = 0

	    def set_border(self, obj):
	        """
	        设置单元格样式
	        """
	        for i in range(3, self.border_h + 3):
	            for j in ["A", "B", "C", "D", "E", "F", "G", "H"]:
	                obj[j + str(i)].border = self.border
	                obj[j + str(i)].alignment = self.align
	                if i % 2 == 0:
	                    obj[j + str(i)].fill = self.fill1
	                else:
	                    obj[j + str(i)].fill = self.fill2
	
	    def get_args(self):
	        """
	        获取命令行参数
	        """
	        arg_path = {}
	        try:
	            opts, args = getopt.getopt(sys.argv[1:], "t:d:", ["--template", "--demo"])
	            del args
	        except getopt.GetoptError:
	            raise ValueError('输入参数错误')
	        for name, value in opts:
	            if name in ("-t", "--template"):
	                arg_path["template"] = value
	            if name in ("-d", "--demo"):
	                arg_path["demo"] = value
	        return arg_path
	
	    def get_excel_data(self):
	        """
	        读取json文件与excel模板
	        """
	        template = self.arg["template"]
	        json_d = self.arg["demo"]
	        with open(json_d, 'r', encoding='utf8') as f:
	            json_data = json.load(f)
	        return self.get_excel(template, json_data)
	
	    def get_excel(self, template, json_data):
	        """
	        两个表准备数据填入
	        """
	        wb_etest = load_workbook(filename=template)
	        for k, v in json_data.items():
	            if k == "etest":
	                self.get_data_etest(v, wb_etest["测试设备"])
	                self.set_border(wb_etest["测试设备"])
	                self.border_h = 0
	            elif k == "uut":
	                self.get_data_etest(v, wb_etest["被测设备"])
	                self.set_border(wb_etest["被测设备"])
	                self.border_h = 0
	        return self.save_file("设备接线表_" + time.strftime("%Y%m%d%H%M%S") + ".xlsx", wb_etest)
	
	    def get_data_etest(self, etest, etest_dev):
	        """
	        模板中填充数据
	        """
	        for item in etest:
	            num = str(etest.index(item)+3)
	            etest_dev["A"+num] = item.get("dev", None)
	            etest_dev["B"+num] = item.get("connector", None)
	            etest_dev["C"+num] = item.get("channel", None)
	            etest_dev["D"+num] = item.get("pin", None)
	            link = item.get("link", None)
	            if link is not None:
	                etest_dev["E"+num] = link.get("pin", None)
	                etest_dev["F"+num] = link.get("channel", None)
	                etest_dev["G"+num] = link.get("connector", None)
	                etest_dev["H"+num] = link.get("dev", None)
	            self.border_h = self.border_h + 1
	
	    def save_file(self, name, wb_obj):
	        """
	        保存临时文件夹并打开
	        """
	        file_path = os.path.join(os.environ["TMP"], name)
	        wb_obj.save(file_path)
	        os.startfile(file_path)
	
	
	if __name__ == "__main__":
	    try:
	        obj = Excel()
	        obj.get_excel_data()
	    except:
	        current_path = os.path.dirname(__file__)
	        parent_path = os.path.dirname(current_path)
	        err_path = parent_path + "\\pin_join\\error.xlsx"
	        os.startfile(err_path)
	 

生成的Excel效果

以上例子只需要配置好文件路径，以及需要的json文件即刻， 亲测有效

	# json文件格式如下
	{
    "etest": [{
        "link": {
            "dev": "dev_uut",
            "connector": "conn2",
            "channel": "do2",
            "pin": "22"
        },
        "dev": "dev_test",
        "connector": "conn2",
        "channel": "di2"
    }],
    "uut": [{
        "dev": "dev_uut",
        "connector": "conn2",
        "channel": "do2",
        "pin": "22",
        "link": {
            "dev": "dev_test",
            "connector": "conn2",
            "channel": "di2"
        }
    }, {
        "link": {
            "dev": "dev_test",
            "connector": "conn2",
            "channel": "di1"
        },
        "dev": "dev_uut",
        "connector": "conn2",
        "channel": "do1",
        "pin": "11"
    }]
}
# 模板可以给空Excel建立两个表测试设备、被测设备即可

生成Excel保存在系统临时路径下

• 保存临时路径 file_path = os.path.join(os.environ["TMP"], name)
• 调用操作系统API自动打开os.startfile(file_path)

openpyxl的一些其他功能

	from openpyxl import load_workbook
	from openpyxl.styles import Border, Side, Alignment, PatternFill
	from openpyxl.utils import FORMULAE
	
	self.thin = Side(border_style="thin", color="000000")
	Border(left=self.thin, right=self.thin, top=self.thin, bottom=self.thin) #单元格上下左右设置边框
	Alignment(horizontal='center',vertical='center', wrap_text=True) #设置字体的上下左右居中显示
	PatternFill(start_color="cde6c7", end_color="cde6c7", fill_type="solid")# 设置表格的背景色
	wb_etest = load_workbook(filename=template)# 打开一个已有的Excel
	wb_etest["sheet1"] #切换到名称为sheet1的表
	wb_etest.merge_cells("C1:D2") #合并单元格
	wb_etest.merge_cells(start_row=7,start_column=1,end_row=8,end_column=3)#合并单元格
	FORMULAE #表示python支持的Excel公式

更多高级功能请参见官方文档