TExcelApplication and how to read a spreadsheet and get cell info 1

[BuilderSpec]

Hi

Below is some sample code that will perform the following under C++ Builder 6 ( prob work for 5 as well ) using the TExcelApplication component. Please let me know if is useful.

Open up a new project
New form
Add an OpenDialog from the Dialogs tab to the form
Add a Button from the Standard tab to the form
Add a Memo from the Standard tab to the form
Add a TExcelApplication from the Office2k tab to the form

Add a click event to the button and add the following code


if ( OpenDialog1->Execute() )
{
_WorkbookPtr wb;
AnsiString SheetName;
_WorksheetPtr ws;
Excel_2k::SheetsPtr sheets;
Excel_2k::RangePtr R;

AnsiString FN = OpenDialog1->FileName;

ExcelApplication1->Connect();

wb = ExcelApplication1->Workbooks->Open(WideString(FN));
ws = ExcelApplication1->ActiveSheet;
SheetName = ws->get_Name();
sheets = wb->get_Sheets();
int i = 0;
for ( i = 1 ; i <= sheets->Count; i++ )
{
ws = sheets->get_Item((TVariant)i);

if ( ws->get_Visible() == -1 )
{
AnsiString CellContent;
AnsiString CellRef = "A1";

SheetName = ws->Name;
Memo1->Lines->Add ( "*** Sheet [ " + SheetName + " ] " );
R = ws->get_Range((TVariant)CellRef.c_str());
CellContent = (String)((TVariant)R->get_FormulaR1C1());
Memo1->Lines->Add ( "Cell A1 contains " + CellContent );
}
}
ExcelApplication1->Disconnect();
}


What it does ?

This mini app when you run it :

1) Click the button and it should open the dialog for you to select the Excel spreadsheet you want
2) It then opens the spreadsheet
3) For each sheet in the Spreadsheet it will add it;s name to the Memo box
4) For each sheet it will extract the contents of cell A1 and add it's contents to the Memo as well
5) it then closes the spreadsheet.


Feel free to copy the code or add comments, or add more code for other Excel tricks.

PS the code is not as optimized as it could be, leave that to you !




Hope this helps!

Regards

BuilderSpec