Solved

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

Posted on 2003-12-10
10
2,533 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
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…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
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.

919 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

18 Experts available now in Live!

Get 1:1 Help Now