django-spreadsheets


Namedjango-spreadsheets JSON
Version 1.4.3 PyPI version JSON
download
home_pagehttps://gitlab.com/kapt/open-source/django-spreadsheets
SummaryImport from and export to XLSX files with data validation.
upload_time2024-10-30 16:11:48
maintainerNone
docs_urlNone
authorKapt dev team
requires_python<4.0,>=3.8
licenseBSD-3-Clause
keywords
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage No coveralls.
            # 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"
}
        
Elapsed time: 0.33639s