#49 Changes:

• added field dictionary to check field types more easily
• added global date format settings
• added support for rest framework date formats

This was a bit more complicated than I originally thought because the data is already formatted by drf, and because we needed to know the field type (couldn't rely on the type since it was a str).

I realized the current version would break when using drf's DATETIME_FORMAT, DATE_FORMAT or TIME_FORMAT because parse_datetime() and parse_date() could not parse non-iso formats.

Take a look and let me know what you think.

Also what should the settings be name and where should they be? I went with DRF_RENDERER_XLSX_ prefix in global django settings:

• DRF_RENDERER_XLSX_DATETIME_FORMAT
• DRF_RENDERER_XLSX_DATE_FORMAT
• DRF_RENDERER_XLSX_TIME_FORMAT

Another option would be to stick these settings inside REST_FRAMEWORK = {} instead?

a case of CSV injections have become public/popular in mainstream media here. This PR sanitizes field values by prepending ' in front of possible malicious code. xlsx readers handle these values as strings Tested with MS Excel, Numbers and LibreOffice.

ESCAPE_CHARS are taken from https://owasp.org/www-community/attacks/CSV_Injection

There is a new drf release https://www.django-rest-framework.org/community/release-notes/#3140 Here NullBooleanField is no longer available and so I removed the one reference to allow support for drf 3.14.0

It was also not required as the isinstance(field, BooleanField) would return true for a NullBooleanFIeld due to object inheritance:

>>> from rest_framework.fields import NullBooleanField
>>> field = NullBooleanField()
>>> from rest_framework.fields import BooleanField
>>> isinstance(field, BooleanField)
True
>>> 

So I think we should be good :+1:

Thank you for the package :heart: @FlipperPA

there is already a fix, a PR (#36) opened by @paveloder

I have applied the fix locally and I can confirm it works.

My API was returning:

HTTP 200 OK
Allow: GET
Content-Type: application/json
Vary: Accept

[
    {
        "id": 1,
        "rfid_tag": {
            "id": 1,
            "number": "1234567890",
            "product": {
                "id": 1,
                "name": "Prosop mare",
                "ean": "11111",
                "description": "",
                "category": 1,
                "info": []
            },
            "status": "I",
            "info": []
        },
        "security_location": 1,
        "aknowledged": false
    },
    {
        "id": 2,
        "rfid_tag": {
            "id": 2,
            "number": "1234567891",
            "product": {
                "id": 1,
                "name": "Prosop mare",
                "ean": "11111",
                "description": "",
                "category": 1,
                "info": []
            },
            "status": "I",
            "info": []
        },
        "security_location": 2,
        "aknowledged": false
    }
]

• viewset

class SecurityEventsGetXLSXViewset(XLSXFileMixin, ReadOnlyModelViewSet):
    """ Generate XLSX File """
    queryset = models.SecurityEvents.objects.all()
    serializer_class = serializers.SecurityEventsGetSerializer
    renderer_classes = (XLSXRenderer,)
    filename = 'SecurityEventsGet_export.xlsx'

• serializer

class SecurityEventsGetSerializer(serializers.ModelSerializer):
    rfid_tag = RfidTagNestedSerializer()
    class Meta:
        model = models.SecurityEvents
        fields = ['id', 'rfid_tag', 'security_location', 'aknowledged']
        read_only_fields = ['created_at']

Before PR #36 patch

After PR #36 patch

Would it make sense to mirror how most of the DRF & Django's CBVs work by having a get_ method for get_filename that would allow a user to dynamically set the filename when using the XLSXFileMixin?

Maybe as simple as:

def get_filename(self):
    return self.filename

From my viewpoint it's required to return lists also as a separated list.

If possible please take the change to next release so that i can use it via pip in my project :-)

My serializer returns type-reach content:

OrderedDict([('link', u'https://yandex.com'), ('name', u'name'), ('num', 123), ('post_date', datetime.datetime(2018, 11, 5, 17, 12, 42)), ('num2', 1.23), ('in_my_network', False)])

But in resulting XLSX all these cells are strings. E.g. '123

I didn't find in documentation any way to specify that given cell is integer / float / data / boolean.

according to https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/MIME_types/Common_types application/vnd.openxmlformats-officedocument.spreadsheetml.sheet should be used. Closes #60

Fixes a problem that happened while flattening headers from nested serialisers. Example response:

{
   "address": {
      "client": {
         "name": "foo",
         "id": 1
   }
}

Renderer put only client.name and client.id to header names. And returned empty data for those fields. This PR fixes this.

When serialising, we iterate through the headers we find in the first result:

elif isinstance(results, ReturnList) or type(results) is list:
  column_names_first_row = self._flatten(results[0])

Let's say the response looks like this:

{
  results: [
    {
      title: 'Example 1',
      some_relation: {
        id: 1,
        title: 'I am a relation'
      },
      some_value: 100
    },
    {
      title: 'Example 2',
      some_relation: null,
      some_value: 200
    }
  ]
}

This will lead to the headers title, some_relation.id, some_relation.title, some_value. While this works for the first result, the second row will have less values and therefore shift some_value to the left, ending up in the column of some_relation.title, with the one most right being empty.

A second problem would be when the second result in the example would actually be the first. In that case we would end up with incomplete headers.

I changed the flattening to use the serializer instead and iterate through all header keys for each result, to keep the correct order and not end up with shifted values.

I also added a property xlsx_use_labels that uses field labels instead of keys.

Update 25.03.2021:

Since this PR is still not merged or responded to, and I needed to add more functionality based on this commit, this is much more than a bugfix now:

# set xlsx_use_labels = True inside API view to enable labels
use_labels = getattr(drf_view, 'xlsx_use_labels', False)

# A list of header keys to ignore in our export
self.ignore_headers = getattr(drf_view, 'xlsx_ignore_headers', [])

# set dict named xlsx_use_labels inside API View. i.e. { True: 'Yes', False: 'No' }
self.boolean_display = getattr(drf_view, 'xlsx_boolean_labels', None)

# set dict named xlsx_date_format_mappings with headers as keys and formatting as value. i.e. { 'created_at': '%d.%m.%Y, %H:%M' }
self.date_format_mappings = getattr(drf_view, 'xlsx_date_format_mappings', None)

# Map a specific key to a column (i.e. if the field returns a json) or pass a function to format the value
# Example with key: { 'custom_choice': 'custom_choice.display' }, showing 'display' in the 'custom_choice' col
# Example with function { 'custom_choice': custom_func }, passing the value of 'custom_choice' to 'custom_func', allowing for formatting logic
self.custom_mappings = getattr(drf_view, 'xlsx_custom_mappings', None)

Hi guys!

I try to download Excel file but i can't. After send a request I've got some trash in response and I can't download file. Do I need to define download method?

Here is my view and serializer:

class ReportViewSet(XLSXFileMixin, viewsets.ReadOnlyModelViewSet):
    queryset = models.Item.objects.all()
    serializer_class = serializers.ReportSerializer
    renderer_classes = (XLSXRenderer,)
    filename = 'report.xlsx'

    column_header = {
        'titles': [
            "Column_1_name",
            "Column_2_name",
            "Column_3_name",
        ]

class ReportSerializer(serializers.Serializer):
    color = serializers.SerializerMethodField()

    def get_color(self, instance):
        color_map = {'w': 'FFFFFFCC', 'a': 'FFFFCCCC'}
        return color_map.get(instance.name, 'FFFFFFFF')

Thank you in advance!

Currently XLSXListField does not behave very nicely if prepping a value of None. This field corresponds to DRF's ListField, which does support allow_null=True.

    def prep_value(self) -> Any:
>       if len(self.value) > 0 and isinstance(self.value[0], Iterable):
E       TypeError: object of type 'NoneType' has no len()

It seems as though this field should nicely handle null (None) values. Happy to open a PR if folks agree.

I work a lot with related fields which are 1-M, and currently anything that is an array of objects just gets the whole child json dumped into one field.

It would be nice to have some further "flatification" in one way or another

I can imagine two sensible ways to do this nonsense I'm describing:

• 1NF table: "left-join"ing all the columns into one massive table)
• 2NF multiple sheets: a sheet per every key which holds an array, and dropping just the "row" references back into the "parent"

There is a problem occuring with the validation checker. The current one:

def _check_validatation_data(self, data):
        detail_key = "detail"  
        if detail_key in data:  
            return False  
        return True

checks only for 'detail' inside the data and this creates a problem when a custom exception handling exists or even if some response actually has the key "detail" inside.

Proposal: Validate via the response status code. If it is in the range of HTTP 2xx it's True.

I have a model with column UUID id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)

So when I use it as render class:

renderer_classes = (XLSXRenderer, ) I get: ValueError: Cannot convert UUID('my_uuid') to Excel

This is a very useful package, but I encountered a small problem in use. Can we use it to include multiple sheet tags in the same Excel, and can set header information, etc.