wharton / drf-excel Goto Github PK

Currently the package exporting data in xlsx format. can we export tabular data in CSV format?

some fileds satisfied the fellowing condition: isinstance(self.drf_field, DateField) is True but actual the type of the fields is datetime.date, so it return null accding the coding . i think it is a problem, it set null for existing value.

so i add a return as below and solve the problem. pls check it

        try:
            if (
                isinstance(self.drf_field, DateTimeField)
                and type(value) != datetime.datetime
            ):
                return self._parse_date(
                    value, "DATETIME_FORMAT", parse_datetime
                ).replace(tzinfo=None)
            elif isinstance(self.drf_field, DateField) and type(value) != datetime.date:
                return self._parse_date(value, "DATE_FORMAT", parse_date)
            elif isinstance(self.drf_field, TimeField) and type(value) != datetime.time:
                return self._parse_date(value, "TIME_FORMAT", parse_time).replace(
                    tzinfo=None
                )
            return value  #  add this code!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I tried to use this package for my drf application
this is my ViewSet which i defined in my view.py

class HolidayExportViewSet(XLSXFileMixin, ReadOnlyModelViewSet):
    queryset = Data.objects.all()
    serializer_class = data_serializer
    renderer_classes = (XLSXRenderer,)
    filename = 'my_export.xlsx'

but unfortunately i got following error

[13/Sep/2023 15:36:19] "GET /user/data-export/ HTTP/1.1" 500 121066
Internal Server Error: /user/data-export/
Traceback (most recent call last):
  File "/home/dev/projects/lab/test_project/.venv/lib/python3.8/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/home/dev/projects/lab/test_project/.venv/lib/python3.8/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/dev/projects/lab/test_project/.venv/lib/python3.8/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/home/dev/projects/lab/test_project/.venv/lib/python3.8/site-packages/rest_framework/viewsets.py", line 125, in view
    return self.dispatch(request, *args, **kwargs)
  File "/home/dev/projects/lab/test_project/.venv/lib/python3.8/site-packages/rest_framework/views.py", line 509, in dispatch
    response = self.handle_exception(exc)
  File "/home/dev/projects/lab/test_project/.venv/lib/python3.8/site-packages/rest_framework/views.py", line 469, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/home/dev/projects/lab/test_project/.venv/lib/python3.8/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
    raise exc
  File "/home/dev/projects/lab/test_project/.venv/lib/python3.8/site-packages/rest_framework/views.py", line 506, in dispatch
    response = handler(request, *args, **kwargs)
  File "/home/dev/projects/lab/test_project/src/hr_management/views/data_export_view.py", line 16, in list
    return super(DataExportViewSet, self).list(request, *args, **kwargs)
  File "/home/dev/projects/lab/test_project/.venv/lib/python3.8/site-packages/rest_framework/mixins.py", line 45, in list
    serializer = self.get_serializer(queryset, many=True)
  File "/home/dev/projects/lab/test_project/.venv/lib/python3.8/site-packages/rest_framework/generics.py", line 110, in get_serializer
    return serializer_class(*args, **kwargs)
TypeError: 'module' object is not callable
[13/Sep/2023 15:36:22] "GET /user/data-export/ HTTP/1.1" 500 121066

I noticed everything is formatted as a string, mostly due to the sanitization function.
I started looking into it and I think I found a way to customize cell formats etc.

This is probably similar to #4 and I think also overlaps a bit of #49
I also think this would provide the basis for dealing with #44

My idea here is to create XLSXField classes for each type, map it from drf types and value types, and then deal with the formatting of each type directly in the XLSXField class.

For example XLSXNumberField would map from drf IntegerField, FloatField or DecimalField, and also from values of type int, float or decimal.

I will be updating #50 for this.
Hope this is okay

hello, after to see the README.MD. I never to know about how to add some dynamic label .they don't in serializer , but could in results. something in README.MD is not detailed.
Expect more of your instruction.

I'm looking at the general use case I have with large files, and it seems as if using xlsxwriter would be faster than OpenPyXL. Can this be an option? I don't see anything to this ends in the documentation.

Hey,

I tried to used something like this:

