Sending Data to Excel with C++

Hi folks,


I'm working on a program that reads a text file which has columns of dates, times and associated values, then sends the columns to an excel sheet. The entire file follows the same pattern.


Text file sample:

Code:

---

2012-05-14 14:08:12.498 8 InfoMSG1  N  2.719  7.117 11.335 12.795 16.228 16.619 18.117 19.608 20.899  6883      0  2994  2153  2089      0 {InfoMSG2}
2012-08-14 14:08:38.644 8 InfoMSG3 N  2.974  6.732  9.577 10.596 13.355 13.737 14.871 16.327 17.641  7142      0  1399  1709  1637      0 {InfoMSG4}

---

At the moment, I'm using a fairly inelegant solution of breaking each sentence into tokens delimited by whitespace and storing each column in a separate array as follows:

Code:

---

    string            line;
    string            *col1, *col2, *col4, *col5, *col21;       


        col1 = new string[fileLength];
    col2 = new string[fileLength];
    col3 = new LONG[fileLength];
    col4 = new string[fileLength];
    col5 = new string[fileLength];
    col6 = new FLOAT[fileLength];
    col7 = new FLOAT[fileLength];
    col8 = new FLOAT[fileLength];
    col9 = new FLOAT[fileLength];
    col10 = new FLOAT[fileLength];
    col11 = new FLOAT[fileLength];
    col12 = new FLOAT[fileLength];
    col13 = new FLOAT[fileLength];
    col14 = new FLOAT[fileLength];
    col15 = new FLOAT[fileLength];
    col16 = new FLOAT[fileLength];
    col17 = new FLOAT[fileLength];
    col18 = new FLOAT[fileLength];
    col19 = new FLOAT[fileLength];
    col20 = new FLOAT[fileLength];
    col21 = new string[fileLength];


while(getline(file, line))
    {
        std::stringstream    linestream(line);
               
        //Reading space separated columns into string arrays
        linestream >> col1[i] >> col2[i] >> col3[i] >> col4[i] >> col5[i] >> col6[i] >> col7[i] >> col8[i] >> col9[i] >> col10[i] >> col11[i];
        linestream >> col11[i] >> col13[i] >> col14[i] >> col15[i] >> col16[i] >> col17[i] >> col18[i] >> col19[i] >> col20[i] >> col21[i];


        i++;
    }

---

In my research of the COM interface process from C++ to Excel I haven't been able to find a method of passing single byte strings to excel. The only method I've been able to have any success with is detailed in this MSDN article - How to automate Excel from C++ without using MFC or #import


It uses a VARIANT safearray to pass data to Excel, and the only way I can see to use my arrays as above is to convert the std::strings in each element first to wstrings, then to BSTR.


I would think there's probably a better way to do this conversion but haven't come up with one.


Anyway, my conversion form single byte to wide string is achieved using the following function:

Code:

---

wstring stws(const string &src_string)
{
    size_t src_len = src_string.length();


    if(0 == src_len)
        return L"";


    wchar_t *buf = new(std::nothrow) wchar_t[src_len + 1];


    if(0 == buf)
        return L"";


    mbstowcs(buf, src_string.c_str(), src_len);
    buf[src_len] = L'\0';


    wstring final_string = buf;


    if(0 != buf)
        delete [] buf;


    return final_string;
}

---

The further conversion from wide string to BSTR is carried out like so:

Code:

---

   
    wcol1 = new wstring[fileLength];
    wcol2 = new wstring[fileLength];
    wcol4 = new wstring[fileLength];
    wcol5 = new wstring[fileLength];
    wcol21 = new wstring[fileLength];


    bcol1 = new BSTR[fileLength];
    bcol2 = new BSTR[fileLength];
    bcol4 = new BSTR[fileLength];
    bcol5 = new BSTR[fileLength];
    bcol21 = new BSTR[fileLength];


for(int i=0; i<10; i++)
    {
        wcol1[i] = stws(col1[i]);
        wcol2[i] = stws(col2[i]);
        wcol4[i] = stws(col4[i]);
        wcol5[i] = stws(col5[i]);
        wcol21[i] = stws(col21[i]);


        bcol1[i] = SysAllocString(wcol1[i].c_str());
        bcol2[i] = SysAllocString(wcol1[i].c_str());
        bcol4[i] = SysAllocString(wcol1[i].c_str());
        bcol5[i] = SysAllocString(wcol1[i].c_str());
        bcol21[i] = SysAllocString(wcol1[i].c_str());
    }

---

I've printed the BSTR values to the console using wcout to confirm that they are correct.


Now, to pass this data into an excel sheet. I'm using the method outlined in the MSDN article linked above, creating a safe array with my list of values from the BSTR arrays:

Code:

---

   
// Create a safearray of variants...
    VARIANT arr;
    arr.vt = VT_ARRAY | VT_VARIANT;
    {
        SAFEARRAYBOUND sab[2];
        sab[0].lLbound = 1; sab[0].cElements = 10;
        sab[1].lLbound = 1; sab[1].cElements = 1;
        arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
    }


// Fill safearray with some values...
    for(int i=0; i<10; i++) {
        for(int j=0; j<2; j++) {
            // Create entry value for (i,j)
            VARIANT tmp;
            tmp.vt = VT_BSTR;
            tmp.bstrVal = bcol2[i];
            // Add to safearray...
            long indices[] = {i,j};
            SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
        }
    }

