function TForm1.GetLetraRango(NumCampos: Integer): String;
const
elementos = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
begin
if NumCampos < 27 then
Result:= elementos[NumCampos]
else
begin
Result:=elementos[(NumCampos - 1) div 26];
Result:=Result + elementos[((NumCampos - 1) mod 26)+1];
end;
end;
procedure TForm1.SendToExcel(Grid: TDBGrid; Forma: TForm);
var
bm: TBookmark;
col, row, renExcel: Integer;
sline, LetraRango, rango: String;
mem: TMemo;
ExcelApp: Variant;
Format : OleVariant;
Evento:TDataSetNotifyEvent;
ToExcel:Boolean;
begin
ToExcel := true;
Screen.Cursor := crHourglass;
Grid.DataSource.DataSet.DisableControls;
bm := Grid.DataSource.DataSet.GetBookmark;
Grid.DataSource.DataSet.First;
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.WorkBooks.Add($FFFFEFB9);
ExcelApp.WorkBooks[1].WorkSheets[1].Name := 'Sheet1';
Grid.DataSource.DataSet.First;
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range['A2:B2'].Select;
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range['A2:B2'].Font.FontStyle := 'Bold';
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range['A2:B2'].Font.Color := clNavy;
mem := TMemo.Create(Self);
mem.Visible := false;
mem.Parent := Forma;
mem.WordWrap := false;
mem.Clear;
sline := '';
for col := 0 to Grid.FieldCount-1 do
sline := sline + Grid.Fields[col].DisplayLabel + #9;
mem.Lines.Add(sline);
if toExcel then
begin
LetraRango := GetLetraRango(Grid.FieldCount);
mem.SelectAll;
mem.CopyToClipBoard;
rango := 'A3:' + LetraRango + '3';
renExcel := 3;
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range[rango].Select;
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range[rango].Font.FontStyle := 'Bold';
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range[rango].Font.Color := clBlack;
ExcelApp.Workbooks[1].WorkSheets['Sheet1'].Paste;
mem.Lines.Clear;
end;
if assigned(Grid.DataSource.DataSet.AfterScroll) then
begin
Evento:=Grid.DataSource.DataSet.AfterScroll;
Grid.DataSource.DataSet.AfterScroll:=nil;
end;
Grid.DataSource.DataSet.DisableControls;
for row := 0 to Grid.DataSource.DataSet.RecordCount-1 do
begin
sline := '';
for col := 0 to Grid.FieldCount-1 do
sline := sline + Grid.Fields[col].AsString + #9;
mem.Lines.Add(sline);
Grid.DataSource.DataSet.Next;
if toExcel then
if mem.Lines.Count = 100 then
begin
mem.SelectAll;
mem.CopyToClipBoard;
rango := 'A' + IntToStr(renExcel+1) + ':' + LetraRango + IntToStr(renExcel+1+mem.Lines.Count-1);
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range[rango].Select;
Format := '@';
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range[rango].NumberFormat := Format;
ExcelApp.Workbooks[1].WorkSheets['Sheet1'].Paste;
renExcel := renExcel + mem.Lines.Count;
mem.Lines.Clear;
end;
end;
if mem.Lines.Count > 0 then
begin
mem.SelectAll;
mem.CopyToClipboard;
if toExcel then
begin
rango := 'A' + IntToStr(renExcel+1) + ':' + LetraRango + IntToStr(renExcel+1+mem.Lines.Count-1);
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range[rango].Select;
Format := '@';
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range[rango].NumberFormat := Format;
ExcelApp.Workbooks[1].WorkSheets['Sheet1'].Paste;
ExcelApp.WorkBooks[1].WorkSheets['Sheet1'].Range['A3:A3'].Select;
ExcelApp.Visible := true;
end;
end;
Grid.DataSource.DataSet.AfterScroll:=Evento;
Grid.DataSource.DataSet.EnableControls;
mem.Free;
Grid.DataSource.DataSet.GotoBookmark(bm);
Grid.DataSource.DataSet.FreeBookmark(bm);
Grid.DataSource.DataSet.EnableControls;
Screen.Cursor := crDefault;
end;