def xls_write_workbook(file, data, request=None, image_keys=None,
title_function=None, list_brackets=None):
'''
***WARNING*** xlsx files load fully into memory on display - if there are
>~ 2000 images, this will cause performance issues on the client.***
@param sheet_rows iterable of dicts, one per row
'''
if not isinstance(data, dict):
raise BadRequest(
'unknown data for generic xls serialization: %r' % type(data))
wb = xlsxwriter.Workbook(file, {'constant_memory': True})
logger.info('xls_write_workbook for data: %r', data.keys())
for key, sheet_rows in data.items():
logger.info('type sheet_rows: %r', type(sheet_rows))
if isinstance(sheet_rows, (dict, OrderedDict)):
sheet_name = default_converter(key)
logger.info('writing sheet %r...', sheet_name)
sheet = wb.add_worksheet(sheet_name)
for i, row in enumerate(csvutils.dict_to_rows(sheet_rows)):
sheet.write_row(i,0,row)
elif isinstance(sheet_rows, basestring):
sheet_name = default_converter(key)
logger.info('writing single string sheet %r...', sheet_name)
sheet = wb.add_worksheet(sheet_name)
sheet.write_string(0,0,sheet_rows)
else:
sheet_name = default_converter(key)
logger.info('writing sheets for base name %r...', sheet_name)
write_rows_to_sheet(wb, sheet_rows, sheet_name, request=request,
image_keys=image_keys, title_function=title_function,
list_brackets=list_brackets)
wb.close()
def to_html(self, data, options=None):
''' For error reporting '''
if isinstance(data, six.string_types):
return data
else:
return '<br>'.join([str(row) for row in dict_to_rows(data)])
def generic_xls_write_workbook(file, data):
'''
Writes a dict of iterables to a workbook,
where key=sheet name, value=iterable that is ready to write (e.g. a list)
'''
wb = xlsxwriter.Workbook(file, {'constant_memory': True})
if isinstance(data, dict):
logger.info('generic_xls_write_workbook for data: %r', data.keys())
for key, sheet_rows in data.items():
sheet_name = default_converter(key)
logger.info('writing sheet %r...', sheet_name)
sheet = wb.add_worksheet(sheet_name)
if isinstance(sheet_rows, dict):
for i, row in enumerate(csvutils.dict_to_rows(sheet_rows)):
sheet.write_row(i,0,row)
elif isinstance(sheet_rows, basestring):
sheet.write_string(0,0,sheet_rows)
else:
generic_write_rows_to_sheet(sheet_rows, sheet)
else:
raise BadRequest(
'unknown data for generic xls serialization: %r' % type(data))
logger.info('save to file; %r', file.name)
wb.close()
def to_html(self, data, options=None):
''' For error reporting '''
if isinstance(data, six.string_types):
return data
else:
return '<br>'.join([str(row) for row in dict_to_rows(data)])
def generic_xls_write_workbook(file, data):
'''
Writes a dict of iterables to a workbook,
where key=sheet name, value=iterable that is ready to write (e.g. a list)
'''
wb = xlsxwriter.Workbook(file, {'constant_memory': True})
if isinstance(data, dict):
logger.info('generic_xls_write_workbook for data: %r', data.keys())
for key, sheet_rows in data.items():
sheet_name = default_converter(key)
logger.info('writing sheet %r...', sheet_name)
sheet = wb.add_worksheet(sheet_name)
if isinstance(sheet_rows, dict):
for i, row in enumerate(csvutils.dict_to_rows(sheet_rows)):
sheet.write_row(i,0,row)
elif isinstance(sheet_rows, basestring):
sheet.write_string(0,0,sheet_rows)
else:
generic_write_rows_to_sheet(sheet_rows, sheet)
else:
raise Exception(
'unknown data for generic xls serialization: %r' % type(data))
logger.info('save to file; %r', file.name)
wb.close()
def to_csv(self, data, root='objects', options=None):
'''
Note: csv.py doesn't do Unicode; encode values as UTF-8 byte strings
'''
# Note: cStringIO ok because csv writer will write only bytes
raw_data = cStringIO.StringIO()
# raw_data = StringIO.StringIO()
writer = unicodecsv.writer(raw_data)
if 'error' in data:
for row in dict_to_rows(data['error']):
writer.writerow(row)
# writer.writerow(['error'])
# writer.writerow([data['error']])
# logger.warn(str(('error', data)))
return raw_data.getvalue()
if 'objects' in data:
data = data['objects']
if isinstance(data, dict):
# usually, this happens when the data is actually an error message;
# but also, it could be just one item being returned
data = dict_to_rows(data)
for item in data:
writer.writerow(item)
else:
i = 0
keys = None
for item in data:
### Removed, handled in convert_list_vals,
### data = to_simple(data)
if i == 0:
keys = item.keys()
# writer.writerow([smart_text(key).encode('utf-8') for key in keys])
writer.writerow([smart_text(key) for key in keys])
i += 1
writer.writerow(
[csvutils.convert_list_vals(val) for val in item.values()])
return raw_data.getvalue()
def to_csv(self, data, root='objects', options=None):
'''
Note: csv.py doesn't do Unicode; encode values as UTF-8 byte strings
'''
# Note: cStringIO ok because csv writer will write only bytes
raw_data = cStringIO.StringIO()
# raw_data = StringIO.StringIO()
writer = unicodecsv.writer(raw_data)
if 'error' in data:
for row in dict_to_rows(data['error']):
writer.writerow(row)
# writer.writerow(['error'])
# writer.writerow([data['error']])
# logger.warn(str(('error', data)))
return raw_data.getvalue()
if 'objects' in data:
data = data['objects']
if isinstance(data, dict):
# usually, this happens when the data is actually an error message;
# but also, it could be just one item being returned
data = dict_to_rows(data)
for item in data:
writer.writerow(item)
else:
i = 0
keys = None
for item in data:
### Removed, handled in convert_list_vals,
### data = to_simple(data)
if i == 0:
keys = item.keys()
# writer.writerow([smart_text(key).encode('utf-8') for key in keys])
writer.writerow([smart_text(key) for key in keys])
i += 1
writer.writerow([csvutils.convert_list_vals(val)
for val in item.values()])
return raw_data.getvalue()
def to_csv(self, data, root='objects', options=None):
data = to_simple(data)
raw_data = cStringIO.StringIO()
writer = csv.writer(raw_data)
if 'error' in data:
for row in dict_to_rows(data['error']):
writer.writerow(row)
# writer.writerow(['error'])
# writer.writerow([data['error']])
# logger.warn(str(('error', data)))
return raw_data.getvalue()
if 'objects' in data:
data = data['objects']
if len(data) == 0:
return data
if isinstance(data, dict):
# usually, this happens when the data is actually an error message;
# but also, it could be just one item being returned
data = dict_to_rows(data)
for item in data:
writer.writerow(item)
else:
i = 0
keys = None
for item in data:
if i == 0:
keys = item.keys()
writer.writerow([smart_text(key) for key in keys])
i += 1
writer.writerow([csvutils.csv_convert(val) for val in item.values()])
return raw_data.getvalue()
def xls_write_workbook(file,
data,
request=None,
image_keys=None,
title_function=None,
list_brackets=None):
'''
***WARNING*** xlsx files load fully into memory on display - if there are
>~ 2000 images, this will cause performance issues on the client.***
@param sheet_rows iterable of dicts, one per row
'''
if not isinstance(data, dict):
raise BadRequest('unknown data for generic xls serialization: %r' %
type(data))
wb = xlsxwriter.Workbook(file, {'constant_memory': True})
logger.info('xls_write_workbook for data: %r', data.keys())
for key, sheet_rows in data.items():
logger.info('type sheet_rows: %r', type(sheet_rows))
if isinstance(sheet_rows, (dict, OrderedDict)):
sheet_name = default_converter(key)
logger.info('writing sheet %r...', sheet_name)
sheet = wb.add_worksheet(sheet_name)
for i, row in enumerate(csvutils.dict_to_rows(sheet_rows)):
sheet.write_row(i, 0, row)
elif isinstance(sheet_rows, basestring):
sheet_name = default_converter(key)
logger.info('writing single string sheet %r...', sheet_name)
sheet = wb.add_worksheet(sheet_name)
sheet.write_string(0, 0, sheet_rows)
else:
sheet_name = default_converter(key)
logger.info('writing sheets for base name %r...', sheet_name)
write_rows_to_sheet(wb,
sheet_rows,
sheet_name,
request=request,
image_keys=image_keys,
title_function=title_function,
list_brackets=list_brackets)
wb.close()
def get_xls_response(
data, output_filename,request=None,image_keys=None,
title_function=None, list_brackets=None):
'''
Create an xlsx file that will be streamed through the StreamingHttpResponse.
- if length exceeds MAX_ROWS_PER_XLS_FILE, create multiple files and zip them.
- TODO: when using xlsx, can simply add extra sheets to the file.
@param output_filename - for naming temp files
FIXME: wrap cursor with cursorgenerator; pass in the image columns as arg
FIXME: rework this using the generic_xlsx_response as a template:
- this method is used for all xlsx serialization at this time, except
for in testing, and in ScreenResultSerializer - 20160419.
'''
if not isinstance(data, dict):
raise BadRequest(
'unknown data for xls serialization: %r, must be a dict of '
'sheet_row entries' % type(data))
# create a temp dir
# with TemporaryFile() as f:
temp_dir = os.path.join(
settings.TEMP_FILE_DIR, str(time.clock()).replace('.', '_'))
os.mkdir(temp_dir)
try:
# Create an new Excel file and add a worksheet.
filename = '%s.xlsx' % (output_filename)
temp_file = os.path.join(temp_dir, filename)
file_names_to_zip = [temp_file]
if DEBUG_STREAMING: logger.info('temp file: %r', temp_file)
workbook = xlsxwriter.Workbook(temp_file, {'constant_memory': True})
for key, sheet_rows in data.items():
logger.info('type sheet_rows: %r', type(sheet_rows))
if isinstance(sheet_rows, (dict, OrderedDict)):
sheet_name = default_converter(key)
logger.info('writing sheet %r...', sheet_name)
sheet = workbook.add_worksheet(sheet_name)
for i, row in enumerate(csvutils.dict_to_rows(sheet_rows)):
sheet.write_row(i,0,row)
elif isinstance(sheet_rows, basestring):
sheet_name = default_converter(key)
logger.info('writing single string sheet %r...', sheet_name)
sheet = workbook.add_worksheet(sheet_name)
sheet.write_string(0,0,sheet_rows)
else:
sheet_name = default_converter(key)
logger.info('writing sheets for base name %r...', sheet_name)
max_rows_per_sheet = 2**20
sheet = workbook.add_worksheet(sheet_name)
filerow = 0
sheets = 1
for row,values in enumerate(sheet_rows):
if filerow == 0:
for i,(key,val) in enumerate(values.items()):
title = key
if title_function:
title = title_function(key)
sheet.write_string(filerow,i,title)
filerow += 1
for i, (key,val) in enumerate(values.items()):
val = csvutils.csv_convert(
val, delimiter=LIST_DELIMITER_XLS,
list_brackets=list_brackets)
if val is not None:
if len(val) > 32767:
logger.error('warn, row too long, %d, key: %r, len: %d',
row,key,len(val) )
if image_keys and key in image_keys:
max_rows_per_sheet = MAX_IMAGE_ROWS_PER_XLS_FILE
if not request:
raise Exception(
'must specify the request parameter for image export')
# hack to speed things up:
if ( key == 'structure_image' and
'library_well_type' in values and
values['library_well_type'].lower() == 'empty' ):
continue
write_xls_image(sheet, filerow, i, val, request)
else:
sheet.write_string(filerow,i,val)
filerow += 1
if row % 10000 == 0:
logger.info('wrote %d rows to temp file', row)
if filerow > max_rows_per_sheet:
workbook.close()
logger.info('wrote file: %r', temp_file)
# Create an new Excel file and add a worksheet.
filename = '%s_%s.xlsx' % (output_filename, filerow)
temp_file = os.path.join(temp_dir, filename)
workbook = xlsxwriter.Workbook(temp_file, {'constant_memory': True})
sheet = workbook.add_worksheet(sheet_name)
file_names_to_zip.append(temp_file)
filerow = 0
workbook.close()
logger.info('wrote file: %r', temp_file)
content_type = '%s; charset=utf-8' % XLSX_MIMETYPE
if len(file_names_to_zip) >1:
# create a temp zip file
content_type='application/zip; charset=utf-8'
temp_file = os.path.join('/tmp',str(time.clock()))
logger.info('temp ZIP file: %r', temp_file)
with ZipFile(temp_file, 'w') as zip_file:
for _file in file_names_to_zip:
zip_file.write(_file, os.path.basename(_file))
logger.info('wrote file %r', temp_file)
filename = '%s.zip' % output_filename
_file = file(temp_file)
logger.info('download tmp file: %r, %r',temp_file,_file)
wrapper = FileWrapper(_file)
response = StreamingHttpResponse(
wrapper, content_type=content_type)
response['Content-Length'] = os.path.getsize(temp_file)
response['Content-Disposition'] = \
'attachment; filename=%s' % filename
return response
except Exception, e:
logger.exception('xls streaming error')
raise e
