Sending Data to Excel with C++

This is a discussion on Sending Data to Excel with C++ within the C++ Programming forums, part of the General Programming Boards category; Hi folks, I'm working on a program that reads a text file which has columns of dates, times and associated ...

  1. #1
    Registered User
    Join Date
    May 2012
    Posts
    10

    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.

  2. #2
    and the hat of wrongness Salem's Avatar
    Join Date
    Aug 2001
    Location
    The edge of the known universe
    Posts
    32,452
    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;
      }
    }
    If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
    If at first you don't succeed, try writing your phone number on the exam paper.
    I support http://www.ukip.org/ as the first necessary step to a free Europe.

  3. #3
    Registered User
    Join Date
    May 2012
    Posts
    10
    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

  4. #4
    Registered User
    Join Date
    May 2012
    Posts
    10
    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

  5. #5
    Registered User
    Join Date
    May 2012
    Posts
    10
    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?

  6. #6
    and the hat of wrongness Salem's Avatar
    Join Date
    Aug 2001
    Location
    The edge of the known universe
    Posts
    32,452
    > 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.
    If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
    If at first you don't succeed, try writing your phone number on the exam paper.
    I support http://www.ukip.org/ as the first necessary step to a free Europe.

  7. #7
    Registered User
    Join Date
    May 2012
    Posts
    10
    Boom - got it working just the way I wanted

    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.

  8. #8
    Registered User
    Join Date
    May 2012
    Posts
    10
    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

  9. #9
    and the hat of wrongness Salem's Avatar
    Join Date
    Aug 2001
    Location
    The edge of the known universe
    Posts
    32,452
    You should be able to do something like
    Code:
         if ( linestream >> f ) {
            fields.push_back(f);
         } else {
            break;
         }
    If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
    If at first you don't succeed, try writing your phone number on the exam paper.
    I support http://www.ukip.org/ as the first necessary step to a free Europe.

  10. #10
    - - - - - - - - oogabooga's Avatar
    Join Date
    Jan 2008
    Posts
    2,808
    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).
    The cost of software maintenance increases with the square of the programmer's creativity. - Robert D. Bliss

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Data Organization and Excel
    By uniqst3r in forum C Programming
    Replies: 4
    Last Post: 05-26-2008, 01:37 AM
  2. excel (detecting duplicate data)
    By xddxogm3 in forum Tech Board
    Replies: 6
    Last Post: 01-11-2007, 09:21 PM
  3. Writing data to Excel file
    By Hankyaku in forum C++ Programming
    Replies: 3
    Last Post: 04-05-2003, 07:33 AM
  4. how do access and read data in from an excel file
    By hamilton73 in forum C++ Programming
    Replies: 2
    Last Post: 09-26-2001, 10:00 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21