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

Discussion in 'Python' started by noydb, Dec 3, 2010.

  1. noydb

    noydb Guest

    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
    noydb, Dec 3, 2010
    #1
    1. Advertising

  2. noydb

    Steve Holden Guest

    On 12/3/2010 6:21 PM, noydb wrote:
    > 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
    --
    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/
    Steve Holden, Dec 5, 2010
    #2
    1. Advertising

  3. noydb

    noydb Guest

    On Dec 5, 8:42 am, Steve Holden <> wrote:
    > On 12/3/2010 6:21 PM, noydb wrote:
    >
    >
    >
    >
    >
    > > 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
    > --
    > 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
    noydb, Dec 5, 2010
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    1,794
    extentechjohn
    Nov 21, 2006
  2. John Morgan
    Replies:
    4
    Views:
    338
    John Morgan
    Dec 5, 2006
  3. Deniz Bahar
    Replies:
    2
    Views:
    468
    Andrey Tarasevich
    Mar 9, 2005
  4. =?ISO-8859-2?Q?Miros=B3aw?= Makowiecki

    Reading of file by next of map file and by next of file descriptor.

    =?ISO-8859-2?Q?Miros=B3aw?= Makowiecki, Jul 10, 2007, in forum: C++
    Replies:
    1
    Views:
    787
    Alf P. Steinbach
    Jul 10, 2007
  5. Greg
    Replies:
    1
    Views:
    169
    Gunnar Hjalmarsson
    Jun 6, 2005
Loading...

Share This Page