As a web backend developer, I have often found myself tasked with processing large datasets that were submitted via Excel. However, the process of manually parsing the data from Excel files, identifying errors, and reconciling discrepancies was time-consuming and error-prone.

Often the work was duplicated somehow but not exactly the same, and the data was not always consistent.

After struggling with the same problem for multiple projects, I realized that a more streamlined solution was needed, as there is a saying Don't Repeat Yourself.

That’s where ExcelAlchemy comes in.

ExcelAlchemy, provides a streamlined interface for interacting with Excel files. With ExcelAlchemy, you can easily download Excel files, parse user inputs, and generate Pydantic classes without breaking a sweat.

One of ExcelAlchemy’s key features is its ability to generate Excel templates from Pydantic classes. This makes it easy for you to set up Excel spreadsheets with specific data types and layouts, and ensures that data is submitted in a standardized format. Additionally, ExcelAlchemy supports adding default values for optional fields, making it easier to fill out Excel forms.

Another key feature of ExcelAlchemy is its ability to parse Pydantic classes from Excel files.

This minimizes the need for manual data entry and reduces the risk of errors. ExcelAlchemy also provides a custom data converter, allowing developers to customize how parsed data is returned.

Finally, ExcelAlchemy can read data from parsed Excel files using Minio. This functionality allows developers to store Excel files in a bucket and create data from them asynchronously. This is particularly useful for managing large datasets, and ensures that data is stored in a secure and reliable manner.

Overall, ExcelAlchemy is a high-quality, well-documented Python library that is perfect for anyone who works with Excel spreadsheets. Its ability to generate templates from Pydantic classes, parse Pydantic classes from Excel files, and read data from parsed Excel files using Minio make it a valuable tool for anyone who needs to manage Excel data in their Python projects.

Here is how to use it.

ExcelAlchemy User Guide

📊 ExcelAlchemy

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.

Installation

Use pip to install:

pip install ExcelAlchemy

Usage

Generate Excel template from Pydantic class

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)

The above is a simple example of generating an Excel template from a Pydantic class. The Excel template will have a sheet named "Sheet1" with four columns: "Age", "Name", "Phone", and "Address". "Age" and "Name" are required fields, while "Phone" and "Address" are optional.
The method returns a base64-encoded string that represents the Excel file. You can directly use the window.open method to open the Excel file in the front-end, or download it by typing the base64 content in the browser's address bar.
When downloading a template, you can also specify some default values, for example:

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:

Parse a Pydantic class from an Excel file and create data

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.
The imported Excel file must be generated by the download_template() method, otherwise, it will produce a parsing error.
In the above example, we define a 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.
The 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.
The 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.
The method returns an 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.
An example of the importing result is shown in the following image:

Contributing

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.