使用Python读取Excel数据并写入到CSV、XML和文本_Python

前言

Excel工作簿是常用的表格格式，许多数据呈现、数据分析和数据汇报都是以Excel工作表的形式进行。然而，在实际的数据管理、分析或自动化流程构建过程中，我们常常需要将这些Excel中的数据迁移至更其他数据系统，或者以文本形式存储以便与其他程序进行交互。Python作为一种强大且灵活的编程语言，能够高效地实现这一目标。本文将演示如何运用Python编程语言，将Excel工作表中的丰富数据导入到CSV、XML或文本中，我们也可以使用文中的方法读取数据并写入到其他文件或数据系统中。

本文所使用的方法需要用到Spire.XLS for Python，可从官网获取或通过PyPI：pip install Spire.XLS。

示例Excel文件

使用Python读取Excel数据并写入到CSV、XML和文本

直接将Excel工作表转换为CSV

使用此API，我们可以直接获取指定工作表并转换为CSV文件。以下是操作步骤：

导入所需模块。
创建 Workbook 类的对象 wb。
使用 wb.LoadFromFile() 方法加载指定路径下的Excel文件。
获取指定索引位置处的工作表 ws。
使用 Worksheet.SaveToFile(string fileName, string Separator, Encoding) 方法将工作表转换为CSV文件并保存。
释放资源。

代码示例

from spire.xls import *
from spire.xls.common import *

# 创建 Workbook 对象
wb = Workbook()

# 加载 Excel 文件
wb.LoadFromFile("示例.xlsx")

# 获取工作表
ws = wb.Worksheets.get_Item(0)

# 将工作表保存为 CSV 文件
ws.SaveToFile(f"output/{ws.Name}.csv", ",", Encoding.get_UTF8())
wb.Dispose()

提取结果

使用Python读取Excel数据并写入到CSV、XML和文本

读取Excel工作表数据保存到CSV

除了直接转换外，我们还可以直接读取指定单元格范围的数据，并写入CSV文件，来实现自定义的数据提取。以下是操作步骤：

导入所需模块。
创建 Workbook 类的对象 wb。
使用 wb.LoadFromFile() 方法加载指定路径下的Excel文件。
获取指定索引位置处的工作表 ws。
确定工作表的已使用范围（即有数据的部分）作为 usedRange。
遍历 usedRange 中的每一行和每一列：
- 获取单元格的值，并对包含逗号的字符串单元格值添加双引号。
- 将处理过的单元格值添加到 rowData 列表中。
对每一行的 rowData 列表进行处理，将其转换为逗号分隔的字符串，并在末尾添加换行符，然后将结果追加到 data 字符串中。
打开一个CSV文件（以当前工作表名称命名），以写模式和UTF-8编码将 data 字符串内容写入该文件。
使用 wb.Dispose() 方法释放资源，清理内存。

代码示例

from spire.xls import *
from spire.xls.common import *

# 创建 Workbook 对象
wb = Workbook()

# 加载 Excel 文件
wb.LoadFromFile("示例.xlsx")

# 获取工作表
ws = wb.Worksheets.get_Item(0)

# 获取已使用的区域
usedRange = ws.AllocatedRange

data = ""
# 遍历已使用的区域
for i in range(1, usedRange.Rows.Count):
    rowData = []
    for j in range(len(usedRange.Rows.get_Item(i).Columns)):
        # 获取单元格的值
        cellValue = usedRange[i + 1, j + 1].Value
        # 对包含逗号的数据添加引号
        if isinstance(cellValue, str) and ',' in cellValue:
            cellValue = f'"{cellValue}"'
        rowData.append(cellValue)
    data += ','.join(rowData) + '\n'

# 将数据写入 CSV 文件
with open(f"output/CSV/{ws.Name}.csv", "w", encoding='utf-8') as f:
    f.write(data)
wb.Dispose()

提取效果

使用Python读取Excel数据并写入到CSV、XML和文本

将Excel工作簿转换为OpenXML

使用 Workbook 类的 SaveAsXml 方法可以直接将一个工作簿转换为Open XML文件。以下是操作步骤：

导入所需模块。
创建 Workbook 类的对象 wb。
使用 wb.LoadFromFile() 方法加载指定路径下的Excel文件。
使用 Workbook.SaveAsXml() 方法将工作簿转换为Open XML文件并保存。
释放资源。

代码示例

from spire.xls import *
from spire.xls.common import *

# 创建 Workbook 对象
wb = Workbook()

# 加载 Excel 文件
wb.LoadFromFile("示例.xlsx")

# 保存为 OpenXML 文件
wb.SaveAsXml("output/工作簿转OpenXML.xml")
wb.Dispose()

提取效果

使用Python读取Excel数据并写入到CSV、XML和文本

读取Excel工作表数据写入XML

除了将工作簿直接转换为Open XML文件外，我们还可以读取表格数据，制作自定义的XML文件。我们可以引入 xml.etree.ElementTree 来方便对XML的写入。以下是操作示例：

