Solved

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

Posted on 2003-12-10
10
2,539 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many modern programming languages support the concept of a property -- a class member that combines characteristics of both a data member and a method.  These are sometimes called "smart fields" because you can add logic that is applied automaticall…
This article shows you how to optimize memory allocations in C++ using placement new. Applicable especially to usecases dealing with creation of large number of objects. A brief on problem: Lets take example problem for simplicity: - I have a G…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

777 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