Comment lire des centaines de milliers de lignes dans Excel sans remplir la mémoire d'un coup

Comment lire un grand tableau sans utiliser de mémoire ? Cet article est destiné aux développeurs et aux équipes de données qui doivent utiliser Python pour traiter des centaines de milliers de lignes Excel. Après avoir lu, vous connaîtrez les raisons de l'explosion de la mémoire des fichiers volumineux et de la troncature des données, et comment utiliser le mode lecture seule, la réparation des dimensions et la libération des ressources d'openpyxl pour éviter ces problèmes.

Les exemples de cet article utilisent des échantillons synthétiques. Le code a été vérifié avec des exemples dans un environnement isolé, mais cela ne constitue pas une garantie de tous les fichiers réels ou des performances du produit. Avant de traiter des documents importants, veuillez conserver les originaux et les examiner en fonction des limites et des éléments d'acceptation du texte.

Comment lire un grand tableau sans exploser la mémoire ? Conclusion en une phrase

Utilisez read_only=True pour lire en continu des fichiers volumineux et fermez explicitement le classeur après la lecture ; si le nombre de lignes lues est manifestement trop petit, vérifiez d'abord les informations de dimension du fichier, puis combinez-les avec une comparaison manuelle pour confirmer que les données sont complètes.

La cause directe de l'explosion de la mémoire ou de la troncature des données lors de la lecture de centaines de milliers de lignes dans Excel

Lorsque vous utilisez load_workbook('huge.xlsx') pour ouvrir une table comportant un demi-million de lignes, le programme peut utiliser jusqu'à 8 Go de mémoire en quelques secondes ou s'arrêter après avoir lu uniquement les 1 000 premières lignes.

La première raison est que le mode par défaut charge simultanément tous les objets de cellule et leurs styles, commentaires et autres métadonnées en mémoire. Ce dernier est dû au fait qu'en mode read_only=True, openpyxl fait entièrement confiance à l'attribut dimensions déclaré dans le fichier afin d'améliorer les performances.

