Excel automation
Gary Crider
worksheet, but I can't seem to get any further no matter what I try.
Does anyone have a snippet, unit, or project that actually accesses
indivudual cells within the worksheet and can modify their values and other
propertires. I've tried several different routes with this and failed on
all of them.
Any help, documentation, code or direction would be greatly appreciated.
The MS documentation of the objects doesn't really help because the
components don't seem to expose the same properties and methods.
Thanks,
Gary
Fishface
> Does anyone have a snippet, unit, or project that actually accesses
> indivudual cells within the worksheet and can modify their values and
> other propertires. I've tried several different routes with this and failed
>on all of them.
I'm not sure if this Delphi info will be any help, but I had it in my bookmarks:
http://www.djpate.freeserve.co.uk/Automation.htm
Detlef
see my answer from February.
Hi Marcello,
I hope you will be satisfied with an C example:
The variables:
Variant Excel; //the application itself
Variant CurrentWorksheet;
Variant Workbook; //er darf nur eins geöffnet sein (vereinfachung)
The beginning:
Excel = Excel.CreateObject("Excel.Application");
Excel.OlePropertySet("DisplayAlerts", False);
the middle:
Excel.OlePropertyGet("WorkBooks").OleProcedure("Add",(TVariant)asFileName);
// add a workbook
Workbook = Excel.OlePropertyGet("Workbooks").OlePropertyGet("Item",1);
//calculate the number of work sheets
iNumberOfSheetsInCurrentWorkbook = Workbook.OlePropertyGet("Worksheets").
OlePropertyGet("Count").operator int();
//select a sheet
CurrentWorksheet =
Workbook.OlePropertyGet("Worksheets").OlePropertyGet("Item",1);
// get a value from a cell
Variant Value = CurrentWorksheet.OlePropertyGet("Cells").OlePropertyGet
("Item",iRowIndex,iColIndex).OlePropertyGet("Value");
asValue = Value.operator AnsiString();
// set a value
CurrentWorksheet.OlePropertyGet("Cells").OlePropertyGet("Item",iRowIndex,iCo
lIndex).
OlePropertySet("Value",(TVariant)iValue);
The end
Workbook.OleProcedure("Save"); // save the file
Excel.OlePropertyGet("WorkBooks").OleProcedure("Close"); // close
the file
Excel.OleProcedure("Quit");
The rest is to translate it into Pascal.
Detlef
"Gary Crider" <gcr...@metersmart.com> schrieb im Newsbeitrag
news:406350ff$1...@newsgroups.borland.com...
Gary Crider
I have used the OLE container approach before and it works somewhat, but
what I am trying to do is use C++ Builder 6's Office2K VCL components,
TApplication, TWorkbook and TWorksheet. These new components look as if they
should remove a lot of the programming overhead, but they are totally
undocumented and no examples seem to exist of anyone using them successfully
to actually update a worksheet. I have to suspect SOMEONE has done it, but I
can't find them. I wish Borland wouldn't provide components without any
documentation whatsoever.
The VBA help system documents the objects for Microsoft, but these VCL
components don't seem to expose those obects' properties and methods. I
can't even find a definition of the Range object. I can get a Range pointer,
but you can't use it to get to properties such a Range->Value. Message says
Value is not a member of Range.
"Gary Crider" <gcr...@metersmart.com> wrote in message
news:406350ff$1...@newsgroups.borland.com...
Adrian Carter
and TExcelWorksheet. I'm not claiming to be an expert - but after many
hours of banging my head against walls, I got this to work. First is
Unit1.h
followed by Unit1.cpp . These compile and execute under CBuilder6 and
Windows 2000. Button1 just displays the content of Cell A2. Button2
turns the font bold in the first & last rows, centre aligns column 2, and
turns the text in column 2 blue.
HTH,
Adrian Carter
// ****************************
#ifndef Unit1H
#define Unit1H
//---------------------------
#include <Classes.hpp>
#include <Controls.hpp>
#include <StdCtrls.hpp>
#include <Forms.hpp>
#include <OleServer.hpp>
#include "Excel_2K_SRVR.h"
//---------------------------------------------
class TForm1 : public TForm
{
__published: // IDE-managed Components
TExcelApplication *MyEXCEL;
TExcelWorkbook *MyBOOK;
TExcelWorksheet *MySHEET;
TButton *RunButton1;
TButton *RunButton2;
void __fastcall RunButton1Click(TObject *Sender);
void __fastcall RunButton2Click(TObject *Sender);
void __fastcall FormClose(TObject *Sender, TCloseAction &Action);
private: // User declarations
//int lcid;
public: // User declarations
__fastcall TForm1(TComponent* Owner);
};
//------------------------------
extern PACKAGE TForm1 *Form1;
//------------------------------
#endif
// ****************************
#include <vcl.h>
#pragma hdrstop
#include "Unit1.h"
//--------------------------------------------------------------------------
-
#pragma package(smart_init)
#pragma link "Excel_2K_SRVR"
#pragma resource "*.dfm"
TForm1 *Form1;
//--------------------------------------------------------------------------
-
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
try
{
MyEXCEL->Connect();
}
catch(...)
{
ShowMessage("Unable to load MS EXCEL");
Application->Terminate();
}
}
//--------------------------------------------------------------------------
-
void __fastcall TForm1::RunButton1Click(TObject *Sender)
{
Variant tV = 1;
String SBkN = "C:\\Temp\\Test.XLS";
WorkbooksPtr MyBooks = MyEXCEL->get_Workbooks();
MyBooks->Open(StringToOleStr(SBkN));
MyEXCEL->set_Visible(LOCALE_SYSTEM_DEFAULT, true);
MyBOOK->ConnectTo(MyBooks->get_Item(tV));
SheetsPtr MySheets = MyBOOK->get_Sheets();
MySHEET->ConnectTo(MySheets->get_Item(tV));
RangePtr CellsPtr = MySHEET->get_Cells();
Variant CellText;
CellText = CellsPtr->get_Item(tV+1,tV);
String Result = CellText;
ShowMessage("This is Cell(2,1): " + Result);
}
//--------------------------------------------------------------------------
-
void __fastcall TForm1::RunButton2Click(TObject *Sender)
{
Variant V1 = 1;
Variant V2 = 2;
String SBkN = "C:\\CBuilder6\\Projects\\ExlSvr\\Log.CSV";
WorkbooksPtr MyBooks = MyEXCEL->get_Workbooks();
MyBooks->Open(StringToOleStr(SBkN));
MyEXCEL->set_Visible(LOCALE_SYSTEM_DEFAULT, true);
MyBOOK->ConnectTo(MyBooks->get_Item(V1));
SheetsPtr MySheets = MyBOOK->get_Sheets();
MySHEET->ConnectTo(MySheets->get_Item(V1));
TVariant Cell_1;
TVariant Cell_ND;
TVariant tvTrue = true;
RangePtr Urang = MySHEET->get_UsedRange(LOCALE_SYSTEM_DEFAULT);
RangePtr ColsPtr = Urang->get_Columns();
RangePtr RowsPtr = Urang->get_Rows();
RangePtr CellsPtr = Urang->get_Cells();
Variant NCols = ColsPtr->get_Count();
Variant NRows = RowsPtr->get_Count();
Cell_1 = CellsPtr->get_Item(V1,V1);
Cell_ND = CellsPtr->get_Item(V1,NCols);
RangePtr CellPtr1 = MySHEET->get_Range(Cell_1, Cell_ND);
FontPtr FP = CellPtr1->get_Font();
FP->set_Bold(tvTrue);
Cell_1 = CellsPtr->get_Item(NRows,V1);
Cell_ND = CellsPtr->get_Item(NRows,NCols);
CellPtr1 = MySHEET->get_Range(Cell_1, Cell_ND);
FP = CellPtr1->get_Font();
FP->set_Bold(tvTrue);
Cell_1 = CellsPtr->get_Item(V1,V2);
Cell_ND = CellsPtr->get_Item(NRows,V2);
CellPtr1 = MySHEET->get_Range(Cell_1, Cell_ND);
TVariant Centalig = 3;
CellPtr1->set_HorizontalAlignment(Centalig);
FP = CellPtr1->get_Font();
TVariant tvBlue = clBlue;
FP->set_Color(tvBlue);
}
//--------------------------------------------------------------------------
-
void __fastcall TForm1::FormClose(TObject *Sender, TCloseAction &Action)
{
TVariant tvFalse = false;
MyBOOK->Close(tvFalse);
}
// ****************************
// ****************************
"Gary Crider" <gcr...@metersmart.com> wrote in message
news:40643ed2$1...@newsgroups.borland.com...
Kasper Larsen
news:406350ff$1...@newsgroups.borland.com...
time, but it is hard to come by. I posted some code 2004-03-13 in this
group, hope you can use it. If you should find a gold mine of Excel related
stuff, pls let us know.
Kasper Larsen
"Kasper Larsen" <kb_l...@hotmail.com> skrev i en meddelelse
news:4065...@newsgroups.borland.com...
>
Just found some more Excel related code in a posting by Barthoff
(u...@barthoff.de) in the database group dated 2000/07/11
Kasper Larsen
news:406350ff$1...@newsgroups.borland.com...
> Does anyone have a snippet, unit, or project that actually accesses
> indivudual cells within the worksheet and can modify their values and
other
> propertires. I've tried several different routes with this and failed on
> all of them.
>
> Any help, documentation, code or direction would be greatly appreciated.
> The MS documentation of the objects doesn't really help because the
> components don't seem to expose the same properties and methods.
I have collected some source code related to C++ Builder interaction with
Excel here :
Robert Emmons
The code below does not access individual cells. It previews or prints
an existing spreadsheet. Perhaps, it will provide some clues.
//---------------------------------------------------------------------------
void display(AnsiString xlBookPathName)
{
Variant vIndex = 1;
Variant vEnable = false;
// xlBook->PrintOut() parameters
Variant vFromPage = Variant(TNoParam());
Variant vToPage = Variant(TNoParam());
vFromPage = 5;
vToPage = 5;
Variant vCopies = 1;
Variant vPreview = false;
Variant vActivePrinter = Variant(TNoParam());
Variant vPrintToFile = false;
Variant vColate = false;
Variant vPrToFileName = Variant("");
long int lcid = 0;
short int sRhs = 0;
xlApp->set_DisplayAlerts(lcid, sRhs);
if(destination == 0){
xlApp->set_Visible(LOCALE_SYSTEM_DEFAULT, true);
vPreview = true;
}
WorkbooksPtr MyBooks = xlApp->get_Workbooks();
MyBooks->Open(StringToOleStr(xlBookPathName));
xlBook->ConnectTo(MyBooks->get_Item(vIndex));
xlBook->PrintOut(vFromPage, vToPage, vCopies, vPreview,
vActivePrinter, vPrintToFile, vColate, vPrToFileName, lcid);
xlBook->Disconnect();
MyBooks->Close(lcid);
}
Kasper Larsen
news:409ffa97$1...@newsgroups.borland.com...
> Gary Crider wrote:
> > Using the VCL components, I have managed to load a workbook and display
the
> > worksheet, but I can't seem to get any further no matter what I try.
> >
> > Does anyone have a snippet, unit, or project that actually accesses
> > indivudual cells within the worksheet and can modify their values and
other
> > propertires. I've tried several different routes with this and failed
on
> > all of them.
> >
I could find on
http://tinyurl.com/2xo7w
Kasper