批量填充excel单元格，生成不同的excel文件

源码分享：

import os
import time
import sys
import subprocess
import xlwings as xw
from PyQt5 import QtGui, QtCore
from PyQt5.QtWidgets import *
from PyQt5.QtGui import QIcon, QPalette, QBrush, QPixmap
from PyQt5.QtCore import pyqtSignal, QTimer, QSize, QStorageInfo
from PyQt5.QtWidgets import QMessageBox
from PyQt5.QtCore import Qt
from openpyxl import load_workbook
from os.path import abspath
from PyQt5.QtWidgets import QCheckBox
class MainWindow(QWidget):
    signal1 = pyqtSignal(str)

    def __init__(self,*args,**kwargs):
        super().__init__(*args,**kwargs)
        # # 窗体标题和尺寸
        self.resize(1200,600)
        self.font = QtGui.QFont()
        self.font.setFamily("微软雅黑")  # 括号里可以设置成自己想要的其它字体
        self.font.setPointSize(12)  # 括号里的数字可以设置成自己想要的字体大小
        self.setFont(self.font)
        self.setWindowTitle('word与excel数据批量填充')
        layout = QVBoxLayout()
        self.layout = layout

        self.layout211 = QVBoxLayout()
        self.layout.addLayout(self.layout211)
        self.layout21 = QHBoxLayout()
        self.layout211.addLayout(self.layout21)
        self.layout_2 = QHBoxLayout()
        self.layout21.addLayout(self.layout_2)
        self.layout_2_1 = QHBoxLayout()
        self.layout21.addLayout(self.layout_2_1)

        self.layout31 = QHBoxLayout()
        self.layout211.addLayout(self.layout31)
        self.layout_31 = QHBoxLayout()
        self.layout31.addLayout(self.layout_31)
        self.layout_32 = QHBoxLayout()
        self.layout31.addLayout(self.layout_32)


        self.header_layout = QHBoxLayout()
        self.layout_4 = QVBoxLayout()
        self.layout.addLayout(self.layout_4)
        self.middle_layout = QHBoxLayout()

        self.layout_3 = QHBoxLayout()
        self.layout.addLayout(self.layout_3)
        self.footer_layout = QHBoxLayout()

        self.init_header()
        self.init_header2()
        self.init_middle()
        self.init_footer()

        #给窗体设置元素的排列方式
        self.setLayout(layout)  #设置整体的布局
        #参数设置
        self.path1 = None
        self.path2 = None
        self.number = 0
        # self.signal_test2.connect (self.test2)
        # self.signal_test3.connect (self.test3)
        # self.signal_test4.connect (self.test4)
        self.str1 = None
        self.str2 = None
        self.data_cols = []

    def init_header(self):
        # 创建按钮
        self.btn_choice_file = QPushButton('选择模版文件')
        self.btn_choice_file.clicked.connect(self.choose_file)
        self.header_layout.addWidget(self.btn_choice_file)
        self.layout_2.addLayout(self.header_layout)
        self.layout_2.addSpacing(50)
        #标签
        header_layout2 = QHBoxLayout()
        self.label = QLabel()
        self.label.setText('路径:')
        header_layout2.addWidget(self.label)
        # 输入框
        txt_asin = QLineEdit()
        self.txt_asin = txt_asin
        self.txt_asin.setReadOnly(True)
        header_layout2.addWidget(txt_asin)
        self.layout_2_1.addLayout(header_layout2)
        return self.header_layout

    def init_header2(self):
        # 创建按钮
        self.btn_choice_file2 = QPushButton('选填充数据')
        self.btn_choice_file2.clicked.connect(self.choose_file2)
        self.header_layout12 = QHBoxLayout()
        self.header_layout12.addWidget(self.btn_choice_file2)
        self.layout_31.addLayout(self.header_layout12)
        self.layout_31.addSpacing(50)
        # 标签
        header_layout2 = QHBoxLayout()
        self.label = QLabel()
        self.label.setText('路径:')
        header_layout2.addWidget(self.label)
        # 输入框
        txt_asin2 = QLineEdit()
        self.txt_asin2 = txt_asin2
        self.txt_asin2.setReadOnly(True)
        header_layout2.addWidget(txt_asin2)
        self.layout_32.addLayout(header_layout2)
        return self.header_layout

    def init_middle(self):
        # 文本框
        self.table_widget = QTableWidget(self)
        self.table_widget.setFont (self.font)
        self.middle_layout.addWidget(self.table_widget)
        self.layout_4.addLayout(self.middle_layout)
        return self.middle_layout

    def init_footer(self):

        #  开始按钮
        btn_1 = QPushButton('开始')
        btn_1.clicked.connect(self.start)
        self.layout_3.addWidget(btn_1)
        self.layout_3.addStretch()
        btn_2 = QPushButton('结束')
        self.layout_3.addWidget(btn_2)
        return self.footer_layout

    def choose_file(self):
        filename,_ = QFileDialog.getOpenFileName(self, "选择文件", "/", "All Files (*)")
        self.path1 = filename
        self.txt_asin.setText(self.path1)

    def choose_file2(self):
        filename, _ = QFileDialog.getOpenFileName(self, "选择文件2", "/", "All Files (*)")
        self.path2 = filename
        self.txt_asin2.setText(self.path2)
        self.fill_date()

    def fill_date(self):
        print(23422)
        wb = load_workbook (abspath (self.path2))
        wb.active
        sh = wb['Sheet1']
        max_row = sh.max_row
        self.col = max_col = sh.max_column
        self.table_widget.setColumnCount(sh.max_column+1)
        self.table_widget.setRowCount(sh.max_row)
        #隐藏列表头
        self.table_widget.verticalHeader().setVisible(False)
        # #隐藏行表头
        self.table_widget.horizontalHeader( ).setVisible (False)
        #QTableWidget设置整行选中
        self.table_widget.setSelectionBehavior (QAbstractItemView.SelectRows);
        self.all_header_combobox = []
        self.selcet_data = []
        self.selcet_data2 = []
        header_field = []
        # 逐行循环读取数据并填充到QTableWidget
        for row in range (1, max_row + 1):
            # 插入单选框
            checkBox = QCheckBox ( )
            if row==1:
                checkBox.setText('全选')
            self.table_widget.setCellWidget (row-1, 0, checkBox)
            checkBox.stateChanged.connect(self.on_state_changed)
            self.all_header_combobox.append(checkBox)
            print (f"读取第一行数据：{row}")

            for col in range (0, max_col):
                row_value = sh[row][col].value
                if row ==1:
                    self.data_cols.append(row_value)
                self.table_widget.setItem(row-1, col+1, QTableWidgetItem(str(row_value)))
        self.table_widget.horizontalHeader ( ).setSectionResizeMode (QHeaderView.Stretch)
        self.table_widget.horizontalHeader ( ).setSectionResizeMode (0, QHeaderView.Interactive)
        self.table_widget.setColumnWidth (0, 100)  # 设置第0列宽度
        self.all_index = []
        self.index = []
        for k in range(len(self.all_header_combobox)):
            self.all_index.append(k)

    def on_state_changed(self):
        row = self.table_widget.currentRow()
        print(row,'hang')
        try:
            if self.sender().checkState() == Qt.Checked:
                if row == 0:
                    for i in self.all_header_combobox:
                        i.setChecked(True)
                    self.index = self.all_index
                    print(self.index)
                else:
                    self.index.append(row)
                    self.index.sort()
                    self.index = list(set(self.index))
                    print(self.index,'222',self.all_index)
                    if self.index == self.all_index[1::]:
                        self.all_header_combobox[0].setChecked (True)
            else:
                if row == 0:
                    for i in self.all_header_combobox:
                        i.setChecked(False)
                    self.index = []
                    print(self.index,'kong')
                else:
                    self.index = list(set(self.index))
                    try:
                        self.index.remove (row)
                    except:
                         pass
                    if not self.index == self.all_index[1::]:
                        self.all_header_combobox[0].setChecked (False)
                        try:
                            self.index.remove (0)
                        except:
                            pass
                    if self.index ==[]:
                        self.index = []
                        try:
                            self.all_header_combobox[0].setChecked(False)
                            self.index.remove (0)
                        except Exception as e:
                            print(e)
        except Exception as e:
            print(e)

    def start(self):
        print(self.index,self.col)
        self.data2 = []
        for i in self.index:
            data = []
            for j in range(1,self.col-1):
                contents = self.table_widget.item(i, j).text()
                data.append(contents)
            self.data2.append(data)
        print(self.data2)
        self.tihuan()

    def tihuan(self):
        if 0 in self.index:
            self.index.remove(0)
        print(self.index,98985555)
        try:
            subprocess.call ("taskkill /f /im  wps.exe",shell=True)

            subprocess.call ("taskkill /f /im  excel.exe",shell=True)
        except Exception as e:
            print(e)
        try:
            app = xw.App (visible=False, add_book=False)
            for row in self.index:
                wb = app.books.open (self.path1)
                app.display_alerts = False
                app.screen_updating = False
                self.sht = wb.sheets[0]
                a = b = 0
                save_path = None
                for index,v in enumerate(self.data_cols):
                    if index == len(self.data_cols)-2:
                        a = self.table_widget.item (row,index+1).text ( )
                    elif index == len(self.data_cols)-1:
                        b = self.table_widget.item (row,index+1).text ( )
                    else:
                        # 赋值替换
                        self.sht.range(v).value = self.table_widget.item (row,index+1).text ( )
                    if not (a == 0 or b == 0):
                        save_path = os.path.join(a,b)
                if not len(save_path) == 0:
                    print(a,b,999)
                    print(save_path,9999)
                    wb.save(save_path)
            app.quit()
            subprocess.call ("taskkill /f /im  wps.exe",shell=True)
            subprocess.call("taskkill /f /im  excel.exe",shell=True)
        except Exception as e:
            print(e,'111')

if __name__ == '__main__':
    app = QApplication(sys.argv)
    window = MainWindow()
    window.show()
    app.exec_()