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!E((pyexcel_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 and in_format[-1] == '.xlsx': self.data = self._load_pyexcel_xlsx() elif in_format[-1] == '.json': if len(in_format) > 1 and 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.setdefault('_styles', dict())['excel'] = ExcelFormatter(self.in_file).data return self._set_updated_data(updated_data) def _load_pyexcel_json(self): with self.in_file.open(encoding='utf-8') 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 self.in_file.open(encoding='utf8') as f: data = json.load(f, object_pairs_hook=OrderedDict) return self._set_updated_data(data) def _load_yaml(self): with self.in_file.open(encoding='utf8') 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 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 len(out_format) > 1 and 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.setdefault('_styles', dict())['excel'] = 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: """ for k, v in out_data.items(): for i, row in enumerate(v): out_data[k][i] = RowExport(row) if not isinstance(out_file, Path): out_file = Path(out_file) with out_file.open('w', encoding='utf8') 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 out_file.open('w', encoding='utf8') 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 out_file.open('w', encoding='utf8') as f: yaml.dump(out_data, f, allow_unicode=True) PK!Id d 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), ('minimum_col_width', 20), ('wrap_text', True), ('align_top', 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!Vv+$$pyexcel_export/formatter.pyimport openpyxl from openpyxl.utils import get_column_letter from openpyxl.worksheet.copier import WorksheetCopy from openpyxl.styles import Alignment import logging import json from io import BytesIO from collections import OrderedDict from pathlib import Path import math import base64 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): if isinstance(_styles, (dict, OrderedDict)): _styles = _styles['excel'] if not isinstance(_styles, BytesIO): _styles = BytesIO(base64.b64decode(_styles)) 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(): if isinstance(meta['_styles'], (dict, OrderedDict)): if 'excel' in meta['_styles'].keys(): self.data = meta['_styles']['excel'] meta['_styles']['excel'] = self.data else: 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) self.fill_matrix(ws, cell_matrix, rules=meta) 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'): if ws.title == '_meta': 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 str(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 if ws.title != '_meta': col_width = [] for i in range(len(next(ws.iter_rows()))): col_letter = get_column_letter(i + 1) minimum_width = rules.get('minimum_col_width', 20) current_width = ws.column_dimensions[col_letter].width if not current_width or current_width < minimum_width: ws.column_dimensions[col_letter].width = minimum_width col_width.append(ws.column_dimensions[col_letter].width) for i, row in enumerate(ws): default_height = 12.5 multiples_of_font_size = [default_height] for j, cell in enumerate(row): wrap_text = False vertical = None if rules.get('wrap_text', True): wrap_text = True if cell.value is not None: mul = 0 for v in str(cell.value).split('\n'): mul += math.ceil(len(v) / col_width[j]) * cell.font.size if mul > 0: multiples_of_font_size.append(mul) if rules.get('align_top', True): vertical = "top" cell.alignment = Alignment(wrap_text=wrap_text, vertical=vertical) original_height = ws.row_dimensions[i+1].height if original_height is None: original_height = default_height new_height = max(multiples_of_font_size) if original_height < new_height: ws.row_dimensions[i + 1].height = new_height @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.3.1.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.3.1.dist-info/WHEEL A н#Z;/"d&F[xzw@Zpy3Fv]n0H*J>mlcAPK!H< = 'pyexcel_export-0.3.1.dist-info/METADATAYoh *h˒mal˱$Hr܅ְGH,a9em@s)zis/ MNAd͈"寬@ y<Ξ:4PrI*m: f DKU~y4K2!cz4?!)fHZ71wN=P߁Oգ0"8LFQ „Gxd+@@-CO| \g"td(S7`2GSIS-*: b?ZPl0rBT EF"!9Bv+V8p|_FuH#Bq|cn(&!< '1|B޲Xi\Uр2;M\&f)v&|&FaYn\&C3בXMtDy׉4BK *uTg*wu=15q)ݏݸg. %3LBBCCC.hRޫ߼$W!<0z0 )߈G~"E|ce92LNFj́.Ga2 \QSF2;Q@6Ȉfl91&gܕ2^iweB>}-߁m0 Tp슙.BSVʥN\ٮVL1.H219FzPق,B) cSҤVoלuøz>3~p!a@lH9C؄Gڎ3 #a'BF r1)gƞ4#aXA*yFzR^"H9[R τE8tX)paNΜCnGLHNDRZIuҏ6{=z͞>heFHH' P醥?&?՗T Jy8F$~ FMRx-_^b!'kg Nơd\p %STO'Bi]!DÝ?!!ver n 7 u窲P76pQ{R&w6jŪV6K&M$x?WJ.P=HŖ[2K61Vy,r$j 7ǡ.aWn z7r7r| %s1)=:ߝ[pZշg@S@rB׹ _簔_P+JzZ| sɖVekUWRjNDHv<,gɀJr^ _zla)&/ 9  ½ O6еS!>yпkHeH)cH{!՚eJ0v`~wIUZ]z\z.NќLBf%bn&pjEPNb!QDUwkPFx)8aR2asu r㳸]/_pJèkp_r3 _n[#n[Ŷ=Ѐ*1h'X˔[A<ҽ(zG(qQTݽDH$Px(_Sh 0"DWfS8W<ǡ*# dJ`EXxk#q͔/arj0AŖU܈-uOq'D2srq;GFIZ}u>i6Iчwykp95ڃWsDWe}ϻfO:=u=i5a>89;l?&[j:TjQiwp Ik4Z62 1d?PK!HL6%pyexcel_export-0.3.1.dist-info/RECORDrk@~ b-lPDBHeGMH>u&7i&z?Ғ{.zBuy&pvc2ͳ)ZfA?3ۂezl$4Y5KۍgOwyu7ByԘ=SV#ټ|-%FjXM/h|hbOjσ_EE}bcUt5¬kV|7@497vs;cx>E+AK8< yis]ȩ7[Է>+v"Zjhůp/"@vvmY+kƩ,B^PK!0WCCpyexcel_export/__init__.pyPK!E(({pyexcel_export/app.pyPK!Id d #pyexcel_export/defaults.pyPK!Vv+$$r.pyexcel_export/formatter.pyPK!sSpyexcel_export/serialize.pyPK!O [pyexcel_export/yaml_serialize.pyPK! ::&]pyexcel_export-0.3.1.dist-info/LICENSEPK!H_zTT$bpyexcel_export-0.3.1.dist-info/WHEELPK!H< = 'bpyexcel_export-0.3.1.dist-info/METADATAPK!HL6%npyexcel_export-0.3.1.dist-info/RECORDPK p