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