#!/usr/bin/python
# -*- coding=utf-8 -*-

import openpyxl

from openpyxl import worksheet


class Excel(object):

    def __init__(self):
        self.work_book = None
        self.work_sheet: worksheet = None

    def load(self, path, index=None, sheet_name=None):
        """

        :param path: excel文件地址
        :param index: 表格下标
        :param sheet_name: 表格名字
        :return:
        """
        if path is None:
            raise ValueError("请配置excel文件地址")

        self.work_book = openpyxl.load_workbook(path, read_only=False)
        self.set_sheet(index, sheet_name)

    def set_sheet(self, index=None, sheet_name=None):
        """
        设置表格，默认第一个，优先按照index
        :param index: 表格下标
        :param sheet_name: 表格名字
        :return:
        """
        if isinstance(index, int) and index >= 0:
            self.work_sheet = self.work_book.worksheets[index]
        elif isinstance(sheet_name, str):
            self.work_sheet = self.work_book[sheet_name]
        else:
            self.work_sheet = self.work_book.worksheets[0]

    def update_by_condition(self, param):
        """

        :param param:
            {
                "condition":{
                    "col":"A",
                    "value":["张三","李四"]
                },
                "value":{
                    "H":"填入的值",
                    "G":"G列的值"
                }
            }
        :return:
        """
        updates = 0
        condition = param.get("condition", {})
        condition_aim = condition.get("value", [])
        for row in range(1, self.work_sheet.max_row + 1):
            condition_index = "{}{}".format(condition.get("col"), row)
            condition_value = self.work_sheet[condition_index].value
            if condition_value in condition_aim:
                updates += 1
                for col, val in param.get("value", {}).items():
                    cell = "{}{}".format(col, row)
                    self.work_sheet[cell] = val

        # print("表格\t条件列\t条件\t更新数")
        # print("{}\t{}\t{}\t{}".format(self.work_sheet.title, condition.get("col"), condition_aim, updates))
        print("表格:{},列在{},条件为{}之一,更新条数:{}".format(self.work_sheet.title, condition.get("col"), condition_aim, updates))

    def save(self, path):
        self.work_book.save(path)


if __name__ == '__main__':
    handler = Excel()

    handler.load("/Users/lh/Downloads/联名&IP合——鞋服2.xlsx")

    handler.set_sheet(sheet_name="服饰映射")

    handler.update_by_condition(
        {
            "condition": {
                "col": "A",
                "value": ["IP合作"]
            },
            "value": {
                "E": "填入的值",
                "G": "G列的值"
            }
        }
    )
    handler.save("./test.xlsx")