import xlrd
import openpyxl
from xlutils.copy import copy
import os
from decimal import Decimal

class XLSHelper:
    def __init__(self, fpath=None):
        self.read_fpath = fpath
        self.read_book = None
        self.read_sheet = None
        self.write_path = fpath
        self.write_book = None
        self.write_sheet = None
    
    def __del__(self):
        del self.read_book
        del self.read_sheet
        del self.write_book
        del self.write_sheet
    
    def load_excel(self, fpath):
        self.read_fpath = fpath
        self.read_book = xlrd.open_workbook(self.read_fpath, formatting_info=True)
        self.write_book = copy(self.read_book)
        return self

    def load_sheet(self, index):
        if not self.read_book:
            raise ValueError("先调用load_excel")
        self.read_sheet = self.read_book.sheets()[index]
        self.write_sheet = self.write_book.get_sheet(index)
        return self
    
    def get_sheet_count(self):
        return len(self.read_book.sheets())
    
    def set_write_path(self, fpath):
        self.write_path = fpath
        return self
    
    def get_cell_value(self, row, column):
        if not self.read_sheet:
            raise ValueError('先调用load_sheet')
        return self.read_sheet.cell_value(row, column)

    #如果小数位只有1位 则把第二位小数的0删除, etc: 0.10 --> 0.1
    # thanks to: https://www.cnblogs.com/feifeifeisir/p/15246080.html
    def _remove_exponent(self, num):
        return num.to_integral() if num == num.to_integral() else num.normalize()
    
    def get_rows_values(self):
        row_count = self.read_sheet.nrows
        col_count = self.read_sheet.ncols
        rows_data = []
        for row in range(row_count):
            row_data = []
            for col in range(col_count):
                cell = self.read_sheet.cell(row, col)
                value = cell.value
                ctype = cell.ctype
                # 针对number特殊处理
                if ctype == xlrd.XL_CELL_NUMBER:
                    # print(type(value), value, str(value))
                    value = str(self._remove_exponent(Decimal(value).quantize(Decimal('.01'), rounding = 'ROUND_HALF_UP')))
                    # print(value)
                row_data.append(value)
            rows_data.append(row_data)
        return rows_data
        # rows_data = []
        # for i in range(row_count):
        #     row_data = self.read_sheet.row_values(i)
        #     rows_data.append(row_data)
        # return rows_data
    
    def write_cell_value(self, row, col, value):
        if not self.write_sheet:
            raise ValueError('请先逐次调用load_excel,load_sheet')
        self.write_sheet.write(row, col, value)
        self.write_book.save(self.write_path)
        return self


class XLSXHelper:
    def __init__(self, fpath=None):
        self.read_fpath = fpath
        self.read_book = None
        self.read_sheet = None
        self.write_fpath = fpath
    
    def __del__(self):
        del self.read_book
        del self.read_sheet
    
    def load_excel(self, fpath):
        self.read_fpath = fpath
        self.read_book = openpyxl.load_workbook(self.read_fpath)
        return self

    def load_sheet(self, index):
        if not self.read_book:
            raise ValueError("先调用load_excel")
        self.read_sheet = self.read_book.worksheets[index]
        return self
    
    def get_sheet_count(self):
        return len(self.read_book.worksheets)

    def set_write_path(self, fpath):
        self.write_fpath = fpath
        return self
    
    def get_cell_value(self, row, column):
        if not self.read_sheet:
            raise ValueError('先调用load_sheet')
        return self.read_sheet.cell(row=row+1, column=column+1).value
    
    def get_rows_values(self):
        rows = [row for row in self.read_sheet.rows]
        rows_raw = []
        for row in rows:
            row_raw = []
            for cell in row:
                value = cell.value
                row_raw.append(value)
            rows_raw.append(row_raw)
        return rows_raw

    def write_cell_value(self, row, col, value):
        self.read_sheet.cell(row+1, col+1).value = value
        self.read_book.save(self.write_fpath)
        return self



class ExcelHelper:
    def __init__(self):
        self.instance = None
        
    def load_excel(self, fpath):
        """根据文件格式初始化excel instance
        """
        if not os.path.exists(fpath):
            raise ValueError('excel文件不存在')
        if fpath.endswith('.xls'):
            self.instance = XLSHelper(fpath)
        elif fpath.endswith('.xlsx'):
            self.instance = XLSXHelper(fpath)
        else:
            raise ValueError('文件格式不正确')
        self.instance.load_excel(fpath)
        return self
    
    def load_sheet(self, index):
        """载入sheet
        """
        if not self.instance:
            raise ValueError('先调用load_excel')
        self.instance.load_sheet(index)
        return self
    
    def get_sheet_count(self):
        """ 计算表格有多少个sheet
        """
        if not self.instance:
            raise ValueError('先调用load_excel')
        return self.instance.get_sheet_count()

    def set_write_path(self, fpath):
        """ 设定输出excel文件路径,如果不设置,则追加在读取的excel文件内
        """
        self.instance.set_write_path(fpath)
        return self
    
    def get_cell_value(self, row, column):
        """ 获取某个单元格的值
        """
        if not self.instance:
            raise ValueError('先调用load_excel')
        return self.instance.get_cell_value(row, column)
    
    def get_rows_values(self):
        """ 获取整个sheet的内容,格式为二维数据
        example:
        [[1, 2, 3],
         [1, 2, 3],
         [1, 2, 3],
         ...
        ]
        """
        return self.instance.get_rows_values()
    
    def write_cell_value(self, row, col, value):
        """ 往某个单元格写入内容
        """
        self.instance.write_cell_value(row, col, value)
        return self