# Django Excel Tools
[](https://badge.fury.io/py/django-excel-tools)
[](https://travis-ci.org/NorakGithub/django-excel-tools)
Serializing excel data to python format for easier to manage.
## Requirements
- Django (1.8 or higher version)
- OpenPYXL
## Installation
Install via pip
```bash
pip install django-excel-tools
```
## Documentation
- [Serializer Overridable Functions](#serializer-overridable-functions)
- [Fields References](#fields-references)
- [Common Argument](#common-argument)
- [BooleanField](#booleanfield)
- [CharField](#charfield)
- [IntegerField](#integerfield)
- [DateField](#datefield)
- [DateTimeField](#datetimefield)
- [Example Usage](#example-usage)
### Serializer Overridable Functions
`row_extra_validation`
This function will call each time a row has been validated.
This is for the case where you want a row of excel data that has been
validated and then you want to add your own validation.
`extra_clean_{field name}`
This function will be call when a column cell is validated and you would like to add custom validation. **field name** must be match with field name defined in `Meta.fields`.
`validated`
This function will be call when all data in excel is validated.
`invalid`
This function will be call when there are one or more errors happen during data validation. Simply put `validation_errors` is not empty.
`operation_failed`
This function will be call when there are one or more errors happen during import operation. Simply put `operation_errors` is not empty.
`operation_success`
This function will be call when there is no error happen during import operation. Simply put `operation_errors` is empty.
`import_operation`
This function will be call after all data in excel is validated, and this is also the place where you add your function of how you gonna insert all the cleaned excel data to your database. Check usage below for the example code.
### Fields References
#### Common Argument
`verbose_name`
This field will be used in case of error, so we know exactly which column fix.
#### BooleanField
Required argument:
`verbose_name`
Corresponds to `django_excel_tools.fields.BooleanField`
#### CharField
Required arguments:
`max_length` The max length of text that is acceptable.
`verbose_name` This field will be used in case of error, so we know exactly which column fix.
Optional arguments:
`convert_number` automatically convert int or number to string. Default `True`.
`choices` only accept value in choices if not match `ValidationError` will raise. Default `None`.
`default` this value will be used when excel is blank. Default `None`.
`case_sensitive` this is used in case of choices are set, comparing choices with case sensitive or not. Default `True`.
Corresponds to `django_excel_tools.fields.CharField`
#### IntegerField
Required arguments:
`verbose_name`
Optional arguments:
`default` this value will be used when excel is blank. Default is `None`
`convert_str` this value will be used to convert string to int, if failed `ValidationError` will be raised. Default is `True`.
`blank` this tell the field is allowed to blank or not. Default is `False`.
`choices` this tell the field is only accept value from choices, otherwise `ValidationError` will be raised. Default is `None`.
Corresponds to `django_excel_tools.fields.IntegerField`
#### DateField
Required arguments:
`date_format` This will be use for string formatting date from string.
`date_format_verbose` This will be used when error occurred, so that user may know how to change to the correct format.
`verbose_name`
Optional Arguments:
`blank` this tell the field is allowed to blank or not. Default is `False`.
Corresponds to `django_excel_tools.fields.DateField`
#### DateTimeField
Required arguments:
`date_format` This will be use for string formatting date from string.
`date_format_verbose` This will be used when error occurred, so that user may know how to change to the correct format.
`verbose_name`
Optional Arguments:
`blank` this tell the field is allowed to blank or not. Default is `False`.
Corresponds to `django_excel_tools.fields.DateTimeField`
### Example Usage
**Class Excel Serializer**
```python
from django_excel_tools import serializers
class StaffExcelSerializer(serializers.ExcelSerializer)
GENDER_CHOICES = ['male', 'female']
code = serializers.IntegerField(verbose_name='Code')
name = serializers.CharField(max_length=100, verbose_name='Name')
gender = serializers.CharField(
max_length=5,
verbose_name='GENDER',
choices=GENDER_CHOICES
)
date_of_birth = serializers.DateField(
blank=True,
date_format='%Y-%m-%d',
date_format_verbose='YYYY-MM-DD',
verbose_name='Date of Birth'
)
class Meta:
start_index = 1
fields = ('code', 'name', 'gender', 'date_of_birth')
enable_transaction = True # Default is True
def row_extra_validation(self, index, cleaned_row):
# Code 100 must be female
code = cleaned_row['code']
gender = clenaed_row['gender']
if code != 100:
return cleaned_row
if gender != 'female':
raise serializers.exceptions.ValidationError(
message='[Row {index}] {message}'.format(
index=index,
message='Code 100 cannot be other than female'
)
return cleaned_row
def extra_clean_name(self, cleaned_value):
return 'Hello {}'.format(cleaned_value)
def validated(self):
# Notified that excel is successfully validated
def invalid(self, errors):
# Notify that excel format is not valid and errors argument is
# the list of errors message
def operation_failed(self, errors):
# Error during import operation
def operation_success(self):
# Import operation success
def import_operation(self, cleaned_data):
for index, row in enumerate(cleaned_data):
try:
Staff.objects.create(**row)
except Exception as e:
self.operation_errors.append(self.gen_error(index, str(e)))
continue
```
**Usage**
```python
from openpyxl import load_workbook
def import_view(request):
excel_file = request.files[0]
workbook = load_workbook(file)
worksheet = workbook.worksheets[0]
serializer = StaffExcelSerializer(worksheet=worksheet)
if serializer.validation_errors:
return Response(data=serializer.validation_errors, status=400)
if serializer.operation_errors:
return Response(data=serializer.operation_errors, status=400)
return Response(status=201)
```
## License
MIT License
Copyright (c) 2017, Khemanorak Khath
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
# Changelog
All notable changes to this project will be documented in this file.
The format is based on [Keep a Changelog](https://keepachangelog.com/en/1.0.0/),
and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html).
## [1.0.1] - 2018-12-11
#### Changed
- Removed `BASE_MESSAGE` in fields
### Added
- Translation for Japanese and Khmer
- `error_trans` util function for generate error message
### Fixed
- Added validation for columns in excel is less than defined in serializer fields
## [1.0.0] - 2018-10-19
### Breaking Change
- Meta.django_enable_transaction has changed to enable_transaction
### Changed
- Moved all fields class from `django_excel_tools.serializers` to `django_excel_tools.fields`
- Code refactored for `django_excel_tools.serializers.ExcelSerializer` to more readable and pure function that doesn't produce effect.
### Added
- More test coverage
Raw data
{
"_id": null,
"home_page": "https://github.com/NorakGithub/django-excel-tools",
"name": "django-excel-tools",
"maintainer": null,
"docs_url": null,
"requires_python": null,
"maintainer_email": null,
"keywords": "django, excel, tools",
"author": "Khemanorak Khath",
"author_email": "khath.khemanorak@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/c8/77/04b2cfaa3a62a44f776bc4c5f2afa49eca4baea5a4b9ea045dd2e6cf17e5/django_excel_tools-1.1.1.tar.gz",
"platform": null,
"description": "# Django Excel Tools\n\n[](https://badge.fury.io/py/django-excel-tools)\n[](https://travis-ci.org/NorakGithub/django-excel-tools)\n\nSerializing excel data to python format for easier to manage.\n\n## Requirements\n- Django (1.8 or higher version)\n- OpenPYXL\n\n## Installation\nInstall via pip\n\n```bash\npip install django-excel-tools\n```\n\n## Documentation\n- [Serializer Overridable Functions](#serializer-overridable-functions)\n- [Fields References](#fields-references)\n - [Common Argument](#common-argument)\n - [BooleanField](#booleanfield)\n - [CharField](#charfield)\n - [IntegerField](#integerfield)\n - [DateField](#datefield)\n - [DateTimeField](#datetimefield)\n- [Example Usage](#example-usage)\n\n### Serializer Overridable Functions\n\n`row_extra_validation`\nThis function will call each time a row has been validated.\nThis is for the case where you want a row of excel data that has been\nvalidated and then you want to add your own validation.\n\n`extra_clean_{field name}`\nThis function will be call when a column cell is validated and you would like to add custom validation. **field name** must be match with field name defined in `Meta.fields`.\n\n`validated`\nThis function will be call when all data in excel is validated.\n\n`invalid`\nThis function will be call when there are one or more errors happen during data validation. Simply put `validation_errors` is not empty.\n\n`operation_failed`\nThis function will be call when there are one or more errors happen during import operation. Simply put `operation_errors` is not empty.\n\n`operation_success`\nThis function will be call when there is no error happen during import operation. Simply put `operation_errors` is empty.\n\n`import_operation`\nThis function will be call after all data in excel is validated, and this is also the place where you add your function of how you gonna insert all the cleaned excel data to your database. Check usage below for the example code.\n\n### Fields References\n#### Common Argument\n`verbose_name`\n\nThis field will be used in case of error, so we know exactly which column fix.\n\n#### BooleanField\nRequired argument:\n`verbose_name`\n\nCorresponds to `django_excel_tools.fields.BooleanField`\n\n#### CharField\nRequired arguments:\n`max_length` The max length of text that is acceptable.\n`verbose_name` This field will be used in case of error, so we know exactly which column fix.\n\nOptional arguments:\n`convert_number` automatically convert int or number to string. Default `True`.\n`choices` only accept value in choices if not match `ValidationError` will raise. Default `None`.\n`default` this value will be used when excel is blank. Default `None`.\n`case_sensitive` this is used in case of choices are set, comparing choices with case sensitive or not. Default `True`.\n\nCorresponds to `django_excel_tools.fields.CharField`\n\n#### IntegerField\nRequired arguments:\n`verbose_name`\n\nOptional arguments:\n`default` this value will be used when excel is blank. Default is `None`\n`convert_str` this value will be used to convert string to int, if failed `ValidationError` will be raised. Default is `True`.\n`blank` this tell the field is allowed to blank or not. Default is `False`.\n`choices` this tell the field is only accept value from choices, otherwise `ValidationError` will be raised. Default is `None`.\n\nCorresponds to `django_excel_tools.fields.IntegerField`\n\n#### DateField\nRequired arguments:\n`date_format` This will be use for string formatting date from string.\n`date_format_verbose` This will be used when error occurred, so that user may know how to change to the correct format.\n`verbose_name`\n\nOptional Arguments:\n`blank` this tell the field is allowed to blank or not. Default is `False`.\n\nCorresponds to `django_excel_tools.fields.DateField`\n#### DateTimeField\nRequired arguments:\n`date_format` This will be use for string formatting date from string.\n`date_format_verbose` This will be used when error occurred, so that user may know how to change to the correct format.\n`verbose_name`\n\nOptional Arguments:\n`blank` this tell the field is allowed to blank or not. Default is `False`.\n\nCorresponds to `django_excel_tools.fields.DateTimeField`\n\n### Example Usage\n**Class Excel Serializer**\n```python\nfrom django_excel_tools import serializers\n\n\nclass StaffExcelSerializer(serializers.ExcelSerializer)\n GENDER_CHOICES = ['male', 'female']\n\n code = serializers.IntegerField(verbose_name='Code')\n name = serializers.CharField(max_length=100, verbose_name='Name')\n gender = serializers.CharField(\n max_length=5,\n verbose_name='GENDER',\n choices=GENDER_CHOICES\n )\n date_of_birth = serializers.DateField(\n blank=True,\n date_format='%Y-%m-%d',\n date_format_verbose='YYYY-MM-DD',\n verbose_name='Date of Birth'\n )\n\n class Meta:\n start_index = 1\n fields = ('code', 'name', 'gender', 'date_of_birth')\n enable_transaction = True # Default is True\n\n def row_extra_validation(self, index, cleaned_row):\n # Code 100 must be female\n code = cleaned_row['code']\n gender = clenaed_row['gender']\n\n if code != 100:\n return cleaned_row\n if gender != 'female':\n raise serializers.exceptions.ValidationError(\n message='[Row {index}] {message}'.format(\n index=index,\n message='Code 100 cannot be other than female'\n )\n return cleaned_row\n\n def extra_clean_name(self, cleaned_value):\n return 'Hello {}'.format(cleaned_value)\n\n def validated(self):\n # Notified that excel is successfully validated\n\n def invalid(self, errors):\n # Notify that excel format is not valid and errors argument is\n # the list of errors message\n\n def operation_failed(self, errors):\n # Error during import operation\n\n def operation_success(self):\n # Import operation success\n\n def import_operation(self, cleaned_data):\n for index, row in enumerate(cleaned_data):\n try:\n Staff.objects.create(**row)\n except Exception as e:\n self.operation_errors.append(self.gen_error(index, str(e)))\n continue\n```\n\n**Usage**\n```python\nfrom openpyxl import load_workbook\n\n\ndef import_view(request):\n excel_file = request.files[0]\n workbook = load_workbook(file)\n worksheet = workbook.worksheets[0]\n serializer = StaffExcelSerializer(worksheet=worksheet)\n if serializer.validation_errors:\n return Response(data=serializer.validation_errors, status=400)\n if serializer.operation_errors:\n return Response(data=serializer.operation_errors, status=400)\n return Response(status=201)\n```\n\n\n## License\nMIT License\n\nCopyright (c) 2017, Khemanorak Khath\n\nPermission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the \"Software\"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:\n\nThe above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.\n\nTHE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.\n\n\n# Changelog\nAll notable changes to this project will be documented in this file.\n\nThe format is based on [Keep a Changelog](https://keepachangelog.com/en/1.0.0/),\nand this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html).\n\n## [1.0.1] - 2018-12-11\n#### Changed\n- Removed `BASE_MESSAGE` in fields\n\n### Added\n- Translation for Japanese and Khmer\n- `error_trans` util function for generate error message\n\n### Fixed\n- Added validation for columns in excel is less than defined in serializer fields\n\n## [1.0.0] - 2018-10-19\n### Breaking Change\n- Meta.django_enable_transaction has changed to enable_transaction\n\n### Changed\n- Moved all fields class from `django_excel_tools.serializers` to `django_excel_tools.fields`\n- Code refactored for `django_excel_tools.serializers.ExcelSerializer` to more readable and pure function that doesn't produce effect.\n\n### Added\n- More test coverage\n",
"bugtrack_url": null,
"license": "MIT license",
"summary": "Common function when working with excel.",
"version": "1.1.1",
"project_urls": {
"Homepage": "https://github.com/NorakGithub/django-excel-tools"
},
"split_keywords": [
"django",
" excel",
" tools"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "f451f7f40bb06dfde4fbbade161c517c2a26060786ecdeb8c0f7a34fe4365c36",
"md5": "4476ef933bcd2ed29bf0c3dd07767303",
"sha256": "111e89b06c18412cffc61ecb1e85aa4daacc35bf9c7c59395b6a940edcde316a"
},
"downloads": -1,
"filename": "django_excel_tools-1.1.1-py2.py3-none-any.whl",
"has_sig": false,
"md5_digest": "4476ef933bcd2ed29bf0c3dd07767303",
"packagetype": "bdist_wheel",
"python_version": "py2.py3",
"requires_python": null,
"size": 14636,
"upload_time": "2024-06-20T04:26:57",
"upload_time_iso_8601": "2024-06-20T04:26:57.311329Z",
"url": "https://files.pythonhosted.org/packages/f4/51/f7f40bb06dfde4fbbade161c517c2a26060786ecdeb8c0f7a34fe4365c36/django_excel_tools-1.1.1-py2.py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "c87704b2cfaa3a62a44f776bc4c5f2afa49eca4baea5a4b9ea045dd2e6cf17e5",
"md5": "31b1890bbc2ba07169e00ba213d3f657",
"sha256": "9695cc25f0582256ff83434b18864823ca526423c44fa6009353eb6e35fdb6b0"
},
"downloads": -1,
"filename": "django_excel_tools-1.1.1.tar.gz",
"has_sig": false,
"md5_digest": "31b1890bbc2ba07169e00ba213d3f657",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 24966,
"upload_time": "2024-06-20T04:26:59",
"upload_time_iso_8601": "2024-06-20T04:26:59.145087Z",
"url": "https://files.pythonhosted.org/packages/c8/77/04b2cfaa3a62a44f776bc4c5f2afa49eca4baea5a4b9ea045dd2e6cf17e5/django_excel_tools-1.1.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-06-20 04:26:59",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "NorakGithub",
"github_project": "django-excel-tools",
"travis_ci": true,
"coveralls": false,
"github_actions": true,
"requirements": [
{
"name": "alabaster",
"specs": [
[
"==",
"0.7.12"
]
]
},
{
"name": "argh",
"specs": [
[
"==",
"0.26.2"
]
]
},
{
"name": "asgiref",
"specs": [
[
"==",
"3.2.10"
]
]
},
{
"name": "attrs",
"specs": [
[
"==",
"19.3.0"
]
]
},
{
"name": "Babel",
"specs": [
[
"==",
"2.8.0"
]
]
},
{
"name": "bump2version",
"specs": [
[
"==",
"1.0.0"
]
]
},
{
"name": "bumpversion",
"specs": [
[
"==",
"0.6.0"
]
]
},
{
"name": "cffi",
"specs": [
[
"==",
"1.14.1"
]
]
},
{
"name": "coverage",
"specs": [
[
"==",
"5.2.1"
]
]
},
{
"name": "Django",
"specs": [
[
"==",
"3.1"
]
]
},
{
"name": "et-xmlfile",
"specs": [
[
"==",
"1.0.1"
]
]
},
{
"name": "idna",
"specs": [
[
"==",
"2.10"
]
]
},
{
"name": "importlib-metadata",
"specs": [
[
"==",
"1.7.0"
]
]
},
{
"name": "iniconfig",
"specs": [
[
"==",
"1.0.1"
]
]
},
{
"name": "jdcal",
"specs": [
[
"==",
"1.4.1"
]
]
},
{
"name": "more-itertools",
"specs": [
[
"==",
"8.4.0"
]
]
},
{
"name": "openpyxl",
"specs": [
[
"==",
"3.0.4"
]
]
},
{
"name": "packaging",
"specs": [
[
"==",
"20.4"
]
]
},
{
"name": "pluggy",
"specs": [
[
"==",
"0.13.1"
]
]
},
{
"name": "py",
"specs": [
[
"==",
"1.9.0"
]
]
},
{
"name": "pyasn1",
"specs": [
[
"==",
"0.4.8"
]
]
},
{
"name": "pycparser",
"specs": [
[
"==",
"2.20"
]
]
},
{
"name": "pyparsing",
"specs": [
[
"==",
"2.4.7"
]
]
},
{
"name": "pytest",
"specs": [
[
"==",
"6.0.1"
]
]
},
{
"name": "pytz",
"specs": [
[
"==",
"2020.1"
]
]
},
{
"name": "six",
"specs": [
[
"==",
"1.15.0"
]
]
},
{
"name": "sqlparse",
"specs": [
[
"==",
"0.3.1"
]
]
},
{
"name": "toml",
"specs": [
[
"==",
"0.10.1"
]
]
},
{
"name": "zipp",
"specs": [
[
"==",
"3.1.0"
]
]
}
],
"tox": true,
"lcname": "django-excel-tools"
}
JSON
