Export Python list to Excel

Question

I am trying to export a list for Excel through the Win32COM client, which I imported in the header. The created object is encoded as shown below, but I cannot force it to export each value to its own row in the spreadsheet. If I can get a good pointer (other than giving up python !!: D), I would appreciate it.

class XcelExport(): def excel(self): app = 'Excel' xl = win32.gencache.EnsureDispatch('%s.Application' % app) ss = xl.Workbooks.Open(r'C:\MyFile.xls') sh = ss.ActiveSheet xl.Visible = True sleep(.1) sh.Cells(1,1).Value = 'Export-to-%s : Items' % app sleep(.1) for i in EventTableRFT: sh.Range("A").Value = i sh.Cells(i+2,1).Value = "End of the List!" xprt = XcelExport() xprt.excel() 

Answer 1

Since you seem to like my answer / comment, here is the correct answer:

Python Excel has almost everything you will ever need. If you want something more integrated, but it seems limited, IronSpread . XLRD and XLWT are great packages, but they do not support * .xlsx files. IronSpread is Windows only and supports Excel versions '07 and '10 only. Everyone has warnings. In the end you can use both (change as * .xlsx and then save as * .xls (I had someone who had speed problems with large *. Xls files, but my script wrote 200 mb of text from this things in like 1 minutes.)).

Oh, and I would definitely read through the documentation of interesting features like getting cell types, etc. xlrd / xlwt. It’s worth it, if only because it is short and will save you from studying the experiment.

Super short xlwt example:

 import xlwt from tempfile import TemporaryFile book = xlwt.Workbook() sheet1 = book.add_sheet('sheet1') supersecretdata = [34,123,4,1234,12,34,12,41,234,123,4,123,1,45123,5,43,61,3,56] for i,e in enumerate(supersecretdata): sheet1.write(i,1,e) name = "random.xls" book.save(name) book.save(TemporaryFile()) 

Super short xlrd example:

 import xlrd from xlrd import open_workbook book = open_workbook('random.xls') sheet1 = book.sheet_by_index(0) data = [] for i in xrange(sheet1.nrows): data.append(sheet1.cell(i,1).value) 

Answer 2

You are missing the cell row number in Range and you need to increase the cell row after each iteration of the loop.

 sh.Range("A1").Offset(0,x).Value = i 

This change should work.

 class XcelExport(): def excel(self): app = 'Excel' xl = win32.gencache.EnsureDispatch('%s.Application' % app) ss = xl.Workbooks.Open(r'C:\MyFile.xls') sh = ss.ActiveSheet xl.Visible = True sleep(.1) sh.Cells(1,1).Value = 'Export-to-%s : Items' % app sleep(.1) x=0 for i in EventTableRFT: sh.Range("A1").Offset(0,x).Value = i #You need to increment the cell row x+=1 sh.Cells(i+2,1).Value = "End of the List!" xprt = XcelExport() xprt.excel()