|
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:
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);
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:
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);
}
for(int i=0; i<10; i++) {
for(int j=0; j<2; j++) {
VARIANT tmp;
tmp.vt = VT_BSTR;
tmp.bstrVal = bcol2[i];
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:
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.
|
|
|
|
|
keane2097 wrote: Anyway, my conversion form single byte to wide string is achieved using the following function:
Would A2W() be of any help?
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
|
|
|
|
|
Hi David, I'm away from my computer now so can't check, but I'm guessing the A2W() function carries out the single-byte to wide conversion for me?
If so it will be a help by replacing my own function with a presumably more well tested one so thanks for that.
I'm going to hold out hope that it does the entire conversion to BSTR until I can disprove the theory which will also improve my mood for a couple of hours at least so thanks for that too 
|
|
|
|
|
I've done something similar to this a couple of years back. In my case I was doing it from VBA, though (I guess) it shouldn't matter.
I was able to fill the cells in a range with the values contained in a 2D array.
So, rather than 1 call per column it was 1 call for the whole lot.
I'll have a poke around in the morning and see if I still have a copy of the code lying around. (I may very well not, since it was for a project at an old job)
Fantastically articulate post btw. 
|
|
|
|
|
Ha! Thanks for the compliment 
If you do manage to dig out that old code I'd be much obliged, thanks.
|
|
|
|
|
Pleasure. It's unfriendly to bash the inept - but there's nothing wrong with complimenting those at the opposite end of the spectrum, methinks!
Can't be bothered to find the old code (Gawd knows how many gigs of data I'd have to sift through)
BUT, I can offer you some brand-new freshly written code 
Here's something that fills in 100 (iSize*iSize cells) in 1 fell swoop.
Sub test1()
Const iSize = 10
Dim myArray(1 To iSize, 1 To iSize)
Dim iRow As Integer, iCol As Integer
iRow = 1
For iCol = 1 To iSize
myArray(iRow, iCol) = "Column " + CStr(iCol)
Next iCol
curCellNum = 1
For iRow = 2 To iSize
For iCol = 1 To iSize
myArray(iRow, iCol) = curCellNum
curCellNum = curCellNum + 1
Next iCol
Next iRow
Cells.Range(Cells(1, 1), Cells(iSize, iSize)) = myArray
End Sub
Hope that's of some use.
|
|
|
|
|
|
After “doing” MFC for eternity, I finally got puzzled by this.
Using basic MFC wizard to create application I see that the client rectangle of the default view is not the actual size of the rectangle on the screen. It is much smaller.
All the documentation I have ever read on the suject describe the view client are as the entire screen.
So why does GetClientRect comes up with lesser top/ bottom values?
Thanks for reading
Vaclav
|
|
|
|
|
Starting view in maximize way could be an solution ? If not, you can call ResizeParentToFit(...) in CScrollView::OnInitialUpdate() ...
|
|
|
|
|
CScrollView::OnInitialUpdate() ... works only in scroll view. I have tried that in plain CView derivative. VC complier said so.
As far as starting maximized – that was not the question. I said using wizard created default view.
Sorry.
|
|
|
|
|
Perhaps I don't give a reasonable ideea, but you can look where MFC implement CScrollView::ResizeParentToFit(..) and reproduce the code ...
|
|
|
|
|
Vaclav_Sal wrote: So why does GetClientRect comes up with lesser top/ bottom values?
Aside from the top value always being 0, lesser than what? Unless the window is borderless (e.g., desktop), it can't occupy the entire screen. Things like borders, scroll bars, captions, etc take up room.
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
|
|
|
|
|
Sorry, that was suppose to be right dimension.
I know about the frame / border, what I am asking are the right / bottom dimesions not being SAME as the actual view screen client dimensions.
For example - when I build a new window, with frame, in the default view ( parent frame) and use the view client rectange ( of the default view area) to size it - these dimesions are not the same as the actual view area. They are smaller.
I suppose I should get the new window dimesions AFTER it is maximized. Than it should show larger size than when originally sized to the client area.
Vaclav
|
|
|
|
|
Another ideea is to send to the view a menu->window->tile message, and your view will spreadsheet along mainframe ...
|
|
|
|
|
Good idea, but still does not answer the original questio. SOrry.
|
|
|
|
|
It needs to write string to the file.
But it Open file return FALSE, GetLastError() = 3.
m_File2Write.Open(webRes.strLocalFile, CFile::modeCreate | CFile::modeWrite | CFile::shareDenyWrite))
{
DWORD error = GetLastError();
return -1L;
}
The file does not exists, but I think it will create a file. I do not know why Open file falied.
-----------------------------
Now I know why. Because there is the chararcter '-' in the file name. Such as 12-444.html
But if use the explorer to create file 12-444.html, it is ok. Why use CFile, it error?
modified 16-May-12 9:03am.
|
|
|
|
|
WinError.h:
OK the file does not exist and you want to create it, but what about its folder?
Veni, vidi, vici.
|
|
|
|
|
I know 3 means can not find the path, but I donot know why.
|
|
|
|
|
I would check with the debugger the runtime value of webRes.strLocalFile.
Veni, vidi, vici.
|
|
|
|
|
yu-jian wrote: It needs to write string to the file.
Having some Gollum flashbacks, are we?
Seriously, though, what is the value of strLocalFile?
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
|
|
|
|
|
d:\temp\C030FA87-73C4-4989-99A2-CAABEB1F1FE2.html
|
|
|
|
|
You are trying to create a file and a path. Not possible with Open().
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
|
|
|
|
|
In the above code you do not test the return value from Open, so the return value of GetLastError() does not mean anything.
Programming is work, it isn't finger painting. Luc Pattyn
|
|
|
|
|
The full code is as follows:
if (!m_File2Write.Open(webRes.strLocalFile, CFile::modeCreate | CFile::modeWrite | CFile::shareDenyWrite))
{
DWORD error = GetLastError();
return -1L;
}
Just Open return FALSE, it will call GetLastError(), error is the err code.
|
|
|
|
|
Perhaps CFile doesnot support file name with character "-".
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
