How to add data into exisitng Excel file at next open row?


N

noydb

How can you determine the next open row in an existing Excel file such
that you can start adding data to the cells in that row? As in below,
I want a variable in place of the 6 (row 6 in the four ws1.Cells(x,1)
lines), but have no other way of knowing what row I am on besides
looking to the first free cell in column A. How to do? Examples I
see make it seem really complicated - this can't be that hard.

Thanks for any help.

worksheet = "C:\\Excel_Reports\\ea" + ea + "report"# + ".xls"
xlApp = win32com.client.Dispatch("Excel.Application")
xlApp.Visible = 1
xlApp.Workbooks.Open(worksheet) ## for existing file
##xlApp.SheetsInNewWorkbook = 1
##wb = xlApp.Workbooks()
ws1 = xlApp.Worksheets(1)

ws1.Cells(6,1).Value = "selection"
ws1.Cells(6,2).Value = count
ws1.Cells(6,3).Value = epcFloat
ws1.Cells(6,8).Value = currentGMT

wb.SaveAs(worksheet)
wb.Close(False) ## False/1
 
Ad

Advertisements

S

Steve Holden

How can you determine the next open row in an existing Excel file such
that you can start adding data to the cells in that row? As in below,
I want a variable in place of the 6 (row 6 in the four ws1.Cells(x,1)
lines), but have no other way of knowing what row I am on besides
looking to the first free cell in column A. How to do? Examples I
see make it seem really complicated - this can't be that hard.

Thanks for any help.

worksheet = "C:\\Excel_Reports\\ea" + ea + "report"# + ".xls"
xlApp = win32com.client.Dispatch("Excel.Application")
xlApp.Visible = 1
xlApp.Workbooks.Open(worksheet) ## for existing file
##xlApp.SheetsInNewWorkbook = 1
##wb = xlApp.Workbooks()
ws1 = xlApp.Worksheets(1)

ws1.Cells(6,1).Value = "selection"
ws1.Cells(6,2).Value = count
ws1.Cells(6,3).Value = epcFloat
ws1.Cells(6,8).Value = currentGMT

wb.SaveAs(worksheet)
wb.Close(False) ## False/1

You might want to take a look at the xlrd library. This lets you read
Excel spreadsheets even on Unix platforms and without the use of COM
magic. There's also an xlwt module for writing spreadsheets. However I
understand that the two together may not be as convenient as modifying a
spreadsheet in place.

In particular, if sh is a spreadsheet then sh.nrows gives you the number
of rows currently used in the sheet.

regards
Steve
 
Ad

Advertisements

N

noydb

You might want to take a look at the xlrd library. This lets you read
Excel spreadsheets even on Unix platforms and without the use of COM
magic. There's also an xlwt module for writing spreadsheets. However I
understand that the two together may not be as convenient as modifying a
spreadsheet in place.

In particular, if sh is a spreadsheet then sh.nrows gives you the number
of rows currently used in the sheet.

regards
 Steve
--
Steve Holden           +1 571 484 6266   +1 800 494 3119
PyCon 2011 Atlanta March 9-17      http://us.pycon.org/
See Python Video!      http://python.mirocommunity.org/
Holden Web LLC                http://www.holdenweb.com/- Hide quoted text -

- Show quoted text -

Thanks, good to keep in mind. I have used xlrd before in cases where
i'm not sure if excel is installed on a user's machine.

Someone else helped, provided this>

NextRow = ws1.Range("A1").SpecialCells(xlLastCell).Row + 1

Although to get it to work for me, I have to use the number code for
some reason, like >

NextRow = ws1.Range("A1").SpecialCells(11).Row + 1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top