procedure DeriveToExcel(Title: String; DBGrid: TDBGrid; Total: Boolean); var ExcelApp, WorkBook: Variant; i, j: Integer; Row, Col: Integer; FieldName: string; DataSet: TDataSet; S: String; begin // 数据发送到 Excel try ExcelApp := CreateOleObject('Excel.Application'); WorkBook := CreateOleObject('Excel.Sheet'); except Application.MessageBox('你的机器里未安装Microsoft Excel. ', '', 32); Exit; end; Application.ProcessMessages; WorkBook := ExcelApp.WorkBooks.Add; Col := 1; ExcelApp.Cells(2, Col) := Title; Row := 4; DataSet := DBGrid.DataSource.DataSet; for I := 0 to DBGrid.Columns.Count - 1 do begin if DBGrid.Columns[I].Visible then begin FieldName := DBGrid.Columns[I].Title.Caption; ExcelApp.Cells(Row, Col) := FieldName; Col := Col + 1; end; end; Row := Row + 1; DataSet.First; while not DataSet.Eof do begin Col := 1; for J := 0 to DBGrid.Columns.Count - 1 do begin FieldName := DBGrid.Columns[J].FieldName; ExcelApp.Cells(Row, Col) := ' ' + DataSet.FieldByName(FieldName).AsString + ' '; Col := Col + 1; end; Row := Row + 1; DataSet.Next; end; if Total then begin Col := 1; for J := 0 to DBGrid.Columns.Count - 1 do begin S := Char(64 + ((J+1) mod 26)); if (J+1) > 26 then begin S := Char(65+(((J+1)-26) div 26)) + S; end; if J = 0 then begin ExcelApp.Cells(Row, Col) := '合计'; end else if DBGrid.Columns[J].Field.DataType in [ftInteger, ftSmallint, ftFloat, ftBCD] then begin FieldName := DBGrid.Columns[J].FieldName; ExcelApp.Cells(Row, Col) := '=SUM('+S+'4:'+S+IntToStr(Row-1)+')'; end; Col := Col + 1; end; end; ExcelApp.Visible := True; // WorkBook.SaveAs(SaveDialog1.FileName); // WorkBook.Close; // ExcelApp.Quit; // ExcelApp := Unassigned; end;
var
ExcelApp, WorkBook: Variant;
i, j: Integer;
Row, Col: Integer;
FieldName: string;
DataSet: TDataSet;
S: String;
begin // 数据发送到 Excel
try
ExcelApp := CreateOleObject('Excel.Application');
WorkBook := CreateOleObject('Excel.Sheet');
except
Application.MessageBox('你的机器里未安装Microsoft Excel. ', '', 32);
Exit;
end; Application.ProcessMessages;
WorkBook := ExcelApp.WorkBooks.Add;
Col := 1;
ExcelApp.Cells(2, Col) := Title;
Row := 4;
DataSet := DBGrid.DataSource.DataSet;
for I := 0 to DBGrid.Columns.Count - 1 do
begin
if DBGrid.Columns[I].Visible then
begin
FieldName := DBGrid.Columns[I].Title.Caption;
ExcelApp.Cells(Row, Col) := FieldName;
Col := Col + 1;
end;
end; Row := Row + 1; DataSet.First;
while not DataSet.Eof do
begin
Col := 1;
for J := 0 to DBGrid.Columns.Count - 1 do
begin
FieldName := DBGrid.Columns[J].FieldName;
ExcelApp.Cells(Row, Col) := ' ' + DataSet.FieldByName(FieldName).AsString + ' ';
Col := Col + 1;
end;
Row := Row + 1;
DataSet.Next;
end; if Total then
begin
Col := 1;
for J := 0 to DBGrid.Columns.Count - 1 do
begin
S := Char(64 + ((J+1) mod 26));
if (J+1) > 26 then
begin
S := Char(65+(((J+1)-26) div 26)) + S;
end;
if J = 0 then
begin
ExcelApp.Cells(Row, Col) := '合计';
end
else if DBGrid.Columns[J].Field.DataType in [ftInteger, ftSmallint, ftFloat, ftBCD] then
begin
FieldName := DBGrid.Columns[J].FieldName;
ExcelApp.Cells(Row, Col) := '=SUM('+S+'4:'+S+IntToStr(Row-1)+')';
end;
Col := Col + 1;
end;
end;
ExcelApp.Visible := True;
// WorkBook.SaveAs(SaveDialog1.FileName);
// WorkBook.Close;
// ExcelApp.Quit;
// ExcelApp := Unassigned;
end;