# django-spreadsheet
Validate your XLSX spreadsheet file before importing it. Export your data to a formatted XLSX spreadsheet with data validation set up.
# Requirements
- Python 3.8+
- Django 2.0+
# Installation
- run `pip install django_spreadsheets`
- add `django_spreadsheets.apps.SpreadsheetAppConfig` to your `INSTALLED_APPS`
- add `path("admin/spreadsheets/", include("django_spreadsheets.urls", namespace="spreadsheets"))` to your root `urls.py`
- run `python manage.py migrate django_spreadsheets`
# Usage
Start to create a file `spreadsheets.py` in your app. Inside this file, describe your spreadsheets configurations such as the following example:
```py
from django_spreadsheets.base_config import SpreadsheetConfig, SheetConfig
from django_spreadsheets.registry import register
from libs.bananas.models import Banana
class BananaSheetConfig(SheetConfig):
"""Describes each columns of this sheet."""
ID = {
"name": "Identifier",
"required": True,
}
COLOR = {
"name": "Color",
"validation": {
"type": "list",
"formula1": ["yellow", "green", "brown"],
},
"required": True,
}
SIZE = {
"name": "Size",
"validation": {
"type": "whole",
"operator": "greaterThan",
"formula1": 0,
"error": "Please enter a positive number",
}
}
def get_data_to_export(self):
return Banana.objects.values_list("id", "color", "size")
@register
class BananaSpreadsheetConfig(SpreadsheetConfig):
sheets_config = {
"Banana": BananaSheetConfig()
# the key will be used as the name of the sheet, the value is a class describing the sheet's configuration.
}
class Meta:
verbose_name = "Bananas"
def update_database(self, sheets):
for sheet_name, rows in sheets.items():
for row in rows:
Banana.objects.get_or_create(
id=row["ID"],
color=row["COLOR"],
size=row["SIZE"],
)
```
Then, you can go to `//localhost:8000/admin/spreadsheets/export/` to export a XSLX file and `//localhost:8000/admin/spreadsheets/import/` to import into your database.
# `SpreadsheetConfig` class
Inherit this class to describe your spreadsheets' configuration. Define a `sheets_config` dict attribute containing the name of your sheet as a key, and a `SheetConfig` class as a value.
## Validations
Several validations are run before importing a spreadsheet:
### Config version validation
Validate that the configuration version stored in the file is the same as the `SpreadsheetConfig.version` used.
### Sheets validation
Validate that all sheets in this `SpreadsheetConfig` class used, exist in imported file. Validate that all sheets in imported file, exist in this `SpreadsheetConfig` class used.
### Columns headers validation
Validate that the imported file contains the same columns headers as described in the `SheetConfig` used.
### Required data validation
Validate that there is a value in the imported file for each `required`, `required_unless`, `required_if` columns.
### Uniqueness validation [/!\ not implemented]
Validate that multiple cells in a row are unique together in the imported file.
### Types validation
Validate that each column in the imported file contains data of correct type (date, positive integer, ...).
### Data match validation
Validate that we find an object in the database matching the value in the imported file. If not, we ask the user to chose from a list of possibles values.
## Import data
If you want to import data in y our database from this spreadsheet, you need to define a `update_database(self, sheets)` method. This method is called after all data validations have been completed, and receive a python dict with an item for each sheet. The key is the name of the sheet, and the value is a python list of rows. Each row is a list containing a value for each column.
```py
def update_database(self, sheets):
for sheet_name, rows in sheets.items():
for row in rows:
for value in row:
print(value)
```
# `SheetConfig` class
Inherit this class to describe your sheets' configuration. Each attribute describes a column using a dictionary such as the following:
```py
<column_identifier> = {
"name": str (required), # The name of the column used in the header row
"required": bool, # Whether a value in the column is required when importing
"required_unless": str, # Whether a value in this column is required if the pointed column is empty
"required_if": str, # Whether a value in this column is required if the pointed column is filled
"comment": str, # A comment that appears in the header cell
"admin_only": bool, # Whether this column should be exported only when the current user is an administrator
"validation": { # A data validation applied to each cell of this column
"name": str, # A name used as header in the hidden config sheet
"type": str (required), # <url|date|list|range|whole...> See openpyxl documentation to get all available validation types
... # Same args as openpyxl's DataValidation objects (operator, formula1, formula2, showErrorMessage, ...)
... # Note that you can pass callables to `formula1` and `formula2` keys
},
"header_style": str, # The name of a style that already exists in the SpreadsheetConfig to which this sheet belongs
"style": str, # The name of a style that already exists in the SpreadsheetConfig to which this sheet belongs
"number_format": str # A value among FORMAT_* constants from openpyxl.styles.numbers or others values supported by XLSX spec
},
```
## Note on validation
To create a _boolean_ validation, you may use a `list` validation type with `django_spreadsheets.constants.YES_NO_CHOICES` as `formula1` value. To ensure a boolean value is displayed in your exported file, add `"number_format": "BOOLEAN"` in your column configuration.
The `formula1` and `formula2` keys accept callables as values.
## Note on number format
When the value in your exported file is not displayed correctly or not consistent across rows, you may define the `number_format` key in your column configuration.
Openxpyxl's format constants are not exhaustive. You may use other supported strings (eg: "BOOLEAN").
## Export data
If you want to export data to this sheet, you need to define a `get_data_to_export(self)` method. This method must return a python list containing an item by row. Each item in the list is a list containing a value for each column.
```py
def get_data_to_export(self):
return [
[1, "yellow", 20],
[1, "green", 22],
]
```
# Settings
## `SPREADSHEETS_CONFIG_SHEET_TITLE`
Default: `_("Configuration")`
The name of the (hidden) config sheet in the generated file. This special sheet is used to store data needed for validation (eg. lists of choices).
## `SPREADSHEETS_MAX_VALIDATION_ROWS`
Default: `700`
When exporting data, the generated spreadsheet will have at least `SPREADSHEETS_MAX_VALIDATION_ROWS` rows with data validation set up.
## `SPREADSHEETS_MAX_STYLED_ROWS`
Default: `700`
When exporting data, the generated spreadsheet will have at least `SPREADSHEETS_MAX_STYLED_ROWS` rows styled.
## `SPREADSHEETS_COMMENT_AUTHOR`
Default: `"Django Spreadsheet"`
The name of the author used when adding comments on cells.
## `SPREADSHEETS_VALIDATION_LIST_REPLACEMENT_DELIMITER`
Default: `"/"`
The delimiter used to replace `,` found in list items as `,` is not permitted inside list items.
## `SPREADSHEETS_EXPORT_FILE_NAME`
Default: `"export {config_name} {date}.xlsx"`
The name of the exported file when downloaded. Token `config_name` and `date` will be replaced respectively by the name of the configuration that is currently exported and the current date.
## `SPREADSHEETS_TEMPLATE_FILE_NAME`
Default: `"{config_name} template file.xlsx"`
The name of the template file when downloaded. Token `config_name` will be replaced by the name of the configuration that is currently exported.
Raw data
{
"_id": null,
"home_page": "https://gitlab.com/kapt/open-source/django-spreadsheets",
"name": "django-spreadsheets",
"maintainer": null,
"docs_url": null,
"requires_python": "<4.0,>=3.8",
"maintainer_email": null,
"keywords": null,
"author": "Kapt dev team",
"author_email": "dev@kapt.mobi",
"download_url": "https://files.pythonhosted.org/packages/3f/c3/50ed28f397c05c61bace933ad04fdc33efea105558e1028f4be077dd1fd2/django_spreadsheets-1.4.3.tar.gz",
"platform": null,
"description": "# django-spreadsheet\n\nValidate your XLSX spreadsheet file before importing it. Export your data to a formatted XLSX spreadsheet with data validation set up.\n\n# Requirements\n\n- Python 3.8+\n- Django 2.0+\n\n# Installation\n\n- run `pip install django_spreadsheets`\n- add `django_spreadsheets.apps.SpreadsheetAppConfig` to your `INSTALLED_APPS`\n- add `path(\"admin/spreadsheets/\", include(\"django_spreadsheets.urls\", namespace=\"spreadsheets\"))` to your root `urls.py`\n- run `python manage.py migrate django_spreadsheets`\n\n# Usage\n\nStart to create a file `spreadsheets.py` in your app. Inside this file, describe your spreadsheets configurations such as the following example:\n\n```py\nfrom django_spreadsheets.base_config import SpreadsheetConfig, SheetConfig\nfrom django_spreadsheets.registry import register\nfrom libs.bananas.models import Banana\n\n\nclass BananaSheetConfig(SheetConfig):\n \"\"\"Describes each columns of this sheet.\"\"\"\n\n ID = {\n \"name\": \"Identifier\",\n \"required\": True,\n }\n COLOR = {\n \"name\": \"Color\",\n \"validation\": {\n \"type\": \"list\",\n \"formula1\": [\"yellow\", \"green\", \"brown\"],\n },\n \"required\": True,\n }\n SIZE = {\n \"name\": \"Size\",\n \"validation\": {\n \"type\": \"whole\",\n \"operator\": \"greaterThan\",\n \"formula1\": 0,\n \"error\": \"Please enter a positive number\",\n }\n }\n\n def get_data_to_export(self):\n return Banana.objects.values_list(\"id\", \"color\", \"size\")\n\n\n@register\nclass BananaSpreadsheetConfig(SpreadsheetConfig):\n sheets_config = {\n \"Banana\": BananaSheetConfig()\n # the key will be used as the name of the sheet, the value is a class describing the sheet's configuration.\n }\n\n class Meta:\n verbose_name = \"Bananas\"\n\n def update_database(self, sheets):\n for sheet_name, rows in sheets.items():\n for row in rows:\n Banana.objects.get_or_create(\n id=row[\"ID\"],\n color=row[\"COLOR\"],\n size=row[\"SIZE\"],\n )\n```\n\nThen, you can go to `//localhost:8000/admin/spreadsheets/export/` to export a XSLX file and `//localhost:8000/admin/spreadsheets/import/` to import into your database.\n\n# `SpreadsheetConfig` class\n\nInherit this class to describe your spreadsheets' configuration. Define a `sheets_config` dict attribute containing the name of your sheet as a key, and a `SheetConfig` class as a value.\n\n## Validations\n\nSeveral validations are run before importing a spreadsheet:\n\n### Config version validation\n\nValidate that the configuration version stored in the file is the same as the `SpreadsheetConfig.version` used.\n\n### Sheets validation\n\nValidate that all sheets in this `SpreadsheetConfig` class used, exist in imported file. Validate that all sheets in imported file, exist in this `SpreadsheetConfig` class used.\n\n### Columns headers validation\n\nValidate that the imported file contains the same columns headers as described in the `SheetConfig` used.\n\n### Required data validation\n\nValidate that there is a value in the imported file for each `required`, `required_unless`, `required_if` columns.\n\n### Uniqueness validation [/!\\ not implemented]\n\nValidate that multiple cells in a row are unique together in the imported file.\n\n### Types validation\n\nValidate that each column in the imported file contains data of correct type (date, positive integer, ...).\n\n### Data match validation\n\nValidate that we find an object in the database matching the value in the imported file. If not, we ask the user to chose from a list of possibles values.\n\n## Import data\n\nIf you want to import data in y our database from this spreadsheet, you need to define a `update_database(self, sheets)` method. This method is called after all data validations have been completed, and receive a python dict with an item for each sheet. The key is the name of the sheet, and the value is a python list of rows. Each row is a list containing a value for each column.\n\n```py\ndef update_database(self, sheets):\n for sheet_name, rows in sheets.items():\n for row in rows:\n for value in row:\n print(value)\n```\n\n\n# `SheetConfig` class\n\nInherit this class to describe your sheets' configuration. Each attribute describes a column using a dictionary such as the following:\n\n```py\n<column_identifier> = {\n \"name\": str (required), # The name of the column used in the header row\n \"required\": bool, # Whether a value in the column is required when importing\n \"required_unless\": str, # Whether a value in this column is required if the pointed column is empty\n \"required_if\": str, # Whether a value in this column is required if the pointed column is filled\n \"comment\": str, # A comment that appears in the header cell\n \"admin_only\": bool, # Whether this column should be exported only when the current user is an administrator\n \"validation\": { # A data validation applied to each cell of this column\n \"name\": str, # A name used as header in the hidden config sheet\n \"type\": str (required), # <url|date|list|range|whole...> See openpyxl documentation to get all available validation types\n ... # Same args as openpyxl's DataValidation objects (operator, formula1, formula2, showErrorMessage, ...)\n ... # Note that you can pass callables to `formula1` and `formula2` keys\n },\n \"header_style\": str, # The name of a style that already exists in the SpreadsheetConfig to which this sheet belongs\n \"style\": str, # The name of a style that already exists in the SpreadsheetConfig to which this sheet belongs\n \"number_format\": str # A value among FORMAT_* constants from openpyxl.styles.numbers or others values supported by XLSX spec\n},\n```\n\n## Note on validation\n\nTo create a _boolean_ validation, you may use a `list` validation type with `django_spreadsheets.constants.YES_NO_CHOICES` as `formula1` value. To ensure a boolean value is displayed in your exported file, add `\"number_format\": \"BOOLEAN\"` in your column configuration.\n\nThe `formula1` and `formula2` keys accept callables as values.\n\n## Note on number format\n\nWhen the value in your exported file is not displayed correctly or not consistent across rows, you may define the `number_format` key in your column configuration.\n\nOpenxpyxl's format constants are not exhaustive. You may use other supported strings (eg: \"BOOLEAN\").\n\n## Export data\n\nIf you want to export data to this sheet, you need to define a `get_data_to_export(self)` method. This method must return a python list containing an item by row. Each item in the list is a list containing a value for each column.\n\n```py\ndef get_data_to_export(self):\n return [\n [1, \"yellow\", 20],\n [1, \"green\", 22],\n ]\n```\n\n# Settings\n\n## `SPREADSHEETS_CONFIG_SHEET_TITLE`\n\nDefault: `_(\"Configuration\")`\n\nThe name of the (hidden) config sheet in the generated file. This special sheet is used to store data needed for validation (eg. lists of choices).\n\n## `SPREADSHEETS_MAX_VALIDATION_ROWS`\n\nDefault: `700`\n\nWhen exporting data, the generated spreadsheet will have at least `SPREADSHEETS_MAX_VALIDATION_ROWS` rows with data validation set up.\n\n## `SPREADSHEETS_MAX_STYLED_ROWS`\n\nDefault: `700`\n\nWhen exporting data, the generated spreadsheet will have at least `SPREADSHEETS_MAX_STYLED_ROWS` rows styled.\n\n## `SPREADSHEETS_COMMENT_AUTHOR`\n\nDefault: `\"Django Spreadsheet\"`\n\nThe name of the author used when adding comments on cells.\n\n## `SPREADSHEETS_VALIDATION_LIST_REPLACEMENT_DELIMITER`\n\nDefault: `\"/\"`\n\nThe delimiter used to replace `,` found in list items as `,` is not permitted inside list items.\n\n## `SPREADSHEETS_EXPORT_FILE_NAME`\n\nDefault: `\"export {config_name} {date}.xlsx\"`\n\nThe name of the exported file when downloaded. Token `config_name` and `date` will be replaced respectively by the name of the configuration that is currently exported and the current date.\n\n## `SPREADSHEETS_TEMPLATE_FILE_NAME`\n\nDefault: `\"{config_name} template file.xlsx\"`\n\nThe name of the template file when downloaded. Token `config_name` will be replaced by the name of the configuration that is currently exported.\n\n",
"bugtrack_url": null,
"license": "BSD-3-Clause",
"summary": "Import from and export to XLSX files with data validation.",
"version": "1.4.3",
"project_urls": {
"Homepage": "https://gitlab.com/kapt/open-source/django-spreadsheets",
"Repository": "https://gitlab.com/kapt/open-source/django-spreadsheets"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "2f7288627211c3fc109d354e528ad8753d84ed8a67201a2161beff1fde6f08f2",
"md5": "79d54973086502803f53034b71f03bfb",
"sha256": "d1345ba87713a126689124ab91b0643a3fce4361b8d7b1dc13206fc9ab60eb86"
},
"downloads": -1,
"filename": "django_spreadsheets-1.4.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "79d54973086502803f53034b71f03bfb",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": "<4.0,>=3.8",
"size": 36535,
"upload_time": "2024-10-30T16:11:46",
"upload_time_iso_8601": "2024-10-30T16:11:46.576442Z",
"url": "https://files.pythonhosted.org/packages/2f/72/88627211c3fc109d354e528ad8753d84ed8a67201a2161beff1fde6f08f2/django_spreadsheets-1.4.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "3fc350ed28f397c05c61bace933ad04fdc33efea105558e1028f4be077dd1fd2",
"md5": "29b4539da4d3425a4305aeca40707852",
"sha256": "3e4b2ad64864a0bb19a6a300f7c6a2070bb84a800e00f37e6b730329ab888616"
},
"downloads": -1,
"filename": "django_spreadsheets-1.4.3.tar.gz",
"has_sig": false,
"md5_digest": "29b4539da4d3425a4305aeca40707852",
"packagetype": "sdist",
"python_version": "source",
"requires_python": "<4.0,>=3.8",
"size": 29863,
"upload_time": "2024-10-30T16:11:48",
"upload_time_iso_8601": "2024-10-30T16:11:48.148383Z",
"url": "https://files.pythonhosted.org/packages/3f/c3/50ed28f397c05c61bace933ad04fdc33efea105558e1028f4be077dd1fd2/django_spreadsheets-1.4.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-10-30 16:11:48",
"github": false,
"gitlab": true,
"bitbucket": false,
"codeberg": false,
"gitlab_user": "kapt",
"gitlab_project": "open-source",
"lcname": "django-spreadsheets"
}