# django-xlsx-serializer
[][pypi]
[][pypi]
[][pypi]
[][pypi]
[][pre-commit]
[][python-ci]
[][codecov]
[][nox]
[][ruff]
[][mypy]
[][prettier]
[][conventional-commits]
## Overview
`django-xlsx-serializer` is a [Django][django] application designed to handle
the data serialization and deserialization between Django models and Microsoft
Excel 2007+ workbooks. Utilizing the [OpenPyXL][openpyxl] engine, this tool
provides robust methods to export data from Django databases into XLSX files and
import data from the files back into the databases. This functionality is
essential for applications that require data exchange between Django-based
systems and Excel, facilitating such tasks as data migration, reporting, and
backups.
## Features
The app allows you to:
- Export Django models from a database to an Excel workbook via the
[`dumpdata`][django-dumpdata] command.
- Populate databases from Excel fixtures using the [`loaddata`][django-loaddata]
command.
- Interact with Excel workbooks (either files or `openpyxl.Workbook` objects)
and the database using the Django's core [serialization][django-serialization]
utilities.
## Requirements
| Python | Django | Database engines |
| :----- | :--------------------------- | :------------------ |
| 3.9 | 3.2, 4.0, 4.1, 4.2 | SQLite3, PostgreSQL |
| 3.10 | 3.2, 4.0, 4.1, 4.2, 5.0, 5.1 | SQLite3, PostgreSQL |
| 3.11 | 4.1, 4.2, 5.0, 5.1 | SQLite3, PostgreSQL |
| 3.12 | 4.2, 5.0, 5.1 | SQLite3, PostgreSQL |
All setups require OpenPyXL < 4.
## Installation
The fastest way to add the package to your Python environment is to download and
install it directly from [PyPI][pypi]. Use `pip`:
```console
pip install django-xlsx-serializer
```
or any other dependency manager of your preference.
As soon as the installation is completed, all the app's functionalities can be
accessed from the `xlsx_serializer` module:
```python
import xlsx_serializer
```
> The app is compatible with Excel 2007+ XLSX workbooks only. Adding support for
> the older XLS format is not planned.
## Django Configuration
The app utilities can be incorporated into your Django project by following one
of the approaches listed below:
1. Installing the package as an app.
2. Adding the package to serialization modules.
3. Registering the app's serializers module from another app.
All of them associate the app's serializer with the `xlsx` format.
### Install as an App
In your project settings module add `xlsx_serializers` to `INSTALLED_APPS`:
```python
INSTALLED_APPS = [
# ...
"xlsx_serializer",
# ...
]
```
### Add to Serialization Modules
In your project settings module update the `SERIALIZATION_MODULES` dictionary:
```python
SERIALIZATION_MODULES = {
# ...
"xlsx": "xlsx_serializer",
# ...
}
```
### Register from Another App
In any of the apps installed in your projects (let us call it `myapp`), register
the `xlsx_serializer` manually in the app's `ready` hook:
```python
# myapp/apps.py
from django.apps import AppConfig
from django.core import serializers
class MyAppConfig(AppConfig):
name = "myapp"
def ready(self) -> None:
super().ready()
# ...
# Register serializers.
serializers.register_serializer("xlsx", "xlsx_serializer")
```
> There are many Django projects using a "core" app for defining project-wide
> utilities (e.g., custom commands, template tags, etc.). The configuration
> class of such an app is a good place to apply the code snippet above.
## Usage
### Excel Workbooks vs. Django Models
The app adopts quite intuitive correspondence between Excel workbooks (i.e., the
collections of worksheets) and Django models:
- A Django model is represented by a single worksheet.
- In an Excel workbook, the models are identified by worksheet names.
- Within an Excel worksheet, model instances are represented by rows, while the
columns correspond to the model's fields.
### Serialization
Serialization can be run either by the built-in [`dumpdata`][django-dumpdata]
Django management command:
```console
python manage.py dumpdata --format xlsx --output dump.xlsx
```
or from Django interactive shell:
```python
>>> from django.core import serializers
>>> from polls.models import Question
>>> serializers.serialize("xlsx", Question.objects.all(), output="dump.xlsx")
# Prints: <openpyxl.workbook.workbook.Workbook object at ...>
```
Both the command and expression shown above save `dump.xlsx` workbook file. The
latter additionally returns an `openpyxl.Workbook` object, which can be used
later if necessary (e.g., in development or maintenance scripts).
When serializing, the app creates worksheets named using fully qualified model
labels. For example, the `Question` model defined in the `polls` app is
serialized to the "polls.Question" worksheet. Excel does not accept worksheet
names longer than 31 characters. If the model's label is longer, it's truncated.
A useful feature allowing you to circumvent this issue is that the output
worksheet names can be customized using the `model_sheet_names` option. So, the
command:
```python
>>> workbook = serialize(
"xlsx",
Question.objects.all(),
model_sheet_names={"polls.Question": "Questions"},
)
>>> workbook
# Prints: <openpyxl.workbook.workbook.Workbook object at ...>
```
results in the `polls.Question` model data serialized in the "Questions"
worksheet. Note that this option is not available when using the app via the
`dumpdata` command.
> The app inspects each key and value of the `model_sheet_names` dictionary. For
> the keys, it validates whether they represent valid model identifiers. The
> values, in turn, are checked to see if they are unique, are not too long, and
> do not contain invalid characters (`?`, `*`, `:`, `\`, `/`, `[`, `]`).
Other key points:
- `DateField`, `DateTimeField`, and `TimeField` values are serialized as
ISO 8601 strings.
- `JSONField` values are serialized as JSON strings returned by the respective
field's encoders.
- `ManyToManyField` values are serialized as stringified lists of foreign keys.
- The app supports serialization by using natural keys. If it is triggered (by
applying the `--natural-primary`/`--natural-foreign` flags), the natural keys
are serialized as stringified tuples (or their lists in the case of
many-to-many relations).
### Deserialization
The recommended way of employing the app to load the model data from an Excel
fixture to the database is to call it via the [`loaddata`][django-loaddata]
command:
```console
python manage.py loaddata fixture.xlsx
```
Deserialization requires the input workbook's worksheets to have names that are
either the fully qualified labels or model names (case-insensitive). The latter
can be applied if the model name is unique. For example, if the project uses
models `polls.Question` and `exams.Question`, the worksheet named "Question"
will not be deserialized.
Within a worksheet, ensure that the column headers correspond to the field names
of the respective model. The app ignores a column if it does not represent
a field. Empty rows and columns surrounding the data range are ignored as well.
However, the app does not check the data for the missing or invalid values.
Other key points:
- Populating `DateField`, `DateTimeField`, and `TimeField` with timezone support
enabled in Django settings requires date/time values to be saved as ISO 8601
strings (date/time type values in Excel don't store timezone information).
- Deserializing `JSONfield` requires values in a format compatible with the JSON
decoder of the respective field.
- In the case of `ManyToManyField` provide string representations of Python
lists containing the primary (or natural, see the next bullet) keys of the
related objects.
- The app handles deserialization from natural keys by using `ast.literal_eval`.
Make sure to provide the keys that are valid string representations of the
corresponding values (i.e., tuples of primitive Python literals; in most
cases, they are strings &nddash; if so, use single quotes as text delimiters).
## Contributing
This is an open-source project that embraces contributions of all types. We
require all contributors to adhere to our [Code of Conduct][code-of-conduct].
For comprehensive instructions on how to contribute to the project, please refer
to our [Contributing Guide][contributing].
## Authors
Created and maintained by Kamil Paduszyński ([@paduszyk][paduszyk]).
## License
Released under the [MIT license][license].
[code-of-conduct]: https://github.com/paduszyk/django-xlsx-serializer/blob/main/docs/CODE_OF_CONDUCT.md
[codecov]: https://app.codecov.io/gh/paduszyk/django-xlsx-serializer
[contributing]: https://github.com/paduszyk/django-xlsx-serializer/blob/main/docs/CONTRIBUTING.md
[conventional-commits]: https://www.conventionalcommits.org/en/v1.0.0/
[django-dumpdata]: https://docs.djangoproject.com/en/5.0/ref/django-admin/#dumpdata
[django-loaddata]: https://docs.djangoproject.com/en/5.0/ref/django-admin/#loaddata
[django-serialization]: https://docs.djangoproject.com/en/5.0/topics/serialization/
[django]: https://www.djangoproject.com
[license]: https://github.com/paduszyk/django-xlsx-serializer/blob/main/LICENSE
[mypy]: https://mypy.readthedocs.io
[nox]: https://github.com/wntrblm/nox
[openpyxl]: https://openpyxl.readthedocs.io/en/stable/
[paduszyk]: https://github.com/paduszyk
[pre-commit]: https://github.com/paduszyk/django-xlsx-serializer/actions/workflows/pre-commit-run.yml
[prettier]: https://prettier.io
[pypi]: https://pypi.org/project/django-xlsx-serializer/
[python-ci]: https://github.com/paduszyk/django-xlsx-serializer/actions/workflows/python-ci.yml
[ruff]: https://docs.astral.sh/ruff/
Raw data
{
"_id": null,
"home_page": null,
"name": "django-xlsx-serializer",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.9",
"maintainer_email": null,
"keywords": "django, django-application, excel, excel-export, excel-import",
"author": null,
"author_email": "Kamil Paduszy\u0144ski <92403542+paduszyk@users.noreply.github.com>",
"download_url": "https://files.pythonhosted.org/packages/36/28/6a328e3f738ffe85ca60f08bcecb89346b3d16a0a8d9fabd09125535502b/django_xlsx_serializer-1.0.1.tar.gz",
"platform": null,
"description": "# django-xlsx-serializer\n\n[][pypi]\n[][pypi]\n[][pypi]\n[][pypi]\n\n[][pre-commit]\n[][python-ci]\n[][codecov]\n\n[][nox]\n[][ruff]\n[][mypy]\n[][prettier]\n[][conventional-commits]\n\n## Overview\n\n`django-xlsx-serializer` is a [Django][django] application designed to handle\nthe data serialization and deserialization between Django models and Microsoft\nExcel 2007+ workbooks. Utilizing the [OpenPyXL][openpyxl] engine, this tool\nprovides robust methods to export data from Django databases into XLSX files and\nimport data from the files back into the databases. This functionality is\nessential for applications that require data exchange between Django-based\nsystems and Excel, facilitating such tasks as data migration, reporting, and\nbackups.\n\n## Features\n\nThe app allows you to:\n\n- Export Django models from a database to an Excel workbook via the\n [`dumpdata`][django-dumpdata] command.\n- Populate databases from Excel fixtures using the [`loaddata`][django-loaddata]\n command.\n- Interact with Excel workbooks (either files or `openpyxl.Workbook` objects)\n and the database using the Django's core [serialization][django-serialization]\n utilities.\n\n## Requirements\n\n| Python | Django | Database engines |\n| :----- | :--------------------------- | :------------------ |\n| 3.9 | 3.2, 4.0, 4.1, 4.2 | SQLite3, PostgreSQL |\n| 3.10 | 3.2, 4.0, 4.1, 4.2, 5.0, 5.1 | SQLite3, PostgreSQL |\n| 3.11 | 4.1, 4.2, 5.0, 5.1 | SQLite3, PostgreSQL |\n| 3.12 | 4.2, 5.0, 5.1 | SQLite3, PostgreSQL |\n\nAll setups require OpenPyXL < 4.\n\n## Installation\n\nThe fastest way to add the package to your Python environment is to download and\ninstall it directly from [PyPI][pypi]. Use `pip`:\n\n```console\npip install django-xlsx-serializer\n```\n\nor any other dependency manager of your preference.\n\nAs soon as the installation is completed, all the app's functionalities can be\naccessed from the `xlsx_serializer` module:\n\n```python\nimport xlsx_serializer\n```\n\n> The app is compatible with Excel 2007+ XLSX workbooks only. Adding support for\n> the older XLS format is not planned.\n\n## Django Configuration\n\nThe app utilities can be incorporated into your Django project by following one\nof the approaches listed below:\n\n1. Installing the package as an app.\n2. Adding the package to serialization modules.\n3. Registering the app's serializers module from another app.\n\nAll of them associate the app's serializer with the `xlsx` format.\n\n### Install as an App\n\nIn your project settings module add `xlsx_serializers` to `INSTALLED_APPS`:\n\n```python\nINSTALLED_APPS = [\n # ...\n \"xlsx_serializer\",\n # ...\n]\n```\n\n### Add to Serialization Modules\n\nIn your project settings module update the `SERIALIZATION_MODULES` dictionary:\n\n```python\nSERIALIZATION_MODULES = {\n # ...\n \"xlsx\": \"xlsx_serializer\",\n # ...\n}\n```\n\n### Register from Another App\n\nIn any of the apps installed in your projects (let us call it `myapp`), register\nthe `xlsx_serializer` manually in the app's `ready` hook:\n\n```python\n# myapp/apps.py\n\nfrom django.apps import AppConfig\nfrom django.core import serializers\n\n\nclass MyAppConfig(AppConfig):\n name = \"myapp\"\n\n def ready(self) -> None:\n super().ready()\n\n # ...\n\n # Register serializers.\n serializers.register_serializer(\"xlsx\", \"xlsx_serializer\")\n```\n\n> There are many Django projects using a \"core\" app for defining project-wide\n> utilities (e.g., custom commands, template tags, etc.). The configuration\n> class of such an app is a good place to apply the code snippet above.\n\n## Usage\n\n### Excel Workbooks vs. Django Models\n\nThe app adopts quite intuitive correspondence between Excel workbooks (i.e., the\ncollections of worksheets) and Django models:\n\n- A Django model is represented by a single worksheet.\n- In an Excel workbook, the models are identified by worksheet names.\n- Within an Excel worksheet, model instances are represented by rows, while the\n columns correspond to the model's fields.\n\n### Serialization\n\nSerialization can be run either by the built-in [`dumpdata`][django-dumpdata]\nDjango management command:\n\n```console\npython manage.py dumpdata --format xlsx --output dump.xlsx\n```\n\nor from Django interactive shell:\n\n```python\n>>> from django.core import serializers\n>>> from polls.models import Question\n>>> serializers.serialize(\"xlsx\", Question.objects.all(), output=\"dump.xlsx\")\n# Prints: <openpyxl.workbook.workbook.Workbook object at ...>\n```\n\nBoth the command and expression shown above save `dump.xlsx` workbook file. The\nlatter additionally returns an `openpyxl.Workbook` object, which can be used\nlater if necessary (e.g., in development or maintenance scripts).\n\nWhen serializing, the app creates worksheets named using fully qualified model\nlabels. For example, the `Question` model defined in the `polls` app is\nserialized to the \"polls.Question\" worksheet. Excel does not accept worksheet\nnames longer than 31 characters. If the model's label is longer, it's truncated.\nA useful feature allowing you to circumvent this issue is that the output\nworksheet names can be customized using the `model_sheet_names` option. So, the\ncommand:\n\n```python\n>>> workbook = serialize(\n \"xlsx\",\n Question.objects.all(),\n model_sheet_names={\"polls.Question\": \"Questions\"},\n )\n>>> workbook\n# Prints: <openpyxl.workbook.workbook.Workbook object at ...>\n```\n\nresults in the `polls.Question` model data serialized in the \"Questions\"\nworksheet. Note that this option is not available when using the app via the\n`dumpdata` command.\n\n> The app inspects each key and value of the `model_sheet_names` dictionary. For\n> the keys, it validates whether they represent valid model identifiers. The\n> values, in turn, are checked to see if they are unique, are not too long, and\n> do not contain invalid characters (`?`, `*`, `:`, `\\`, `/`, `[`, `]`).\n\nOther key points:\n\n- `DateField`, `DateTimeField`, and `TimeField` values are serialized as\n ISO 8601 strings.\n- `JSONField` values are serialized as JSON strings returned by the respective\n field's encoders.\n- `ManyToManyField` values are serialized as stringified lists of foreign keys.\n- The app supports serialization by using natural keys. If it is triggered (by\n applying the `--natural-primary`/`--natural-foreign` flags), the natural keys\n are serialized as stringified tuples (or their lists in the case of\n many-to-many relations).\n\n### Deserialization\n\nThe recommended way of employing the app to load the model data from an Excel\nfixture to the database is to call it via the [`loaddata`][django-loaddata]\ncommand:\n\n```console\npython manage.py loaddata fixture.xlsx\n```\n\nDeserialization requires the input workbook's worksheets to have names that are\neither the fully qualified labels or model names (case-insensitive). The latter\ncan be applied if the model name is unique. For example, if the project uses\nmodels `polls.Question` and `exams.Question`, the worksheet named \"Question\"\nwill not be deserialized.\n\nWithin a worksheet, ensure that the column headers correspond to the field names\nof the respective model. The app ignores a column if it does not represent\na field. Empty rows and columns surrounding the data range are ignored as well.\nHowever, the app does not check the data for the missing or invalid values.\n\nOther key points:\n\n- Populating `DateField`, `DateTimeField`, and `TimeField` with timezone support\n enabled in Django settings requires date/time values to be saved as ISO 8601\n strings (date/time type values in Excel don't store timezone information).\n- Deserializing `JSONfield` requires values in a format compatible with the JSON\n decoder of the respective field.\n- In the case of `ManyToManyField` provide string representations of Python\n lists containing the primary (or natural, see the next bullet) keys of the\n related objects.\n- The app handles deserialization from natural keys by using `ast.literal_eval`.\n Make sure to provide the keys that are valid string representations of the\n corresponding values (i.e., tuples of primitive Python literals; in most\n cases, they are strings &nddash; if so, use single quotes as text delimiters).\n\n## Contributing\n\nThis is an open-source project that embraces contributions of all types. We\nrequire all contributors to adhere to our [Code of Conduct][code-of-conduct].\nFor comprehensive instructions on how to contribute to the project, please refer\nto our [Contributing Guide][contributing].\n\n## Authors\n\nCreated and maintained by Kamil Paduszy\u0144ski ([@paduszyk][paduszyk]).\n\n## License\n\nReleased under the [MIT license][license].\n\n[code-of-conduct]: https://github.com/paduszyk/django-xlsx-serializer/blob/main/docs/CODE_OF_CONDUCT.md\n[codecov]: https://app.codecov.io/gh/paduszyk/django-xlsx-serializer\n[contributing]: https://github.com/paduszyk/django-xlsx-serializer/blob/main/docs/CONTRIBUTING.md\n[conventional-commits]: https://www.conventionalcommits.org/en/v1.0.0/\n[django-dumpdata]: https://docs.djangoproject.com/en/5.0/ref/django-admin/#dumpdata\n[django-loaddata]: https://docs.djangoproject.com/en/5.0/ref/django-admin/#loaddata\n[django-serialization]: https://docs.djangoproject.com/en/5.0/topics/serialization/\n[django]: https://www.djangoproject.com\n[license]: https://github.com/paduszyk/django-xlsx-serializer/blob/main/LICENSE\n[mypy]: https://mypy.readthedocs.io\n[nox]: https://github.com/wntrblm/nox\n[openpyxl]: https://openpyxl.readthedocs.io/en/stable/\n[paduszyk]: https://github.com/paduszyk\n[pre-commit]: https://github.com/paduszyk/django-xlsx-serializer/actions/workflows/pre-commit-run.yml\n[prettier]: https://prettier.io\n[pypi]: https://pypi.org/project/django-xlsx-serializer/\n[python-ci]: https://github.com/paduszyk/django-xlsx-serializer/actions/workflows/python-ci.yml\n[ruff]: https://docs.astral.sh/ruff/\n",
"bugtrack_url": null,
"license": null,
"summary": "Load/dump Django models from/to Excel 2007+ workbooks",
"version": "1.0.1",
"project_urls": {
"Repository": "https://github.com/paduszyk/django-xlsx-serializer"
},
"split_keywords": [
"django",
" django-application",
" excel",
" excel-export",
" excel-import"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "95fac0ab8443942246f3e720e20dac8c6ce3f56571f231282a41543319082150",
"md5": "658817747f6420906668617133f4bf24",
"sha256": "ad81a1260b7056714b5e773ae2dd4091222827b6816420bc258b4fd48758da1d"
},
"downloads": -1,
"filename": "django_xlsx_serializer-1.0.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "658817747f6420906668617133f4bf24",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.9",
"size": 11807,
"upload_time": "2024-08-09T20:28:09",
"upload_time_iso_8601": "2024-08-09T20:28:09.598104Z",
"url": "https://files.pythonhosted.org/packages/95/fa/c0ab8443942246f3e720e20dac8c6ce3f56571f231282a41543319082150/django_xlsx_serializer-1.0.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "36286a328e3f738ffe85ca60f08bcecb89346b3d16a0a8d9fabd09125535502b",
"md5": "42f0e0d556cd7b480f1b1a6fc01c0439",
"sha256": "ea3e6b3c406327223ae1d3970907721d0f39ceff2c771d72dd02a704e048f983"
},
"downloads": -1,
"filename": "django_xlsx_serializer-1.0.1.tar.gz",
"has_sig": false,
"md5_digest": "42f0e0d556cd7b480f1b1a6fc01c0439",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9",
"size": 12429,
"upload_time": "2024-08-09T20:28:11",
"upload_time_iso_8601": "2024-08-09T20:28:11.009950Z",
"url": "https://files.pythonhosted.org/packages/36/28/6a328e3f738ffe85ca60f08bcecb89346b3d16a0a8d9fabd09125535502b/django_xlsx_serializer-1.0.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-08-09 20:28:11",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "paduszyk",
"github_project": "django-xlsx-serializer",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "django-xlsx-serializer"
}
JSON
