Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
18 kB
3
Indexable
Never
import logging
import os
import sys
import datetime
import openpyxl
from concurrent.futures import ThreadPoolExecutor, as_completed
from openpyxl.drawing.image import Image
from openpyxl.cell.rich_text import CellRichText
import win32com.client as win32


from Checksum import Checksum


class ExcelComparator:
    @staticmethod
    def serialize_value(value):
        if isinstance(value, datetime.datetime):
            return value.isoformat()
        return str(value)

    @staticmethod
    def get_cell_details(cell):
        cell_details = {
            'value': ExcelComparator.serialize_value(cell.value),
            'data_type': str(cell.data_type),
            'font': cell.font.__dict__,
            'border': cell.border.__dict__,
            'fill': cell.fill.__dict__,
            'number_format': str(cell.number_format),
            'alignment': cell.alignment.__dict__,
            'protection': cell.protection.__dict__,
            'hyperlink': str(cell.hyperlink),
            'comment': str(cell.comment),
            'rich_text': ExcelComparator.serialize_value(cell.value) if isinstance(cell.value, CellRichText) else None,
            'formula': ExcelComparator.serialize_value(cell.value) if cell.data_type == 'f' else None
        }
        return cell_details

    @staticmethod
    def get_image_details(sheet):
        images = []
        for drawing in sheet._images:
            if isinstance(drawing, Image):
                try:
                    width = getattr(drawing, 'width', None)
                    height = getattr(drawing, 'height', None)
                    anchor = getattr(drawing, 'anchor', None)

                    # If anchor is a complex object with `_from` attribute
                    if hasattr(anchor, '_from'):
                        position = (anchor._from.col, anchor._from.row)
                    else:
                        position = anchor  # Assuming anchor is a simple attribute like 'A1'

                    image_details = {
                        'name': drawing.path,
                        'size': (width, height),
                        'position': position
                    }
                    images.append(image_details)
                except Exception as e:
                    print(f"Error processing image: {e}")
        return images

    @staticmethod
    def compare_images(images_old, images_new, sheet_name):
        mismatches = []

        old_image_names_positions = {(img['name'], img['position']) for img in images_old}
        new_image_names_positions = {(img['name'], img['position']) for img in images_new}

        added_images = new_image_names_positions - old_image_names_positions
        removed_images = old_image_names_positions - new_image_names_positions

        for img_name, img_position in added_images:
            img_new = next(img for img in images_new if img['name'] == img_name and img['position'] == img_position)
            mismatches.append({
                'sheet_name': sheet_name,
                'cell_reference': f"Image at {img_new['position']}",
                'difference_type': 'Image Added',
                'base_line_value': 'N/A',
                'test_env_value': img_new['name'],
                'details': f"New size: {img_new['size']}\nPosition: {img_new['position']}"
            })

        for img_name, img_position in removed_images:
            img_old = next(img for img in images_old if img['name'] == img_name and img['position'] == img_position)
            mismatches.append({
                'sheet_name': sheet_name,
                'cell_reference': f"Image at {img_old['position']}",
                'difference_type': 'Image Removed',
                'base_line_value': img_old['name'],
                'test_env_value': 'N/A',
                'details': f"Old size: {img_old['size']}\nPosition: {img_old['position']}"
            })

        for img_name, img_position in old_image_names_positions & new_image_names_positions:
            img_old = next(img for img in images_old if img['name'] == img_name and img['position'] == img_position)
            img_new = next(img for img in images_new if img['name'] == img_name and img['position'] == img_position)
            if img_old['position'] != img_new['position']:
                mismatches.append({
                    'sheet_name': sheet_name,
                    'cell_reference': f"Image at {img_old['position']} to {img_new['position']}",
                    'difference_type': 'Image Position Changed',
                    'base_line_value': img_old['position'],
                    'test_env_value': img_new['position'],
                    'details': f"Image: {img_old['name']}\nOld position: {img_old['position']}\nNew position: {img_new['position']}"
                })
            if img_old['size'] != img_new['size']:
                mismatches.append({
                    'sheet_name': sheet_name,
                    'cell_reference': f"Image at {img_old['position']}",
                    'difference_type': 'Image Size Changed',
                    'base_line_value': img_old['size'],
                    'test_env_value': img_new['size'],
                    'details': f"Image: {img_old['name']}\nOld size: {img_old['size']}\nNew size: {img_new['size']}"
                })

        return mismatches

    @staticmethod
    def compare_rich_text_objects(old_rich_text, new_rich_text):
        def extract_text(rich_text):
            if hasattr(rich_text, 'text'):
                return rich_text.text
            elif isinstance(rich_text, str):
                return rich_text
            return ""

        old_text = extract_text(old_rich_text)
        new_text = extract_text(new_rich_text)

        return old_text != new_text

    @staticmethod
    def extract_and_compare_formats(old_format, new_format):
        def compare_dicts(dict1, dict2):
            differences = []
            for key in set(dict1.keys()).union(set(dict2.keys())):
                value1 = dict1.get(key)
                value2 = dict2.get(key)
                if value1 != value2:
                    differences.append((key, 'diff'))
            return differences

        differences = compare_dicts(old_format, new_format)
        return differences

    @staticmethod
    def compare_values(old_value, new_value):
        if isinstance(old_value, datetime.datetime) and isinstance(new_value, datetime.datetime):
            return old_value != new_value
        return str(old_value) != str(new_value)

    @staticmethod
    def check_content(sheet_name, ws_new, ws_old):
        output_list = []

        max_row = max(ws_new.max_row, ws_old.max_row)
        max_column = max(ws_new.max_column, ws_old.max_column)

        for row in range(1, max_row + 1):
            for col in range(1, max_column + 1):
                cell_new = ws_new.cell(row=row, column=col)
                cell_old = ws_old.cell(row=row, column=col)

                if ExcelComparator.compare_values(cell_old.value, cell_new.value):
                    output_list.append({
                        'sheet_name': sheet_name,
                        'cell_reference': cell_old.coordinate,
                        'difference_type': 'Value Mismatch',
                        'base_line_value': str(cell_old.value),
                        'test_env_value': str(cell_new.value),
                        'details': 'Cell value mismatch'
                    })

                if cell_old.data_type == 'f' or cell_new.data_type == 'f':
                    if cell_old.value != cell_new.value:
                        output_list.append({
                            'sheet_name': sheet_name,
                            'cell_reference': cell_old.coordinate,
                            'difference_type': 'Formula Mismatch',
                            'base_line_value': str(cell_old.value),
                            'test_env_value': str(cell_new.value),
                            'details': 'Formula mismatch'
                        })

        return output_list

    @staticmethod
    def check_format(sheet_name, ws_new, ws_old):
        output_list = []

        max_row = max(ws_new.max_row, ws_old.max_row)
        max_column = max(ws_new.max_column, ws_old.max_column)

        for row in range(1, max_row + 1):
            for col in range(1, max_column + 1):
                cell_new = ws_new.cell(row=row, column=col)
                cell_old = ws_old.cell(row=row, column=col)

                old_details = ExcelComparator.get_cell_details(cell_old)
                new_details = ExcelComparator.get_cell_details(cell_new)

                # Exclude value and formula mismatches from format checks
                if ExcelComparator.compare_values(cell_old.value, cell_new.value) or \
                        (cell_old.data_type == 'f' or cell_new.data_type == 'f') and cell_old.value != cell_new.value:
                    continue

                mismatches = {
                    'rich_text': ExcelComparator.compare_rich_text_objects(old_details['rich_text'],
                                                                           new_details['rich_text']) or
                                 isinstance(old_details['rich_text'], CellRichText) != isinstance(
                        new_details['rich_text'], CellRichText),
                    'font': old_details['font'] != new_details['font'],
                    'border': old_details['border'] != new_details['border'],
                    'fill': old_details['fill'] != new_details['fill'],
                    'number_format': old_details['number_format'] != new_details['number_format'],
                    'alignment': old_details['alignment'] != new_details['alignment']
                }

                if any(mismatches.values()):
                    details = "\n".join(
                        [f"{key.replace('_', ' ').title()} differences" for key, mismatch in mismatches.items() if
                         mismatch])

                    output_list.append({
                        'sheet_name': sheet_name,
                        'cell_reference': str(cell_old.coordinate),
                        'difference_type': 'Format Mismatch',
                        'base_line_value': str(old_details['value']),
                        'test_env_value': str(new_details['value']),
                        'details': details
                    })

        images_old = ExcelComparator.get_image_details(ws_old)
        images_new = ExcelComparator.get_image_details(ws_new)

        image_mismatches = ExcelComparator.compare_images(images_old, images_new, sheet_name)
        output_list.extend(image_mismatches)

        return output_list

    @staticmethod
    def check_sheet_order_and_missing(wb_new, wb_old):
        output_list = []
        sheet_order_mismatch = {
            "status": True,
            "details": ""
        }

        new_sheets = wb_new.sheetnames
        old_sheets = wb_old.sheetnames

        common_sheets = set(new_sheets).intersection(set(old_sheets))
        missing_in_new = set(old_sheets) - set(new_sheets)
        missing_in_old = set(new_sheets) - set(old_sheets)

        if missing_in_new:
            output_list.append({
                'sheet_name': 'N/A',
                'cell_reference': 'N/A',
                'difference_type': 'Missing Sheets in New File',
                'base_line_value': ', '.join(missing_in_new),
                'test_env_value': 'N/A',
                'details': 'Sheets are present in old file but missing in new file.'
            })
        if missing_in_old:
            output_list.append({
                'sheet_name': 'N/A',
                'cell_reference': 'N/A',
                'difference_type': 'Missing Sheets in Old File',
                'base_line_value': 'N/A',
                'test_env_value': ', '.join(missing_in_old),
                'details': 'Sheets are present in new file but missing in old file.'
            })

        common_sheets_list = [sheet for sheet in old_sheets if sheet in common_sheets]
        new_sheets_list = [sheet for sheet in new_sheets if sheet in common_sheets]

        if common_sheets_list != new_sheets_list:
            sheet_order_mismatch["status"] = False
            mismatch_details = []
            min_length = min(len(common_sheets_list), len(new_sheets_list))

            for i in range(min_length):
                if common_sheets_list[i] != new_sheets_list[i]:
                    mismatch_details.append(f"Position {i + 1}: {common_sheets_list[i]} -> {new_sheets_list[i]}")

            if len(new_sheets_list) > min_length:
                mismatch_details.append(f"Additional sheets in new file: {new_sheets_list[min_length:]}")
            elif len(common_sheets_list) > min_length:
                mismatch_details.append(f"Additional sheets in old file: {common_sheets_list[min_length:]}")

            sheet_order_mismatch["details"] = "\n".join(mismatch_details)

            output_list.append({
                'sheet_name': 'N/A',
                'cell_reference': 'N/A',
                'difference_type': 'Sheet Order Mismatch',
                'base_line_value': str(common_sheets_list),
                'test_env_value': str(new_sheets_list),
                'details': "\n".join(mismatch_details)
            })

        return output_list, sheet_order_mismatch
    @staticmethod
    def enable_editing_and_save(file_path):
        file_path = os.path.abspath(file_path)
        try:
            excel = win32.gencache.EnsureDispatch('Excel.Application')
            workbook = excel.Workbooks.Open(file_path)
            workbook.Save()
            workbook.Close()
            excel.Application.Quit()
            logging.info(f"Enabled editing and saved the file {file_path}")
        except Exception as e:
            logging.error(f"Failed to enable editing and save the file {file_path}: {e}")

    def compare_files(self, test_env_file, base_line_file, compare_type):
        if Checksum.md5(test_env_file) != Checksum.md5(base_line_file):
            try:
                wb_new = openpyxl.load_workbook(test_env_file, data_only=False, rich_text=True)
                wb_old = openpyxl.load_workbook(base_line_file, data_only=False, rich_text=True)
            except Exception as e:
                print(f"Error loading workbooks: {e}", file=sys.stderr)
                if 'NoneType' in str(e):
                    ExcelComparator.enable_editing_and_save(test_env_file)
                    ExcelComparator.enable_editing_and_save(base_line_file)
                    try:
                        wb_new = openpyxl.load_workbook(test_env_file, data_only=False, rich_text=True)
                        wb_old = openpyxl.load_workbook(base_line_file, data_only=False, rich_text=True)
                    except Exception as e:
                        print(f"Error loading workbooks after enabling editing: {e}", file=sys.stderr)
                        sys.exit(1)
                else:
                    sys.exit(1)

            sheet_order_mismatches, sheet_order_mismatch = self.check_sheet_order_and_missing(wb_new, wb_old)

            output_list = []

            new_sheets = set(wb_new.sheetnames)
            old_sheets = set(wb_old.sheetnames)

            common_sheets = new_sheets.intersection(old_sheets)

            with ThreadPoolExecutor() as executor:
                if compare_type == "content":
                    futures = [executor.submit(self.check_content, sheet_name, wb_new[sheet_name], wb_old[sheet_name])
                               for sheet_name in common_sheets]
                elif compare_type == "format":
                    futures = [executor.submit(self.check_format, sheet_name, wb_new[sheet_name], wb_old[sheet_name])
                               for sheet_name in common_sheets]

                for future in as_completed(futures):
                    output_list.extend(future.result())

            if compare_type == "content":
                combined_result = {
                    "overall_status": not output_list and sheet_order_mismatch["status"],
                    "sheet_order_comparison": sheet_order_mismatch,
                    "mismatches": sheet_order_mismatches + output_list
                }
            else:
                combined_result = {
                    "overall_status": not output_list,
                    "mismatches": output_list
                }

            return combined_result
        else:
            return {
                "overall_status": True,
                "sheet_order_comparison": {"status": True, "details": ""} if compare_type == "content" else None,
                "mismatches": []
            }


    def do_content_compare(self, test_env_file, base_line_file):
        return self.compare_files(test_env_file, base_line_file, "content")

    def do_format_compare(self, test_env_file, base_line_file):
        return self.compare_files(test_env_file, base_line_file, "format")
Leave a Comment