将多个csv文件合并成单个xls工作簿 Python 3

Question

我们正在努力完成从Python 2.7到Python 3.5的工作转换。这是公司范围内的变化，我们大部分当前的脚本都是用2.7编写的，没有其他的库。我利用了我们正在使用的Anaconda发行版，并且已经使用了2to3模块或完全重写了大部分的脚本。不过还有一段代码卡住了我，我没有写过，原始作者也不在现场。他也没有提供注释，所以我只能猜测脚本的全部内容。95%的脚本正常运行，直到最后它创建了七个不同解析数据的CSV文件，然后有一个自定义函数将CSV文件合并为一个XLS工作簿，每个CSV作为新选项卡。

import csv
import xlwt
import glob
import openpyxl
from openpyxl import Workbook

Parsefiles = glob.glob(directory + '/' + "Parsed*.csv")
def xlsmaker():
    for f in Parsefiles:
        (path, name) = os.path.split(f)
        (chort_name, extension) = os.path.splittext(name)
        ws = wb.add_sheet(short_name)
        xreader = csv.reader(open(f, 'rb'))
        newdata = [line for line in xreader]
        for rowx, row in enumerate(newdata)
            for colx, value in enumerate(row):
                if value.isdigit():
            ws.write(rowx, colx, value)

xlsmaker()

for f in Parsefiles:
    os.remove(f)

wb.save(directory + '/' + "Finished" + '' + oshort + '' + timestr + ".xls")

这段代码完全使用 Python 2.7 编写，并在 Python 2.7 中运行正常。但是，在 Python 3.5 中运行时会报错。

File "parsetool.py", line 521, in (module)
  xlsmaker()
File "parsetool.py", line 511, in xlsmaker
  ws = wb.add_sheet(short_name)
File "c:\pythonscripts\workbook.py", line 168 in add_sheet
  raise TypeError("The paramete you have given is not of the type '%s'"% self._worksheet_class.__name__)
TypeError: The parameter you have given is not of the type "Worksheet"

针对上述错误，有什么解决方法吗？我已经尝试了多次重写，但是出现了类似的错误或新的错误。我正在考虑采用全新的方法创建xls文件，可能使用pandas。

- Tobias Wright

Answer 1

不确定为什么会出错。重写代码并使用pandas代替是值得尝试的。Pandas可以将每个csv文件读入单独的数据框，并将所有数据框保存为xls(x)文件中的单独工作表。这可以通过使用pandas的ExcelWriter完成。例如：

import pandas as pd
writer = pd.ExcelWriter('yourfile.xlsx', engine='xlsxwriter')
df = pd.read_csv('originalfile.csv')
df.to_excel(writer, sheet_name='sheetname')
writer.save()

由于您有多个csv文件，您可能希望读取所有csv文件并将它们作为数据框储存在字典中。然后使用新的工作表名称将每个数据框写入Excel。

多-csv 示例：

import pandas as pd
import sys
import os

writer = pd.ExcelWriter('default.xlsx') # Arbitrary output name
for csvfilename in sys.argv[1:]:
    df = pd.read_csv(csvfilename)
    df.to_excel(writer,sheet_name=os.path.splitext(csvfilename)[0])
writer.save()

（请注意，为解决缺少 xlsxwriter 导入的错误可能需要运行pip install openpyxl）

Note: It may be necessary to run pip install openpyxl to resolve errors with missing xlsxwriter import.

- user5550905

---

感谢您的回复。真正解决问题的是修复了一些CSV文件的编码。除此之外，一些文件必须具有与工作簿不同的特定编码才能使脚本正常运行。非常奇怪，但它确实有效，现在我有四个在PyQt GUI下运行的脚本。我可能会添加pandas以确保它将来可以正常工作。 - Tobias Wright

嘿@TobiasWright，你能在这里发布代码吗？我正在尝试跟进。谢谢。 - JPC

名称错误：未定义 'xlsxwriter'。此外，似乎 pandas 假定 openpyxl 可用。您能否让这个答案更完整一些？（我从 miniconda 开始，如果这解释了为什么我没有安装所有内容。） - r2evans

NameError: name 'xlsxwriter' is not defined 出问题了，因为需要按名称发送。你正在发送一个模块。正确的行是： writer = pd.ExcelWriter('yourfile.xlsx', engine='xlsxwriter')请将 'xlsxwriter' 作为字符串发送。 - hecvd

@r2evans: 当我做这个的时候，我需要运行 pip install openpyxl 才能让它工作。似乎 pandas 不会为您完成此部分。 - Stabledog

如果您收到有关对writer.save()的Pandas警告，则答案是改为使用writer作为上下文管理器。完整示例 - Christian Long

Answer 2

这是对接受的答案的一点扩展。Pandas 1.5会对writer.save()的调用进行投诉。解决方法是将writer作为上下文管理器使用。

import sys
from pathlib import Path
import pandas as pd

with pd.ExcelWriter("default.xlsx") as writer:
    for csvfilename in sys.argv[1:]:
        p = Path(csvfilename)
        sheet_name = p.stem[:31]
        df = pd.read_csv(p)
        df.to_excel(writer, sheet_name=sheet_name)

该版本还会缩短表格名称以符合Excel的最大表格名称长度，即31个字符。

- Christian Long

Answer 3

您可以使用以下代码将多个 .csv 文件读入一个大的 .xlsx Excel 文件中。 我还添加了用于将 ',' 替换为 '.'（或反之亦然）以提高在 Windows 环境下的兼容性和根据您的语言环境设置的代码。

import pandas as pd
import sys
import os
import glob
from pathlib import Path

extension = 'csv'
all_filenames = [i for i in glob.glob('*.{}'.format(extension))]

writer = pd.ExcelWriter('fc15.xlsx') # Arbitrary output name
for csvfilename in all_filenames:

    txt = Path(csvfilename).read_text()
    txt = txt.replace(',', '.')

    text_file = open(csvfilename, "w")
    text_file.write(txt)
    text_file.close()
    
    print("Loading "+ csvfilename)
    df= pd.read_csv(csvfilename,sep=';', encoding='utf-8')

    df.to_excel(writer,sheet_name=os.path.splitext(csvfilename)[0])
    print("done")
writer.save()
print("task completed")

- Miguel Tomás

Answer 4

如果您的 CSV 文件是使用 GBK 编码的中文文件，您可以使用以下代码：

import pandas as pd
import glob
import datetime
from pathlib import Path

now = datetime.datetime.now()

extension = "csv"
all_filenames = [i for i in glob.glob(f"*.{extension}")]


with pd.ExcelWriter(f"{now:%Y%m%d}.xlsx") as writer:
    for csvfilename in all_filenames:
        print("Loading " + csvfilename)
        df = pd.read_csv(csvfilename, encoding="gb18030")
        df.to_excel(writer, index=False, sheet_name=Path(csvfilename).stem)
        print("done")
print("task completed")

- brucmao