Exporting data to Excel

[Fabrizio Conti]

Hi all,

I currently use the TMS component TTIWAdvWebGridExcelIO to import/export data to Excel. It works fine and it is very easy to use.

But it is old, it doesn’t support new Excel formats and it is my understanding that TMS will not develop his IW Suite anymore.

During the process to convert my applications moving away from TMS I need to find a solution for Exporting data to Excel from a table or dataset. Before diving into internet trying to find a convenient solution, I wonder if anyone can point me in the right direction of the best practice to do this fairly common task.

Thanks in advance

Fabrizio

[PaulWeem]

Hi Fabrizio,

I have been working with ExcelExport for a while.

It works, but in some situations, performance isn't very high.

But it's worth to have a look, it's (practically) free.

Cheers, Paul

[joelcc]

I have not tried this in the latest version of IW but here is the flexcell routine that works for us.


procedure fxExportToExcel(ADataSet: TDataSet; const AFileName, ABreakFieldName: string;
AOpenFileInExcel: Boolean = True);
var
lXls: TXlsFile;
begin
//this must be called to run Flexcel in a dll
FlexCelDllInit;
lXls := TXlsFile.Create(True);
try
fxPrepareExcelData( ADataSet, lXls, ABreakFieldName );

// Save to File
lXls.Save( AFileName );

{ Open File in Excel }
if AOpenFileInExcel then
ShellExecute(0, 'open', PCHAR(AFileName), nil, nil, SW_SHOWNORMAL);

ADataSet.First;

finally
lXls.Free;
//this must be called to run Flexcel in a dll
FlexCelDllShutdown;
ADataSet.EnableControls;
end;
end;

[Fabrizio Conti]

Hi Paul,

thanks. This is the type of solutions I was looking for. I will give a try.

Joel thank you, too. Wanting to stay in the TMS world this seems to be an easy solution.

Fabrizio

[joelcc]

If it helps anyone here is the procedure that it calls. Just make sure that in the procedure above you do not execute the shellexecute in IW.


procedure fxPrepareExcelData( ADataset: TDataSet; AXls: TXlsFile; ABreakFieldName: string = ''; AUseFieldName: Boolean = True);
var
i,j: integer;
lRow, lCol, lrc, lfc: integer;

procedure fxExcelDataWFieldName;
var
lHoldCaseMgr: string;
j1: integer;
begin
ADataSet.First;

// Write out data again to individual Worksheets - Break on ABreakFieldName
while not ADataSet.eof do
begin

if Trim(ADataSet.FieldByName(ABreakFieldName).AsString) = '' then
begin
ADataSet.next;
Continue;
end;

AXls.AutofitCol(1,lfc,FALSE,1.1,0,-1,0);// AutoFit Columns

AXls.AddSheet;// Add blank WorkSheet
AXls.ActiveSheet := AXls.SheetCount;// Set as Active Sheet
AXls.SheetName := Trim(ADataSet.FieldByName(ABreakFieldName).AsString);

fxCreateHeaderRow(AXls, ADataSet, AUseFieldName);

lHoldCaseMgr := Trim(ADataSet.FieldByName(ABreakFieldName).AsString);

lRow := 2;

while (not ADataSet.eof) and
(Trim(ADataSet.FieldByName(ABreakFieldName).AsString) = lHoldCaseMgr) do
begin
lCol := 1;
lfc := ADataSet.Fields.Count;
for j1 := 0 to lfc - 1 do
begin
if ADataSet.Fields[j1].Visible then
begin
AXls.SetCellValue(lRow,lCol,ADataSet.Fields[j1].Value);
inc(lCol);
end;
end;
ADataSet.next;
inc(lRow);
end; //while
AXls.AutofitCol(1,lfc,FALSE,1.1,0,-1,0); // AutoFit Columns
end; // while
end;

begin
if ((not ADataSet.Active) or (ADataSet.IsEmpty)) then
begin
Raise Exception.Create('Dataset Empty');
end;

ADataSet.First;
//this should be set in the calling procedure, but for now it is left in here since we do not have time to research what
//is calling this.
ADataSet.DisableControls;

// Create Excel object
AXls.NewFile(1);
if ABreakFieldName <> '' then
AXls.SheetName := 'ALL';

fxCreateHeaderRow(AXls, ADataSet, AUseFieldName);

// Write all data to Spreadsheet
lRow := 2;
lCol := 1;

lrc := ADataSet.RecordCount;

if lrc = -1 then
begin
lrc := 0;
ADataSet.First;
while not ADataSet.eof do
begin
Inc(lrc);
ADataSet.Next;
end;
end;

ADataSet.First;

if lrc > 0 then
begin
lfc := 0;
for i := 0 to lrc - 1 do
begin
lfc := ADataSet.Fields.Count;
for j := 0 to lfc - 1 do
begin
// Show field if visible and not a nested dataset
if (ADataSet.Fields[j].Visible) and
(ADataSet.Fields[j].DataType <> ftDataSet) then
begin
AXls.SetCellValue(lRow, lCol, ADataSet.Fields[j].Value);
Inc(lCol);
end;
end;
ADataSet.Next;
lCol := 1;
Inc(lRow);
end;

if ABreakFieldName <> '' then
begin
fxExcelDataWFieldName;
AXls.ActiveSheet := 1;
end;

AXls.AutofitCol(1
, lfc
, FALSE
, 1.1
, 0
, -1
, 0
); // AutoFit Columns
ADataSet.First;
end;
end;