10

I am trying to export a list to excel via the Win32COM client whihc i have imported at the header. The object i created is coded as below, but I cant seem to get it to export each value to its own row in the spreadsheet. If I can get a good pointer (other than give 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()
Improve this question
3
  • 1
    Is there any reason to use Win32COM rather than xlwt? for i,e in enumerate(datalist): sheet.write(i,1,e) would do what you want, after you open the book and sheet of course. Commented Aug 1, 2012 at 20:51
  • Why not just write it as a csv and use excel's own import facilities? Commented Aug 2, 2012 at 3:22
  • @Logan Thanks mate. That worked perfect, I will look into xlwt/xlrd functionality as I need. Commented Aug 2, 2012 at 8:28

3 Answers 3

Reset to default
20

Since you seemed to like my answer/comment, here's an answer proper:

Python Excel has just about everything you'd ever need. If you want something more integrated but seems limited, there is IronSpread. XLRD and XLWT are great packages, but they don't support *.xlsx files. IronSpread is Windows only and only support '07 and '10 versions of Excel. Each has it's caveats. In the end, you can use both (edit as *.xlsx, then save as to *.xls (I had someone who had speed issues with large *.xls files, but my script wrote 200mb of text from that thing in like 1 minute.)).

Oh, and I would definitely read (skim) the documentation for interesting features such as getting the cell types etc of xlrd/xlwt. It's worth it, if only because it's short and will save you the learning curve of experimenting.

Super short example of xlwt:

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 example of xlrd:

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

WOW at IronSpread. Thats is impressive. Really nice to keep a user in the Excel environment, so no nose bleeds looking at coding. +1000 to you sir for your answers.
one caveat, i just noticed some slight compatibility issues with Office 64 bit version, throws some errors on exiting Excel, but seems to work. Hopefully a 64bit version is in the pipeline soon.
Which has compatibility issues?
It seems that Ironspread does not fully like Excel 64bit. On excel open, a dialog states "Failed to Load xll" I disabled every other Addin and also COM addins, but like I said, it works perfectly after I dismiss the dialog.
Well, give them time. It's a new product.
2

You are missing both the cell row number in Range and need to increment 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()
Improve this answer

3 Comments

thanks, did you mean to write x++ as x+1. the error that occurs when I run: ` File "C:\MyFile.py", line 195, in excel sh.Range("A1").Offset(0,Rowx).Value = i #You need to increment the cell row File "C:\Python27\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x7\Range.py", line 686, in call , ColumnIndex) File "C:\Python27\lib\site-packages\win32com\client_init_.py", line 459, in ApplyTypes self._oleobj_.InvokeTypes(dispid, 0, wFlags, retType, argTypes, *args),` <:continued next:>
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)` By any chance, does the fact I am running Win and Office 64bit affect the COM connection? (I know i had that problem with another software recently using ActiveX)
I've updated my post to fix the issue you addressed in the first comment.
0 
class Signal(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y

    def to_dict(self):
        return {
            'x': self.x,
            'y': self.y,
        }

def file_save_excel(dataset, filename, sheet_name, new_sheet):
    dataset = dataset.applymap(lambda x: x.encode('unicode_escape').
                               decode('utf-8') if isinstance(x, str) else x)

    print('Writing the processed dataset to excel file...')
    writer = pd.ExcelWriter(filename, engine='openpyxl')

    if os.path.exists(filename) and new_sheet:
        book = openpyxl.load_workbook(filename)
        writer.book = book

    dataset.to_excel(writer, sheet_name=sheet_name, index=False)
    writer.save()
    writer.close()
    return True

dataframe = pandas.DataFrame.from_records([s.to_dict() for s in signals])
file_save_excel(dataframe, 'FileName.xlsx', 'SheetName', True)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.