中文 English
ExcelAlchemy is a Python library that allows you to download Excel files from Minio, parse user inputs, and generate corresponding Pydantic classes. It also allows you to generate Excel files based on Pydantic classes for easy user downloads.
Use pip to install:
pip install ExcelAlchemy
from excelalchemy import ExcelAlchemy, FieldMeta, ImporterConfig, Number, String
from pydantic import BaseModel
class Importer(BaseModel):
age: Number = FieldMeta(label='Age', order=1)
name: String = FieldMeta(label='Name', order=2)
phone: String | None = FieldMeta(label='Phone', order=3)
address: String | None = FieldMeta(label='Address', order=4)
alchemy = ExcelAlchemy(ImporterConfig(Importer))
base64content = alchemy.download_template()
print(base64content)
from excelalchemy import ExcelAlchemy, FieldMeta, ImporterConfig, Number, String
from pydantic import BaseModel
class Importer(BaseModel):
age: Number = FieldMeta(label='Age', order=1)
name: String = FieldMeta(label='Name', order=2)
phone: String | None = FieldMeta(label='Phone', order=3)
address: String | None = FieldMeta(label='Address', order=4)
alchemy = ExcelAlchemy(ImporterConfig(Importer))
sample = [
{'age': 18, 'name': 'Bob', 'phone': '12345678901', 'address': 'New York'},
{'age': 19, 'name': 'Alice', 'address': 'Shanghai'},
{'age': 20, 'name': 'John', 'phone': '12345678901'},
]
base64content = alchemy.download_template(sample)
print(base64content)
In the above example, we specify a sample, which is a list of dictionaries. Each dictionary represents a row in the Excel sheet, and the keys represent column names. The method returns an Excel template with default values filled in. If a field doesn’t have a default value, it will be empty. For example:
import asyncio
from typing import Any
from excelalchemy import ExcelAlchemy, FieldMeta, ImporterConfig, Number, String
from minio import Minio
from pydantic import BaseModel
class Importer(BaseModel):
age: Number = FieldMeta(label='Age', order=1)
name: String = FieldMeta(label='Name', order=2)
phone: String | None = FieldMeta(label='Phone', order=3)
address: String | None = FieldMeta(label='Address', order=4)
def data_converter(data: dict[str, Any]) -> dict[str, Any]:
"""Custom data converter, here you can modify the result of Importer.dict()"""
data['age'] = data['age'] + 1
data['name'] = {"phone": data['phone']}
return data
async def create_func(data: dict[str, Any], context: None) -> Any:
"""Your defined creation function"""
# do something to create data
return True
async def main():
alchemy = ExcelAlchemy(
ImporterConfig(
create_importer_model=Importer,
creator=create_func,
data_converter=data_converter,
minio=Minio(endpoint=''), # reachable minio address
bucket_name='excel',
url_expires=3600,
)
)
result = await alchemy.import_data(input_excel_name='test.xlsx', output_excel_name="test.xlsx")
print(result)
asyncio.run(main())
The importing function is based on Minio
, so you need to install Minio and create a bucket to use this functionality for storing the Excel files.
download_template()
method, otherwise, it will produce a parsing error.data_converter
function, which is used to modify the result of Importer.dict().
The final result of data_converter
function will be the parameter of the create_func function. This function is optional if you don’t need to modify the data.create_func
function is used to create data, and the parameter is the result of the data_converter function, and context is None. You can create data, for example, by storing the data in a database.input_excel_name
parameter of the import_data()
method is the name of the Excel file in Minio, and the output_excel_name
parameter is the name of the Excel file with the parsing result in Minio. This file contains all the input data, and if any data fails the parsing, the first column of that data has an error message, and the error-producing cell is highlighted in red.ImportResult
type result. You can see the definition of this class in the code. This class contains all the information about the parsing result, such as the number of successfully imported data, the number of failed data, the failed data, etc.If you have any questions or suggestions regarding the ExcelAlchemy library, please raise an issue in GitHub Issues. We also welcome you to submit a pull request to contribute your code.
ExcelAlchemy is licensed under the MIT license. For more information, please see the LICENSE file.