使用xlwt将excel文件列表的列表写入 [英] Write list of lists to excel file using xlwt
问题描述
我有一个列表,如:
[
[u'email',u'salutation' ,u'firstname',u'lastname',u'remarks',None,None,None,None,None],
[u'harry@harrypotter.com',u'Mr',u'Daniel' ,u'Radcliffe',u'expecto patronum',None,None,None,None,None],
[u'snape@harrypotter.com',u'Mr',u'Severus',u'Snape ',u'Always',None,None,None,None,None],
]
我想把它插入一个excel文件。可以通过写每个元素一个接一个地执行。
book = xlwt.Workbook(encoding =utf-8 )
/ pre>
sheet1 = book.add_sheet(Sheet 1)
row = 0
在列表数据中的l:
col = 0
for e在l:
如果e:
sheet1.write(row,col,e)
col + = 1
row + = 1
但是,由于整个列表的每个元素都必须遍历,所以这种方法看起来效率并不高。使用
xlwt
在python中执行相同的更有效的方法?解决方案编辑:修正了基准代码中的错误。
你可以缩短一些事情,使他们更加pythonic:
for i,l in enumerate(listdata):
for j,col in enumerate(l):
sheet.write(i,j,col)
但据我所知,没有简单的方法写入,因为
csv.reader
。
PS:在您提供的代码中,您不会增加
行
或col
,所以您覆盖嵌套for循环的每次迭代(0,0)
中的单元格。小心!使用枚举
将修复它。
基准
事实证明,
join
将每行与逗号一起写入,比使用枚举一次要快三倍。
这里有一些测试代码:
import xlwt
import timeit
def wrapper(fn, * args,** kwargs)
def wrapped():
return fn(* args,** kwargs)
return wrapped
def excel_writer()
xldoc = xlwt.Workbook()
sheet1 = xldoc.add_sheet(Sheet1,cell_overwrite_ok = True)
rows = [[str(y)for x in xrange(100)] for x in xrange(10000)]
fn1 = wrapper(cell_writer,rows,sheet1)
fn2 = wrapper(row_writer,rows,sheet1)
打印timeit.timeit(fn1,number = 10) / 10
print timeit.timeit(fn2,number = 10)/ 10
xldoc.save('myexcel.xls')
def cell_writer(rows,表格):
为i,列在枚举(行)中:
为j,col为enu
sheet.write(i,j,col)
def row_writer(rows,sheet):
rows = [','.join(row)对于行行$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
excel_writer()
与
number = 1
(当然除以1):
cell_writer:
15.2915050441
p>
row_writer :
0.205128928987
与
number = 10
:
cell_writer :
17.3386430596
row_writer :
0.204951626882
我将大时差归因于
加入的增加速度
超过写作excel。速度方面最大的瓶颈,当然是excel写作。
但是,请注意,在Excel中拆分单元格所需的时间可能会超过用
row_writer
方法这也可能给最终用户带来不便。锻炼判断!I have a list of lists like:
[ [u'email', u'salutation', u'firstname', u'lastname', u'remarks', None, None, None, None, None], [u'harry@harrypotter.com', u'Mr', u'Daniel', u'Radcliffe', u'expecto patronum', None, None, None, None, None], [u'snape@harrypotter.com', u'Mr', u'Severus', u'Snape', u'Always', None, None, None, None, None], ]
I want to insert this to an excel file. It is possible to do so one by one by writing each element.
book = xlwt.Workbook(encoding="utf-8") sheet1 = book.add_sheet("Sheet 1") row = 0 for l in listdata: col = 0 for e in l: if e: sheet1.write(row, col, e) col+=1 row+=1
But this method does not look very efficient as the each element of the entire list has to be traversed. Is there a more efficient method to do the same in python with
xlwt
?解决方案EDIT: Fixed error in benchmark code.
You can shorten things a bit to make them more pythonic:
for i, l in enumerate(listdata): for j, col in enumerate(l): sheet.write(i, j, col)
But as far as I know there is no easy method to write to as there is with
csv.reader
.
PS: In your supplied code, you never increment
row
orcol
, so you overwrite the cell at(0,0)
every iteration of the nested for loop. Careful! Usingenumerate
will fix that.
Benchmarks
As it turns out,
join
ing each row together with a comma and writing it is roughly 3 times faster than using enumerate once.Here's some test code:
import xlwt import timeit def wrapper(fn, *args, **kwargs): def wrapped(): return fn(*args, **kwargs) return wrapped def excel_writer(): xldoc = xlwt.Workbook() sheet1 = xldoc.add_sheet("Sheet1", cell_overwrite_ok=True) rows = [[str(y) for y in xrange(100)] for x in xrange(10000)] fn1 = wrapper(cell_writer, rows, sheet1) fn2 = wrapper(row_writer, rows, sheet1) print timeit.timeit(fn1, number=10)/10 print timeit.timeit(fn2, number=10)/10 xldoc.save('myexcel.xls') def cell_writer(rows, sheet): for i, row in enumerate(rows): for j, col in enumerate(row): sheet.write(i, j, col) def row_writer(rows, sheet): rows = [', '.join(row) for row in rows] for i, strrow in enumerate(rows): sheet.write(i, 0, strrow) if __name__ == '__main__': excel_writer()
with
number = 1
(divided by 1 of course):cell_writer:
15.2915050441
row_writer:
0.205128928987
with
number = 10
:cell_writer:
17.3386430596
row_writer:
0.204951626882
I attribute the big time difference to the increased speed of
join
over writing to excel. The biggest bottleneck in terms of speed, of course, the excel writing.However, be aware that the time it takes to split the cells apart in excel may outweigh the time saved with the
row_writer
approach. It may also inconvenience the end user; exercise judgement!这篇关于使用xlwt将excel文件列表的列表写入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!