将多个csv文件合并到一个xls工作簿Python 3中 [英] Combine multiple csv files into a single xls workbook Python 3

问题描述

我们正在从python 2.7过渡到python 3.5.这是公司范围内的变化，我们当前的大多数脚本都是用2.7编写的，没有其他库.我已经利用了我们正在使用的Anaconda发行版，并且已经通过使用2to3模块或完全重写了它们来更改了大多数脚本.不过，我只停留在一段代码上，而我没有编写，原始作者不在这里.他也没有提供评论，所以我只能猜测整个脚本.95％的脚本可以正常工作，直到最终创建具有不同解析信息的7个csv文件后，该脚本具有一个自定义功能，可以将csv文件组合到和xls工作簿中，每个csv作为新选项卡.

We are in the transition at work from python 2.7 to python 3.5. It's a company wide change and most of our current scripts were written in 2.7 and no additional libraries. I've taken advantage of the Anaconda distro we are using and have already change most of our scripts over using the 2to3 module or completely rewriting them. I am stuck on one piece of code though, which I did not write and the original author is not here. He also did not supply comments so I can only guess at the whole of the script. 95% of the script works correctly until the end where after it creates 7 csv files with different parsed information it has a custom function to combine the csv files into and xls workbook with each csv as new tab.

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中运行时会引发错误.

This was written all in python 2.7 and still works correctly if I run it in python 2.7. The issue is that it throws an error when running in 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(可能是熊猫).

Any ideas about what should be done to fix the above error? Iv'e tried multiple rewrites, but I get similar errors or new errors. I'm considering just figuring our a whole new method to create the xls, possibly pandas instead.

推荐答案

不确定为什么会出错.重写代码并使用pandas是值得的.熊猫可以将每个csv文件读取到一个单独的数据框中，并将所有数据框另存为xls(x)文件中的单独图纸.这可以通过使用熊猫的ExcelWriter来完成.例如.

Not sure why it errs. It is worth the effort to rewrite the code and use pandas instead. Pandas can read each csv file into a separate dataframe and save all dataframes as a separate sheet in an xls(x) file. This can be done by using the ExcelWriter of pandas. E.g.

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文件，并将它们作为df存储在字典中.然后使用新的工作表名称将每个df写入Excel.

Since you have multiple csv files, you would probably want to read all csv files and store them as a df in a dict. Then write each df to Excel with a new sheet name.

多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()

(请注意，可能需要 pip安装openpyxl 来解决缺少 xlsxwriter 导入的错误.)

(Note that it may be necessary to pip install openpyxl to resolve errors with xlsxwriter import missing.)

这篇关于将多个csv文件合并到一个xls工作簿Python 3中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！