Python高效转TXT到Excel教程

引言：文本数据转Excel与数据类型处理

在数据处理工作中，我们经常需要将非结构化的文本数据（如.txt文件）转换成结构化的表格形式，并导入到Excel等工具中进行进一步分析。在此过程中，确保数据类型（尤其是数值类型）的准确性至关重要。本教程将指导您如何使用Python的openpyxl库，高效地完成这一任务，并着重解决在转换过程中可能遇到的数据类型识别与处理问题，例如将文本中的数字正确地转换为Excel中的整数或浮点数，以及处理混合数据类型（如包含文本和数字的行）。

准备工作：安装与导入openpyxl

在开始之前，请确保您的Python环境中已安装openpyxl库。如果尚未安装，可以通过以下命令进行安装：

pip install openpyxl

然后，在您的Python脚本中导入该库：

import openpyxl

读取文本文件

首先，我们需要打开并读取包含原始数据的文本文件。假设我们有一个名为AI.txt的文件，其中包含学生姓名和两门考试成绩，每行代表一个学生的数据，各数据项之间用空格分隔。

示例AI.txt内容：

Name Score1 Score2
StudentA 85 90
StudentB 78 82
StudentC 92 88
StudentD 70 75
StudentE 95 93
StudentF 80 85

我们可以使用Python的with open()语句来安全地打开并读取文件内容。readlines()方法将文件中的每一行读取为一个字符串，并存储在一个列表中。

with open('AI.txt', 'r', encoding='utf-8') as file:
    lines = file.readlines()

注意： 建议指定encoding参数，以避免潜在的编码问题。

数据清洗与写入Excel（关键：混合数据类型处理）

读取文件后，我们需要逐行处理数据。每行数据通常是一个字符串，需要进行分割和类型转换。一个常见的挑战是，某些列可能是文本（如学生姓名），而另一些列是数字（如分数）。直接对所有数据项进行int()转换会导致错误。

为了解决这个问题，我们需要在转换前判断数据项是否为数字。str.isdigit()方法可以帮助我们判断一个字符串是否只包含数字。

# 创建一个新的Excel工作簿
workbook = openpyxl.Workbook()
# 创建一个名为“qq”的工作表，并将其设置为第一个工作表
sheet = workbook.create_sheet(index=0, title='qq')

# 逐行处理从txt文件读取的数据
for line in lines:
    # 移除行首尾的空白符，并按空格分割成列表
    cur = line.strip().split()

    # 使用列表推导式智能转换数据类型
    # 如果数据项是纯数字，则转换为整数；否则保留为字符串
    processed_row = [int(item) if item.isdigit() else item for item in cur]

    # 将处理后的行数据追加到Excel工作表
    sheet.append(processed_row)

解释：

• line.strip().split()：strip()移除行末的换行符和首尾空白，split()将字符串按空格分割成列表。
• int(item) if item.isdigit() else item：这是处理混合数据类型的关键。它检查item是否为全数字字符串。如果是，则将其转换为int类型；否则，它将保持为原始字符串类型。这样，像"StudentA"这样的文本数据就不会被强制转换为整数而引发错误。

计算并添加平均值列

在数据写入Excel后，我们可能需要根据现有数据计算新的指标，例如两门分数的平均值。这需要在Excel中遍历已写入的行，提取数值数据进行计算，并将结果写入新的列。

为了确保计算的准确性，我们需要再次检查单元格内容的类型，只对数值类型（整数或浮点数）进行平均值计算。

# 为平均值列添加标题
# 假设数据从第二行开始是实际数据，第一行是标题
# 原始数据有3列 (姓名, 分数1, 分数2)，所以平均值将是第4列
sheet.cell(row=1, column=4, value="Mean")

# 从第二行开始遍历所有数据行（跳过标题行）
# max_col=3表示遍历到第3列（即Score2）
for row_idx, row in enumerate(sheet.iter_rows(min_row=2, max_col=3, max_row=sheet.max_row), start=2):
    # 提取当前行中的所有数值（整数或浮点数）
    numeric_values = [cell.value for cell in row if isinstance(cell.value, (int, float))]

    # 确保有数值可供计算，避免除以零错误
    if numeric_values:
        avg_value = sum(numeric_values) / len(numeric_values)
        # 将计算出的平均值写入当前行的第4列
        sheet.cell(row=row_idx, column=4, value=avg_value)

