Greetings,
I’m using Delphi, and I have installed this CData components
- CData FireDAC Components for Microsoft Excel
- CData FireDAC Components for Excel
Background:
I’m executing a stored procedure (SP) in sql server that will return the result of two queries; (this SP can be updated and columns could be renamed, added or even deleted later).
Currently, I manually copy the results of each query into a new Excel file each time I am asked for such information.
And I want to automate this: Using these two results and from a VCL application in Delphi, I want to store them in an xlsx file on two sheets each.
Support directed me to this url: https://cdn.cdata.com/help/RXH/fire/pg_sp-createworksheet.htm
But I can't find a correct example to use it in Delphi.
I am still waiting for a follow up answer, then I discovered this new community and hope to get help here.
This is what I have currently tried.
In a Delphi project: with this components:
The driver that I can use is “CDataExcel”; the one shown on the page https://www.cdata.com/drivers/excel/firedac/ does not work for me.
FDConnection1.Name := 'ExcelConnection';FDConnection1.DriverName := 'CData.Fire.Excel';This is the code that I can think of to try. At the moment I am just trying to create the xlsx file, without success.
Lacking a pascal/Delphi formatter, I am using Java.
unit Unit1;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf,
FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Stan.Param,
FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, FireDAC.Phys.CDataExcelDef, FireDAC.VCLUI.Wait, FireDAC.Comp.UI,
FireDAC.Phys.CDataExcel, Data.DB, FireDAC.Comp.DataSet, FireDAC.Comp.Client, Vcl.StdCtrls;
type
TForm1 = class(TForm)
Button1: TButton;
mmoLog: TMemo;
conToExcel: TFDConnection;
FDQuery1: TFDQuery;
FDPhysCDataExcelDriverLink1: TFDPhysCDataExcelDriverLink;
FDGUIxWaitCursor1: TFDGUIxWaitCursor;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
procedure LogTo(msg: string);
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
uses ShlObj;
{$R *.dfm}
procedure TForm1.LogTo(msg: string);
begin
mmoLog.Lines.Add(msg);
end;
procedure TForm1.Button1Click(Sender: TObject);
var
desktopfolder, sNewFile: string;
RowsAffected : integer;
begin
//sNewFile:= 'file://E:\CData\New1.xlsx';
sNewFile:= 'E:/CData/New1.xlsx';
// Test #1. Got error:
// [FireDAC][Phys]-300. Driver [CData.Fire.Excel] is not registered.
// Correct driver ID or define [CDATA.FIRE.EXCEL] virtual driver in FDDrivers.ini
try
conToExcel.DriverName := 'CData.Fire.Excel';
//conToExcel.Params.Values['URI'] := 'E:\CData\Existing.xlsx'; // THIS EXISTS
conToExcel.Params.Values['URI'] := 'E:/CData/Existing.xlsx'; // THIS EXISTS
conToExcel.Connected := True;
LogTo('#1 Connected to: '+conToExcel.Params.Values['URI']);
except
on E: Exception do
begin LogTo('#1'); LogTo(E.Message); end;
end;
// Test #2. No errors here
try
conToExcel.DriverName := 'CDataExcel';
//conToExcel.Params.Values['URI'] := 'E:\CData\Existing.xlsx'; // THIS EXISTS
conToExcel.Params.Values['URI'] := 'E:/CData/Existing.xlsx'; // THIS EXISTS
conToExcel.Connected := True;
LogTo('#2 Connected to: '+conToExcel.Params.Values['URI']);
except
on E: Exception do
begin LogTo('#2'); LogTo(E.Message); end;
end;
// Test #3. Got error:
// [FireDAC][Phys][CData]Malformed SQL Statement: Unexpected token encountered: [CreateWorksheet].
// Statement:CreateWorksheet 'E:/CData/New1.xlsx'
try
RowsAffected := conToExcel.ExecSQL('CreateWorksheet '+ QuotedStr(sNewFile) );
LogTo(format('#3 %d, created',[RowsAffected]));
except
on E: Exception do
begin LogTo('#3'); LogTo(E.Message); end;
end;
// Test #4. Got error:
// [FireDAC][Phys][CData]Malformed SQL Statement: Unexpected token encountered: [CreateWorksheet].
// Statement:CreateWorksheet 'E:\CData\New1.xlsx'
try
RowsAffected := conToExcel.ExecSQL('CreateWorksheet '+ QuotedStr(sNewFile) );
LogTo(format('#4 %d, created',[RowsAffected]));
except
on E: Exception do
begin LogTo('#4'); LogTo(E.Message); end;
end;
// Test #5.1
//[FireDAC][Phys][CData]Malformed SQL Statement: Unexpected token encountered: [CreateWorksheet].
//Statement:CreateWorksheet ?
try
FDQuery1.SQL.Text := 'CreateWorksheet :File';
FDQuery1.Params[0].AsString := QuotedStr(sNewFile);
FDQuery1.ExecSQL; LogTo('#5.1 created');
except
on E: Exception do
begin LogTo('#5.1'); LogTo(E.Message); end;
end;
// Test #5.2
//[FireDAC][Phys][CData]Malformed SQL Statement: Unexpected token encountered: [CreateWorksheet].
//Statement:CreateWorksheet (?)
try
FDQuery1.SQL.Text := 'CreateWorksheet (:File)';
FDQuery1.Params[0].AsString := QuotedStr(sNewFile);
//FDQuery1.Params[0].AsString := sNewFile;
FDQuery1.ExecSQL; LogTo('#5.2 created');
except
on E: Exception do
begin LogTo('#5.2'); LogTo(E.Message); end;
end;
// Test #5.3
//[FireDAC][Phys][CData]Malformed SQL Statement: Unexpected token encountered: [?].
//Statement:exec CreateWorksheet (?)
try
FDQuery1.SQL.Text := 'exec CreateWorksheet (:File)';
FDQuery1.Params[0].AsString := QuotedStr(sNewFile);
FDQuery1.ExecSQL; LogTo('#5.3 created');
except
on E: Exception do
begin LogTo('#5.3'); LogTo(E.Message); end;
end;
// Test #5.4
//(exec CreateWorksheet '..') [FireDAC][Phys][CData]Object reference not set to an instance of an object.
try
FDQuery1.SQL.Text := 'CreateWorksheet '+QuotedStr(sNewFile);
FDQuery1.ExecSQL; LogTo('#5.4 created');
except
on E: Exception do
begin LogTo('#5.4'); LogTo(E.Message); end;
end;
// Test #5.5
//[FireDAC][Phys][CData]Malformed SQL Statement: Unexpected token encountered: [CreateWorksheet].
//Statement:CreateWorksheet 'E:/CData/New1.xlsx'
try
FDQuery1.SQL.Text := 'CreateWorksheet '+QuotedStr(sNewFile);
FDQuery1.ExecSQL; LogTo('#5.5 created');
except
on E: Exception do
begin LogTo('#5.5'); LogTo(E.Message); end;
end;
// Test #5.6
//[FireDAC][Phys][CData]Malformed SQL Statement: Unexpected token encountered: [CreateWorksheet].
//Statement:CreateWorksheet E:/CData/New1.xlsx
try
FDQuery1.SQL.Text := 'CreateWorksheet '+sNewFile;
FDQuery1.ExecSQL; LogTo('#5.6 created');
except
on E: Exception do
begin LogTo('#5.6'); LogTo(E.Message); end;
end;
end;
end.Thank you in advance for the help.
