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.