Open Excel in VC++ and write data - Page 4
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 4 of 6 FirstFirst 123456 LastLast
Results 46 to 60 of 78

Thread: Open Excel in VC++ and write data

  1. #46
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    Re: Open Excel in VC++ and write data

    Have you tried releasing all the dispatches and clearing all the VARIANTs after you use them?
    Verere testudinem! (Fear the turtle)

    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein

    Robots are trying to steal my luggage.

  2. #47
    Join Date
    Dec 2006
    Posts
    10

    Re: Open Excel in VC++ and write data

    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.

  3. #48
    Join Date
    Feb 2005
    Location
    Pune (India)
    Posts
    644

    Thumbs up Re: Open Excel in VC++ and write data

    Hi,

    Use OLE Automation.

    Here is a link may be useful to you !

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


    -Anant
    "Devise the simplest possible solution that solves the problems"

  4. #49
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    Re: Open Excel in VC++ and write data

    Quote Originally Posted by 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.
    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.

    Code:
    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.
    Verere testudinem! (Fear the turtle)

    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein

    Robots are trying to steal my luggage.

  5. #50
    Join Date
    Dec 2006
    Posts
    10

    Re: Open Excel in VC++ and write data

    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.

  6. #51
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    Re: Open Excel in VC++ and write data

    I'm not familiar with any overall reference.
    Verere testudinem! (Fear the turtle)

    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein

    Robots are trying to steal my luggage.

  7. #52
    Join Date
    Dec 2006
    Posts
    10

    Re: Open Excel in VC++ and write data

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

  8. #53
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    Re: Open Excel in VC++ and write data

    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.

    Code:
    {  
    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.
    Verere testudinem! (Fear the turtle)

    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein

    Robots are trying to steal my luggage.

  9. #54
    Join Date
    Dec 2006
    Posts
    10

    Unhappy Re: Open Excel in VC++ and write data

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

  10. #55
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    Re: Open Excel in VC++ and write data

    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.
    Verere testudinem! (Fear the turtle)

    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein

    Robots are trying to steal my luggage.

  11. #56
    Join Date
    Apr 2005
    Posts
    23

    Re: Open Excel in VC++ and write data

    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.
    Last edited by minagu; April 10th, 2007 at 03:59 AM.

  12. #57
    Join Date
    Jun 2007
    Posts
    1

    Question Re: Open Excel in VC++ and write data

    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.

  13. #58
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    Re: Open Excel in VC++ and write data

    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

    Code:
    _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
    Code:
    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.


    Code:
    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);
         }
    }
    Last edited by Tom Frohman; July 2nd, 2007 at 07:02 AM.
    Verere testudinem! (Fear the turtle)

    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein

    Robots are trying to steal my luggage.

  14. #59
    Join Date
    Jul 2007
    Location
    Pune, India
    Posts
    20

    Question Re: Open Excel in VC++ and write data

    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
    Last edited by DnyanAnand; October 12th, 2007 at 02:25 AM. Reason: add some lines in it

  15. #60
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    Re: Open Excel in VC++ and write data

    Code:
     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);
    }
    Verere testudinem! (Fear the turtle)

    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein

    Robots are trying to steal my luggage.

Page 4 of 6 FirstFirst 123456 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center