1""" 2 pyexcel.book 3 ~~~~~~~~~~~~~~~~~~~ 4 5 Excel book 6 7 :copyright: (c) 2014-2019 by Onni Software Ltd. 8 :license: New BSD License, see LICENSE for more details 9""" 10from pyexcel import _compact as compact 11from pyexcel.sheet import Sheet 12from pyexcel.internal.meta import BookMeta 13from pyexcel.internal.common import SheetIterator 14 15LOCAL_UUID = 0 16 17 18class Book(BookMeta): 19 """ 20 Read an excel book that has one or more sheets 21 22 For csv file, there will be just one sheet 23 """ 24 25 def __init__(self, sheets=None, filename="memory", path=None): 26 """ 27 Book constructor 28 29 Selecting a specific book according to filename extension 30 31 :param sheets: a dictionary of data 32 :param filename: the physical file 33 :param path: the relative path or absolute path 34 :param keywords: additional parameters to be passed on 35 """ 36 self.__path = None 37 self.__name_array = [] 38 self.filename = None 39 self.__sheets = compact.OrderedDict() 40 self.init(sheets=sheets, filename=filename, path=path) 41 42 def init(self, sheets=None, filename="memory", path=None): 43 """indpendent function so that it could be called multiple times""" 44 self.__path = path 45 self.filename = filename 46 self.load_from_sheets(sheets) 47 48 def load_from_sheets(self, sheets): 49 """ 50 Load content from existing sheets 51 52 :param dict sheets: a dictionary of sheets. Each sheet is 53 a list of lists 54 """ 55 if sheets is None: 56 return 57 keys = sheets.keys() 58 for name in keys: 59 value = sheets[name] 60 if isinstance(value, Sheet): 61 sheet = value 62 sheet.name = name 63 else: 64 # array 65 sheet = Sheet(value, name) 66 # this sheets keep sheet order 67 self.__sheets.update({name: sheet}) 68 # this provide the convenience of access the sheet 69 self.__dict__[name.replace(" ", "_")] = sheet 70 self.__name_array = list(self.__sheets.keys()) 71 72 def __iter__(self): 73 return SheetIterator(self) 74 75 def __len__(self): 76 return len(self.__name_array) 77 78 def sort_sheets(self, key=None, reverse=False): 79 self.__name_array = sorted(self.__name_array, key=key, reverse=reverse) 80 81 def number_of_sheets(self): 82 """ 83 Return the number of sheets 84 """ 85 return len(self.__name_array) 86 87 def sheet_names(self): 88 """ 89 Return all sheet names 90 """ 91 return self.__name_array 92 93 def sheet_by_name(self, name): 94 """ 95 Get the sheet with the specified name 96 """ 97 return self.__sheets[name] 98 99 def sheet_by_index(self, index): 100 """ 101 Get the sheet with the specified index 102 """ 103 if index < len(self.__name_array): 104 sheet_name = self.__name_array[index] 105 return self.sheet_by_name(sheet_name) 106 107 def remove_sheet(self, sheet): 108 """ 109 Remove a sheet 110 """ 111 if isinstance(sheet, int): 112 if sheet < len(self.__name_array): 113 sheet_name = self.__name_array[sheet] 114 del self.__sheets[sheet_name] 115 self.__name_array = list(self.__sheets.keys()) 116 else: 117 raise IndexError 118 elif isinstance(sheet, str): 119 if sheet in self.__name_array: 120 del self.__sheets[sheet] 121 self.__name_array = list(self.__sheets.keys()) 122 else: 123 raise KeyError 124 else: 125 raise TypeError 126 127 def __getitem__(self, key): 128 """Override operator[]""" 129 if isinstance(key, int): 130 return self.sheet_by_index(key) 131 else: 132 return self.sheet_by_name(key) 133 134 def __delitem__(self, other): 135 """ 136 Override del book[index] 137 """ 138 self.remove_sheet(other) 139 return self 140 141 def __add__(self, other): 142 """ 143 Override operator + 144 145 example:: 146 147 book3 = book1 + book2 148 book3 = book1 + book2["Sheet 1"] 149 150 """ 151 content = {} 152 current_dict = self.to_dict() 153 for k in current_dict.keys(): 154 new_key = k 155 if len(current_dict.keys()) == 1: 156 new_key = "%s_%s" % (self.filename, k) 157 content[new_key] = current_dict[k] 158 if isinstance(other, Book): 159 other_dict = other.to_dict() 160 for key in other_dict.keys(): 161 new_key = key 162 if len(other_dict.keys()) == 1: 163 new_key = other.filename 164 if new_key in content: 165 uid = local_uuid() 166 new_key = "%s_%s" % (key, uid) 167 content[new_key] = other_dict[key] 168 elif isinstance(other, Sheet): 169 new_key = other.name 170 if new_key in content: 171 uid = local_uuid() 172 new_key = "%s_%s" % (other.name, uid) 173 content[new_key] = other.array 174 else: 175 raise TypeError 176 output = Book() 177 output.load_from_sheets(content) 178 return output 179 180 def __iadd__(self, other): 181 """ 182 Operator overloading += 183 184 example:: 185 186 book += book2 187 book += book2["Sheet1"] 188 189 """ 190 if isinstance(other, Book): 191 names = other.sheet_names() 192 for name in names: 193 new_key = name 194 if len(names) == 1: 195 new_key = other.filename 196 if new_key in self.__name_array: 197 uid = local_uuid() 198 new_key = "%s_%s" % (name, uid) 199 self.__sheets[new_key] = Sheet(other[name].array, new_key) 200 elif isinstance(other, Sheet): 201 new_key = other.name 202 if new_key in self.__name_array: 203 uid = local_uuid() 204 new_key = "%s_%s" % (other.name, uid) 205 self.__sheets[new_key] = Sheet(other.array, new_key) 206 else: 207 raise TypeError 208 self.__name_array = list(self.__sheets.keys()) 209 return self 210 211 def to_dict(self): 212 """Convert the book to a dictionary""" 213 the_dict = compact.OrderedDict() 214 for sheet in self: 215 the_dict.update({sheet.name: sheet.array}) 216 return the_dict 217 218 219def to_book(bookstream): 220 """Convert a bookstream to Book""" 221 if isinstance(bookstream, Book): 222 return bookstream 223 else: 224 return Book( 225 bookstream.to_dict(), 226 filename=bookstream.filename, 227 path=bookstream.path, 228 ) 229 230 231def local_uuid(): 232 """create home made uuid""" 233 global LOCAL_UUID 234 LOCAL_UUID = LOCAL_UUID + 1 235 return LOCAL_UUID 236