For Beginners: Useful C++ Code to extract/read the values in a CELL RANGE(e.g: A1:A5) - for Excel Add-Ins

May 3, 2014 at 12:03 PM
Edited May 3, 2014 at 12:15 PM
Hi folks,
I'm new to Excel API development but am learning quickly!
Just thought I'd post this bit of code - which I know will help many newbies like myself - trying to receive data/strings from Excel API, and find (to my amusement) that it returns funny-looking strings! (They are I believe are Pascal-style strings, which begin with the LENGTH field as the fist byte).

------------ This code is my personal work and not released in any guaranteed form-
It's aim is to give you a quick way(without having to scour the internet and Excel APi documentation) to :
1) extract data from cells, and,
2) convert Pascal-styled NON null-terminating strings
3) and have them all neatly placed in a std::vector- ready for play!

Works like a charm for me, but not to much error checking included yet. So use your own discretion! :)
//----------------------------------------------------------------------
/* This function  extracts the meaingful part of a Pascal-style STRING value that is being returned by Excel API.
   The first BYTE stores the total characters in the string; then follows those characters(actual string), 
   followed by what seems like Excel garbage! 

*/
int  ExcelStr2CStr(const LPSTR  lpInStr, char* pcOutStr, const int cnOutBufLen )
{
    const int cnLen = lpInStr[0]; // get the length sotred in the first byte..(a Pascal-style String?)
    memset(pcOutStr, cnOutBufLen, '\0');
    int i=0;

    for ( i=1; i <= cnLen ; i++) // extract c-string out of Pascal-style String..
    {
        pcOutStr[i-1] = lpInStr[i];
    }
// null-terminate string to make it cmopatible with all string processing functions/requirements
    pcOutStr[i-1] ='\0'; 
    return i-1; // since we started counting at index 1, and not 0.
}

//----------------------------------------------------------------------
/* This function takes as input- an Excel CellRange(e.g. A2:A10), reads the values stored in each cell(in Pascal string format, I believe)
 and then, extracts the actual string content(for each ecll), and returns it in the 2nd input parame(vCellValues).
 It returns the number of strings extracted

 */

int GetRangeValues(OPER oRange, vector<string> &vCellValues)
{
    static char buf[SMALL_STRING_LEN] ;
    int nStrLen=0;
    vCellValues.clear(); // empty vector of current contents
       
      // SINGLE cell , and not a range. Therefore, it will hold a string value in the .str 
    if (oRange.xltype== xltypeStr) field
    {
        nStrLen = ExcelStr2CStr(oRange.val.str, buf, SMALL_STRING_LEN); // extract the actual value out of the "Pascal-style String"
        vCellValues.push_back(buf);
    }
    else  // (if range such as A2: A5)
    {
        for (int i=0; i < (oRange.val.array.rows * oRange.val.array.columns); i++  )// iterate thru all rows in this columns(am unable to get the HTML escape sequence for '+', so apologies for the#43 bit in the for-loop
        {
                       // extract c-style string from pascal-style str returned by Excel
            ExcelStr2CStr(oRange.val.array.lparray[i].val.str, buf, SMALL_STRING_LEN); 
            vCellValues.push_back(buf) ;
        }
    }

    return (int) vCellValues.size();
}