PK!0WCCpyexcel_export/__init__.pyfrom .app import ExcelLoader from .defaults import Meta def get_data(in_file, **flags): """ :param str|Path in_file: supported file formats are *.xlsx, *.yaml, *.json and *.pyexcel.json :param flags: currently supported flags are 'has_header', 'freeze_header', 'col_width_fit_param_keys', 'col_width_fit_ids', 'bool_as_string', 'allow_table_hiding' :return: """ loader = ExcelLoader(in_file, **flags) return loader.data, loader.meta def get_meta(in_file=None, **flags): """ :param str|Path in_file: :param flags: :return: """ if in_file: loader = ExcelLoader(in_file, **flags) return loader.meta else: return Meta(**flags) def save_data(out_file, data, meta=None, retain_meta=False, retain_styles=False, **flags): """ :param str|Path out_file: supported file formats are *.xlsx, *.json and *.pyexcel.json :param data: :param meta: :param retain_styles: whether you want to retain the overwritten worksheet's formatting :param retain_meta: whether you want to write _meta to your file :param flags: :return: """ loader = ExcelLoader(**flags) if meta is not None: loader.meta = meta loader.data = data loader.save(out_file, retain_styles=retain_styles, retain_meta=retain_meta) PK!,cpyexcel_export/app.pyimport pyexcel from collections import OrderedDict from datetime import datetime import json import copy import oyaml as yaml import logging from pathlib import Path import re from .serialize import RowExport, PyexcelExportEncoder, MyEncoder from .yaml_serialize import PyExcelYamlLoader from .defaults import Meta from .formatter import ExcelFormatter debugger_logger = logging.getLogger('debug') class ExcelLoader: def __init__(self, in_file=None, **flags): """ :param str|Path|None in_file: :param flags: """ if in_file: if not isinstance(in_file, Path): in_file = Path(in_file) self.in_file = in_file self.meta = Meta(**flags) in_format = in_file.suffixes if in_format[-1] == '.xlsx': self.data = self._load_pyexcel_xlsx() elif in_format[-1] == '.json': if in_format[-2] == '.pyexcel': self.data = self._load_pyexcel_json() else: self.data = self._load_json() elif in_format[-1] in ('.yaml', '.yml'): self.data = self._load_yaml() else: raise ValueError('Unsupported file format, {}.'.format(in_format)) else: self.meta = Meta(**flags) def _load_pyexcel_xlsx(self): updated_data = pyexcel.get_book_dict(file_name=str(self.in_file.absolute())) self.meta['_styles'] = ExcelFormatter(self.in_file).data return self._set_updated_data(updated_data) def _load_pyexcel_json(self): with open(str(self.in_file.absolute())) as f: data = json.load(f, object_pairs_hook=OrderedDict) for sheet_name, sheet_data in data.items(): for j, row in enumerate(sheet_data): for i, cell in enumerate(row): data[sheet_name][j][i] = json.loads(cell) return self._set_updated_data(data) def _load_json(self): with open(str(self.in_file.absolute())) as f: data = json.load(f, object_pairs_hook=OrderedDict) return self._set_updated_data(data) def _load_yaml(self): with open(str(self.in_file.absolute())) as f: data = yaml.load(f, Loader=PyExcelYamlLoader) return self._set_updated_data(data) def _set_updated_data(self, updated_data): data = OrderedDict() if '_meta' in updated_data.keys(): for row in updated_data['_meta']: if not row or not row[0]: break if len(row) < 2: updated_meta_value = None else: try: updated_meta_value = list(json.loads(row[1]).values())[0] except (json.decoder.JSONDecodeError, TypeError, AttributeError): updated_meta_value = row[1] self.meta[row[0]] = updated_meta_value try: self.meta.move_to_end('modified', last=False) self.meta.move_to_end('created', last=False) except KeyError as e: debugger_logger.debug(e) updated_data.pop('_meta') for k, v in updated_data.items(): data[k] = v return data @property def formatted_object(self): formatted_object = OrderedDict( _meta=self.meta.matrix ) for sheet_name, sheet_data in self.data.items(): formatted_sheet_object = [] for row in sheet_data: formatted_sheet_object.append(RowExport(row)) formatted_object[sheet_name] = formatted_sheet_object return formatted_object def save(self, out_file: str, retain_meta=True, out_format=None, retain_styles=True): """ :param str|Path out_file: :param retain_meta: :param out_format: :param retain_styles: :return: """ if not isinstance(out_file, Path): out_file = Path(out_file) self.meta['modified'] = datetime.fromtimestamp(datetime.now().timestamp()).isoformat() self.meta.move_to_end('modified', last=False) if 'created' in self.meta.keys(): self.meta.move_to_end('created', last=False) if out_format is None: out_format = out_file.suffixes else: out_format = re.findall('\.[^.]+', out_format) save_data = copy.deepcopy(self.data) if retain_meta: save_data['_meta'] = self.meta.matrix if not retain_styles: for i, row in enumerate(save_data['_meta']): if row[0] == '_styles': save_data['_meta'].pop(i) break save_data.move_to_end('_meta', last=False) else: if '_meta' in save_data.keys(): save_data.pop('_meta') to_remove = [] for sheet_name, sheet_matrix in save_data.items(): if sheet_name == '_meta' or not sheet_name.startswith('_'): for i, row in enumerate(sheet_matrix): if out_format == '.json': save_data[sheet_name][i] = RowExport(row) else: to_remove.append(sheet_name) for sheet_name in to_remove: save_data.pop(sheet_name) if out_format[-1] == '.xlsx': self._save_openpyxl(out_file=out_file, out_data=save_data, retain_meta=retain_meta) elif out_format[-1] == '.json': if out_format[-2] == '.pyexcel': self._save_pyexcel_json(out_file=out_file, out_data=save_data) else: self._save_json(out_file=out_file, out_data=save_data) elif out_format[-1] in ('.yaml', '.yml'): self._save_yaml(out_file=out_file, out_data=save_data) else: raise ValueError('Unsupported file format, {}.'.format(out_file)) def _save_openpyxl(self, out_file, out_data: OrderedDict, retain_meta: bool=True): """ :param str|Path out_file: :param out_data: :param retain_meta: :return: """ if not isinstance(out_file, Path): out_file = Path(out_file) formatter = ExcelFormatter(out_file) if out_file.exists(): self.meta['_styles'] = formatter.data formatter.save(out_data, out_file, meta=self.meta, retain_meta=retain_meta) @staticmethod def _save_pyexcel_json(out_file, out_data: OrderedDict): """ :param str|Path out_file: :param out_data: :return: """ if not isinstance(out_file, Path): out_file = Path(out_file) with open(str(out_file.absolute()), 'w') as f: export_string = json.dumps(out_data, cls=PyexcelExportEncoder, indent=2, ensure_ascii=False) f.write(export_string) @staticmethod def _save_json(out_file, out_data: OrderedDict): """ :param str|Path out_file: :param out_data: :return: """ if not isinstance(out_file, Path): out_file = Path(out_file) with open(str(out_file.absolute()), 'w') as f: export_string = json.dumps(out_data, cls=MyEncoder, indent=2, ensure_ascii=False) f.write(export_string) @staticmethod def _save_yaml(out_file, out_data: OrderedDict): """ :param str|Path out_file: :param out_data: :return: """ if not isinstance(out_file, Path): out_file = Path(out_file) with open(str(out_file.absolute()), 'w') as f: yaml.dump(out_data, f, allow_unicode=True) PK!* pyexcel_export/defaults.pyfrom datetime import datetime from collections import OrderedDict from io import BytesIO import base64 import binascii class Meta(OrderedDict): def __init__(self, **kwargs): default = OrderedDict([ ('created', datetime.fromtimestamp(datetime.now().timestamp()).isoformat()), ('has_header', True), ('freeze_header', True), ('col_width_fit_param_keys', True), ('col_width_fit_ids', True), ('bool_as_string', True), ('allow_table_hiding', True), ]) default.update(**kwargs) super().__init__(**default) @property def excel_matrix(self): result = OrderedDict(self) for k, v in self.items(): assigned = False if self.get('bool_as_string', False): if v is True: result[k] = 'true' assigned = True elif v is False: result[k] = 'false' assigned = True if not assigned: if type(v) not in (int, float, str, bool): result[k] = {str(type(v)): v} return list(result.items()) @property def view(self): result = OrderedDict(self) for k, v in self.items(): if type(v) not in (int, float, str, bool, BytesIO): result[k] = {str(type(v)): v} return result @property def matrix(self): return [list(k_v_pair) for k_v_pair in self.view.items()] def __setitem__(self, key, item): if self.get('bool_as_string', False): if item in ('true', '\'true'): item = True elif item in ('false', '\'false'): item = False if isinstance(item, dict) and len(item) == 1: k, v = list(item.items())[0] if k == "": item = v elif k == "": if isinstance(v, BytesIO): item = v else: item = BytesIO(base64.b64decode(v)) if isinstance(item, str): try: item = BytesIO(base64.b64decode(item)) except binascii.Error: pass super().__setitem__(key, item) def __repr__(self): output = [] for k, v in self.items(): output.append('(\'{}\', {})'.format(k, repr(v))) return 'Meta([\n {}\n])'.format(',\n '.join(output)) PK!̙pyexcel_export/formatter.pyimport openpyxl from openpyxl.utils import get_column_letter from openpyxl.worksheet.copier import WorksheetCopy import logging import json from io import BytesIO from collections import OrderedDict from pathlib import Path from .serialize import MyEncoder from .defaults import Meta debug_logger = logging.getLogger('debug') class ExcelFormatter: def __init__(self, template_file=None): """ :param str|Path|None template_file: """ is_read = False if template_file: if not isinstance(template_file, Path): template_file = Path(template_file) if template_file.exists(): self.styled_wb = openpyxl.load_workbook(str(template_file.absolute())) self.to_stylesheets(self.styled_wb) is_read = True if not is_read: self.styled_wb = openpyxl.Workbook() self.styled_wb.active.title = '_template' @property def data(self): output = BytesIO() self.styled_wb.save(output) return output @data.setter def data(self, _styles): assert isinstance(_styles, BytesIO) self.styled_wb = openpyxl.load_workbook(_styles) def save(self, raw_data, out_file, meta=None, retain_meta=True): """ :param raw_data: :param str|Path out_file: :param meta: :param retain_meta: :return: """ if not isinstance(out_file, Path): out_file = Path(out_file) if not meta: meta = Meta() if '_styles' in meta.keys(): self.data = meta['_styles'] meta['_styles'] = self.data if out_file.exists(): wb = openpyxl.load_workbook(str(out_file.absolute())) self.to_stylesheets(wb) self.append_styled_sheets(wb) original_sheet_names = [] extraneous_sheet_names = [] else: wb = self.styled_wb original_sheet_names = wb.sheetnames extraneous_sheet_names = [] extraneous_sheet_names.append('_template') inserted_sheets = [] if not retain_meta: extraneous_sheet_names.append('_meta') else: if '_meta' in original_sheet_names: wb.remove(wb['_meta']) self.create_styled_sheet(wb, '_meta', 0) meta_matrix = [] for k, v in meta.excel_matrix: if not k.startswith('_'): if isinstance(v, (dict, OrderedDict)): v = json.dumps(v, cls=MyEncoder) meta_matrix.append([k, v]) self.fill_matrix(wb['_meta'], meta_matrix, rules=meta) if '_meta' in raw_data.keys(): raw_data.pop('_meta') for sheet_name, cell_matrix in raw_data.items(): if sheet_name not in original_sheet_names: self.create_styled_sheet(wb, sheet_name) inserted_sheets.append(sheet_name) else: if meta.get('allow_table_hiding', True) in (True, 'true'): if not sheet_name.startswith('_'): self.fill_matrix(wb[sheet_name], cell_matrix, rules=meta) else: for i, cell in enumerate(next(wb['_meta'].iter_cols())): if not cell.value: matrix = [[sheet_name]] matrix.extend(cell_matrix) self.fill_matrix(wb['_meta'], matrix, start_row=i+1, rules=meta) break else: self.fill_matrix(wb[sheet_name], cell_matrix, rules=meta) ws = wb[sheet_name] for row_num, row in enumerate(cell_matrix): for col_num, value in enumerate(row): if isinstance(value, (dict, OrderedDict)): value = json.dumps(value, cls=MyEncoder) ws.cell(column=(col_num + 1), row=(row_num + 1), value=value) for sheet_name in extraneous_sheet_names: if sheet_name in wb.sheetnames: wb.remove(wb[sheet_name]) for sheet_name in wb.sheetnames: if (sheet_name.startswith('_') and sheet_name != '_meta') or self.is_empty_sheet(wb[sheet_name]): wb.remove(wb[sheet_name]) wb.save(str(out_file.absolute())) @staticmethod def create_styled_sheet(wb, sheet_name, pos: int=None): wb.create_sheet(sheet_name, pos) if '_template' in wb.sheetnames and sheet_name != '_template': WorksheetCopy(wb['_template'], wb[sheet_name]).copy_worksheet() # wb[sheet_name].copy_worksheet(wb['_template']) def append_styled_sheets(self, wb): if '_template' not in wb.sheetnames: wb.create_sheet('_template') if '_template' in self.styled_wb.sheetnames: WorksheetCopy(self.styled_wb['_template'], wb['_template']).copy_worksheet() # wb['_template'].copy_worksheet(wb['_template']) return wb @staticmethod def to_stylesheets(wb): for ws in wb: for row in ws: for cell in row: cell.value = None return wb @staticmethod def fill_matrix(ws, cell_matrix, start_row=0, rules=None): for row_num, row in enumerate(cell_matrix): for col_num, value in enumerate(row): if isinstance(value, (dict, OrderedDict)): value = json.dumps(value, cls=MyEncoder) ws.cell(column=(col_num + 1), row=(row_num + start_row + 1), value=value) if rules is not None: if rules.get('has_header', False) in (True, 'true') \ and rules.get('freeze_header', False) in (True, 'true'): if ws.title != '_meta': ws.freeze_panes = 'A2' if rules.get('col_width_fit_param_keys', False) in (True, 'true'): width = max([len(str(cell.value)) for cell in next(ws.iter_cols())]) ws.column_dimensions['A'].width = width + 2 if rules.get('col_width_fit_ids', False) in (True, 'true'): for i, header_cell in enumerate(next(ws.iter_rows())): header_item = header_cell.value if header_item and header_item.endswith('id'): col_letter = get_column_letter(i + 1) width = max([len(str(cell.value)) for cell in list(ws.iter_cols())[i]]) ws.column_dimensions[col_letter].width = width + 2 @staticmethod def is_empty_sheet(ws): def is_not_empty(): for row in ws.iter_rows(): for cell in row: if cell.value: return True return False return not is_not_empty() PK!spyexcel_export/serialize.pyimport uuid import json import base64 from io import BytesIO class RowExport(object): def __init__(self, raw_row): self.value = [] for raw_cell in raw_row: self.value.append(json.dumps(raw_cell, ensure_ascii=False, cls=MyEncoder)) def __repr__(self): if not isinstance(self.value, list): return repr(self.value) else: # Sort the representation of any dicts in the list. reps = ('{{{}}}'.format(', '.join( ('{!r}:{}'.format(k, v) for k, v in sorted(v.items())) )) if isinstance(v, dict) else repr(v) for v in self.value) return '[' + ', '.join(reps) + ']' @property def data(self): raw_row = [] for cell in self.value: raw_row.append(json.loads(cell)) return raw_row class PyexcelExportEncoder(json.JSONEncoder): def __init__(self, *args, **kwargs): super(PyexcelExportEncoder, self).__init__(*args, **kwargs) self.kwargs = dict(kwargs) del self.kwargs['indent'] self._replacement_map = {} def default(self, o): if isinstance(o, RowExport): key = uuid.uuid4().hex self._replacement_map[key] = json.dumps(o.value, **self.kwargs) return "@@%s@@" % (key,) elif isinstance(o, BytesIO): return base64.b64encode(o.getvalue()).decode() else: return super(PyexcelExportEncoder, self).default(o) def encode(self, o): result = super(PyexcelExportEncoder, self).encode(o) for k, v in self._replacement_map.items(): result = result.replace('"@@%s@@"' % (k,), v) return result class MyEncoder(json.JSONEncoder): def default(self, o): if isinstance(o, RowExport): return o.data elif isinstance(o, BytesIO): return base64.b64encode(o.getvalue()).decode() return json.JSONEncoder.default(self, o) PK!O pyexcel_export/yaml_serialize.pyimport yaml from io import BytesIO import base64 class PyExcelYamlLoader(yaml.SafeLoader): def __init__(self, s): super().__init__(s) self.add_constructor(u'tag:yaml.org,2002:python/object/new:_io.BytesIO', self.construct_bytes_io) @staticmethod def construct_bytes_io(node, node_value): return BytesIO(base64.b64decode(node_value.value[0][1].value[0].value)) PK! ::'pyexcel_export-0.2.11.dist-info/LICENSEMIT License Copyright (c) 2018 Pacharapol Withayasakpunt Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. PK!H_zTT%pyexcel_export-0.2.11.dist-info/WHEEL A н#Z;/"d&F[xzw@Zpy3Fv]n0H*J>mlcAPK!H87H q(pyexcel_export-0.2.11.dist-info/METADATAXIoWԨHPI-;%7c D,Un4$9CN\̜ yUM=rIm{Jω>3pʢ!r-xC2Dx$h%(8M'!:I'A3|4eI ]I| }lƄ G><~Y> fY',$13*X؎kQDO%>"1YLxq)1R1g Ux9Hd 1 >i)EJ! `!Zg< 0tJ 쟙Cq®/|yI$a3Rk%sWQYbcG;\)r¶zm޷Td^DŽ{ 0EDu8܈60gבa[BO #ACl=hTka>=LiSE9E(oyblzK_LysA-8.coJN'J4sJ[pf6RQ-1VKev<,߭1!5Ƌa[i!+at IIpR{ S]mqz-4 bmdZ(/RnB$.P^CS8!<'U7X \oZ7Q8!1ԟDNi@5R|E@`ek-X8 ~5C)]B/Fl\~9T$p~G>c>IppK4`*^uzM e&p` >2^)^-OK tdYdB/I gLk% 4 r=4HCaJpkeC&,2$`FzG8 -Km"Z"B"ԧOO8 xrītbE8/D.rRZꛄAb8WG uQ)SN6?q%sʪ܆xAJEW8H 73Mp+&[aHY1M rB: 1yOsy Icy"ρ; KtE1i<4 jjG)5 H'1ilBSap!KP˄i >2 R=l, э8<<|]nߌK, 2'c &$G`tPШ hLԛC6-Q`ZVFeHS.=SןD|<0Z>}7T>>e x䜮3w aIȋ_Vo\0+rA2b3!PK_'ok&@)F5Y748ț:,5cWR)ٜuʰxdf}~yOyD4CLO\]\g.&3Brbr>5B- g7u?8kxWU΃6.+ 5Iس 79s3'wCoa%7eu;Dwncq=iݶMCudMxIiJ!< vN1:BƷnȶC (&'^c\BKmgA=ыNPSmHTұi8!hz PK!0WCCpyexcel_export/__init__.pyPK!,c{pyexcel_export/app.pyPK!* $pyexcel_export/defaults.pyPK!̙.pyexcel_export/formatter.pyPK!sJpyexcel_export/serialize.pyPK!O Rpyexcel_export/yaml_serialize.pyPK! ::'Tpyexcel_export-0.2.11.dist-info/LICENSEPK!H_zTT%?Ypyexcel_export-0.2.11.dist-info/WHEELPK!H87H q(Ypyexcel_export-0.2.11.dist-info/METADATAPK!H6Zk :&(cpyexcel_export-0.2.11.dist-info/RECORDPK We