导入所需模块。
创建 Workbook 类的对象 wb。
使用 wb.LoadFromFile() 方法加载指定路径下的Excel文件。
获取工作表 ws，其索引为0。
获取工作表中的已使用区域 usedRange。
创建 XML 的根元素 root，命名为 “Worksheet”。
在 XML 根元素下创建子元素 “Name”，并将其文本内容设置为当前工作表的名称。
遍历已使用的区域（按行）：
- 对于每一行，创建一个 “Row” 子元素。
- 再对该行的每个单元格进行遍历（按列）：
  - 获取单元格的值。
  - 在 “Row” 元素下创建一个 “Cell” 子元素。
  - 在 “Cell” 元素下进一步创建一个 “Data” 子元素，将单元格值转换为字符串并设置为其文本内容。
将所有生成的 XML 元素构建成一个完整的 XML 树结构，存储在 xml_tree 变量中。
使用 xml_tree.write() 方法将 XML 数据写入名为 “工作表写入XML.xml” 的文件中，同时指定 UTF-8 编码和添加 XML 声明。
最后，调用 wb.Dispose() 方法释放资源，关闭并清理 Excel 工作簿对象。

代码示例

import xml.etree.ElementTree as ET
from spire.xls import *
from spire.xls.common import *

# 创建 Workbook 对象
wb = Workbook()

# 加载 Excel 文件
wb.LoadFromFile("示例.xlsx")

# 获取工作表
ws = wb.Worksheets.get_Item(0)

# 获取已使用的区域
usedRange = ws.AllocatedRange

# 创建 XML 根元素
root = ET.Element("Worksheet")

# 设置工作表名称
name_element = ET.SubElement(root, "Name")
name_element.text = ws.Name

# 遍历已使用的区域
for i in range(1, usedRange.Rows.Count):
    row_element = ET.SubElement(root, "Row")
    for j in range(len(usedRange.Rows.get_Item(i).Columns)):
        # 获取单元格的值
        cellValue = usedRange[i + 1, j + 1].Value
        # 创建单元格元素
        cell_element = ET.SubElement(row_element, "Cell")
        # 创建数据元素
        data_element = ET.SubElement(cell_element, "Data")
        data_element.text = str(cellValue)

# 创建 XML 文档
xml_tree = ET.ElementTree(root)
# 将数据写入 XML 文件
xml_tree.write("output/XML/工作表写入XML.xml", encoding="utf-8", xml_declaration=True)
wb.Dispose()

代码示例

使用Python读取Excel数据并写入到CSV、XML和文本

读取Excel工作表数据保存为文本

我们还可以直接读取表格文件写入普通文本文件，用于其他用途。以下是操作步骤：

导入模块。
初始化一个新的 Workbook 实例 wb。
使用 wb.LoadFromFile() 函数加载 Excel 文件。
选取第一个工作表（索引为0），赋值给变量 ws。
获取该工作表的已使用区域，存放在变量 usedRange 中。
初始化一个空字符串 data，用于收集所有单元格的数据。
遍历已使用的行与列范围：
- 对于每一行（从第二行开始计数，因为Excel索引从1开始）：
  - 对于该行内的每一个单元格：
    - 获取单元格的值，并将其转换为字符串形式，追加到 data 后面。
    - 如果当前单元格不是本行的最后一个单元格，则在其后添加制表符 \t 分隔数据。
  - 完成一行的所有单元格之后，在 data 后面添加换行符 \n，以便在输出时区分不同行的数据。
使用 with open() 语句以“写入”模式打开一个新文件，文件名基于当前工作表的名称加上 .txt 扩展名，且路径设为 “output/” 目录下。
将之前拼接好的包含所有单元格数据的字符串 data 写入所创建的文本文件中。
关闭文件流，确保数据成功写入。
调用 wb.Dispose() 方法释放资源，关闭并清理 Excel 工作簿对象。

代码示例

from spire.xls import *
from spire.xls.common import *

# 创建 Workbook 对象
wb = Workbook()

# 加载 Excel 文件
wb.LoadFromFile("示例.xlsx")

# 获取工作表
ws = wb.Worksheets.get_Item(0)

# 获取已使用的区域
usedRange = ws.AllocatedRange

data = ""
# 遍历已使用的区域
for i in range(1, usedRange.Rows.Count):
    for j in range(len(usedRange.Rows.get_Item(i).Columns)):
        # 获取单元格的值
        cellValue = usedRange[i + 1, j + 1].Value
        data += str(cellValue)
        if j < len(usedRange.Rows.get_Item(i).Columns) - 1:
            data += "\t"
    data += "\n"

# 将数据写入 CSV 文件
with open(f"output/{ws.Name}.txt", "w", encoding='utf-8') as f:
    f.write(data)
wb.Dispose()

提取结果

使用Python读取Excel数据并写入到CSV、XML和文本