django-import-export How to format the exported excel's cell?

user_10588606 0
Viewed 1806

Is there any way I can format the exported excel file? When i export the files, the column is too small to fit the words. Im quite new to this so any help would be much appreciated.

Exported excel file looks something like this, the title cell is too small to fit the words.

If django-import-export is unable to do this, then is there any other methods to export database information as excel and is able to format the files?

There's actually someone who asked a similar question but there's no answer:

Is there a way to manage the column/cell widths when exporting to Excel with django-import-export?

Some of my code in admin.py

class LogResource(resources.ModelResource):
    date = Field(attribute='date', column_name='Date')
    dtime = Field(attribute='dtime', column_name='Departure Time')
    pilot = Field(attribute='pilot', column_name='Pilot')
    cpilot = Field(attribute='cpilot', column_name='Co-Pilot')
    purpose = Field(attribute='purpose', column_name='Purpose of Flight')
    others = Field(attribute='others', column_name='Others')

    class Meta:
        model=Log
        exclude=('id',)


class LogAdmin(ExportActionModelAdmin, admin.ModelAdmin):
    resource_class = LogResource
    list_display = ('date', 'dtime', 'purpose', 'pilot', 'cpilot')
    list_filter = ('date', 'purpose', 'pilot')

In views.py

def logentry_form_submission(request):
    date = request.POST["date"]
    dtime = request.POST["dtime"]
    pilot = request.POST["pilot"]
    cpilot = request.POST["cpilot"]
    purpose = request.POST["purpose"]
    others = request.POST["others"]

    log_info = Log(date=date, dtime=dtime, pilot=pilot, cpilot=cpilot,         
    purpose=purpose, others=others)
    log_info.save()
    return render(request, 'myhtml/logentry_form_submission.html')

My code is abit messy since I learn everything online so feel free to improve my code.

django-import-export excel django
1 Answers

There's no official way. I was able to workaround it by:

  • registering own tablib format
  • defining formatting per Resource
  • passing formatter callback from resource class to tablib export code

Register your own tablib format:

from tablib.formats import registry
from tablib.formats._xlsx import XLSXFormat
class FormattedXLSX(XLSXFormat):
    @classmethod
    def export_set(cls, dataset, freeze_panes=True, formatter=None):
        """Returns XLSX representation of Dataset."""
        wb = Workbook()
        ws = wb.worksheets[0]
        ws.title = dataset.title if dataset.title else "Tablib Dataset"

        cls.dset_sheet(dataset, ws, freeze_panes=freeze_panes)

        ### Just added this lines to original code
        if formatter:
            formatter(ws)
        ###

        stream = BytesIO()
        wb.save(stream)
        return stream.getvalue()


registry.register("xlsx", FormattedXLSX())

Adjust columns widths in resource class:

class OrderResource(resources.ModelResource):
    @staticmethod
    def formatter(ws):
        ws.column_dimensions["B"].width = 15
        ws.column_dimensions["C"].width = 35
        ws.column_dimensions["D"].width = 12
        ws.column_dimensions["F"].width = 18

    class Meta:
        model = OrderFull

Now we need to pass it somehow to tablib. Take a look at source: https://github.com/django-import-export/django-import-export/blob/2.0.2/import_export/admin.py#L461 You can override this method, and change export_data = file_format.export_data(data) to export_data = file_format.export_data(data, resource_class.formatter) (Didn't tested it).

Since I'm using it different way (without admin integration) I can provide a bit different implementation, but inspired by original ExportMixin code. 

class ImportExportView(APIView):
    # ...
    def get(self, request, **kwargs):
        dataset = self.resource().export(self.queryset.all())
        # Inject formatter function from resource
        xslx = XLSX().export_data(dataset=dataset, formatter=self.resource.formatter)

        response = HttpResponse(
            content=xslx,
            content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        )
        response["Content-Disposition"] = f'attachment; filename="{self.filename}"'
        return response
user_53426580
Related