some_date = serializers.DateField(format="%m-%d-%Y", source="one_step.two_step.my_some_date", allow_null=True")

So, when I tried to export, all that columns come blank. That's why we will have a moment that one item will bring us a NoneType value.

Hi there, In the examples in the readme.md the imports are drf_excel and def_excel, I guess the latter is a typo? Thanks!

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

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

This class from example should have "color" changed to "row_color", as described here https://stackoverflow.com/questions/57709727/drf-formatting-xlsx-content

The default common media_type for xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Is it possible to support this media_type. Maybe in configuration or via a classmethod.

For now I resorted to use :

from django.apps import AppConfig
from drf_excel.renderers import XLSXRenderer

class AppConfig(AppConfig):
    name = "app.app"
    verbose_name = _("app")

def ready(self):
        XLSXRenderer.media_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

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.

Hi @FlipperPA

I would like to help maintain this project (reviewing PRs & creating releases). We use this package quite heavily at my company and it would be great to give back (and maybe speed up the process PR to new release 😉).

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

Having a little trouble figuring out how to get this library to work. It's not quite as simple as:

@action(detail=True, methods=['get'], renderer_classes=(XLSXRenderer,))
def report(self, request, *args, **kwargs):
    instance = self._get_cached_object()

    wb = Workbook()
    ws = wb.create_sheet("Mysheet", 0)
    ws.title = "range names"
    for row in range(1, 40):
        ws.append(range(600))

    return Response(wb)

>> KeyError: 'Worksheet 0 does not exist.'

So a little guidance would be appreciated for those of us who can't figure it out!

If you have a list of strings as value, the XLSXListField incorrectly preps it using json.dumps().

I believe this is not an intended behaviour, judging from the code comments.

This probably has to do with the fact that str is also an Iterable in newer versions of Python.

One potential solution that I found is:

https://stackoverflow.com/a/44328500/10955067

Workaround

Use xlsx_custom_mappings:

xlsx_custom_mappings = {
        col: lambda value: ", ".join(map(str, value))
        for col in [
            "col_1", "col_2", "col_3
        ]
    }

Hello, thanks for the library, it is a great contribution and very useful.

Is it possible to set the column width, automatic for each column take the minimum possible width?

Thanks

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 package is powerfull, formatting and the like support, nice. But I'm struggling with basic stuff: when adding only the titles key to my column_header config attribute of my view, I have to add dict keys of my rows in the correct order. That's the only way I found, how to assign these keys to the correct column in the xlsx...hopefully, I'm missing something obvious?!

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.

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.

For tables which contains sensitive data, How to export the excel as password protected file.

ie, only if we enter the password the excel file should open.

When you use given renderer on empty sequence, it raises an exception:

Traceback (most recent call last):
  File "/usr/local/lib/python2.7/site-packages/django/core/handlers/exception.py", line 41, in inner
    response = get_response(request)
  File "/usr/local/lib/python2.7/site-packages/django/core/handlers/base.py", line 217, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/usr/local/lib/python2.7/site-packages/django/core/handlers/base.py", line 215, in _get_response
    response = response.render()
  File "/usr/local/lib/python2.7/site-packages/django/template/response.py", line 107, in render
    self.content = self.rendered_content
  File "/usr/local/lib/python2.7/site-packages/rest_framework/response.py", line 72, in rendered_content
    ret = renderer.render(self.data, accepted_media_type, context)
  File "/usr/local/lib/python2.7/site-packages/drf_renderer_xlsx/renderers.py", line 120, in render
    for column_name in results[0].keys():
IndexError: list index out of range

Please add proper handling there. It should work fine with empty results (then it should show only header).

I get the following error when I use this package:
{
"detail": "Could not satisfy the request Accept header."
}

As a user I want to get returned file based on my request. For example, I whant to return file name based on request path:

• GET /users -> will return file name 'users_list.xlsx'
• GET /users/2 -> will return file name 'user_2_details.xlsx'

That is pretty easy to do: add to XLSXFileMixin.get_filename method parameters (self, request, *args, **kwargs) and pass them from finalize_response method. Thus developer may override XLSXFileMixin with correct file name getter based on request processing.

So for reasons I have the endpoint and I do some various manipulation aggregating various stuff (without a model) and generating a dict with the data that I want in the exported file.
An example of my code:

class Export(XLSXFileMixin, Viewset):

    queryset = ""
    renderer_classes = (XLSXRenderer,)
    filename = "export.xlsx"

    http_method_names = [
        "get",
    ]

    def list(self, request):        
        new_output = {}
        if self.request.query_params.get("export") == "data":
            self.column_header = {
                "titles": [
                    [...]
                ]
            }
    		[my code here]
        return self.response(new_output)

I get

AssertionError at /api/export/

'Export' should either include a `serializer_class` attribute, or override the `get_serializer_class()` method.

So What is the right serializer to use to get just this dict in the generated file?

Breaks with djangorestframework 3.14.x
https://www.django-rest-framework.org/community/release-notes/#3140

ImportError: cannot import name 'NullBooleanField' from 'rest_framework.fields'

Added a PR to fix: #65

If I have a integerField, the excel column will be number
but if I have a method in my model or serializer that return int, the excel column will be text
for ex:

class Statuses(models.IntegerChoices):
    SUPERVISOR = 0, _("awaiting supervisor's approval")
    EXPERT = 1, _("awaiting expert's approval")
    ACTIVE = 2, _("active")
    EXPIRE = 3, _("expired")
    EXTENDED = 4, _("extended")
    REJECT = -1, _("rejected")

    user = models.ForeignKey(User, on_delete=models.CASCADE)
    service = models.ForeignKey(Service, on_delete=models.CASCADE)
    status = models.IntegerField(choices=Statuses.choices, default=1)
    start_date = models.DateField(null=True, blank=True)
    duration = models.PositiveBigIntegerField(null=False)
    buy_date_time = models.DateTimeField(auto_now_add=True)
    paid_credit = models.PositiveIntegerField(null=True)

    @property
    def duration_hours(self):
        return self.duration // 3600000

    @property
    def total_price(self):
        return self.service.price * self.duration_hours

and a serializer with all fields, duration column will be number but total_price will be text
I seek in documentation but I didn't find a solution
use format for column data style does not work

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"

Hey

With a DateField or DateTimeField serializers, I'm not able to use the format attribute to manipulate date to get my own output.

In the readme it is missing an x from the end, should say pip install drf-renderer-xlsx

also couple of small other small things - in the renderer it will support unicode output if you put a u' in front of the substitution str:

        for column in row.items():
            column_count += 1
            ws.cell(
                row=row_count,
                column=column_count,
                **value=u'{}'.format(**
                    column[1],
                ), 

and it goes wrong if you don't have a paginated results set, which you might not if you want to download all - a work around would be something like:

results = data['results'] if 'results' in data else data
for row in results:
# Reset the column count
column_count = 0

Sorry that these aren't in a PR

I got syntax error when using drf-renderer-xlsx. I found the code using f-string which is the new feature of python 3.6. Can I keep on using python 3.5 or the README.md should be updated to Python>=3.6?

does it support formatting for specific columns? Example, left align for text columns and right align for numbers.

I get this error message when trying to do export to xlsx file:
I'm using python 3.6.5 on windows 10 64bit

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!

drf_excel.renderers.XLSXRenderer uses openpyxl.writer.excel.save_virtual_workbook which has been deprecated in openpyxl 2.6 (2018) and was removed in openpyxl 3.1.0.

The above causes that projects using this library fail with an import error.

I created #72 to solve this issue. If the proposed solution is not the accepted then It would be great to accept #71 in the meantime.

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.

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

I was wondering if it would be possible to have global date/time formats, to be set in settings.py similar to both django and django rest framework formats: DATETIME_FORMAT, DATE_FORMAT, TIME_FORMAT

The reason is that the django rest framework API is more meant to be accessed programmatically, and for that the date format makes sense to be ISO format (standard, easier to parse etc. in many languages)

However, XLSX is meant to be human readable, and ISO format just doesn't make much sense in that regard. Considering dates are stored in UTC a lot, this makes it confusing in Excel and Excel itself doesn't deal with ISO formats.

Hi,
Unless I missed something obvious, when I tried to export using XLSX, I get an error due to ManyToMany fields.
Specifically, line 332 of renderers.py:
https://github.com/wharton/drf-renderer-xlsx/blob/82af43b879aad2a37b0d1115f5f830bf5c822314/drf_renderer_xlsx/renderers.py#L330-L332

The problem seems to be that if it's a list of non string objects, it throws an error.
And by default for ManyToMany fields, django sends back a list of int (the ids)
It would probably make sense to flatten the list by calling str() on each element of the list?

Something like this:
list_sep.join(map(str,v))