Ok experts, anyone fancy stopping me from commiting suicide?
I'm supposed to writing some code to import Excel data via the Excel Server component. Just one problem: I have no idea what is going on.
What I need to do is open a file, be able to extract data, modify the data, replace it, and save the data back out (possibly into another file).There's also the added bonus of possibly needing to create new tables and mess around with deleting/creating rows and columns.
Considering the fact that I don't even know which component I actually need, is there either a fantastic tutorial/demo or is some kindly soul in Ex-Ex land going to give me enough code samples to at least get me started.
// Open Excell
procedure TForm1.Button1Click(Sender: TObject);
begin
(* try
// if Excell is already open then connect to it
ExcelApp := GetActiveOleObject('Excel.Application');
except
// otherwise open up a new connection
ExcelApp := CreateOleObject('Excel.Application');
end;*)
ExcelApp := CoApplication_.Create;
// Display Excell
ExcelApp.Visible := True;
(* ok here is how to minimize / maximize Excell
//The VB constants are not declared in delphi so we need to use the enumerated value for
//the constant eg xlNormal = 1 where xlNormal is a window state.
//1 = xlNormal
//2 = xlMinimized
//3 = xlMaximized
ExcelApp.WindowState := 3; // this is maximize
*)
end;
// Open an Excell file
procedure TForm1.Button2Click(Sender: TObject);
begin
if (OpenDialog1.Execute) then
begin
//Opens up the excel template file, file returned from the ini file
try
ExcelApp.WorkBooks.Open(OpenDialog1.FileName);
except on exception do
begin
messagedlg('Could not open ' + OpenDialog1.FileName,mterror,[mbok],0);
exit;
end;
end;
end;
end;
// Put in some values
procedure TForm1.Button3Click(Sender: TObject);
var
LoopCol : char;
LoopRow : Integer;
begin
if CheckIfValidToMakeACall = false then exit;
For LoopCol := 'E' to 'J' do
begin
For LoopRow := 5 to 10 do
begin
ExcelApp.WorkBooks[1].ActiveSheet.range[LoopCol+IntToStr(LoopRow)].Value := LoopCol+IntToStr(LoopRow);
end;
end;
end;
// Open a new workbook
procedure TForm1.Button4Click(Sender: TObject);
begin
ExcelApp.WorkBooks.Add;
end;
// get some data
procedure TForm1.Button5Click(Sender: TObject);
var
LoopCol : char;
Col : integer;
LoopRow : Integer;
begin
if CheckIfValidToMakeACall = false then exit;
Col := 5;
For LoopCol := 'E' to 'J' do
begin
For LoopRow := 5 to 10 do
begin
StringGrid1.Cells[Col-5,LoopRow-5] :=
ExcelApp.WorkBooks[1].ActiveSheet.range[LoopCol+IntToStr(LoopRow)].Text;
end;
inc(Col);
end;
end;
procedure TForm1.Button6Click(Sender: TObject);
begin
ExcelApp.Quit;
end;
// Excell will not allow an ole call if a cell is being edited thus we need to check this
// before making a call
function TForm1.CheckIfValidToMakeACall: Boolean;
var
CallRejectionTest : String;
begin
result := True;
try
CallRejectionTest := ExcelApp.WorkBooks[1].ActiveSheet.range['A1'].Text;
except on exception do
begin
messagedlg('Please make sure you are not currently editing a cell in Excell!!',mterror,[mbok],0);
result := False;
end;
end;
end;
end.
you will need to import the Excel8.olb file using project import type library.. this will convert this file into a _tlb.pas file. include that _tlb.pas file in your project.
That seems to beincedibly close to what I want with just one problem...I can't get it to work!
I've pasted your code in, and tried to create the type library but Delphi complains that the several of the types for Excel 8 already exist. I'm using Delphi 5 Enterprise and it already has the server components installed. I'm having trouble understanding how to use them
Hmm I really can't help more than that as I don't have D5. You will just have to work it out from the D5 help, I hope its good help.. otherwise your'e gonna struggle for a while.
sorry I can't help any further
Rob.
0
Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
Sorry Rob but there must be something going on in that switch from Delhi 4 to Delphi 5. I think I have a solution but this isn't an easy job using the Delphi help. If you still want the points then you can have them if you can give me a way to do one of the following (with example code if you can)
1. How do I save my data to a CSV file via Excel (either as a direct function/procedure or using the SendKeys function)
OR
2. How do I get the actual row and column counts (Rows.Count and Columns.Count just return stupid values)
2. Rows.Count and Columns.Count will return the number of rows and columns that are highlited (ie the selected columns and rows) if nothing is selected/highlited this will return rubbish.
unit ExcellDemo01;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Comobj,ActiveX, StdCtrls, Grids,Excel_TLB;
type
TForm1 = class(TForm)
Button1: TButton;
Button2: TButton;
Button3: TButton;
Button4: TButton;
Button5: TButton;
OpenDialog1: TOpenDialog;
StringGrid1: TStringGrid;
Button6: TButton;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
procedure Button5Click(Sender: TObject);
procedure Button6Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
Function CheckIfValidToMakeACall : Boolean;
end;
var
Form1: TForm1;
ExcelApp : Variant;
implementation
{$R *.DFM}
// Open Excell
procedure TForm1.Button1Click(Sender
begin
(* try
// if Excell is already open then connect to it
ExcelApp := GetActiveOleObject('Excel.
except
// otherwise open up a new connection
ExcelApp := CreateOleObject('Excel.App
end;*)
ExcelApp := CoApplication_.Create;
// Display Excell
ExcelApp.Visible := True;
(* ok here is how to minimize / maximize Excell
//The VB constants are not declared in delphi so we need to use the enumerated value for
//the constant eg xlNormal = 1 where xlNormal is a window state.
//1 = xlNormal
//2 = xlMinimized
//3 = xlMaximized
ExcelApp.WindowState := 3; // this is maximize
*)
end;
// Open an Excell file
procedure TForm1.Button2Click(Sender
begin
if (OpenDialog1.Execute) then
begin
//Opens up the excel template file, file returned from the ini file
try
ExcelApp.WorkBooks.Open(Op
except on exception do
begin
messagedlg('Could not open ' + OpenDialog1.FileName,mterr
exit;
end;
end;
end;
end;
// Put in some values
procedure TForm1.Button3Click(Sender
var
LoopCol : char;
LoopRow : Integer;
begin
if CheckIfValidToMakeACall = false then exit;
For LoopCol := 'E' to 'J' do
begin
For LoopRow := 5 to 10 do
begin
ExcelApp.WorkBooks[1].Acti
end;
end;
end;
// Open a new workbook
procedure TForm1.Button4Click(Sender
begin
ExcelApp.WorkBooks.Add;
end;
// get some data
procedure TForm1.Button5Click(Sender
var
LoopCol : char;
Col : integer;
LoopRow : Integer;
begin
if CheckIfValidToMakeACall = false then exit;
Col := 5;
For LoopCol := 'E' to 'J' do
begin
For LoopRow := 5 to 10 do
begin
StringGrid1.Cells[Col-5,Lo
ExcelApp.WorkBooks[1].Acti
end;
inc(Col);
end;
end;
procedure TForm1.Button6Click(Sender
begin
ExcelApp.Quit;
end;
// Excell will not allow an ole call if a cell is being edited thus we need to check this
// before making a call
function TForm1.CheckIfValidToMakeA
var
CallRejectionTest : String;
begin
result := True;
try
CallRejectionTest := ExcelApp.WorkBooks[1].Acti
except on exception do
begin
messagedlg('Please make sure you are not currently editing a cell in Excell!!',mterror,[mbok],0
result := False;
end;
end;
end;
end.
you will need to import the Excel8.olb file using project import type library.. this will convert this file into a _tlb.pas file. include that _tlb.pas file in your project.
should work
Rob;-)