Open Excel in VC++ and write data

[Tom Frohman]

Have you tried releasing all the dispatches and clearing all the VARIANTs after you use them?

[neyadakim]

Hi Tom;

I want to format cell in the excel form.
For example I send a date value as a long to excel. in the excel that cell will not be seen as a date format. Like this the i cant give numeric format to the cell.
Haw can i give format of the cell for excel.

Thanks.

[anantwakode]

Hi,

Use OLE Automation.

Here is a link may be useful to you !

http://www.codeguru.com/cpp/data/mfc...le.php/c11745/


-Anant

[Tom Frohman]

Hi Tom;

I want to format cell in the excel form.
For example I send a date value as a long to excel. in the excel that cell will not be seen as a date format. Like this the i cant give numeric format to the cell.
Haw can i give format of the cell for excel.

Thanks.

Are you saying that the method above for setting the number format didn't work? I'm repeating that section below showing how to use it to format a date.

IDispatch *pXlRange1;
{  
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C1:C3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);

pXlRange1 = result.pdispVal;
}


{  
VARIANT frmt;
frmt.vt = VT_BSTR;
frmt.bstrVal = ::SysAllocString(L"0.000");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"NumberFormat", 1, frmt);


}

//To set a date format
{  
VARIANT frmt;
frmt.vt = VT_BSTR;
frmt.bstrVal = ::SysAllocString(L"dd-mmm-yy");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"NumberFormat", 1, frmt);


}

From Excel help the date formats are
To display Use this code
Months as 1–12 M
Months as 01–12 Mm
Months as Jan–Dec Mmm
Months as January–December Mmmm
Months as the first letter of the month Mmmmm
Days as 1–31 D
Days as 01–31 Dd
Days as Sun–Sat Ddd
Days as Sunday–Saturday Dddd
Years as 00–99 Yy
Years as 1900–9999 Yyyy


Hours, minutes, and seconds To display hours, minutes, and seconds, include the following format codes in a section.

To display Use this code
Hours as 0–23 H
Hours as 00–23 Hh
Minutes as 0–59 M
Minutes as 00–59 Mm
Seconds as 0–59 S
Seconds as 00–59 Ss
Hours as 4 AM h AM/PM
Time as 4:36 PM h:mm AM/PM
Time as 4:36:03 P h:mm:ss A/P
Elapsed time in hours; for example, 25.02 [h]:mm
Elapsed time in minutes; for example, 63:46 [mm]:ss
Elapsed time in seconds [ss]
Fractions of a second h:mm:ss.00


AM and PM If the format contains an AM or PM, the hour is based on the 12-hour clock, where "AM" or "A" indicates times from midnight until noon and "PM" or "P" indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock. The "m" or "mm" code must appear immediately after the "h" or "hh" code or immediately before the "ss" code; otherwise, Microsoft Excel displays the month instead of minutes.

[neyadakim]

Hi tom;
Thanks for help.
Tom i wrote a Excel Automation control and added to my project.It seems that i must have a more knowledge about excel automation to cover all option of excel. And i want to learn all aspact of the excel automation.
Can you give me an like referans to learn all aspact of the excel automation.

Thanks.

[Tom Frohman]

I'm not familiar with any overall reference.

[neyadakim]

Hi Tom;

I want to set a value like '0000' in text mode. When do this; the cell value like '0' . But i want to set in a text format. How can i do?
Also i want to set cell value like 1,000.55 . But when i do this the cell like 1000.55 . That is i want to use excel option "Use 1000 separetor (.)" .
Do you know that how can i do this?


Thanks for your help..

[Tom Frohman]

Above I set the numberFormat for a range to "0.000".
Here if you set the NumberFormat to "@" BEFORE you enter 0000 it will treat the number as text and not remove the leading 0's.

{  
VARIANT frmt;
frmt.vt = VT_BSTR;
frmt.bstrVal = ::SysAllocString(L"@");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"NumberFormat", 1, frmt);
VariantClear(&result);
VariantClear(&frmt);

}

