将多个csv文件合并到一个xls工作簿Python 3中

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.

我们正处于从python 2.7到python 3.5的过渡期。这是一个公司范围内的变化,我们当前的大多数脚本都是用2.7编写的,没有额外的库。我已经利用了我们正在使用的Anaconda发行版,并且已经使用2to3模块更改了我们的大部分脚本或完全重写它们。我被困在一段代码上,我没有写,而原作者不在这里。他也没有提供评论,所以我只能猜测整个剧本。 95%的脚本正常工作,直到它创建7个具有不同解析信息的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")

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.

这是在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"

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.

有关应采取哪些措施来解决上述错误的任何想法?我试过多次重写,但我得到类似的错误或新的错误。我正在考虑只是想出一个全新的方法来创建xls,而不是pandas。

1 个解决方案

#1

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.

不确定为什么它会出错。值得努力重写代码并使用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()

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文件,您可能希望读取所有csv文件并将其作为df存储在dict中。然后使用新的工作表名称将每个df写入Excel。