Solved

Excel 2002 VC++ COM Automation - Read/Write Help

Posted on 2003-12-10
10
2,530 Views
Last Modified: 2007-12-19
Hi Experts!

I am EXTREMELY new to this whole automation stuff so I wll try to be as descriptive as possible with my problem.

First - My Objective - I have an excel file which contains a bunch of data in a certain format (this is my input). I want to extract this data into my app/C++ code and do some conditional statements, manipulate some of the data I've collected and then write it to another excel sheet (my output file).

Secondly - What I have accomplished thus far - I have been able to successfully open both excel files for reading/writing. What I've gotten stuck on is all of the specifics as far as methods/functions for properly reading/writing data. In other words, I don't know how to say - go to cell A10 and grab the interger/string value.

Thirdly - I have already read through most of the MSDN documentation and a lot of it has gone over my head so I would really really appreciate specific examples from any of you if possible.

Lastly - Here is the code I have thus far. I would appreciate input regarding wether or not I'm going in the right direction, and if so, where am I going wrong or where can I go from here. I can successfully write the header portion of my output file but after that it kinda goes downhill. Essentials I would like to know how to do is read the data in different types i.e. strings/ints/doubles etc. Help is GREATLY appreciated! Thanks!


#include <iostream>

// In the #include area,
// import the type library information
#import "C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.DLL"

#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"

#import "C:\Program Files\Microsoft Office\Office10\Excel.exe" \
  rename("DialogBox", "DialogBoxXL") \
  rename("RGB", "RGB_XL") \
  named_guids \