For commas precede the number format with #,##

for example "#,##0" for integers with commas
"#,##0.00" to have 2 decimals and use the comma seperators.

[neyadakim]

Hi Tom;

Thank you very much. Tom i have a problem with date format. I formatted excel cell for date like your code "dd-mm-yyyy". And then send date long value to excel. But Excel doesnt accept the date format given by my project. The reason that my regional setting is Turkish and in excel column format option there is an category named "custom" show the same format must be as "gg.aa.yyyy"

gg means in Turkish is dd (day)
aa means in Turkish is mm (month)

Do you have any solution for this problem? Because when the regional setting language is changed, the custom format characters for the excel must be change .

Instead of solve this problem we can set excel cell format category to Date.
Do you know how can i set excel cell format category to "Date" and "Time".
For "Time" formatting also same problem exist..

Thanks for your help..

[Tom Frohman]

The easiest way to find the characters for your system is to record a macro in Excel while you manually change to the desired format and then examine the characters in the recorded macro.

[minagu]

Hello, does anybody knows how can i fix that error?

"Assertion failed: !"bogus context in _ExceptionHandler()", file XX.CPP, line 2936"

I don't know why, but if anyone has found something similar, i'd be so pleased reading the way to fix it. That happens to me while i'm executing an excel's generator process.

Thanks.

[karthik2k7]

Hi TOM,

Which translator you are using?, the "b2cse" from microsoft website?. Actually this is translating for Excel 97 right?.

Can you help me out how to get this statements in VC++?

Sheet1.cells(5,2) = "10"

Sheets("Sheet1").Copy After:=Sheets(3)

Sheets("Sheet1 (2)").Name = "dfds"

Any specific rules are there to form these.

[Tom Frohman]

The following were all done with Visual C++ 6.0. In Visual Studio.Net the function calls and class names often are slightly different. You can find out
the actual form by examining the clases created after importing the type library.

Copy a worksheet.
Copying a worksheet

Rename the first worksheet

_Application app;
Workbooks books;
_Workbook book;
Worksheets sheets;
_Worksheet sheet;
if (!app.CreateDispatch("Excel.Application"))
               {
               AfxMessageBox("Couldn't start Excel.");
               }

//VOptional is defined similarly in the example cited above.
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);    
books = app.GetWorkbooks();
book = books.Add(covOptional);
sheets =book.GetSheets();
sheet = sheets.GetItem(COleVariant((short)1));
sheet.SetName("Severity");

Set the value of a cell

range = sheet.GetRange(COleVariant("B5"), COleVariant("B5"));
range.SetValue(COleVariant("Filename"));

The following function is usefull in translating row and column number into the string name of the cell.

void IndToStr(int row,int col,char* strResult)
     {
     if(col>26)
          {
          sprintf(strResult,"%c%c%d\0",'A'+(col-1)/26-1,'A'+(col-1)%26,row);
          }else{
     sprintf(strResult,"%c%d\0",'A'+(col-1)%26,row);
     }
}

[DnyanAnand]

hey Tom Frohman,
i used your code for Excel writng i realy so much thanx!
but now i m having one more problem is that i want to set Height, Width to any pericular Cell. so plz help me for that.
u can send me the code for it on my mail account plz
click_dnyan@rediffmail.com
Thanx in advance

[Tom Frohman]

 IDispatch *pXlRange1;
{  
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C1:C3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);
pXlRange1 = result.pdispVal;
}

{
VARIANT height;
height.vt=VT_BSTR;
height.bstrVal=::SysAllocString(L"30"); //height in points
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"RowHeight", 1, height);
VariantClear(&height);
}

{
VARIANT width;
width.vt=VT_BSTR;
width.bstrVal=::SysAllocString(L"40"); //width in characters
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"ColumnWidth", 1, width);
VariantClear(&width);
}