Exporting data to Excel

[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;