Openpyxl Compare Cells

I have 2 sheets with some data (18k rows each) and need to check if value from source.xlsx exists in a target.xlsx file. The rows in the source file should be unique. If the cell f

Solution 1:

Question: check if value from source.xlsx exists in a target.xlsx file.

Implement it like the following example: Documentation: OpenPyXl - accessing-many-cellsPython - Mapping Types — dict, Python - object.__init__

classSourceSheet:
    def__init__(self, ws):
        self.ws = ws

    def__iter__(self):
        """
        Implement iterRows or iterRange
        :return: yield a tuple (value_to_search, value_to_fill)
        """# Example iterRangefor row inrange(1, self.ws.max_rows + 1):
            yield (self.ws.cell(row=row, column=1).value, self.ws.cell(row=row, column=2).value)

classTargetSheet:
    def__init__(self, ws):
        self.ws = ws

        """
        Create a 'dict' from all Values in Column A
        This allows Random Access the Cell Value to get the Cell Row Index
        Dict.key == Cell Value
        Dict.value = Cell Row Index
        _columnA = {} # {cell.value:cell.row}
        """
        self._columnA = dict(((c.value, c.row) for c in ws['A']))

    deffind(self, value):
        """
        Implement either linear Search using iterRows over one Column or
                         search in dict to find 'value'
        :param value: The value to find
        :return: The Cell, to write the 'value_to_fill'
        """# Example using dictif value in self._columnA:
            return self.ws.cell(row=self._columnA[value], column=2)


sourceSheet = SourceSheet(ws1)
targetSheet = TargetSheet(ws2)        

for value_to_search, value_to_fill in sourceSheet:
    print("SourceSheet:{}".format((value_to_search, value_to_fill)))
    targetCell = targetSheet.find(value_to_search)

    if targetCell:
        print("Match: Write value '{}' to TargetSheet:'{}'".format(value_to_fill, targetCell))
        targetCell.value = value_to_fill
    else:
        print("Value '{}' not fount in TargetSheet!".format(value_to_search))

Output:

SourceSheet:('cell.A1.value', 'cell.B1.value')
Match: Write value'cell.B1.value'to TargetSheet:'Cell.B1:'
SourceSheet:('cell.A2.value', 'cell.B2.value')
Match: Write value'cell.B2.value'to TargetSheet:'Cell.B2:'
SourceSheet:('cell.A3.value', 'cell.B3.value')
Match: Write value'cell.B3.value'to TargetSheet:'Cell.B3:'

Tested with Python: 3.5

Solution 2:

From my understanding of your question it seems like the rows in target file are not arranged in the same specific order as the source file.

for i inrange(1, souce_sheet_max_rows):
    for j inrange(1, target_sheet_max_rows):
        if target_wb[temp_sheet_name].cell(row=j, column=1).value== source_wb[temp_sheet_name].cell(row=i, column=1).value:
            target_wb[temp_sheet_name].cell(row=j, column=2).value== source_wb[temp_sheet_name].cell(row=i, column=2).value
            break
target_wb.save(temp_sheet_name)