有两个数组,如何将其转换为xlsx对应格式

Question

我想实现一下功能,每个大列表的小列表为一行数据
list1为必需材料
list2为选配材料
如何做出腿肿图片的样子
我自己能填充数据进去,但是做不到合并he单元格填充颜色和设置边框,最开始的文字居中也做不到
希望在fun函数中实现
表格模型

list1 = [[1,"单元板","/","/","/","/",5,10,50,""],
         [2,"辅材","/","/","/","/",6,11,66,""],
         [3,"电源","/","/","/","/",7,12,84,""],
         [4,"控制卡","/","/","/","/",8,13,104,""],
         [5,"处理器","/","/","/","/",9,14,126,""],
         [6,"配电箱","/","/","/","/",10,15,150,""],
         [7,"结构","/","/","/","/",11,16,176,""],
         [8,"包边","/","/","/","/",12,17,204,""],
         [9,"安装调试","/","/","/","/",13,18,234,""],]
list2 = [[1,"箱体","/","/","/","/",5,50,250,""]
         [2,"多功能卡","/","/","/","/",6,51,306,""]
         [3,"音箱、功放","/","/","/","/",7,52,364,""]
         [4,"电脑","/","/","/","/",8,53,424,""]
         [5,"运输","/","/","/","/",9,54,486,""]
         [6,"施工平台","/","/","/","/",10,55,550,""]
         [7,"综合布线","/","/","/","/",11,56,616,""]]


def fun(list1: list,list2: list,filename: str = "default.xlsx") -> None:
    """python
    
    :param list1: 必须材料详细列表
    :param list2:选配材料详细列表
    :param filename:输出excel名字
    :return:None
    """
    pass


def main():
    fun(list1,list2)


if __name__ == '__main__':
    main()

预期运行后的结果

Answer 1

import win32com.client as win32
list1 = [[1,"单元板","/","/","/","/",5,10,50,""],
         [2,"辅材","/","/","/","/",6,11,66,""],
         [3,"电源","/","/","/","/",7,12,84,""],
         [4,"控制卡","/","/","/","/",8,13,104,""],
         [5,"处理器","/","/","/","/",9,14,126,""],
         [6,"配电箱","/","/","/","/",10,15,150,""],
         [7,"结构","/","/","/","/",11,16,176,""],
         [8,"包边","/","/","/","/",12,17,204,""],
         [9,"安装调试","/","/","/","/",13,18,234,""],]
list2 = [[1,"箱体","/","/","/","/",5,50,250,""],
         [2,"多功能卡","/","/","/","/",6,51,306,""],
         [3,"音箱、功放","/","/","/","/",7,52,364,""],
         [4,"电脑","/","/","/","/",8,53,424,""],
         [5,"运输","/","/","/","/",9,54,486,""],
         [6,"施工平台","/","/","/","/",10,55,550,""],
         [7,"综合布线","/","/","/","/",11,56,616,""]]

excel = win32.Dispatch('Excel.Application')
excel.Visible = True  # 可见
wb1 = excel.Workbooks.Add()   #创建excel
wb1.Worksheets.Add().Name = '汇总'    #命名初始sheet
ws1 = wb1.Worksheets('汇总')
ws1.Range('A1:J2').Merge()
ws1.Range('A1:J23').HorizontalAlignment = -4108
ws1.Range('A3:J3').Value = [['序号','产品名称','品牌','型号','参数','单位','数量','单价','合计','备注']]
ws1.Range('A4:J12').Value = list1
ws1.Range('A15:J21').Value = list2

ws1.Range('A13:H13').Merge()
ws1.Range('A14:J14').Merge()
ws1.Range('A22:H22').Merge()
ws1.Range('A23:H23').Merge()

ws1.Range('A1').Value='室内工程报价模型'
ws1.Range('A1').Font.Bold = True      
ws1.Range('A13').Value='小计'
ws1.Range('A14').Value='以下材料为选配'
ws1.Range('A22').Value='小计'
ws1.Range('A23').Value='合计'

ws1.Range('I13').Value='=sum(I4:I12)'
ws1.Range('I22').Value='=sum(I15:I21)'
ws1.Range('I23').Value='=I13+I22'

ws1.Range('A1:J23').Interior.ColorIndex = 23
ws1.Range('A3:J12').Interior.ColorIndex = 15
ws1.Range('A15:J21').Interior.ColorIndex = 15
ws1.Range("A1:J23").Borders.LineStyle=1 # 边框线段

output_file_path = r'C:\Users\gztsrayz\Desktop\实验场（实时删除）\gg.xlsx'
wb1.SaveAs(output_file_path) #保存
wb1.Close(0)    #关闭

def close_excel_by_force(excel):                        #关闭进程
    
    #Get the window's process id's
    hwnd = excel.Hwnd
    t, p = win32process.GetWindowThreadProcessId(hwnd)
    # Ask window nicely to close  
    try:
        handle = win32api.OpenProcess(win32con.PROCESS_TERMINATE, 0, p)
        if handle:
            win32api.TerminateProcess(handle, 0)
            win32api.CloseHandle(handle)
    except:
        pass
close_excel_by_force(excel)