解释：

• sheet.cell(row=1, column=4, value="Mean")：在第一行第四列添加“Mean”作为平均值列的标题。
• sheet.iter_rows(min_row=2, max_col=3, max_row=sheet.max_row)：这个迭代器用于遍历工作表中的行。
  • min_row=2：从第二行开始处理，跳过第一行的标题。
  • max_col=3：表示每行只考虑前三列（姓名、分数1、分数2）来提取数值。
  • max_row=sheet.max_row：遍历到工作表的最后一行。
• numeric_values = [cell.value for cell in row if isinstance(cell.value, (int, float))]：这是另一个关键点。它遍历当前行的所有单元格，并使用isinstance()函数检查单元格的值是否为int或float类型。只有数值类型的值才会被包含在numeric_values列表中，从而确保平均值计算的准确性。
• if numeric_values:：在计算平均值之前进行检查，防止在没有数值数据的情况下尝试除以零。
• sheet.cell(row=row_idx, column=4, value=avg_value)：将计算出的平均值写入当前行的第四列。row_idx由enumerate提供，确保我们写入正确的行。

保存Excel文件

最后一步是将所有处理过的数据保存到一个新的Excel文件中。

# 保存Excel工作簿到指定文件
workbook.save('AI56.xlsx')

注意： 如果AI56.xlsx文件已存在，它将被覆盖。

完整示例代码

以下是将所有步骤整合在一起的完整Python程序：

import openpyxl

# 1. 读取文本文件
with open('AI.txt', 'r', encoding='utf-8') as file:
    lines = file.readlines()

# 2. 创建Excel工作簿和工作表
workbook = openpyxl.Workbook()
sheet = workbook.create_sheet(index=0, title='qq') # index=0确保它是第一个工作表

# 3. 逐行处理数据，智能转换类型并写入Excel
for line in lines:
    cur = line.strip().split()
    # 智能转换：如果是数字则转int，否则保留字符串
    processed_row = [int(item) if item.isdigit() else item for item in cur]
    sheet.append(processed_row)

# 4. 添加平均值列标题
# 假设原始数据有3列 (姓名, 分数1, 分数2)，平均值将是第4列
sheet.cell(row=1, column=4, value="Mean")

# 5. 计算并添加平均值
# 从第二行开始遍历数据行（跳过标题行）
# max_col=3表示遍历到第3列（即Score2），用于提取分数
for row_idx, row in enumerate(sheet.iter_rows(min_row=2, max_col=3, max_row=sheet.max_row), start=2):
    # 提取当前行中所有数值类型（int或float）的单元格值
    numeric_values = [cell.value for cell in row if isinstance(cell.value, (int, float))]

    if numeric_values:
        avg_value = sum(numeric_values) / len(numeric_values)
        # 将平均值写入当前行的第4列
        sheet.cell(row=row_idx, column=4, value=avg_value)

# 6. 保存Excel文件
workbook.save('AI56.xlsx')

print("数据已成功从AI.txt读取，并写入AI56.xlsx，数值已转换为整数/浮点数，并添加了平均值列。")

注意事项与最佳实践

1. 数据类型验证的必要性： 在处理来自外部源的数据时，永远不要假设所有数据都符合预期的类型。使用isdigit()、isinstance()等方法进行类型检查是编写健壮代码的关键。
2. Excel中数据类型的显示： openpyxl会将Python的int和float类型正确写入Excel。Excel会自动识别并以数字格式显示这些单元格。如果写入的是字符串，即使内容看起来像数字，Excel也可能默认将其视为文本。
3. 错误处理： 对于更复杂的场景，您可能需要添加try-except块来处理文件不存在、数据格式不正确等潜在错误。
4. 列索引与行索引： openpyxl中的行和列索引都是从1开始的，而不是Python列表的0开始。在处理sheet.cell()或iter_rows()时请注意这一点。
5. 内存管理： 对于非常大的文件，readlines()可能会一次性将所有内容加载到内存中。可以考虑逐行读取文件（例如使用for line in file:）以减少内存消耗。
6. 代码可读性： 使用有意义的变量名、添加注释以及适当的空行，可以大大提高代码的可读性和可维护性。

通过遵循本教程的步骤和最佳实践，您将能够高效且准确地将文本数据转换并存储到Excel中，同时确保数据的类型和结构符合您的分析需求。