// In your function
int main()
{
 
  CoInitialize(NULL); // Initialize COM
  try
  {      
        Excel::_ApplicationPtr apps(__uuidof(Excel::Application)) ;
        Excel::_WorkbookPtr workbook_in;
      Excel::_WorksheetPtr worksheet_in;
      Excel::_WorkbookPtr workbook_out ;
      Excel::_WorksheetPtr worksheet_out ;

        workbook_in = apps->Workbooks->Open("c:\\Test_In.xls"); // Open the workbook
        worksheet_in = workbook_in->Sheets->Item[1l];
             
      workbook_out = apps->Workbooks->Add() ;
      worksheet_out = workbook_out->ActiveSheet ;
                 
        for (long i=1; i <= 65536; i++) {
      
             if(i == 1) //write the header portion of the file
             {
                   worksheet_out->Cells->Item[i][1L] = "Person" ;
                  
                   for(long j=1; j <= 128; j++){
                         
                        TCHAR szBuffer[100];
                        wsprintf (szBuffer, "Attribute%d", j);
                        worksheet_out->Cells->Item[i][j] = szBuffer;
                   }
                  
             }else

             { if( i == 2 || i == 3 || i == 4)
                  {
                        TCHAR szBuffer[100];
                        //is this how I get the value from the sheet?
                        wsprintf (szBuffer, "%d", worksheet_in->Cells->Item[i][4L].intVal);
                        worksheet_out->Cells->Item[i][4L]  = szBuffer;      
                  }
      }

      _variant_t vnull, vformat, vconflict ;

      vformat = (long)Excel::xlWorkbookNormal ;
      vconflict = (long)Excel::xlLocalSessionChanges ;
      workbook_out->SaveAs("c:\\test.xls",
                   vformat,                        
// format
                   vnull,                          
// password
                   vnull,                          
// write-res password
                   vnull,                          
// read-only recommended
                   vnull,                          
// create backup ?
                   Excel::xlNoChange,              
// access mode
                   vconflict,                      
// conflict resolution
                   vnull,                          
// add to MRU
                   vnull,vnull) ;                  
// not used

      workbook_in->Close() ;
        workbook_out->Close() ;
      apps->Quit () ;
    //Excel::_ApplicationPtr excel;
    //HRESULT hr = excel.CreateInstance(L"Excel.Application"); // Start excel
    // // Get the first worksheet
      //std::string worksheet_name = static_cast<char *>(worksheet->Name); // Get the worksheet name
    //workbook->Close(); // Close the workbook
    //excel->Quit(); // Quit Excel
  }
  catch(_com_error &ce)
  {
    // Error occurred
  }
  CoUninitialize();
  return 0;
}
0
Comment
Question by:DizZzM
10 Comments
 
LVL 2

Expert Comment

by:federal102
ID: 9914826
Have you considered using the MFC Automation classes?
0
 

Author Comment

by:DizZzM
ID: 9915194
I tried it briefly yesturday and this COM method was what actually got me to the point I'm at now so i figured I'd just stick with it.

I've actually made some progress except now I'm stuck with the writing part. I have strings of characters that represent words... i.e "00AB" and when I go to assign it to worksheet_out->Cells->Item[i][1L], it gets converted to like an integer or something. Is there a way to keep it as a hex word?
0
 
LVL 8

Expert Comment

by:mnashadka
ID: 9917071
To read in a value, you would use something in the Range member:
_variant_t data_in = worksheet_in->Range["A1"]->Value;
Then whatever was in there will be stored in data_in

To write, you would also use the Range member of Worksheet:
worksheet_out->Range["B1"] = 4321;

I hope this helps.  Good luck!
0
 
LVL 8

Expert Comment

by:mnashadka
ID: 9917756
Sorry, I just thought about it, and you can't assign an int directly; it would have to be a long, double, etc:
worksheet_out->Range["B1"] = 4321l;
worksheet_out->Range["B1"] = 4321.778;
worksheet_out->Range["B1"] = "4321";
0
 

Author Comment

by:DizZzM
ID: 9922192
Hi mnashadka! I tried what you've said and it didn't quite work for me. I'm almost at the point where I have everything working though. I'm having a problem though when I do an assignment...

worksheet_out->Cells->Item[i][j] = "0E51" for example gives me a scientific notation 0.0 in the excel cell instead of the "0E51" string I want to write. Any ideas why this is? And if so, how can i correct the problem?

Thanks!
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 8

Accepted Solution

by:
mnashadka earned 250 total points
ID: 9923003
Ok, since 0E51 is scientific notation for 1x10^51, you would actually have to change the number format to text (just like right-clicking on the worksheet, clicking Format, and then choosing Text.  To do this, call PutNumberFormat with the @ to tell Excel that it's text.  Here's an example of how that might work:

    Excel::RangePtr range = worksheet_out->Cells->Item[5][5];
    range->PutNumberFormat("@");
    range->Value = "0E51";

I tried it with your code, and it does work on my system.  Good luck.
0
 
LVL 8

Expert Comment

by:mnashadka
ID: 9923022
By the way, if I ever have trouble figuring out how to do something in Excel, I record a macro in Excel, do what I'm trying to do in code manually, and then look at the generated VB code.  This usually translates really closely to what I need to do in C++.
0
 

Author Comment

by:DizZzM
ID: 9925826
Awsome. Thanks so much for your help!
0
 

Expert Comment

by:tirades
ID: 10457375
Hi DizZzM,

I wonder if you can help me with opening excel files for reading/writing?
I would appreciate me very much if you can help. thanks!
sugarman@singapore.com
0
 

Expert Comment

by:SlaveOfTheMind
ID: 10484310
Hi Dizzzm,
i tried your code to open an xls file and close it
but the compiler says i have problems in the writen existing files
COMIP.H spesific.
do u have any idea what could be the prob andhow to solve it?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When writing generic code, using template meta-programming techniques, it is sometimes useful to know if a type is convertible to another type. A good example of when this might be is if you are writing diagnostic instrumentation for code to generat…
IntroductionThis article is the second in a three part article series on the Visual Studio 2008 Debugger.  It provides tips in setting and using breakpoints. If not familiar with this debugger, you can find a basic introduction in the EE article loc…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now