Exporting data to Excel

<< Click to Display Table of Contents >>

Navigation:  Forum >

Exporting data to Excel

Forum link

 

 

 


 

12-22-2023, 09:32 AM:

 

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

 

 

 


 

12-22-2023, 09:50 AM:

 

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

 

 

 


 

12-22-2023, 02:22 PM:

 

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;

 

 

 


 

12-23-2023, 10:28 AM:

 

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

 

 

 


 

12-27-2023, 02:57 PM:

 

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;