# django-spreadsheet-serializer
[](https://results.pre-commit.ci/latest/github/paduszyk/django-spreadsheet-serializer/main)
[](https://github.com/paduszyk/django-spreadsheet-serializer/actions/workflows/ci.yaml)
[](https://codecov.io/gh/paduszyk/django-spreadsheet-serializer)
[](https://github.com/psf/black)
[](https://flake8.pycqa.org)
[](https://pycqa.github.io/isort)
[](LICENSE)
[](https://gitmoji.dev)
## Summary
`django-spreadsheet-serializer` facilitates model data exchange between the Django model API and commonly used spreadsheet formats using built-in `dumpdata`/`loaddata` management commands.
> NOTE: Serialization part, ref. to `dumpdata` command is not implemented yet.
If you have some data in Microsoft Excel you would like to upload to your Django project or you need to back up your database to a spreadsheet, the app is what you need!
## Installation
Install the latest version of the package directly from PyPI with your favorite dependency management tool, preferably `pip`:
```bash
pip install django-spreadsheet-serializer
```
If you take care about the disk space, be aware that the package is based on `pandas`!
## Configuration
The app does not require any extra configuration, except having `django >= 3.2` (obviously) and its `contenttypes` and `auth` apps (from the batteries-included `contrib` package) installed. To incorporate its functionality, just append `spreadsheet_serializer` to `INSTALLED_APPS` of your Django setting module:
```python
INSTALLED_APPS = [
# ...
"django.contrib.auth",
"django.contrib.contenttypes",
# ...
"spreadsheet_serializer",
# ...
]
```
This update will register the serializer classes provided with the package as Django starts.
### Fixture formats
The app has been tested using the following spreadsheet formats:
- Microsoft Excel ([`*.xlsx`](https://docs.fileformat.com/spreadsheet/xlsx/); since Microsoft Office 2007);
- Open Document Format spreadsheets ([`*.ods`](https://docs.fileformat.com/spreadsheet/ods/)).
Don't expect the app to work with other spreadsheet formats like Google Sheets (from Google) and Numbers (from Apple).
## Deserialization
Term *deserialization* corresponds to the processes of importing model data from fixture spreadsheets, converting them to Django model instances, and saving them in the database.
### Model data in spreadsheets
#### General information
The model data in a spreadsheet should be organized in the same way they are represented in the SQL database, namely, the columns and rows of a spreadsheet correspond to the model's fields and objects, respectively.
Avoid complex formatting in your spreadsheets; keep it clean and simple.
#### Model *vs* sheet
A single spreadsheet (*aka* workbook) file may contain several sheets – just the same as the Django project may consist of a number of apps and models. Referring to that analogy, the following rules apply as the `django-spreadsheet-serializer` app is going to be used:
1. Each sheet represents a single model.
2. The name of the sheet mirrors the full label of the corresponding model.
For example, in the case of the model `Product` installed in the app `store`, the sheet name must be `store.Product` (case-insensitive).
Note that in Microsoft Excel, the sheet name must not be longer than 31 characters.
#### Column headers *vs* field names
Within a sheet (model), **the column headers must be exactly the same as the model fields corresponding to them**. If the sheet contains some extra columns (with some auxiliary information, or comments), the app's deserializer will ignore them automatically.
#### Cell values *vs* field values
The table of supported and tested fields (all of them are from `django.db.models.fields` module) is as follows:
| Type | Fields |
| --- | --- |
| descriptive fields | `CharField`, `TextField`, `SlugField`, `IntegerField` (and subclasses), `DecimalField`, `BooleanField`, `DateField`, `DateTimeField` |
| relation fields | `ForeignKey`, `OneToOneField`, `ManyToManyField` |
Although these lists seem to be short, we believe that the covered fields are usually sufficient to meet data model requirements, even in cases of quite complex business logic.
##### Non-relational ("descriptive") fields
Note that the `django-spreadsheet-serializer` app aims to support deserialization from *spreadsheets*, not from plain text or CSV files. Thus, the values of the cells representing non-relational or descriptive fields should be formatted accordingly.
For example, the values to be deserialized to `BoolField` should be formatted in the spreadsheet as actual booleans, not strings like `"True"`, `"False"`; the same applies to date and time as well as the numeric data.
##### Many-to-one, one-to-one relation fields
The cells in columns representing relation fields (`ForeignKey`, `OneToOneField`) must contain the primary keys (PKs) of related objects. This is for sure one of the weakest point of the app. Deserialization using [natural keys](https://docs.djangoproject.com/en/4.2/topics/serialization/#natural-keys) is planned to be implemented soon.
The only exception to this rule is the field relating to `contenttypes.ContentType` model. This is because, the PKs of `ContentType` objects usually aren't under the control of the developer and (to our best knowledge) may depend on different factors (like the number of apps or models in the project). Therefore, the string representing the full label of the model pinned to a related `ContentType` object is sufficient to deserialize such a field.
##### Many-to-many fields
A column representing `ManyToManyField` field must contain strings with comma-separated lists of PKs of the related models. No extra tables or sheets have to be created to account for many-to-many relations, as it is done on the migration/SQL level in Django.
### Loading fixtures
To load/deserialize the spreadsheet fixture described above, use the built-in Django management command [`loaddata`](https://docs.djangoproject.com/en/4.2/ref/django-admin/#loaddata):
```shell
# Using manage.py script
python manage.py loaddata fixture [fixture]
# Using django-admin
python manage.py loaddata fixture [fixture]
```
Django will detect deserializer to be used based on the fixture file extension. If you want to be more specific, pass `--format` option to the command, with either `xlsx` or `ods` value.
## Testing
Feel free to clone/fork this repository to add features and look for bugs:
```bash
git clone https://github.com/paduszyk/django-spreadsheet-serializer.git && cd django-spreadsheet-serializer
```
We strongly encourage you to develop the package reproduced within a virtual environment created using `venv`:
```bash
python -m venv .venv && . .venv/bin/activate
```
> The name `.venv` is recommended as it is excluded from `flake8` paths (see [`pyproject.toml`](pyproject.toml)). In the activated virtual environment, install both main and development dependencies:
```bash
pip install -r requirements.txt -r requirements-dev.txt
```
Then, install and run `pre-commit` hooks:
```bash
pre-commit install && pre-commit run --all-files
```
Alternatively, run linters using `tox` (see [`tox.ini`](tox.ini)):
```bash
tox -e lint
```
To finally run tests, execute `pytest` (on the active virtual environment's Python and dependencies) or `tox` (on multiple testing environments using different versions of both Python and Django).
## Contributing
You are strongly encouraged to contribute to the project by:
- opening an issue on a bug report or feature request;
- forking the repository and opening pull requests (PRs);
- participating in discussion regarding open issues and/or PRs.
For more information, see the official [GitHub documentation](https://docs.github.com/en/get-started/quickstart/contributing-to-projects) on contributing to open-source projects.
With your contributions, try to follow our convention for naming feature branches and composing commit and PR messages. As you might have noticed, we use [Gitmojis](https://gitmoji.dev) to mark the type of changes introduced by commits or PRs. This is quite a cool feature that we would like to consistently use in the project to keep it clean and easy to follow.
## License
Released under the [MIT license](LICENSE).
Raw data
{
"_id": null,
"home_page": "",
"name": "django-spreadsheet-serializer",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": "Kamil Paduszy\u0144ski <paduszyk@gmail.com>",
"keywords": "Excel,django,ods,python,spreadsheet,xlsx",
"author": "",
"author_email": "Kamil Paduszy\u0144ski <paduszyk@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/94/f8/a2283117d71273d95cf17e4fb9ca6515b503af2c504082ee723155e953d9/django_spreadsheet_serializer-0.1.0rc1.tar.gz",
"platform": null,
"description": "# django-spreadsheet-serializer\n\n[](https://results.pre-commit.ci/latest/github/paduszyk/django-spreadsheet-serializer/main)\n[](https://github.com/paduszyk/django-spreadsheet-serializer/actions/workflows/ci.yaml)\n[](https://codecov.io/gh/paduszyk/django-spreadsheet-serializer)\n\n[](https://github.com/psf/black)\n[](https://flake8.pycqa.org)\n[](https://pycqa.github.io/isort)\n\n[](LICENSE)\n[](https://gitmoji.dev)\n\n## Summary\n\n`django-spreadsheet-serializer` facilitates model data exchange between the Django model API and commonly used spreadsheet formats using built-in `dumpdata`/`loaddata` management commands.\n\n> NOTE: Serialization part, ref. to `dumpdata` command is not implemented yet.\n\nIf you have some data in Microsoft Excel you would like to upload to your Django project or you need to back up your database to a spreadsheet, the app is what you need!\n\n## Installation\n\nInstall the latest version of the package directly from PyPI with your favorite dependency management tool, preferably `pip`:\n\n```bash\npip install django-spreadsheet-serializer\n```\n\nIf you take care about the disk space, be aware that the package is based on `pandas`!\n\n## Configuration\n\nThe app does not require any extra configuration, except having `django >= 3.2` (obviously) and its `contenttypes` and `auth` apps (from the batteries-included `contrib` package) installed. To incorporate its functionality, just append `spreadsheet_serializer` to `INSTALLED_APPS` of your Django setting module:\n\n```python\nINSTALLED_APPS = [\n # ...\n \"django.contrib.auth\",\n \"django.contrib.contenttypes\",\n # ...\n \"spreadsheet_serializer\",\n # ...\n]\n```\n\nThis update will register the serializer classes provided with the package as Django starts.\n\n### Fixture formats\n\nThe app has been tested using the following spreadsheet formats:\n- Microsoft Excel ([`*.xlsx`](https://docs.fileformat.com/spreadsheet/xlsx/); since Microsoft Office 2007);\n- Open Document Format spreadsheets ([`*.ods`](https://docs.fileformat.com/spreadsheet/ods/)).\n\nDon't expect the app to work with other spreadsheet formats like Google Sheets (from Google) and Numbers (from Apple).\n\n## Deserialization\n\nTerm *deserialization* corresponds to the processes of importing model data from fixture spreadsheets, converting them to Django model instances, and saving them in the database.\n\n### Model data in spreadsheets\n\n#### General information\n\nThe model data in a spreadsheet should be organized in the same way they are represented in the SQL database, namely, the columns and rows of a spreadsheet correspond to the model's fields and objects, respectively.\n\nAvoid complex formatting in your spreadsheets; keep it clean and simple.\n\n#### Model *vs* sheet\n\nA single spreadsheet (*aka* workbook) file may contain several sheets – just the same as the Django project may consist of a number of apps and models. Referring to that analogy, the following rules apply as the `django-spreadsheet-serializer` app is going to be used:\n\n1. Each sheet represents a single model.\n2. The name of the sheet mirrors the full label of the corresponding model.\n\nFor example, in the case of the model `Product` installed in the app `store`, the sheet name must be `store.Product` (case-insensitive).\n\nNote that in Microsoft Excel, the sheet name must not be longer than 31 characters.\n\n#### Column headers *vs* field names\n\nWithin a sheet (model), **the column headers must be exactly the same as the model fields corresponding to them**. If the sheet contains some extra columns (with some auxiliary information, or comments), the app's deserializer will ignore them automatically.\n\n#### Cell values *vs* field values\n\nThe table of supported and tested fields (all of them are from `django.db.models.fields` module) is as follows:\n\n| Type | Fields |\n| --- | --- |\n| descriptive fields | `CharField`, `TextField`, `SlugField`, `IntegerField` (and subclasses), `DecimalField`, `BooleanField`, `DateField`, `DateTimeField` |\n| relation fields | `ForeignKey`, `OneToOneField`, `ManyToManyField` |\n\nAlthough these lists seem to be short, we believe that the covered fields are usually sufficient to meet data model requirements, even in cases of quite complex business logic.\n\n##### Non-relational (\"descriptive\") fields\n\nNote that the `django-spreadsheet-serializer` app aims to support deserialization from *spreadsheets*, not from plain text or CSV files. Thus, the values of the cells representing non-relational or descriptive fields should be formatted accordingly.\n\nFor example, the values to be deserialized to `BoolField` should be formatted in the spreadsheet as actual booleans, not strings like `\"True\"`, `\"False\"`; the same applies to date and time as well as the numeric data.\n\n##### Many-to-one, one-to-one relation fields\n\nThe cells in columns representing relation fields (`ForeignKey`, `OneToOneField`) must contain the primary keys (PKs) of related objects. This is for sure one of the weakest point of the app. Deserialization using [natural keys](https://docs.djangoproject.com/en/4.2/topics/serialization/#natural-keys) is planned to be implemented soon.\n\nThe only exception to this rule is the field relating to `contenttypes.ContentType` model. This is because, the PKs of `ContentType` objects usually aren't under the control of the developer and (to our best knowledge) may depend on different factors (like the number of apps or models in the project). Therefore, the string representing the full label of the model pinned to a related `ContentType` object is sufficient to deserialize such a field.\n\n##### Many-to-many fields\n\nA column representing `ManyToManyField` field must contain strings with comma-separated lists of PKs of the related models. No extra tables or sheets have to be created to account for many-to-many relations, as it is done on the migration/SQL level in Django.\n\n### Loading fixtures\n\nTo load/deserialize the spreadsheet fixture described above, use the built-in Django management command [`loaddata`](https://docs.djangoproject.com/en/4.2/ref/django-admin/#loaddata):\n\n```shell\n# Using manage.py script\npython manage.py loaddata fixture [fixture]\n\n# Using django-admin\npython manage.py loaddata fixture [fixture]\n```\nDjango will detect deserializer to be used based on the fixture file extension. If you want to be more specific, pass `--format` option to the command, with either `xlsx` or `ods` value.\n\n## Testing\n\nFeel free to clone/fork this repository to add features and look for bugs:\n```bash\ngit clone https://github.com/paduszyk/django-spreadsheet-serializer.git && cd django-spreadsheet-serializer\n```\n\nWe strongly encourage you to develop the package reproduced within a virtual environment created using `venv`:\n```bash\npython -m venv .venv && . .venv/bin/activate\n```\n\n> The name `.venv` is recommended as it is excluded from `flake8` paths (see [`pyproject.toml`](pyproject.toml)). In the activated virtual environment, install both main and development dependencies:\n```bash\npip install -r requirements.txt -r requirements-dev.txt\n```\n\nThen, install and run `pre-commit` hooks:\n```bash\npre-commit install && pre-commit run --all-files\n```\n\nAlternatively, run linters using `tox` (see [`tox.ini`](tox.ini)):\n```bash\ntox -e lint\n```\n\nTo finally run tests, execute `pytest` (on the active virtual environment's Python and dependencies) or `tox` (on multiple testing environments using different versions of both Python and Django).\n\n## Contributing\n\nYou are strongly encouraged to contribute to the project by:\n\n- opening an issue on a bug report or feature request;\n- forking the repository and opening pull requests (PRs);\n- participating in discussion regarding open issues and/or PRs.\n\nFor more information, see the official [GitHub documentation](https://docs.github.com/en/get-started/quickstart/contributing-to-projects) on contributing to open-source projects.\n\nWith your contributions, try to follow our convention for naming feature branches and composing commit and PR messages. As you might have noticed, we use [Gitmojis](https://gitmoji.dev) to mark the type of changes introduced by commits or PRs. This is quite a cool feature that we would like to consistently use in the project to keep it clean and easy to follow.\n\n## License\n\nReleased under the [MIT license](LICENSE).\n",
"bugtrack_url": null,
"license": "MIT License",
"summary": "Manage Django models using spreadsheets!",
"version": "0.1.0rc1",
"project_urls": {
"Documentation": "https://github.com/paduszyk/django-spreadsheet-serializer#readme",
"Homepage": "https://github.com/paduszyk/django-spreadsheet-serializer",
"Repository": "https://github.com/paduszyk/django-spreadsheet-serializer"
},
"split_keywords": [
"excel",
"django",
"ods",
"python",
"spreadsheet",
"xlsx"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "174120f07d3c4dc675404c14f4bcfef872cae5e098fead018ed5a5d8f0d8165c",
"md5": "0b293b40f616a2bc70d6b44b283c2cb3",
"sha256": "c81dcb678778eebdff2f9018ffa4d13b206365f7d48e0914bf1f40feb689ce9c"
},
"downloads": -1,
"filename": "django_spreadsheet_serializer-0.1.0rc1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "0b293b40f616a2bc70d6b44b283c2cb3",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 9702,
"upload_time": "2023-07-07T22:39:30",
"upload_time_iso_8601": "2023-07-07T22:39:30.775478Z",
"url": "https://files.pythonhosted.org/packages/17/41/20f07d3c4dc675404c14f4bcfef872cae5e098fead018ed5a5d8f0d8165c/django_spreadsheet_serializer-0.1.0rc1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "94f8a2283117d71273d95cf17e4fb9ca6515b503af2c504082ee723155e953d9",
"md5": "a8b2a95697bcfa86e549d3a20f572044",
"sha256": "b1b59ad3ab1451122ebaefb560381591cc1f534d7511545b86efc35a0403bc45"
},
"downloads": -1,
"filename": "django_spreadsheet_serializer-0.1.0rc1.tar.gz",
"has_sig": false,
"md5_digest": "a8b2a95697bcfa86e549d3a20f572044",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 12828,
"upload_time": "2023-07-07T22:39:33",
"upload_time_iso_8601": "2023-07-07T22:39:33.270970Z",
"url": "https://files.pythonhosted.org/packages/94/f8/a2283117d71273d95cf17e4fb9ca6515b503af2c504082ee723155e953d9/django_spreadsheet_serializer-0.1.0rc1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-07-07 22:39:33",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "paduszyk",
"github_project": "django-spreadsheet-serializer#readme",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"requirements": [],
"tox": true,
"lcname": "django-spreadsheet-serializer"
}
JSON
