Sending Data to Excel with C++

[chopshardiman]

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:

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:

	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 - http://support.microsoft.com/kb/216686

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:

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:

	
	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:

	
// 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:

	
// 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.

[VictorN]

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:

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}

Excel can read text files (tab delimited or delimited with some other character like comma, semicolon and so on), so why reinvent the wheel?

Besides, why are you using new to create the arrays of string other classes? What is wrong with using std::vector or, if you prefer MFC then CAraay class?

[chopshardiman]

Hi Victor.

I have an excel template that the values need to be inserted into. Some calculations and graphing will be automatically done using the values then. I briefly explored the CSV option but I couldn't figure out a way to use it with a template so I went down the route you see.

I don't have a good answer to your second question, could you give a quick rundown of what your approach involves?

[VictorN]

...
I don't have a good answer to your second question, could you give a quick rundown of what your approach involves?

Have a look at>
vectors
C++ Tutorial: A Beginner's Guide to std::vector, Part 1

[chopshardiman]

Thanks for that.

It looks like using vector it should be possible to get rid of all those ugly array declarations at the beginning.

I'm not sure if it's possible to use the linestream method I was using earlier with vectors (I wasn't able to get it working trivially easily at least), so I've changed it to this:

    
        vector<string> strVec(fileLength);

        string			line;

        int x = 0;
 
	do{
		strVec[x] = line;
        ++x;
	}while(
		x < fileLength && getline(file, line, ' ')
	);

This reads in the input file "file", and stores each word in the file as an element of the vector as expected. An issue I'm having is that with my single space delimiter, some of the elements being written to the vector are whitespace (since there is more than one space between some of the words).

Is there a simple way to either change my delimiter so this no longer happens or else delete the "empty" vector strings?

[VictorN]

Thanks for that.

It looks like using vector it should be possible to get rid of all those ugly array declarations at the beginning.

You could also create a class or stuct containing all the needed data fro the columns and then use only one vector / vector of this class...

[Eri523]

If I really couldn't use the CSV or whatever-delimited import features of Excel in such a situation, I'd use VBA instead.

I don't have a good answer to your [VictorN] second question, could you give a quick rundown of what your approach involves?

This statement apparently indicates that it probably would be considerably less effort for you to learn some VBA (even from scratch) rather than wrapping your head around what we have here.