export_as_excel.py

#One of those handy scripts, see:http://djangotricks.blogspot.co.ke/2013/12/how-to-export-data-as-excel.html


from .models import *
from django.http import *

"""
# Quick 'how-to' guide
# model in question
class Duplicate(models.Model):
    origin_county = models.CharField(max_length=50)
    origin_centriod = models.CharField(max_length=50)
    terminating_county = models.CharField(max_length=50)
    terminating_centriod = models.CharField(max_length=50)
    
    class Meta(object):
        db_table = 'Duplicate'
        ordering = ['-id']
        verbose_name_plural = 'Duplicates'
        
    def __unicode__(self):
        return self.origin_county
    def save(self,*args,**kwargs):
        super(Duplicate,self).save(*args,**kwargs)
        
# how to add action to admin:
class DuplicateAdmin(admin.ModelAdmin):
    list_display = ('id','origin_county','origin_centriod','terminating_county','terminating_centriod')
    list_filter = ('origin_county','terminating_county',)
    actions = [export_xls,]
admin.site.register(Duplicate,DuplicateAdmin)

"""

def export_xls(modeladmin, request, queryset):
    import xlwt
    response = HttpResponse(content_type='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename=mymodel.xls'
    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet("Duplicate Sections")
    
    row_num = 0
    
    columns = [
        (u"ID", 2000),
        (u"origin_county", 15000),
        (u"origin_centriod", 15000),
        (u"terminating_county", 15000),
        (u"terminating_centriod", 15000),
    ]

    font_style = xlwt.XFStyle()
    font_style.font.bold = True

    for col_num in xrange(len(columns)):
        ws.write(row_num, col_num, columns[col_num][0], font_style)
        # set column width
        ws.col(col_num).width = columns[col_num][1]

    font_style = xlwt.XFStyle()
    font_style.alignment.wrap = 1
    
    for obj in queryset:
        row_num += 1
        row = [
            obj.pk,
            obj.origin_county,
            obj.origin_centriod,
            obj.terminating_county,
            obj.terminating_centriod
        ]
        for col_num in xrange(len(row)):
            ws.write(row_num, col_num, row[col_num], font_style)
            
    wb.save(response)
    return response
    
export_xls.short_description = u"Export As XLS"