---

Then I do the usual COM tasks, creating a new Workbook, acquiring the Active Worksheet, getting a range object, before setting the values in the range as follows:

Code:

---

   
// Set range with our safearray...
    AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, arr);

---

For the scenario described above, where I only copy a single column to excel at a time, what I have does the trick.


My major problem at this stage is finding a way to loop in the two dimensions of the safearray to copy the rest of the columns at the same time. I know I can do it by creating a safearray for each column following a similarly inelegant approach to how I dealt with splitting the data into columns in the first place, but I'm sure there's a better way to do it than that.


Any help would be greatly appreciated, ty.

A few suggestions.

1. Since you're ultimately going to be passing strings onto excel, there is little point in having lots of different types for the fields. Make them all strings.

2. Read up on the Standard Template Library (STL), which can take all the burden of managing memory.

Here's a little something to study

Code:

---

#include <iostream>
#include <string>
#include <fstream>
#include <vector>
#include <sstream>

using namespace std;

int main ( ) {
  ifstream in("wibble.txt");
  vector< vector<string> >  data;
  string line;

  // read the file
  while ( getline(in,line) ) {
    vector<string> fields;
    std::stringstream    linestream(line);
    for ( int col = 0 ; col < 21 ; col++ ) {
      string f;
      linestream >> f;
      fields.push_back(f);
    }
    data.push_back(fields);
  }

  // output by rows
  for ( vector< vector<string> >::size_type row = 0 ; row < data.size() ; row++ ) {
    for ( vector<string>::size_type col = 0 ; col < data[row].size() ; col++ ) {
      cout << data[row][col] << ",";
    }
    cout << endl;
  }

  // output by columns
  for ( vector<string>::size_type col = 0 ; col < data[0].size() ; col++ ) {
    for ( vector< vector<string> >::size_type row = 0 ; row < data.size() ; row++ ) {
      cout << data[row][col] << ",";
    }
    cout << endl;
  }
}

---

Hi Salem, ty for the response.

I had already struck upon vectors as a way of avoiding the ugly array declarations at the start, so that's a bit of progress anyway!

Trying to take a look at the code you posted for me there and I'm getting an error in the line where we define a vector of vectors (line 11 - that's what it looks like it does to me anyway!). The errors are:

Code:

---

Error        1        error C3872: '0xa0': this character is not allowed in an identifier
Error        2        error C2146: syntax error : missing ';' before identifier 'data'       
Error        3        error C2065: 'data' : undeclared identifier

---

Any idea what the issue could be there? I'm using Visual C++ 2005 fwiw.

Thanks

Strangely enough, when I changed the name of the vector of vectors the error went away! Not to worry, I'll continue going through your example and get back to you :)

I can see that this is going to be pretty confusing with the multidimensional action and vectors of vectors, but this is definitely progress at least!

Here's my understanding of what we're doing:

For each line in my input file we create a vector of strings called "fields". We then read in the line, using linestream to break it up into its constituent words. Each word is stored as a separate element of "fields". Each time we reach the end of a line (dictated by the number of words - 21 - on each line) we store the vector "fields" as an element of the "parent" vector "data". This is repeated until we reach the end of the file.

I think I have that right, although my mind is a bit inside out from trying to follow it :P

I'm thinking I'll need to create a similar vector of vectors for my conversion from string to wstring?

> I think I have that right, although my mind is a bit inside out from trying to follow it :P
That's pretty good analysis :)

> I'm thinking I'll need to create a similar vector of vectors for my conversion from string to wstring?
Or you could make data into a wstring to begin with.
Or you could convert each field to a wstring just at the moment you want to send it to excel.

Boom - got it working just the way I wanted :D

TYVM for the help Salem. I'll put together a post detailing the changes I've made soon, might be useful for someone in future.

Actually one question that can probably be answered quickly here without me spending ages looking for an answer, but to make the program more robust is there any way to change the way we determine that we've reached the end of each line so that we're not relying on the line always being 21 columns long?

I'll take a look for a method myself anyway but if anyone has a quick answer I'd be much obliged :)

You should be able to do something like

Code:

---

    if ( linestream >> f ) {
        fields.push_back(f);
    } else {
        break;
    }

---

Are you sure you need the vector? Couldn't you store the data directly into your 2d-safearray and then pass that to Excel?

Code:

---

void putString(VARIANT &varr, long row, long col, string &str) {
    VARIANT elem;
    elem.vt = VT_BSTR;
    elem.bstrVal = SysAllocString(stws(str).c_str());
    long ind[] = {row, col};
    SafeArrayPutElement(arr.parray, ind, (void *) &elem);
}

//...
    VARIANT varr;
    varr.vt = VT_ARRAY | VT_VARIANT;
    {
        SAFEARRAYBOUND sab[2];
        sab[0].lLbound = 1; sab[0].cElements = fileLength;
        sab[1].lLbound = 1; sab[1].cElements = NCOLUMNS;
        arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
    }

    for (long row = 1; getline(file, line); ++row)
    {
        stringstream linestream(line);
        string str;
        for (long col = 1; linestream >> str; col++)
            putString(varr, row, col, str);
    }

// make pXlRange a 2D range

    AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, arr);

---

The above assumes that you really want the lower bound of the safearray to be 1 (that's why row and col start at 1).