Si le système qui a généré le fichier (comme un ancien module d'exportation ERP ou une bibliothèque Python non standard) écrit ces métadonnées de manière incorrecte, l'itérateur se terminera prématurément sans lever d'exception. Il ne s'agit pas d'un bug de code, mais d'un écart entre les spécifications de format et les données du monde réel qui doit être comblé grâce à une programmation défensive.

Méthode d'ouverture correcte et libération des ressources du mode read_only

read_only=True est le chemin principal de gestion des fichiers volumineux, mais il modifie la gestion du cycle de vie des objets. Les classeurs ordinaires libèrent automatiquement les ressources lors du garbage collection, tandis que les classeurs en lecture seule conservent les références au flux de fichiers ZIP et à l'analyseur XML en bas et doivent appeler explicitement wb.close(). Pour les instructions officielles sur le mode lecture seule, veuillez vous référer à documentation officielle des modes optimisés openpyxl.

Oublier cette étape dans un script ETL qui parcourt plusieurs fichiers peut entraîner des fuites de gestionnaires de fichiers, déclenchant éventuellement OSError: Too many open files. Ce type d'erreur est souvent exposé après plusieurs heures d'exécution et est extrêmement difficile à résoudre.

De plus, le mode lecture seule renvoie ReadOnlyCell au lieu de l'objet Cell standard. Il ne prend pas en charge l'accès aux attributs tels que les styles et les annotations, et ne permet pas non plus la recherche inversée ou la modification des valeurs via les coordonnées. Si vous obtenez des informations sur la zone fusionnée ou définissez la couleur des cellules pendant l'itération, vous obtiendrez AttributeError. Cette limite est intentionnelle pour maintenir une empreinte mémoire quasi constante.

📊 Comparaison de base

Exemple de synthèse : réparation de dimensions et vérification de la sécurité des itérations

Le code suivant montre un processus de lecture défensif typique. Notez qu'il s'agit d'un exemple synthétique utilisé pour démontrer la structure logique, et non le résultat du traitement des données de production réelles. Vous devez préparer un fichier nommé input_large.xlsx pour exécuter ce script, et il doit être exécuté dans un environnement isolé qui verrouille les versions dépendantes. L’exemple n’inclut pas les opérations réseau, les commandes shell ou les informations d’identification sensibles.

from openpyxl import load_workbook

# 合成示例：演示read_only模式下的安全读取与维度校验
# 依赖：openpyxl (需在项目requirements中锁定具体版本)
# 注意：需读者自备 input_large.xlsx，本示例不包含真实数据

def safe_read_large_excel(filepath, expected_min_rows=1000):
    """
    安全读取大Excel文件，包含维度修复与完整性校验。
    :param filepath: Excel文件路径
    :param expected_min_rows: 业务预期的最小行数阈值
    :return: 实际读取的行数
    """
    wb = load_workbook(filename=filepath, read_only=True)
    try:
        ws = wb.active
        # 检查并修复可能错误的维度声明
        # calculate_dimension() 可能返回 'A1:A1' 或其他不准确范围
        dim = ws.calculate_dimension()
        if dim == "A1:A1" or not dim:
            # 重置维度以强制扫描实际数据区域
            # 警告：若文件完全无维度信息，此操作可能遍历全表(104万行)
            ws.reset_dimensions()

        row_count = 0
        header = None
        for row in ws.iter_rows(values_only=True):
            if row_count == 0:
                header = row
                assert header is not None and any(v is not None for v in header), \
                    "首行不应为空或全None"
            row_count += 1

            # 模拟业务处理，避免全量加载到内存
            if row_count % 10000 == 0:
                print(f"已处理 {row_count} 行")

        # 校验读取完整性
        assert row_count >= expected_min_rows, \
            f"行数不足: 期望>={expected_min_rows}, 实际={row_count}。请检查源文件或维度设置。"
        return row_count
    finally:
        # 必须显式关闭，防止句柄泄漏
        wb.close()

# 使用示例（需替换为真实文件路径）
# total = safe_read_large_excel("input_large.xlsx", expected_min_rows=50000)

La clé de ce code n’est pas la lecture elle-même, mais les trois niveaux de protection :

• Dimension Reset : Correction de dimensions dans la déclaration de fichier pour éviter la fin prématurée de l'itérateur.
• Stream Counting : comptez le nombre de lignes pendant la lecture pour faciliter la comparaison avec les attentes.
• Fermeture forcée dans finally : Que la lecture soit réussie ou qu'une erreur se produise au milieu, les ressources du fichier sont libérées.

expected_min_rows n'est pas un seuil standard de l'industrie, mais un résultat commercial basé sur votre connaissance préalable des données sources. Si le nombre réel de lignes est inférieur à cette valeur, le fichier peut être endommagé ou la réparation des dimensions a échoué, et vous devez abandonner à ce moment-là au lieu d'accepter silencieusement des données incomplètes.

Critères d'acceptation et limites de révision manuelle

Ce n’est pas parce que le code peut s’exécuter que les données sont correctes. Le tableau suivant répertorie les exceptions typiques pouvant être rencontrées dans des projets réels et comment les gérer, en distinguant la portée que l'automatisation peut couvrir et les scénarios nécessitant une intervention manuelle.

Note spéciale : reset_dimensions() n'est pas une panacée. Si aucune information de dimension n'est écrite dans le fichier lui-même, cette méthode peut étendre la plage jusqu'à la limite supérieure de la feuille de calcul (1 048 576 lignes), ce qui entraînerait un parcours invalide ou même un délai d'attente. À ce stade, max_row doit être utilisé pour définir manuellement une limite supérieure raisonnable, ou il doit être activement terminé lorsque N lignes vides sont rencontrées d'affilée. Ces jugements ne peuvent pas être portés uniquement par le code et nécessitent le soutien de connaissances du domaine.

🎯 Quelle méthode vous convient ?

Si vous devez traiter des tables contenant des centaines de milliers de lignes à la fois

Utilisez la lecture en streaming read_only=True pour éviter l'explosion de la mémoire en mode par défaut.

Si votre script parcourt plusieurs fichiers

Appelez wb.close() explicitement dans un bloc finally pour éviter les fuites de handle.

Si le nombre de lignes lues est nettement inférieur aux données sources

Vérifiez d'abord si dimensions est mal écrit, essayez reset_dimensions(), puis relisez-le et comparez-le avec le journal d'exportation du système source.

Si vous avez besoin de lire des styles ou des commentaires

Ces propriétés ne sont pas prises en charge en mode lecture seule et doivent plutôt être utilisées en mode par défaut ou gérées séparément dans un autre processus.

Entrée d'exécution d'un exemple de mémoire

Le code suivant s'exécute après la fonction ci-dessus et utilise uniquement des données synthétiques et ne lit pas le fichier client.

from io import BytesIO
from openpyxl import Workbook
source = Workbook()
source.active.append(["id", "value"])
source.active.append(["A", 1])
source.active.append(["B", 2])
out = BytesIO()
source.save(out)
out.seek(0)
assert safe_read_large_excel(out, expected_min_rows=3) == 3

Utilisez cette liste de contrôle pour la traduction de documents

Décidez d’abord quel contenu est du texte et lequel est une formule et un codage commercial, puis traduisez le tableau. Le contrôle post-traduction vérifie non seulement si le chinois a été converti dans la langue cible, mais confirme également si les formules, les identifiants et les relations de référence ont été modifiés.

OmniDoc (portail produit) fournit un portail de traduction de documents multiformat et le site officiel présente des fonctions d'OCR, de glossaire et de comparaison bilingue. Pour les équipes qui souhaitent travailler directement à partir des fichiers, il s’agit d’une voie qui mérite d’être évaluée avec des échantillons réels. Veuillez utiliser les fichiers désensibilisés que vous êtes autorisé à télécharger et utilisez les éléments de vérification de cet article pour examiner les résultats. Cet article ne prétend pas qu'OmniDoc utilise les bibliothèques open source ou les algorithmes internes de l'article, et n'utilise pas non plus d'exemples pour remplacer les tests de performances du produit.

❓ Questions fréquemment posées (FAQ)

1. Pourquoi se termine-t-il à mi-chemin de la lecture et aucune erreur n'est-elle signalée ?

Parce que le mode lecture seule fait confiance au dimensions déclaré par le fichier. Si le système de build écrit ces métadonnées de manière incorrecte, l'itérateur se terminera prématurément sans lever d'exception.

2. Tous les fichiers volumineux peuvent-ils être résolus avec reset_dimensions() ?

ne peut pas. Si le fichier lui-même ne contient aucune information de dimension, cette méthode peut étendre la plage à 1 048 576 lignes, provoquant un délai d'attente et doit être combinée avec la stratégie max_row ou de fin de ligne vide continue.

3. Pourquoi ne puis-je pas lire les styles ou modifier les cellules en mode lecture seule ?

Le mode lecture seule renvoie ReadOnlyCell, ne prend pas en charge l'accès aux styles et aux annotations et n'autorise pas la modification des valeurs. Il s’agit de maintenir une empreinte mémoire presque constante.

4. Que dois-je vérifier après avoir traduit le formulaire ?

En plus du langage correct, vérifiez si les formules, les identifiants et les relations de référence ont été modifiés.

📝Résumé

La clé de la lecture de grands tableaux réside dans la lecture en continu, la vérification des dimensions et la libération des ressources en temps opportun. Le code n'est que le point de départ, et le fait que les données soient complètes dépend de la comparaison et de l'examen manuel.

Essayez-le maintenant : téléchargez le document pour lancer la traduction, en conservant la mise en page d'origine.