Creating and processing Excel files using Python

Hi all! Working with Excel files is one of the most common tasks encountered in everyday development. In this article, we'll look at how you can use Python to create, fill, and style Excel files. For this we will use the pandas, openpyxl and faker libraries (for tests).

First, let's install the libraries:

pip install pandas xlsxwriter openpyxl faker

Creating a Blank Excel File

Let's start by creating a blank Excel file with the given columns. For this we use pandas And xlsxwriter.

import os
import pandas as pd


def create_empty_excel(columns: list, filename: str, sheet_name: str="Sheet1"):
    df = pd.DataFrame(columns=columns)

    if not os.path.exists('excel_files'):
        os.makedirs('excel_files')

    filepath = os.path.join('excel_files', filename)
    excel_writer = pd.ExcelWriter(filepath, engine="xlsxwriter")
    df.to_excel(excel_writer, index=False, sheet_name=sheet_name, freeze_panes=(1, 0))
    excel_writer._save()

    return filepath

This code creates a blank Excel file with the specified columns and saves it to a folder excel_files. If the folder did not exist in the root directory, it will be created.

Notice that I have frozen the top row. I think it's convenient.

In the function we see the entry engine="xlsxwriter"this means that XlsxWriter is used as an engine to write data to an Excel file.

Testing

def create_tabel_users():
    filepath = create_empty_excel(columns=['Имя', 'Адрес', 'Email', 'Телефон'],
                                  filename="users.xlsx")


create_tabel_users()

Parsing Excel File into Dictionary List

After creating an Excel file, you may need to read the data from it and convert it into a format that is easy to process – a list of dictionaries.

def parse_excel_to_dict_list(filepath: str, sheet_name="Sheet1"):
    # Загружаем Excel файл в DataFrame
    df = pd.read_excel(filepath, sheet_name=sheet_name)

    # Преобразуем DataFrame в список словарей
    dict_list = df.to_dict(orient="records")

    return dict_list

This simple method loads data from an Excel file into DataFrameand then converts them into a list of dictionaries.

Now I will add some data to the Excel table I created earlier and use this function.

This is what my table looks like in normal form:

Code:

def get_data_to_exel():
    info = parse_excel_to_dict_list('users.xlsx')
    for i in info:
        print(i)


get_data_to_exel()

Let's look:

Creating an Excel File from the Dictionary List

Now let's create an Excel file from the list of dictionaries and apply styling to improve the appearance.

def create_excel_from_dict_list(dict_list: list, output_filename: str, sheet_name="Sheet1"):
    # Создаем директорию, если она не существует
    if not os.path.exists('excel_files'):
        os.makedirs('excel_files')

    filepath = os.path.join('excel_files', output_filename)

    # Создаем новую книгу Excel
    wb = Workbook()
    ws = wb.active
    ws.title = sheet_name

    # Записываем данные из списка словарей в Excel
    if dict_list:
        header = list(dict_list[0].keys())
        ws.append(header)  # Записываем заголовки

        for row in dict_list:
            ws.append([row[col] for col in header])

    # Настраиваем стили для красивого вида
    header_style = NamedStyle(name="header")
    header_style.font = Font(bold=True, color="FFFFFF")
    header_style.alignment = Alignment(horizontal="center", vertical="center")
    header_style.fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
    border_style = Border(
        left=Side(border_style="thin", color="000000"),
        right=Side(border_style="thin", color="000000"),
        top=Side(border_style="thin", color="000000"),
        bottom=Side(border_style="thin", color="000000")
    )
    header_style.border = border_style

    cell_style = NamedStyle(name="cell")
    cell_style.alignment = Alignment(horizontal="left", vertical="center")
    cell_style.border = border_style

    for cell in ws[1]:  # Применяем стиль к заголовкам
        cell.style = header_style

    for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
        for cell in row:
            cell.style = cell_style

    # Автоматическое изменение ширины столбцов
    for col in ws.columns:
        max_length = 0
        column = col[0].column_letter
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                pass
        adjusted_width = (max_length + 2)
        ws.column_dimensions[column].width = adjusted_width

    # Сохраняем файл
    wb.save(filepath)
    return filepath

This code creates an Excel file from a list of dictionaries and applies styles to headers and cells.

Generating test data

To test our functions we can use the library fakerwhich allows you to generate fake data.

from faker import Faker


def generate_fake_user():
    fake = Faker('ru_RU')

    return {
        'name': fake.name(),
        'address': fake.address(),
        'email': fake.email(),
        'phone_number': fake.phone_number(),
        'birth_date': fake.date_of_birth(),
        'company': fake.company(),
        'job': fake.job()
    }


def get_fake_users(count: int):
    return [generate_fake_user() for _ in range(count)]    

Function get_fake_users generate fake data from Russian users in the amount that you transmit, which allows you to test the creation and processing of Excel files.

Usage example

Now let's put all the pieces of code together and create a complete working example:

def main():
    # создадим 100 фейковых пользователей
    fake_users = get_fake_users(100)

    # создадим таблицу с пользователями
    create_excel_from_dict_list(fake_users, 'fake_users.xlsx')

    # получим путь к таблице с пользователями
    filepath = os.path.join('excel_files', 'fake_users.xlsx')

    # получим всех пользователей из таблицы
    all_users = parse_excel_to_dict_list(filepath)
    for i in all_users:
        print(i)


main()

As you can see, here we have created 100 fake users and placed them in a table called 'fake_users.xlsx'. Then we output all user data to the console in the form of a list of Python dictionaries (via a for loop).

The table itself:

Conclusion

Using Python and Libraries pandas, openpyxl And fakeryou can easily create, fill, and style Excel files for various needs. I hope this article will help you better understand how to work with Excel files in Python and inspire you to create your own projects.