We help IT Professionals succeed at work.

Get row count in Excel 2002

bspotswood
bspotswood asked
on
1,118 Views
Last Modified: 2011-09-20
I'm working on a C++ program that needs to read data in from an Excel spreadsheet. I'm using C++ .NET 2003 and #import'ed the excel libraries. I can create a new application, open an existing excel workbook, get an interface to the worksheet in it, and make it all visible just fine. The only problem I'm having right now is converting this Visual Basic function for another program into a C++ equivalent for this program:

Function Excel_GetRowCount(objSheet As Object) As Long
    On Error Goto ErrQuit
    Dim lRowCount As Long
    lRowCount = objSheet.Cells.Find("*", objSheet.Range("A1"), , , xlByRows, xlPrevious).Row
    Excel_GetRowCount = lRowCount
ErrQuit:
End Function


so here is what is not working for me in C++:
namespace Excel{
  int Excel::Excel_GetRowCount(Excel::_WorksheetPtr &xlSheet){
    try{
      _variant_t xlRange = xlSheet->Range["A1"];
      Excel::RangePtr xlRowCountRange = xlSheet->Cells->Find("*", xlRange, NULL, NULL, 1, (XlSearchDirection)2);
      return xlRowCountRange->Row;
    }catch(System::Exception* e){
      MessageBox::Show(e->Message, "D'oh!");
      return 0;
    }catch(...){}
    return 0;
  }
}

Right now I'm getting an exception from the component thrown at the line "Excel::RangePtr xlRowCountRange = ...".
and just incase anyone gets curious, here are the #import's that I'm making:

#import "C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.DLL" \
  rename("RGB", "RGBMSO") \
  rename("DocumentProperties", "DocumentPropertiesMSO")
#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
#import "libid:00020813-0000-0000-C000-000000000046" \
  rename("DialogBox", "DialogBoxXL") \
  rename("CopyFile", "CopyFileXL") \
  rename("RGB", "RGBXL") \
  rename("ReplaceText", "ReplaceTextXL")

...and if I cant get a solution to this then I suppose I'll just go to stopping reading when it reads an empty cell value.
Comment
Watch Question

Author

Commented:
Increasing points +280, gonna have to buy points if you guys don't hurry up and answer! =P

Author

Commented:
bumped it up to 1200 points, oh my!

Author

Commented:
oh, apparently cant put it that high, what a shame. Setting it to 500 point max. Darn :(
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That was exactly what I needed. I didn't know about the SpecialCells method, thanks! Here's the code that makes it work:

namespace Excel{
  int Excel::Excel_GetRowCount(Excel::_WorksheetPtr &xlSheet){
    try{
      int xlRows = xlSheet->Cells->SpecialCells(xlCellTypeLastCell)->Row;
      xlRows--; // Row returns 1 row beyond the last row with any data in it
      return xlRows;
    }catch(...){} //Ignore errors and just return 0
    return 0;
  }
}

Thanks a bunch, pamboo!

Author

Commented:
Hmm, actually, you shouldn't subtract one from that value. Infact, that returns the last cell that has any data or formatting in it. So like if you have 3 rows of data in your spreadsheet, but then you skip down to the 30th row and put any formatting on one of the cells, then it will tell you that you have 30 rows. I was thinking you needed to subtract one because it was counting some formatting on the row after my data as being a good row. So this solution works fine if you have no formatting in the spreadsheet.

Author

Commented:
Okay, since I'm keying off of the first column, I found a work around that gets the last cell with data in it for the first column. The first thing to do is get the very last possible cell in the sheet (65536x1). Check that cell to see if there is text in it. If there is, then the row count is 65536, otherwise use the GetEnd method and specify xlUp as the parameter. This will get the next cell upwards with text in it. If this cells row is greater than 1 then return it, otherwise if it is 1 then there may or may not be text in it. So check it to see if there is text in it when it is on row 1. If there is text, then return one, otherwise return zero because no cells have text in them.

int Excel::Excel_GetRowCount(Excel::_WorksheetPtr &xlSheet){
  try{
    Excel::RangePtr rpVeryLastCell = xlSheet->Cells->GetItem(65536, 1);
    System::String* strCellText = new System::String(rpVeryLastCell->Text.bstrVal);
    if(strCellText->Length > 0) return rpVeryLastCell->Row;
    rpVeryLastCell = rpVeryLastCell->GetEnd(xlUp);
    if(rpVeryLastCell->Row > 1) return rpVeryLastCell->Row;
    strCellText = new System::String(rpVeryLastCell->Text.bstrVal);
    if(strCellText->Length > 0) return 1;
  }catch(...){}
  return 0;
}


and of course if you weren't keying off of column one and so you wanted to find the last row with any text in any column in the row, then you could simply turn the above function into a routine that takes a column number for an argument, and then use it inside of another function which will loop through each column and find the greatest row returned.

Commented:
Welcome wood ... I am happy that it helped ... good ... ur explanation is very good ..actually it will help others when someone come across the same situation ...

Regards,
Raj
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.