Reading excel using Microsoft::Office::Interop::Excel vc++

Im using visual studio 2008 and am working with windows form application . I can write data to a particular cell using Microsoft:Office::Interop::Excel: Worksheet->Cells->Item[x,y]=DataIwantToWrite
but am having difficulty trying to read data. Not sure how that can be done. This is what i have so far:
Object^ Obj;
String^ buf= (String^)worksheet->Cells->Item[x,y];

but I am getting an exception on second line:
"An unhandled exception of type 'System.InvalidCastException' occurred"
"Additional information: Unable to cast COM object of type 'System.__ComObject' to class type 'System.String'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface."
LVL 1
funcodingAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kaylanreilorConnect With a Mentor Commented:
What about something like this:
String^ buf= (String^)worksheet->Cells->Item[(Object^)x,(Object^)y]->ToString();
(probably you don't need to cast with Object^)

Here is a code from MSDN that you can examine in debug mode step by step, you'll learn a lot I guess
using namespace System;
using namespace System::Reflection; // For Missing.Value and BindingFlags
using namespace System::Runtime::InteropServices; // For COMException
using namespace Microsoft::Office::Core;
using namespace Microsoft::Office::Interop::Excel;
 
 
void main () {
    Console::WriteLine ("Creating new Excel.Application");
    Application^ app = safe_cast<Application ^>( gcnew ApplicationClass());
    if (app == nullptr) {
        Console::WriteLine("ERROR: EXCEL couldn't be started!");
        return;
    }
 
    Console::WriteLine ("Making application visible");		
    app->Visible = true;
 
    Console::WriteLine ("Getting the workbooks collection");
    Workbooks^ workbooks = app->Workbooks;
 
    Console::WriteLine ("Adding a new workbook");
 
    _Workbook^ workbook = workbooks->Add(Missing::Value);
 
    Console::WriteLine ("Getting the worksheets collection");
    Sheets^ sheets = workbook->Worksheets;
 
    _Worksheet^ worksheet = safe_cast<_Worksheet^>(sheets->Item[ (Object^)1 ]);
    if (worksheet == nullptr) {
        Console::WriteLine ("ERROR: worksheet == null");
    }
 
    Console::WriteLine ("Setting the value for cell");
    // This paragraph puts the value 5 to the cell G1
    Range^ range1 = worksheet->Range["G1", Missing::Value];
    if (range1 == nullptr) {
        Console::WriteLine ("ERROR: range == null");
    }
    range1->Value2 = 5;
 
    // This paragraph sends single dimension array to Excel
    Range^ range2 = worksheet->Range["A1", "E1"];
    array<int>^ array2 = gcnew array<int>(5);
    for (int i=0; i < array2->GetLength(0); i++) {
        array2[i] = i+1;
    }
    range2->Value2 = array2;
 
    // This paragraph sends two dimension array to Excel
    Range^ range3 = worksheet->Range["A2", "E3"];
    array<int, 2>^ array3 = gcnew array<int, 2>(2,5);
    for (int i=0; i < array3->GetLength(0); i++) {
        for (int j=0; j < array3->GetLength(1); j++) {
            array3[i, j] = i*10 + j;
        }
    }
    array<Object^>^ args3 = gcnew array<Object^>(1);
    args3[0] = array3;
    range3->Value2 = array3;
 
    // This paragraph reads two dimension array from Excel
    Range^ range4 = worksheet->Range["A2", "E3"];
    array<Object^, 2>^ array4;
    array4 =  safe_cast<array<Object^,2>^> (range4->Value2);
 
    for (int i=array4->GetLowerBound(0); i <= array4->GetUpperBound(0); i++) {
        for (int j=array4->GetLowerBound(1); j <= array4->GetUpperBound(1); j++) {
            double d = *safe_cast<Double^>(array4[i,j]);
            if (d != array3[i-1, j-1]) {
                Console::WriteLine ("Test FAILED!");
                return;
            }
        }
    }
 
    // This paragraph sends two dimension array to Excel
    Range^ range5 = worksheet->Range["A5", "J6"];
    array<double, 2>^ array5 = gcnew array<double,2>(2,10);
    for (int j=0; j < array5->GetLength(1); j++) {
        double arg = Math::PI/array5->GetLength(1) * j;
        array5[0, j] = Math::Sin(arg);
        array5[1, j] = Math::Cos(arg);
    }
    range5->Value2 = array5;
 
    // The following code draws the chart
    range5->Select();
 
    ChartObjects^ chartobjects = safe_cast<ChartObjects^>(worksheet->ChartObjects(Missing::Value));
 
    ChartObject^ chartobject = safe_cast<ChartObject^>(chartobjects->Add(10 /*Left*/, 100 /*Top*/, 450 /*Width*/, 250 /*Height*/));
    _Chart^ chart = safe_cast<_Chart^>(chartobject->Chart);
 
    // Call to chart.ChartWizard() is shown using late binding technique solely for the demonstration purposes
    array<Object^>^ args7 = gcnew array<Object^>(11);
    args7[0] = range5; // Source
    args7[1] = XlChartType::xl3DColumn; // Gallery - note, we're using implicit boxing here
    args7[2] = Missing::Value; // Format
    args7[3] = XlRowCol::xlRows; // PlotBy - note, we're using implicit boxing here
    args7[4] = nullptr; // CategoryLabels
    args7[5] = nullptr; // SeriesLabels
    args7[6] = true; // HasLegend - note, we're using implicit boxing here
    args7[7] = "Sample Chart"; // Title
    args7[8] = "Sample Category Type"; // CategoryTitle
    args7[9] = "Sample Value Type"; // ValueTitle
    args7[10] = Missing::Value; // ExtraTitle
    // The last parameter is lcid, but as we use late binding here it should be omited
    //args7[11] = 0; // lcid
    chart->GetType()->InvokeMember("ChartWizard", BindingFlags::InvokeMethod, nullptr, chart, args7);
 
    Console::WriteLine ("Press ENTER to finish the sample:");
    Console::ReadLine();		
 
    try {
        // If user interacted with Excel it will not close when the app object is destroyed, so we close it explicitely
        workbook->Saved = true; // To be able to close it without being asked to save it
        app->UserControl = false;
        app->Quit();
    } catch (COMException^) {
        Console::WriteLine ("User closed Excel manually, so we don't have to do that");
    }
 
    // The following line is necessary to be sure that app object is released when we finish, because
    // the Garbage Collector is non-deterministic and by default can leave app unreleased. 
    GC::Collect();
 
    Console::WriteLine ("Test successfully passed!");
    return;
}

Open in new window

0
 
kaylanreilorCommented:
Sorry, you don't need to cast with String^:
String^ buf= worksheet->Cells->Item[x,y]->ToString();
0
 
funcodingAuthor Commented:
Thanks kayloanreilor. the code that you posted seems very helpful. I will try it out just to learn more on this.
I get the following error doing that:

'System::Object::ToString': function call missing argument list; use '&System::Object::ToString' to create a pointer to member

msdn suggested the following that worked. For others who are trying to do this, the data in a cell can be read using:

String^ buf=(String^) ((Range^)worksheet->Cells[x,y])->Value2;

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
kaylanreilorCommented:
Just because I'm curious : why are you coding with managed C++ ? Why not using C# instead ?
0
 
funcodingAuthor Commented:
I haven't started coding in c# at all and as usual there is a time restriction on getting this done. Thats why i stayed with what i knew... since you mentioned, what would be a good starting reference for c#?
0
 
kaylanreilorConnect With a Mentor Commented:
From MY point of view : http://www.informit.com/store/product.aspx?isbn=0768678811
It can be read in 2 or 3 weeks if you develop the project proposed in the book. But it cannot be the last reading because it is for beginner.
Actually, jumping from native C++ to managed C++ is almost the same job. You don't have to learn C#, you have to learn .Net. 90% of the work with .Net is to understand and to know the .Net framework. So the langage, even VB.Net or C#, is not the point.
A long time after (3 weeks) you can go deeper with this one : http://www.amazon.com/Practical-NET2-Second-Platform-Framework/dp/0976613255/ref=sr_1_1?ie=UTF8&s=books&qid=1242192118&sr=1-1

Or you can try some projects on www.codeproject.com, or www.codeguru.com...
0
All Courses

From novice to tech pro — start learning today.