Columns and Rows in Excel

Discussion in 'Python' started by Anand, Dec 19, 2005.

  1. Anand

    Anand Guest

    Greetings,

    How can I find the number of active columns and rows used in an excel work
    sheet?
    What I mean is how can i find the last column and row or cell position,
    where the data is stored in an excel sheet?

    A code snippet would be of great help.

    Thanks for your cooperation and help.

    Best regards,
    Anand
     
    Anand, Dec 19, 2005
    #1
    1. Advertisements

  2. Is the worksheet currently open in Excel or OpenOffice,
    and you wish to query the application for that information?

    Or do you want to read the spreadsheet in from a file?
     
    Steven D'Aprano, Dec 19, 2005
    #2
    1. Advertisements

  3. Anand

    wittempj Guest

    In Python you probaly best follow a recipe like this.

    1 Save the file in Excel as a csv file.
    2.Study the reader object of Python's csv module :
    http://docs.python.org/lib/module-csv.html
    3. Read a row for row in in a list, split it on comma, count the
    elements, the maximum of all these is the number of columns you look
    for.
    4. The number of rows you read is the number of rows in the sheet.
     
    wittempj, Dec 19, 2005
    #3
  4. Anand

    Anand Guest

    Greetings,

    The worksheet is currently opened in Excel.
    And I want to read the data from the excel worksheet.
    Instead of looping through the entire worksheet, I want to limit the looping
    to the rows and columns used so far!

    Thanks and regards,
    Anand
     
    Anand, Dec 19, 2005
    #4
  5. Anand

    Steve Holden Guest

    Anand wrote [at the top of the post, which was naughty]:
    An observation: if I record a macro and then use CTRL/End to go to the
    "bottom right-hand corner" of the spread, I see that Excel records:

    ActiveCell.SpecialCells(xlLastCell).Select

    It's then just a matter of finding out the row and column number of the
    currently-selected cell (or of xlLastCell if you don't want to change
    locations).

    regards
    Steve
     
    Steve Holden, Dec 19, 2005
    #5
  6. Anand,

    A worksheet has a UsedRange property that may be what you need.

    Gerard

    using Excel = Microsoft.Office.Interop.Excel;

    private Excel.Workbook thisWorkbook = null;

    protected void ThisWorkbook_Open()
    {
    Excel.Worksheet sheet;
    Excel.Range usedrange;
    Excel.Range lastcell;

    sheet = (Excel.Worksheet) thisWorkbook.Sheets[1];
    usedrange = sheet.UsedRange;
    lastcell = sheet.Cells[ usedrange.Rows.Count, usedrange.Columns.Count
    ];
    }


    If you have access to the Spreadsheet in question you can name ranges
    of cells:

    Insert -> Names -> Define

    Then

    usedrange = sheet.get_Range( "DATA", Type.Missing );

    for example. (Don't worry about Type.Missing).
     
    Gerard Flanagan, Dec 19, 2005
    #6
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.