Styjun

I'm comparing two workbooks using Openpyxl I have it incrementing a counter for later usage and then keeping track of rows that should be removed from the initial workbook. How do I go about getting rid of these rows from that workbook or creating a new sheet(With the Original then deleted) or workbook with those rows removed?

I've written the code up until this point but I havent found much in terms of writing or deleting rows from a workbook and I haven't any concrete luck, I was advised by someone to instead create a copy of the workbook but I also have had no success at doing such.

from openpyxl import load_workbook
from tkinter import Tk
from tkinter.filedialog import askopenfilename
import datetime
import time

class ManualReporter:
 def __init__(self):
 '''
 Initializes Variables for use within the Class
 Hides the tkinter pop-up when using the file dialog
 '''
 Tk().withdraw()
 self.sap_file = None
 self.tracker_file = None
 self.wb_sap = None
 self.wb_wt = None
 self.XT = 0
 self.deadrows = []


 def open_sapfile(self):
 '''
 Sets the sap_file variable to be the first directory to the SAP Report based on what the User Selects in the File Dialog
 Sets that directory and the file as the current workbook under the variable self.wb_sap
 Creates a Backup of the SAP Report so that if Errors Occur a Fresh Clean Copy is Present
 '''
 self.sap_file = askopenfilename()
 self.wb_sap = load_workbook(filename=self.sap_file)
 # Code to create a backup File in-case of Error or Fault
 copyfile = "Untimed_Report_SAP_" + str(datetime.date.today())+".xlsx"
 self.wb_sap.save(copyfile)
 print(self.sap_file)
 def open_tracker(self):
 '''
 Same as Above, sets self.tracker_file as a filedialog which retrieves the file's directory (User Inputted)
 Loads the File Workbook as self.wb_wt
 Creates a Backup of the Second SAP Report so that if Error Occurs a Clean Copy is Present.
 '''
 self.tracker_file = askopenfilename()
 self.wb_wt = load_workbook(filename=self.tracker_file)
 print(self.tracker_file)
 def check_rows(self):
 '''
 Sets the Active Sheets in Both the Workbook Variables,
 Creates a New Sheet in the Newest Report to Contain the Modified Data,
 Iterates through the Rows of the Two Sheets checking for a Comparison in Part Number,
 OpCode and then Compares the X/T/P Classification and Adjusts Data in Second Sheet
 '''
 start = time.time()
 sap = self.wb_sap.worksheets[0] #Sets The First Sheet in the Excel Workbook as the variable sap
 wt = self.wb_wt.worksheets[0]#Sets the First Sheet in the Second Report as the var wt
 ws1 = self.wb_sap.create_sheet("Sheet1", 1)#Create a Spare Sheet in the First Report to place the Adjusted Data
 ws1 = self.wb_sap.worksheets[1]#Sets ws1 as the Active Second Sheet for New Data
 for saprow in sap.iter_rows():
 for wtrow in wt.iter_rows():
 if (saprow[3].value == wtrow[4].value and int(saprow[2].value) == int(wtrow[5].value)):# IF Material NUM & OPCode MATCH DO:
 if wtrow[7].value in ("T","P"): #WT Entry is Marked as T/P
 if saprow[4].value is "X": #SAP Report Entry is Marked as X
 self.XT += 1#Increment X->Ts Counts
 #print("X->T")
 self.deadrows.append(saprow)
 else:
 if saprow not in self.deadrows:
 ws1.append(saprow)


 end = time.time()
 #print("Finished, Total X->Ts: ", self.XT)
 print("Time Taken: ", (end - start))



x = ManualReporter()
x.open_sapfile()
x.open_tracker()
x.check_rows()

My expectation is that the output would be an exact copy of workbook one but the rows that had a certain change in values are removed from that workbook. I expected to be able to delete them but no methods I've done have achieved anything other than broken code or issues.

 self.deadrows.append(saprow)
 else:
 if saprow not in self.deadrows:
 for i in saprow:
 #Code to Create a row in ws1.
 #Code to Append value of saprow[i] to current ws1 rows

EDIT 1: I included my Attempts to append the rows to a copied worksheet.
EDIT 2: I though about manually iterating through the Saprow and appending the data into the rows of the new sheet but I've stumped myself thinking about it.

After Ample help I have reached the conclusion that to copy data from one sheet to another you can copy over data row by row through this Method:

self.workbook = load_workbook(filename="filepath")
sheet1 = self.workbook.worksheet[0]
sheet2 = self.workbook.create_sheet("Sheet 2")
sheet2 = self.workbook.worksheets[1]
for row in sheet1.iter_rows():
 sheet2.append([cell.value for cell in row])

I also figured out if you want to filter out data you can add if statements inside of the for-loop above that can limit what rows have their cells written into the new worksheet.

self.RowsToExclude = Some List containing row data that will be excluded.
for row in sheet1.iter_rows():
 if row not in self.RowsToExclude:
 ws1.append([cell.value for cell in row])

Finally, I'd like to thank all those who contributed towards me reaching this conclusion.