Retrieving data from Excel

Question

 

Hello

After some "exercise" I was able to send easily data from my VisualC++.NET (EE) application to Excel. After referencing the corresponding interop (by the way,   the interop Microsoft Excel 10.0 Type Library worked very well in my case), I could send the data to the corresponding worksheet using the following code:


//open link to Excel:
Microsoft:ffice::Interop::Excel::Application^ xlApp = gcnew Microsoft:ffice::Interop::Excel::ApplicationClass();

//open Excel file:
Workbook^ wb = xlApp->Workbooks->Open("C:\\dummy.xls", System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value, System::Reflection::Missing::Value);

xlApp->Visible = true;
              
//set active worksheet (here for example worksheet nb. 1):
static_cast<Worksheet^>(xlApp->ActiveWorkbook->Sheets->Item[(1)])->Select(Type::Missing);

Worksheet^ sheet = dynamic_cast<Microsoft:ffice::Interop::Excel::Worksheet^>(wb->ActiveSheet);


After that, I could simply send the data by the command

sheet->Cells[line, column] = number;


My question: How can I retrieve the value stored in a cell of Excel??? I thought it would work with

number = sheet->Cells[line, column];

But this gives a format error. Has anyone an idea - the solution must be simple...

Thanks a lot for your help,

B E A T

Answer 1

Hello, sorry that I remark something in an other way to your question, ...

 :

a) Excel has a vb - makro: this means:

Easily it's possible to reach the cells with vb.

b) now: yoy can put the values of the cells in one only richTextBox. now you must ask with if's:

1) is the cell filled new.

2) is it the newxt cell ?

3) then you can fill all the cells.

 

The same you must do in c++ vice versa.

If you know start both buttons, wait's vb for the filling.

 

It might sound a little stupid an sure not perfekt.

but in this way it's posslible.

the joke: this map .text or rtf is immediately cleared.

It's not so bad, how it looks in the first way:

this syntax IS clear.

(it works quickley ..)(I am sure, that this is not the right way, but I hope it was OK.)

 

mfg

[e-mail]

 

 

 

Answer 2

 

vb:

open () for .. as ..

#1 ...;

 

(Datatype for Cell-Index must be long. )

 workbook/sheet.cells(..., ...) = "something";

 

3 Datas .txt:

a) Index

b) next cell or not.

c) value of the cell.

 

if()

while()

end while

end if

 

close.

 

 

 

Answer 3

I thought, it was not possible to read and write in the cells.

 

but now I understand the problem:

 

Concept:

 

try an if with a while inside,

define 2 global var's for the cell-Index and the value and a counter.

 

if-option: someValue == CellValue/true: show/write Info.

else :

while-option: put the counter on the next cellIndex and begin again with the if.

one more if in THIS while: if no value is true at all: show Info "error".

 

gonschior

while option

 

Answer 4

 

Hello, I have tested your Code in Visual c++ express.

It is not possible to compile it in my programm.

 

but: there Are excel applications for translating the dataRead for the datagridView.

 

with the onl word Excel and :: the Text - Menue shows different orders for XML.

but I think, that this orders are only made for the properties  and not for the values, because c++ is a very save system and doesn't like to execute this orders.

 

It's possible in c++ with a special bought edition, that is made for data - access.

The price is about 300 €.

 

gonschior

 

Answer 5

found a firther solution, now without vb:

 

Konvert the Excel oder Access - sheet under SaveAs to a simple textfile (.txt). Load it in a richTextBox in cpp and parse the  text. Then work with the values, - however what is to do then ... 

 

after this: resave it in the same .txt-File. Now you can import the the file in an Excelprogramm running.

 

 

 gonschior

 

 

Answer 6

 

hello,  there was a last idea,

but this works. ..:

 

 

It was written today on the 25-1-09

 

Name:

 

Geschenk2/(last message so far ...) ;

 

in common:

 

it is possible, to reach with normal cpp any database.

 

It works with the expression connnection - String. .... with open and close and with SQL-selects.

 

If you still want:

You can copy the code.

I am not able to translate it in perfekt english, but the code works. ...

 

OK,

of course():

 

this is possible with the normal VS express().

 

yours,

 

gonschior

 

 

 

 

---

nachgelernter Programmierer