<s> from tempfile import NamedTemporaryFile <EOL> import openpyxl <EOL> class JSONReaderError ( Exception ) : <EOL> pass <EOL> class HeaderValueError ( Exception ) : <EOL> pass <EOL> class IteratorJSONReader ( object ) : <EOL> """<STR_LIT>""" <EOL> def __init__ ( self , rows ) : <EOL> self . _rows = iter ( rows ) <EOL> try : <EOL> self . headers = list ( self . _rows . next ( ) ) <EOL> except StopIteration : <EOL> self . headers = [ ] <EOL> self . fieldnames = self . get_fieldnames ( ) <EOL> def row_to_json ( self , row ) : <EOL> obj = { } <EOL> for value , header in zip ( row , self . headers ) : <EOL> self . set_field_value ( obj , header , value ) <EOL> return obj <EOL> def __iter__ ( self ) : <EOL> try : <EOL> for row in self . _rows : <EOL> yield self . row_to_json ( row ) <EOL> finally : <EOL> del self . _rows <EOL> def get_fieldnames ( self ) : <EOL> obj = { } <EOL> for field , value in zip ( self . headers , [ '<STR_LIT>' ] * len ( self . headers ) ) : <EOL> if not isinstance ( field , basestring ) : <EOL> raise HeaderValueError ( u'<STR_LIT>' % field ) <EOL> self . set_field_value ( obj , field , value ) <EOL> return obj . keys ( ) <EOL> @ classmethod <EOL> def set_field_value ( cls , obj , field , value ) : <EOL> if isinstance ( value , basestring ) : <EOL> value = value . strip ( ) <EOL> try : <EOL> field , subfield = field . split ( '<STR_LIT::>' ) <EOL> except Exception : <EOL> pass <EOL> else : <EOL> field = field . strip ( ) <EOL> if field not in obj : <EOL> obj [ field ] = { } <EOL> cls . set_field_value ( obj [ field ] , subfield , value ) <EOL> return <EOL> try : <EOL> field , _ = field . split ( ) <EOL> except Exception : <EOL> pass <EOL> else : <EOL> dud = { } <EOL> cls . set_field_value ( dud , field , value ) <EOL> ( field , value ) , = dud . items ( ) <EOL> if field not in obj : <EOL> obj [ field ] = [ ] <EOL> elif not isinstance ( obj [ field ] , list ) : <EOL> obj [ field ] = [ obj [ field ] ] <EOL> if value not in ( None , '<STR_LIT>' ) : <EOL> obj [ field ] . append ( value ) <EOL> return <EOL> try : <EOL> field , nothing = field . split ( '<STR_LIT:?>' ) <EOL> assert ( nothing . strip ( ) == '<STR_LIT>' ) <EOL> except Exception : <EOL> pass <EOL> else : <EOL> try : <EOL> value = { <EOL> '<STR_LIT:yes>' : True , <EOL> '<STR_LIT:true>' : True , <EOL> '<STR_LIT>' : False , <EOL> '<STR_LIT:false>' : False , <EOL> '<STR_LIT>' : False , <EOL> None : False , <EOL> } [ value . lower ( ) if hasattr ( value , '<STR_LIT>' ) else value ] <EOL> except KeyError : <EOL> raise JSONReaderError ( <EOL> '<STR_LIT>' % ( <EOL> field , value ) <EOL> ) <EOL> field = field . strip ( ) <EOL> if field in obj : <EOL> raise JSONReaderError ( <EOL> '<STR_LIT>' % field <EOL> ) <EOL> obj [ field ] = value <EOL> class WorksheetNotFound ( Exception ) : <EOL> def __init__ ( self , title ) : <EOL> self . title = title <EOL> super ( WorksheetNotFound , self ) . __init__ ( ) <EOL> class WorksheetJSONReader ( IteratorJSONReader ) : <EOL> def __init__ ( self , worksheet , title = None ) : <EOL> width = <NUM_LIT:0> <EOL> self . title = title <EOL> self . worksheet = worksheet <EOL> try : <EOL> header_row = self . worksheet . iter_rows ( ) . next ( ) <EOL> except StopIteration : <EOL> header_row = [ ] <EOL> for cell in header_row : <EOL> if cell . value is None : <EOL> break <EOL> else : <EOL> width += <NUM_LIT:1> <EOL> self . worksheet . calculate_dimension ( force = True ) <EOL> def iterator ( ) : <EOL> def _convert_float ( value ) : <EOL> """<STR_LIT>""" <EOL> if isinstance ( value , float ) and int ( value ) == value : <EOL> return int ( value ) <EOL> else : <EOL> return value or '<STR_LIT>' <EOL> for row in self . worksheet . iter_rows ( ) : <EOL> cell_values = [ <EOL> _convert_float ( cell . value ) <EOL> for cell in row [ : width ] <EOL> ] <EOL> if not any ( cell_values ) : <EOL> break <EOL> yield cell_values <EOL> super ( WorksheetJSONReader , self ) . __init__ ( iterator ( ) ) <EOL> class WorkbookJSONReader ( object ) : <EOL> def __init__ ( self , f ) : <EOL> if isinstance ( f , basestring ) : <EOL> filename = f <EOL> elif not isinstance ( f , file ) : <EOL> tmp = NamedTemporaryFile ( mode = '<STR_LIT:wb>' , suffix = '<STR_LIT>' , delete = False ) <EOL> filename = tmp . name <EOL> tmp . write ( f . read ( ) ) <EOL> tmp . close ( ) <EOL> else : <EOL> filename = f <EOL> self . wb = openpyxl . load_workbook ( filename , use_iterators = True ) <EOL> self . worksheets_by_title = { } <EOL> self . worksheets = [ ] <EOL> for worksheet in self . wb . worksheets : <EOL> ws = WorksheetJSONReader ( worksheet , title = worksheet . title ) <EOL> self . worksheets_by_title [ worksheet . title ] = ws <EOL> self . worksheets . append ( ws ) <EOL> def get_worksheet ( self , title = None , index = None ) : <EOL> if title is not None and index is not None : <EOL> raise TypeError ( "<STR_LIT>" ) <EOL> if title : <EOL> try : <EOL> return self . worksheets_by_title [ title ] <EOL> except KeyError : <EOL> raise WorksheetNotFound ( title = title ) <EOL> elif index : <EOL> try : <EOL> return self . worksheets [ index ] <EOL> except IndexError : <EOL> raise WorksheetNotFound ( title = index ) <EOL> else : <EOL> try : <EOL> return self . worksheets [ <NUM_LIT:0> ] <EOL> except IndexError : <EOL> raise WorksheetNotFound ( title = <NUM_LIT:0> ) <EOL> def work_book_headers_as_tuples ( self ) : <EOL> """<STR_LIT>""" <EOL> all_sheet_headers = [ ] <EOL> for sheet in self . worksheets : <EOL> all_sheet_headers . append ( <EOL> ( sheet . title , tuple ( sheet . headers ) ) <EOL> ) <EOL> return tuple ( all_sheet_headers ) <EOL> def work_book_data_as_tuples ( self ) : <EOL> """<STR_LIT>""" <EOL> all_sheet_data = [ ] <EOL> for sheet in self . worksheets : <EOL> current_sheet_data = [ ] <EOL> for row in sheet : <EOL> values = [ row . get ( header ) for header in sheet . headers ] <EOL> current_sheet_data . append ( tuple ( values ) ) <EOL> all_sheet_data . append ( <EOL> ( sheet . title , tuple ( current_sheet_data ) ) <EOL> ) <EOL> return tuple ( all_sheet_data ) <EOL> def flatten_json_to_path ( obj , path = ( ) ) : <EOL> if isinstance ( obj , dict ) : <EOL> for key , value in obj . items ( ) : <EOL> for item in flatten_json_to_path ( value , path + ( key , ) ) : <EOL> yield item <EOL> elif isinstance ( obj , list ) : <EOL> for key , value in enumerate ( obj ) : <EOL> for item in flatten_json_to_path ( value , path + ( key , ) ) : <EOL> yield item <EOL> else : <EOL> yield ( path , obj ) <EOL> def format_header ( path , value ) : <EOL> s = path [ <NUM_LIT:0> ] <EOL> for p in path [ <NUM_LIT:1> : ] : <EOL> if isinstance ( p , basestring ) : <EOL> s += '<STR_LIT>' % p <EOL> elif isinstance ( p , int ) : <EOL> s += '<STR_LIT>' % ( p + <NUM_LIT:1> ) <EOL> if isinstance ( value , bool ) : <EOL> s += '<STR_LIT:?>' <EOL> value = '<STR_LIT:yes>' if value else '<STR_LIT>' <EOL> return s , value <EOL> def flatten_json ( obj ) : <EOL> for key , value in flatten_json_to_path ( obj ) : <EOL> yield format_header ( key , value ) <EOL> def json_to_headers ( obj ) : <EOL> return [ key for key , value in sorted ( flatten_json ( obj ) , key = lambda t : alphanumeric_sort_key ( t [ <NUM_LIT:0> ] ) ) ] <EOL> def alphanumeric_sort_key ( key ) : <EOL> """<STR_LIT>""" <EOL> import re <EOL> convert = lambda text : int ( text ) if text . isdigit ( ) else text <EOL> return [ convert ( c ) for c in re . split ( '<STR_LIT>' , key ) ] </s>
