wharton / drf-excel Goto Github PK
View Code? Open in Web Editor NEWAn XLSX spreadsheet renderer for Django REST Framework.
License: BSD 3-Clause "New" or "Revised" License
An XLSX spreadsheet renderer for Django REST Framework.
License: BSD 3-Clause "New" or "Revised" License
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
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:
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:
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
Request Method: | GET |
---|---|
http://192.168.1.154:8005/api/transaction/excel/taxi/?start=2019-12-30&end=2019-12-30&search= | |
2.2.4 | |
UnboundLocalError | |
local variable 'column_names_first_row' referenced before assignment | |
C:\Users\WEBDEV-5\AppData\Local\Programs\Python\Python36\lib\site-packages\drf_renderer_xlsx\renderers.py in render, line 122 | |
C:\Users\WEBDEV-5\AppData\Local\Programs\Python\Python36\python.exe | |
3.6.5 | |
['D:\Projects\Python\nguraharaitaxi-backend', 'C:\Users\WEBDEV-5\AppData\Local\Programs\Python\Python36\python36.zip', 'C:\Users\WEBDEV-5\AppData\Local\Programs\Python\Python36\DLLs', 'C:\Users\WEBDEV-5\AppData\Local\Programs\Python\Python36\lib', 'C:\Users\WEBDEV-5\AppData\Local\Programs\Python\Python36', 'C:\Users\WEBDEV-5\AppData\Local\Programs\Python\Python36\lib\site-packages'] | |
Sen, 30 Des 2019 17:23:42 +0800 |
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
}
]
class SecurityEventsGetXLSXViewset(XLSXFileMixin, ReadOnlyModelViewSet):
""" Generate XLSX File """
queryset = models.SecurityEvents.objects.all()
serializer_class = serializers.SecurityEventsGetSerializer
renderer_classes = (XLSXRenderer,)
filename = 'SecurityEventsGet_export.xlsx'
class SecurityEventsGetSerializer(serializers.ModelSerializer):
rfid_tag = RfidTagNestedSerializer()
class Meta:
model = models.SecurityEvents
fields = ['id', 'rfid_tag', 'security_location', 'aknowledged']
read_only_fields = ['created_at']
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))
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.