Click here to Skip to main content
15,885,546 members
Home / Discussions / C / C++ / MFC
   

C / C++ / MFC

 
GeneralRe: Call control from static method Pin
enhzflep16-May-12 6:17
enhzflep16-May-12 6:17 
GeneralRe: Call control from static method Pin
_Flaviu16-May-12 19:11
_Flaviu16-May-12 19:11 
GeneralRe: Call control from static method Pin
_Flaviu16-May-12 7:37
_Flaviu16-May-12 7:37 
GeneralRe: Call control from static method Pin
Chris Losinger16-May-12 7:43
professionalChris Losinger16-May-12 7:43 
AnswerRe: Call control from static method Pin
Richard MacCutchan16-May-12 21:09
mveRichard MacCutchan16-May-12 21:09 
GeneralRe: Call control from static method Pin
_Flaviu17-May-12 20:34
_Flaviu17-May-12 20:34 
GeneralRe: Call control from static method Pin
Richard MacCutchan17-May-12 22:17
mveRichard MacCutchan17-May-12 22:17 
QuestionSending Data to Excel with C++ Pin
keane209716-May-12 4:47
keane209716-May-12 4:47 
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 (there's a total of 21 columns, I've deleted some to make it look less messy):

2012-05-14 14:08:12.498 8 InfoMSG1   N  2.719   2994   2153   2089 {InfoMSG2}
2012-08-14 14:08:38.644 8 InfoMSG3 N  2.974  6.732  9.577 10.596 {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:

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

        col1 = new string[fileLength];
	col2 = new string[fileLength];
	col4 = new string[fileLength];
	col5 = new string[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 -
HTML
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:

C++
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:


C++
	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:

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

C++
// 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.
QuestionRe: Sending Data to Excel with C++ Pin
David Crow16-May-12 7:21
David Crow16-May-12 7:21 
AnswerRe: Sending Data to Excel with C++ Pin
keane209716-May-12 8:04
keane209716-May-12 8:04 
AnswerRe: Sending Data to Excel with C++ Pin
enhzflep16-May-12 7:50
enhzflep16-May-12 7:50 
GeneralRe: Sending Data to Excel with C++ Pin
keane209716-May-12 8:05
keane209716-May-12 8:05 
GeneralRe: Sending Data to Excel with C++ Pin
enhzflep16-May-12 8:21
enhzflep16-May-12 8:21 
AnswerRe: Sending Data to Excel with C++ Pin
enhzflep28-May-12 3:31
enhzflep28-May-12 3:31 
QuestionMFC client view rectangle size? Pin
Vaclav_16-May-12 4:42
Vaclav_16-May-12 4:42 
AnswerRe: MFC client view rectangle size? Pin
_Flaviu16-May-12 5:11
_Flaviu16-May-12 5:11 
GeneralRe: MFC client view rectangle size? Pin
Vaclav_16-May-12 5:55
Vaclav_16-May-12 5:55 
GeneralRe: MFC client view rectangle size? Pin
_Flaviu16-May-12 7:40
_Flaviu16-May-12 7:40 
AnswerRe: MFC client view rectangle size? Pin
David Crow16-May-12 7:07
David Crow16-May-12 7:07 
GeneralRe: MFC client view rectangle size? Pin
Vaclav_16-May-12 8:52
Vaclav_16-May-12 8:52 
AnswerRe: MFC client view rectangle size? Pin
_Flaviu16-May-12 7:44
_Flaviu16-May-12 7:44 
GeneralRe: MFC client view rectangle size? Pin
Vaclav_16-May-12 8:53
Vaclav_16-May-12 8:53 
QuestionCFile Open failed GetLastError = 3 Pin
yu-jian16-May-12 2:43
yu-jian16-May-12 2:43 
AnswerRe: CFile Open failed GetLastError = 3 Pin
CPallini16-May-12 3:00
mveCPallini16-May-12 3:00 
GeneralRe: CFile Open failed GetLastError = 3 Pin
yu-jian16-May-12 3:05
yu-jian16-May-12 3:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.