I am creating a program that adds excel workbooks to a queue and using xlwings to open each workbook and copy data and then paste to a specific workbook.
I currently have a working program, however I would like to append the copied data to the next blank row in a specific workbook, instead of inputting a range manually. Can this be done?
The following program works for me, but I have to manually specify the range in the append sheet.
import sys,os,threading,traceback,os
import xlwings as xw
from queue import *
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtCore import Qt, pyqtSlot
from PyQt5.QtGui import QIcon, QPixmap
from PyQt5.QtWidgets import QApplication, QMainWindow, QAction, QProgressBar, QFileDialog,QTableWidgetItem,QHeaderView
#Directory to where excel sheets are located
Directory = ["C:\\Users\\{0}\\Desktop".format(os.getlogin()]
#Specific file to be written to
GSS_Excel = ['Append.xls']
class Ui_QueueWindow(object):
def setupUi(self, QueueWindow):
QueueWindow.setObjectName("QueueWindow")
QueueWindow.resize(430, 250)
sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Fixed, QtWidgets.QSizePolicy.Fixed)
sizePolicy.setHorizontalStretch(0)
sizePolicy.setVerticalStretch(0)
sizePolicy.setHeightForWidth(QueueWindow.sizePolicy().hasHeightForWidth())
QueueWindow.setSizePolicy(sizePolicy)
QueueWindow.setMinimumSize(QtCore.QSize(430, 250))
QueueWindow.setMaximumSize(QtCore.QSize(430, 250))
self.centralwidget = QtWidgets.QWidget(QueueWindow)
self.centralwidget.setObjectName("centralwidget")
self.gridLayout = QtWidgets.QGridLayout(self.centralwidget)
self.gridLayout.setObjectName("gridLayout")
self.verticalLayout = QtWidgets.QVBoxLayout()
self.verticalLayout.setObjectName("verticalLayout")
self.QueueTable = QtWidgets.QTableWidget(self.centralwidget)
self.QueueTable.setObjectName("QueueTable")
self.QueueTable.setColumnCount(2)
self.QueueTable.setRowCount(0)
self.QueueTable.setSizeAdjustPolicy(QtWidgets.QAbstractScrollArea.AdjustToContents)
item = QtWidgets.QTableWidgetItem()
self.QueueTable.setHorizontalHeaderItem(0, item)
item = QtWidgets.QTableWidgetItem()
self.QueueTable.setHorizontalHeaderItem(1, item)
self.verticalLayout.addWidget(self.QueueTable)
self.horizontalLayout = QtWidgets.QHBoxLayout()
self.horizontalLayout.setObjectName("horizontalLayout")
self.add_queue = QtWidgets.QPushButton(self.centralwidget)
self.add_queue.setObjectName("add_queue")
self.horizontalLayout.addWidget(self.add_queue)
self.run_queue = QtWidgets.QPushButton(self.centralwidget)
self.run_queue.setObjectName("run_queue")
self.horizontalLayout.addWidget(self.run_queue)
self.verticalLayout.addLayout(self.horizontalLayout)
self.gridLayout.addLayout(self.verticalLayout, 0, 0, 1, 1)
QueueWindow.setCentralWidget(self.centralwidget)
self.statusbar = QtWidgets.QStatusBar(QueueWindow)
self.statusbar.setObjectName("statusbar")
QueueWindow.setStatusBar(self.statusbar)
self.retranslateUi(QueueWindow)
QtCore.QMetaObject.connectSlotsByName(QueueWindow)
def retranslateUi(self, QueueWindow):
_translate = QtCore.QCoreApplication.translate
QueueWindow.setWindowTitle(_translate("QueueWindow", "Global Shop Program"))
item = self.QueueTable.horizontalHeaderItem(0)
item.setText(_translate("QueueWindow", "File Name"))
self.add_queue.setText(_translate("QueueWindow", "Add"))
self.run_queue.setText(_translate("QueueWindow", "Run"))
class QueueWindow(QMainWindow,Ui_QueueWindow):
def __init__(self,parent = None):
super().__init__(parent)
self.setupUi(self)
self.add_queue.clicked.connect(self.add)
self.run_queue.clicked.connect(self.run)
self.queue = Queue(15)
def add(self):
filter = "Excel(*.xlsx)"
fileToAdd = QFileDialog.getOpenFileName(self, "Select File", "C:\\Users\\{0}\\Desktop\\".format(os.getlogin()), filter)
fileToAdd = fileToAdd[0]
rowPosition = self.QueueTable.rowCount()
self.QueueTable.insertRow(rowPosition)
self.QueueTable.setItem(rowPosition , 0, QTableWidgetItem("{0}".format(fileToAdd)))
self.QueueTable.resizeColumnsToContents()
self.queue.put(fileToAdd)
def run(self):
try:
while True:
app = xw.App(visible=False)
#specific file to write to
appendWorkbook = xw.Book(r'{0}\{1}'.format(Directory[0],GSS_Excel[2]))
appendSheet = appendWorkbook.sheets['Sheet1']
app1 = xw.App(visible=False)
#files used from queue
GSS_EIS = xw.Book(r'{0}'.format(self.queue.get()))
GSS_Sheet1 = GSS_EIS.sheets['Sheet1']
##Copies data from GSS_Sheet1 and pastes to appendSheet
appendSheet.range('A2:A6').value = GSS_Sheet1 .range('A1:A6').options(ndim=2).value # I'd like to not have to specify range in appendSheet and instead find the next blank row to append to and paste data there.
self.queue.task_done()
appendWorkbook.save(r'{0}\{1}'.format(Directory[0],GSS_Excel[2]))
app.kill()
app1.kill()
self.QueueTable.removeRow(self.QueueTable.rowCount()-1)
if self.queue.empty():
break
except:
print(traceback.format_exc())
def main():
app = QApplication(sys.argv)
window = QueueWindow()
window.show()
sys.exit(app.exec_())
if __name__ == '__main__':
main()
Looking for something close to this;
def run(self):
try:
while True:
app = xw.App(visible=False)
appendWorkbook = xw.Book(r'{0}\{1}'.format(Directory[0],GSS_Excel[2]))
Sheet1 = appendWorkbook.sheets['Sheet1']
app1 = xw.App(visible=False)
GSS_EIS = xw.Book(r'{0}'.format(self.queue.get()))
GSS_Sheet1 = GSS_EIS.sheets['GSS Data']
for row in range(1,10):
for col in range(1,10):
if Sheet1.range((row,col)).value == "":
Sheet1.range((row,col)).value = GSS_Sheet1.range('A15:D15').options(ndim=2).value
self.queue.task_done()
appendWorkbook.save(r'{0}\{1}'.format(Directory[0],GSS_Excel[2]))
app.kill()
app1.kill()
self.QueueTable.removeRow(self.QueueTable.rowCount()-1)
if self.queue.empty():
break
except:
print(traceback.format_exc())
Example:
Excel workbooks in queue:
IS_1.xlsx
IS_2.xlsx
IS_3.xlsx
Specific workbook to add data to:
Append.xlsx
As shown in image, I would like to append to A7:F7 - without having to manually specify the row. I am looking for some type of xlwings code that searches the excel sheet for the next blank row and then pastes to it.
Append.xlsx:
