Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2003-12-10
10
2,545 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to understand recursion 12 229
getting a fire fox page (view page source ) in c++ 6 42
FMX StringGrid1->Canvas->FillRect Problem 3 170
computer science syllabus 3 88
Often, when implementing a feature, you won't know how certain events should be handled at the point where they occur and you'd rather defer to the user of your function or class. For example, a XML parser will extract a tag from the source code, wh…
Introduction This article is a continuation of the C/C++ Visual Studio Express debugger series. Part 1 provided a quick start guide in using the debugger. Part 2 focused on additional topics in breakpoints. As your assignments become a little more …
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
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.

840 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