xlrd：想要读取多个 xl 文件的工作表并将其存储在一个列表/数组中？（更好的方法？）

发布于 2024-11-13 20:10:17 字数 1544 浏览 2 评论 0原文

我没有太多的 xp 与 xlrd/xlwt，但我已经设法访问我想要从中收集数据的文件之一。我想从目录中的所有文件收集数据并将其移动到一张纸上。我在想如果有什么办法可以将其全部存储在一个数组/列表中，那么很容易输出到 csv。如果这工作量太大，并且有一个简单的方法，请帮忙，否则我正在使用 Idle 来思考想法，到目前为止已经想出了这个：

>>> import xlrd, xlwt
>>> book = xlrd.open_workbook('c:\excelTry\Papineau.csv.xls')
>>> book.sheet_names()
[u'Charge Codes', u'Month']
>>> sh = book.sheet_by_index(1)
>>> #produces:
>>> sh.book
<xlrd.Book object at 0x01213BF0>
>>> for x in range(0, 10):
        sh.row_values(x)
[u'William Papineau', u'Pay Period 11', '', '', u' ', u' ', '', '', '', u'Weekly Total', '', '', u' ', '', '', '', '', u'Weekly Total', u'Biweekly', u'Percent of Effort']
[u'Index Number', u'Index Description', 40678.0, 40679.0, 40680.0, 40681.0, 40682.0, 40683.0, 40684.0, '', 40685.0, 40686.0, 40687.0, 40688.0, 40689.0, 40690.0, 40691.0, '', u'Total', '']
[u'E45776', u'Seat Belt Study', '', 8.0, 8.0, 8.0, 8.0, u' ', '', 32.0, '', '', '', '', '', u' ', '', 0.0, 32.0, 0.4155844155844156]
[u'E43457', u'MultiScaleWaterQuality', '', '', '', '', '', 8.0, '', 8.0, '', 5.0, 8.0, u' ', '', '', '', 13.0, 21.0, 0.2727272727272727]
[u'E45125', u'GLOSS', '', '', '', '', '', '', '', 0.0, '', '', '', 8.0, 8.0, '', '', 16.0, 16.0, 0.2077922077922078]
[u'E45131', u'GLOS AOC Trib Monitoring', '', '', '', '', '', '', '', 0.0, '', '', '', '', '', 8.0, '', 8.0, 8.0, 0.1038961038961039]

这会产生看起来像列表对象的东西，但我所做的每一次操作尝试或追加它会产生错误，指出不可编写脚本或不可迭代。文件迭代将通过 os 模块使用 os.listdir(path) 和 for 循环来处理。任何帮助将不胜感激！

原文

I don't have much xp with xlrd/xlwt but I have managed to access one of the files I want to collect data from. I want to collect data from all files in the directory and move it to one sheet. I was thinking if there is someway I can store it all in one array/list it would be easy to output to a csv. If this is too much work and there is a simple way plz help, otherwise I am using Idle to play around with ideas and have come up with this so far:

>>> import xlrd, xlwt
>>> book = xlrd.open_workbook('c:\excelTry\Papineau.csv.xls')
>>> book.sheet_names()
[u'Charge Codes', u'Month']
>>> sh = book.sheet_by_index(1)
>>> #produces:
>>> sh.book
<xlrd.Book object at 0x01213BF0>
>>> for x in range(0, 10):
        sh.row_values(x)
[u'William Papineau', u'Pay Period 11', '', '', u' ', u' ', '', '', '', u'Weekly Total', '', '', u' ', '', '', '', '', u'Weekly Total', u'Biweekly', u'Percent of Effort']
[u'Index Number', u'Index Description', 40678.0, 40679.0, 40680.0, 40681.0, 40682.0, 40683.0, 40684.0, '', 40685.0, 40686.0, 40687.0, 40688.0, 40689.0, 40690.0, 40691.0, '', u'Total', '']
[u'E45776', u'Seat Belt Study', '', 8.0, 8.0, 8.0, 8.0, u' ', '', 32.0, '', '', '', '', '', u' ', '', 0.0, 32.0, 0.4155844155844156]
[u'E43457', u'MultiScaleWaterQuality', '', '', '', '', '', 8.0, '', 8.0, '', 5.0, 8.0, u' ', '', '', '', 13.0, 21.0, 0.2727272727272727]
[u'E45125', u'GLOSS', '', '', '', '', '', '', '', 0.0, '', '', '', 8.0, 8.0, '', '', 16.0, 16.0, 0.2077922077922078]
[u'E45131', u'GLOS AOC Trib Monitoring', '', '', '', '', '', '', '', 0.0, '', '', '', '', '', 8.0, '', 8.0, 8.0, 0.1038961038961039]

this produces what looks like a list object but every attempt I have made to manipulate or append it produces errors saying not scriptable or iterable. The file iteration will be handled with the os module using os.listdir(path) and a for loop. Any help would be greatly appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

等你爱我 2024-11-20 20:10:17

到目前为止，在您的代码中，您似乎没有对从工作表中获取的值执行任何操作。也许有些代码没有粘贴到问题中...

您能包含最后一行代码的输出吗？

您说您想将其全部存储在一个列表中。
尝试这样的事情：

final = []
for rowx in xrange(sh.nrows):
    final.extend(sh.row_values(rowx))

另外：
请小心 Windows 路径。仅当后面的字母与反斜杠不形成转义序列（例如 \t 或制表符）时，单反斜杠才起作用。其他选项（选项 3 可能是最好的；除非有特定原因不使用它）：

原始字符串：book = xlrd.open_workbook(r'c:\excelTry\Papineau.csv.xls')
正斜杠： book = xlrd.open_workbook('c:/excelTry/Papineau.csv.xls')
os.path.join：
book = xlrd.open_workbook(os.path.join('c:','excelTry','Papineau.csv.xls'))

So far in your code you don't appear to be doing anything with the values you get from the worksheet. Maybe some of the code didn't get pasted into the question...

Would you be able to include the output of that last line of code?

You say that you want to store it all in one list.
Try something like this:

final = []
for rowx in xrange(sh.nrows):
    final.extend(sh.row_values(rowx))

Also:
Be careful with Windows paths. Single-backslashes will work only if the following letter does not, with the backslash, form an escape sequence (e.g. \t or tab). Other options (option 3 is probably best; unless there is a specific reason not to use it):

Raw strings: book = xlrd.open_workbook(r'c:\excelTry\Papineau.csv.xls')
Forward-slashes: book = xlrd.open_workbook('c:/excelTry/Papineau.csv.xls')
os.path.join:
book = xlrd.open_workbook(os.path.join('c:','excelTry','Papineau.csv.xls'))

回复收藏 0 原文

对你的占有欲 2024-11-20 20:10:17

data = []
for i in xrange(sh.nrows):
    data.append(sh.row_values(i))
it will append each rows from xls file into list "data".
eg: [['a','b'],['c','d'],['e','f']] like this .

data = []
for i in xrange(sh.nrows):
    data.append(sh.row_values(i))
it will append each rows from xls file into list "data".
eg: [['a','b'],['c','d'],['e','f']] like this .

回复收藏 0 原文

~没有更多了~