def export_as_csv(queryset, fields=None, header=None, filename=None, options=None, out=None):
"""
Exports a queryset as csv from a queryset with the given fields.
:param queryset: queryset to export
:param fields: list of fields names to export. None for all fields
:param header: if True, the exported file will have the first row as column names
:param filename: name of the filename
:param options: CSVOptions() instance or none
:param: out: object that implements File protocol. HttpResponse if None.
:return: HttpResponse instance
"""
if out is None:
if filename is None:
filename = filename or "%s.csv" % queryset.model._meta.verbose_name_plural.lower().replace(" ", "_")
response = HttpResponse(content_type='text/csv')
response['Content-Disposition'] = 'attachment;filename="%s"' % filename.encode('us-ascii', 'replace')
else:
response = out
if options is None:
config = csv_options_default
else:
config = csv_options_default.copy()
config.update(options)
if fields is None:
fields = [f.name for f in queryset.model._meta.fields]
dialect = config.get('dialect', None)
if dialect is not None:
writer = csv.writer(response, dialect=dialect)
else:
writer = csv.writer(response,
escapechar=str(config['escapechar']),
delimiter=str(config['delimiter']),
quotechar=str(config['quotechar']),
quoting=int(config['quoting']))
if bool(header):
if isinstance(header, (list, tuple)):
writer.writerow(header)
else:
writer.writerow([f for f in fields])
for obj in queryset:
row = []
for fieldname in fields:
value = get_field_value(obj, fieldname)
if isinstance(value, datetime.datetime):
value = dateformat.format(value, config['datetime_format'])
elif isinstance(value, datetime.date):
value = dateformat.format(value, config['date_format'])
elif isinstance(value, datetime.time):
value = dateformat.format(value, config['time_format'])
row.append(smart_str(value))
writer.writerow(row)
return response
def export_as_csv(queryset,
fields=None,
header=False,
filename=None,
options=None,
out=None):
"""
Exports a queryset as csv from a queryset with the given fields.
:param queryset: queryset to export
:param fields: list of fields names to export. None for all fields
:param header: if True, the exported file will have the first row as column names
:param filename: name of the filename
:param options: CSVOptions() instance or none
:param: out: object that implements File protocol. HttpResponse if None.
:return: HttpResponse instance
"""
filename = filename or "%s.csv" % queryset.model._meta.verbose_name_plural.lower(
).replace(" ", "_")
if out is None:
response = HttpResponse(mimetype='text/csv')
response[
'Content-Disposition'] = 'attachment;filename="%s"' % filename.encode(
'us-ascii', 'replace')
else:
response = out
if options is None:
config = csv_options_default
else:
config = csv_options_default.copy()
config.update(options)
if fields is None:
fields = [f.name for f in queryset.model._meta.fields]
writer = csv.writer(response,
escapechar=str(config['escapechar']),
delimiter=str(config['delimiter']),
quotechar=str(config['quotechar']),
quoting=int(config['quoting']))
if header:
writer.writerow([f for f in fields])
for obj in queryset:
row = []
for fieldname in fields:
value = get_field_value(obj, fieldname)
if isinstance(value, datetime.datetime):
value = dateformat.format(value, config['datetime_format'])
elif isinstance(value, datetime.date):
value = dateformat.format(value, config['date_format'])
elif isinstance(value, datetime.time):
value = dateformat.format(value, config['time_format'])
row.append(smart_str(value))
writer.writerow(row)
return response
def yield_rows():
for obj in queryset:
row = []
for fieldname in fields:
value = get_field_value(obj, fieldname)
if isinstance(value, datetime.datetime):
try:
value = dateformat.format(value.astimezone(settingstime_zone), config['datetime_format'])
except ValueError:
# astimezone() cannot be applied to a naive datetime
value = dateformat.format(value, config['datetime_format'])
elif isinstance(value, datetime.date):
value = dateformat.format(value, config['date_format'])
elif isinstance(value, datetime.time):
value = dateformat.format(value, config['time_format'])
row.append(smart_str(value))
yield writer.writerow(row)
def data():
for obj in queryset_iterator(queryset):
row = []
for fieldname in form.cleaned_data['columns']:
value = get_field_value(obj, fieldname)
if isinstance(value, datetime.datetime):
value = dateformat.format(value, form.cleaned_data['datetime_format'])
elif isinstance(value, datetime.date):
value = dateformat.format(value, form.cleaned_data['date_format'])
elif isinstance(value, datetime.time):
value = dateformat.format(value, form.cleaned_data['time_format'])
row.append(smart_str(value))
writer.writerow(row)
data = read_and_flush()
#import time
#time.sleep(1)
yield data
def export_as_csv(queryset, fields=None, header=False, filename=None, options=None):
"""
Exports a queryset as csv from a queryset with the given fields.
:param queryset: queryset to export
:param fields: list of fields names to export. None for all fields
:param header: if True, the exported file will have the first row as column names
:param filename: name of the filename
:param options: CSVOptions() instance or none
:return: HttpResponse instance
"""
filename = filename or "%s.csv" % queryset.model._meta.verbose_name_plural.lower().replace(" ", "_")
response = HttpResponse(mimetype='text/csv')
response['Content-Disposition'] = 'attachment;filename="%s"' % filename.encode('us-ascii', 'replace')
if options is None:
options = csv_options_default
writer = csv.writer(response,
escapechar=str(options['escapechar']),
delimiter=str(options['delimiter']),
quotechar=str(options['quotechar']),
quoting=int(options['quoting']))
if header:
writer.writerow([f for f in fields])
for obj in queryset:
row = []
for fieldname in fields:
value = get_field_value(obj, fieldname)
if isinstance(value, datetime.datetime):
value = dateformat.format(value, options['datetime_format'])
elif isinstance(value, datetime.date):
value = dateformat.format(value, options['date_format'])
elif isinstance(value, datetime.time):
value = dateformat.format(value, options['time_format'])
row.append(smart_str(value))
writer.writerow(row)
return response
def export_as_csv(modeladmin, request, queryset):
"""
export a queryset to csv file
"""
if not request.user.has_perm('adminactions.export'):
messages.error(request, _('Sorry you do not have rights to execute this action'))
return
try:
adminaction_requested.send(sender=modeladmin.model,
action='export_as_csv',
request=request,
queryset=queryset)
except ActionInterrupted as e:
messages.error(request, str(e))
return
cols = [(f.name, f.verbose_name) for f in queryset.model._meta.fields]
initial = {'_selected_action': request.POST.getlist(helpers.ACTION_CHECKBOX_NAME),
'select_across': request.POST.get('select_across') == '1',
'action': request.POST.get('action'),
'date_format': 'd/m/Y',
'datetime_format': 'N j, Y, P',
'time_format': 'P',
'quotechar': '"',
'columns': [x for x, v in cols],
'quoting': csv.QUOTE_ALL,
'delimiter': ';',
'escapechar': '\\', }
if 'apply' in request.POST:
form = CSVOptions(request.POST)
form.fields['columns'].choices = cols
if form.is_valid():
try:
adminaction_start.send(sender=modeladmin.model,
action='export_as_csv',
request=request,
queryset=queryset,
form=form)
except ActionInterrupted as e:
messages.error(request, str(e))
return
filename = "%s.csv" % queryset.model._meta.verbose_name_plural.lower().replace(" ", "_")
response = HttpResponse(mimetype='text/csv')
response['Content-Disposition'] = 'attachment;filename="%s"' % filename
try:
writer = csv.writer(response,
escapechar=str(form.cleaned_data['escapechar']),
delimiter=str(form.cleaned_data['delimiter']),
quotechar=str(form.cleaned_data['quotechar']),
quoting=int(form.cleaned_data['quoting']))
if form.cleaned_data.get('header', False):
writer.writerow([f for f in form.cleaned_data['columns']])
for obj in queryset:
row = []
for fieldname in form.cleaned_data['columns']:
value = get_field_value(obj, fieldname)
if isinstance(value, datetime.datetime):
value = dateformat.format(value, form.cleaned_data['datetime_format'])
elif isinstance(value, datetime.date):
value = dateformat.format(value, form.cleaned_data['date_format'])
elif isinstance(value, datetime.time):
value = dateformat.format(value, form.cleaned_data['time_format'])
row.append(smart_str(value))
writer.writerow(row)
except Exception as e:
messages.error(request, "Error: (%s)" % str(e))
else:
try:
adminaction_start.send(sender=modeladmin.model, action='export_as_csv', request=request, queryset=queryset)
except ActionInterrupted as e:
messages.error(request, str(e))
return HttpResponseRedirect(request.get_full_path())
return response
else:
form = CSVOptions(initial=initial)
form.fields['columns'].choices = cols
adminForm = helpers.AdminForm(form, modeladmin.get_fieldsets(request), {}, [], model_admin=modeladmin)
media = modeladmin.media + adminForm.media
tpl = 'adminactions/export_csv.html'
ctx = {'adminform': adminForm,
'form': form,
'change': True,
'title': _('Export to CSV'),
'is_popup': False,
'save_as': False,
'has_delete_permission': False,
'has_add_permission': False,
'has_change_permission': True,
'queryset': queryset,
'opts': queryset.model._meta,
'app_label': queryset.model._meta.app_label,
'media': mark_safe(media)}
return render_to_response(tpl, RequestContext(request, ctx))
def export_as_xls3(queryset, fields=None, header=None, # noqa
filename=None, options=None, out=None): # pragma: no cover
# sheet_name=None, header_alt=None,
# formatting=None, out=None):
"""
Exports a queryset as xls from a queryset with the given fields.
:param queryset: queryset to export (can also be list of namedtuples)
:param fields: list of fields names to export. None for all fields
:param header: if True, the exported file will have the first row as column names
:param out: object that implements File protocol.
:param header_alt: if is not None, and header is True, the first row will be as header_alt (same nr columns)
:param formatting: if is None will use formatting_default
:return: HttpResponse instance if out not supplied, otherwise out
"""
import xlsxwriter
def _get_qs_formats(queryset):
formats = {'_general_': book.add_format()}
if hasattr(queryset, 'model'):
for i, fieldname in enumerate(fields):
try:
f, __, __, __, = queryset.model._meta.get_field_by_name(fieldname)
pattern = xlsxwriter_options.get(f.name, xlsxwriter_options.get(f.__class__.__name__, 'general'))
fmt = book.add_format({'num_format': pattern})
formats[fieldname] = fmt
except FieldDoesNotExist:
pass
# styles[i] = xlwt.easyxf(num_format_str=xls_options_default.get(col_class, 'general'))
# styles[i] = xls_options_default.get(col_class, 'general')
return formats
http_response = out is None
if out is None:
# if filename is None:
# filename = filename or "%s.xls" % queryset.model._meta.verbose_name_plural.lower().replace(" ", "_")
# response = HttpResponse(content_type='application/vnd.ms-excel')
# response['Content-Disposition'] = 'attachment;filename="%s"' % filename.encode('us-ascii', 'replace')
# out = io.BytesIO()
if six.PY2:
out = six.StringIO()
elif six.PY3:
out = six.StringIO()
# out = io.BytesIO()
else:
raise EnvironmentError('Python version not supported')
config = xlsxwriter_options.copy()
if options:
config.update(options)
if fields is None:
fields = [f.name for f in queryset.model._meta.fields]
book = xlsxwriter.Workbook(out, {'in_memory': True})
sheet_name = config.pop('sheet_name')
use_display = config.get('use_display', False)
sheet = book.add_worksheet(sheet_name)
book.close()
formats = _get_qs_formats(queryset)
row = 0
sheet.write(row, 0, force_text('#'), formats['_general_'])
if header:
if not isinstance(header, (list, tuple)):
header = [force_text(f.verbose_name)for f in queryset.model._meta.fields if f.name in fields]
for col, fieldname in enumerate(header, start=1):
sheet.write(row, col, force_text(fieldname), formats['_general_'])
settingstime_zone = get_default_timezone()
for rownum, row in enumerate(queryset):
sheet.write(rownum + 1, 0, rownum + 1)
for idx, fieldname in enumerate(fields):
fmt = formats.get(fieldname, formats['_general_'])
try:
value = get_field_value(row,
fieldname,
usedisplay=use_display,
raw_callable=False)
if callable(fmt):
value = fmt(value)
if isinstance(value, (list, tuple)):
value = smart_text(u"".join(value))
if isinstance(value, datetime.datetime):
try:
value = dateformat.format(value.astimezone(settingstime_zone), config['datetime_format'])
except ValueError:
value = dateformat.format(value, config['datetime_format'])
if isinstance(value, six.binary_type):
value = smart_text(value)
sheet.write(rownum + 1, idx + 1, smart_text(value), fmt)
except Exception as e:
raise
sheet.write(rownum + 1, idx + 1, smart_text(e), fmt)
book.close()
out.seek(0)
if http_response:
if filename is None:
filename = filename or "%s.xls" % queryset.model._meta.verbose_name_plural.lower().replace(" ", "_")
response = HttpResponse(out.read(),
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
# content_type='application/vnd.ms-excel')
# response['Content-Disposition'] = six.b('attachment;filename="%s"') % six.b(filename.encode('us-ascii', 'replace'))
response['Content-Disposition'] = six.b('attachment;filename="%s"' % filename)
return response
return out
def export_as_xls2(queryset, fields=None, header=None, # noqa
filename=None, options=None, out=None):
# sheet_name=None, header_alt=None,
# formatting=None, out=None):
"""
Exports a queryset as xls from a queryset with the given fields.
:param queryset: queryset to export (can also be list of namedtuples)
:param fields: list of fields names to export. None for all fields
:param header: if True, the exported file will have the first row as column names
:param out: object that implements File protocol.
:param header_alt: if is not None, and header is True, the first row will be as header_alt (same nr columns)
:param formatting: if is None will use formatting_default
:return: HttpResponse instance if out not supplied, otherwise out
"""
def _get_qs_formats(queryset):
formats = {}
if hasattr(queryset, 'model'):
for i, fieldname in enumerate(fields):
try:
f, __, __, __, = compat.get_field_by_name(queryset.model, fieldname)
fmt = xls_options_default.get(f.name, xls_options_default.get(f.__class__.__name__, 'general'))
formats[i] = fmt
except FieldDoesNotExist:
pass
# styles[i] = xlwt.easyxf(num_format_str=xls_options_default.get(col_class, 'general'))
# styles[i] = xls_options_default.get(col_class, 'general')
return formats
if out is None:
if filename is None:
filename = filename or "%s.xls" % queryset.model._meta.verbose_name_plural.lower().replace(" ", "_")
response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = ('attachment;filename="%s"' % filename).encode('us-ascii', 'replace')
else:
response = out
config = xls_options_default.copy()
if options:
config.update(options)
if fields is None:
fields = [f.name for f in queryset.model._meta.fields]
book = xlwt.Workbook(encoding="utf-8", style_compression=2)
sheet_name = config.pop('sheet_name')
use_display = config.get('use_display', False)
sheet = book.add_sheet(sheet_name)
style = xlwt.XFStyle()
row = 0
heading_xf = xlwt.easyxf('font:height 200; font: bold on; align: wrap on, vert centre, horiz center')
sheet.write(row, 0, u'#', style)
if header:
if not isinstance(header, (list, tuple)):
header = [force_text(f.verbose_name) for f in queryset.model._meta.fields if f.name in fields]
for col, fieldname in enumerate(header, start=1):
sheet.write(row, col, fieldname, heading_xf)
sheet.col(col).width = 5000
sheet.row(row).height = 500
formats = _get_qs_formats(queryset)
settingstime_zone = get_default_timezone()
_styles = {}
for rownum, row in enumerate(queryset):
sheet.write(rownum + 1, 0, rownum + 1)
for idx, fieldname in enumerate(fields):
fmt = formats.get(idx, 'general')
try:
value = get_field_value(row,
fieldname,
usedisplay=use_display,
raw_callable=False)
if callable(fmt):
value = xlwt.Formula(fmt(value))
if hash(fmt) not in _styles:
if callable(fmt):
_styles[hash(fmt)] = xlwt.easyxf(num_format_str='formula')
else:
_styles[hash(fmt)] = xlwt.easyxf(num_format_str=fmt)
if isinstance(value, datetime.datetime):
try:
value = dateformat.format(value.astimezone(settingstime_zone), config['datetime_format'])
except ValueError:
# astimezone() cannot be applied to a naive datetime
value = dateformat.format(value, config['datetime_format'])
if isinstance(value, (list, tuple)):
value = "".join(value)
sheet.write(rownum + 1, idx + 1, value, _styles[hash(fmt)])
except Exception as e:
# logger.warning("TODO refine this exception: %s" % e)
sheet.write(rownum + 1, idx + 1, smart_str(e), _styles[hash(fmt)])
book.save(response)
return response
def export_as_csv(
queryset,
fields=None,
header=None, # noqa
filename=None,
options=None,
out=None):
"""
Exports a queryset as csv from a queryset with the given fields.
:param queryset: queryset to export
:param fields: list of fields names to export. None for all fields
:param header: if True, the exported file will have the first row as column names
:param filename: name of the filename
:param options: CSVOptions() instance or none
:param: out: object that implements File protocol. HttpResponse if None.
:return: HttpResponse instance
"""
if out is None:
if filename is None:
filename = filename or "%s.csv" % queryset.model._meta.verbose_name_plural.lower(
).replace(" ", "_")
response = HttpResponse(content_type='text/csv')
response['Content-Disposition'] = ('attachment;filename="%s"' %
filename).encode(
'us-ascii', 'replace')
else:
response = out
if options is None:
config = csv_options_default
else:
config = csv_options_default.copy()
config.update(options)
if fields is None:
fields = [f.name for f in queryset.model._meta.fields]
dialect = config.get('dialect', None)
if dialect is not None:
writer = csv.writer(response, dialect=dialect)
else:
writer = csv.writer(response,
escapechar=str(config['escapechar']),
delimiter=str(config['delimiter']),
quotechar=str(config['quotechar']),
quoting=int(config['quoting']))
if bool(header):
if isinstance(header, (list, tuple)):
writer.writerow(header)
else:
writer.writerow([f for f in fields])
settingstime_zone = pytz.timezone(settings.TIME_ZONE)
for obj in queryset:
row = []
for fieldname in fields:
value = get_field_value(obj, fieldname)
if isinstance(value, datetime.datetime):
try:
value = dateformat.format(
value.astimezone(settingstime_zone),
config['datetime_format'])
except ValueError:
# astimezone() cannot be applied to a naive datetime
value = dateformat.format(value, config['datetime_format'])
elif isinstance(value, datetime.date):
value = dateformat.format(value, config['date_format'])
elif isinstance(value, datetime.time):
value = dateformat.format(value, config['time_format'])
row.append(smart_str(value))
writer.writerow(row)
return response
