V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
推荐学习书目
Learn Python the Hard Way
Python Sites
PyPI - Python Package Index
http://diveintopython.org/toc/index.html
Pocoo
值得关注的项目
PyPy
Celery
Jinja2
Read the Docs
gevent
pyenv
virtualenv
Stackless Python
Beautiful Soup
结巴中文分词
Green Unicorn
Sentry
Shovel
Pyflakes
pytest
Python 编程
pep8 Checker
Styles
PEP 8
Google Python Style Guide
Code Style from The Hitchhiker's Guide
ruicore
V2EX  ›  Python

ExcelAlchemy: A Python Library for Reading and Writing Excel Files.

  •  
  •   ruicore ·
    ruicore · 4 天前 · 534 次点击

    Hello Everyone, I am a web backend developer, mainly use Python, SQLAlchemy, GraphQL, Pydantic in my daily work.

    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:

    • image

    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: 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.

    License

    ExcelAlchemy is licensed under the MIT license. For more information, please see the LICENSE file.

    6 条回复    2023-03-19 20:34:46 +08:00
    ruicore
        1
    ruicore  
    OP
       4 天前   ❤️ 1
    自己的第一个 package ,用英文写了说明,大家轻喷😂
    matrix1010
        2
    matrix1010  
       4 天前
    既然有中文版本的 README 为什么要复制个英文版的? 另外 test 也不是依靠 print 来保证的,要确实 assert 数据。CI 里也应该加上 test step.
    noparking188
        3
    noparking188  
       3 天前   ❤️ 1
    star 了,很👍,看了依赖,是基于 openpyxl 解析 Excel 的哈
    ruicore
        4
    ruicore  
    OP
       3 天前 via iPhone
    @noparking188 非常感谢大佬的肯定👍
    ruicore
        5
    ruicore  
    OP
       3 天前 via iPhone
    链接是这个 https://github.com/SundayWindy/ExcelAlchemy
    文章里面给错了😂😂😂
    noparking188
        6
    noparking188  
       3 天前
    @ruicore #4 😂 不是大佬,学习一下
    关于   ·   帮助文档   ·   博客   ·   nftychat   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   实用小工具   ·   1026 人在线   最高记录 5556   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 35ms · UTC 22:42 · PVG 06:42 · LAX 15:42 · JFK 18:42
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.