如何将DBGrid中的数据原样导出到Excel表中

最新推荐文章于 2019-08-13 14:08:00 发布

salut

最新推荐文章于 2019-08-13 14:08:00 发布

阅读量774

点赞数

文章标签： excel integer application library null function

本文链接：https://blog.csdn.net/salut/article/details/5436908

版权

这段代码展示了如何使用Delphi创建一个库，该库包含两个函数sendDBGridToExcel和sendDBGridToExcel_F，用于将TDBGrid组件中的数据原样导出到Excel工作表中。在导出过程中，它会打开数据源，检查是否有数据，然后创建一个新的Excel工作簿，设置单元格内容，并将数据逐行写入Excel表格。

摘要由CSDN通过智能技术生成

library LoadDBGridToExcel;

uses
ShareMem,
SysUtils,
Classes, Windows,Variants, Dialogs, DBGrids, ExcelXP,Graphics;

{$R *.res}
function sendDBGridToExcel_F(dbg :TDBGrid;sheetName :string;show: Boolean):TDateTime;stdcall;
var
i,row,column,icount:integer;
excelApplication:TexcelApplication;
excelWorkBook: TExcelWorkbook;
excelWorkSheet: TExcelWorksheet;
begin
dbg.DataSource.DataSet.Open;

if (dbg.DataSource.DataSet.RecordCount < 1) or ( not dbg.DataSource.DataSet.Active) then
begin
    Result := 0;
    messagebox(getactivewindow(),'无数据！','提示',MB_OK+MB_ICONINFORMATION);
    exit;
end;

try
    excelApplication := TExcelApplication.Create(nil);
    excelWorkBook := TExcelWorkbook.Create(nil);
    excelWorkSheet := TExcelWorksheet.Create(nil);
    excelApplication.Connect;
    excelApplication.Visible[0] := show; {显示过程}
    excelApplication.Caption := 'Excel Application';
    excelApplication.Workbooks.Add(null,0);
    excelWorkBook.ConnectTo(excelApplication.Workbooks[1]);
    excelWorkSheet.ConnectTo(excelWorkBook.Worksheets[1] as _Worksheet);
    excelWorkSheet.Name := sheetName;

    row := 2;
    dbg.DataSource.DataSet.First;
    for icount := 0 to dbg.Columns.Count - 1 do
    begin
      excelWorkSheet.Cells.Item[1,icount + 1] := dbg.Columns.Items[icount].Title.Caption;
      excelWorkSheet.Cells.Item[1,icount + 1].Font.color := clred;
      excelWorkSheet.Cells.Item[1,icount + 1].Font.Name := '黑体';
    end;
    while not (dbg.DataSource.DataSet.Eof) do
    begin
      column := 1;
      for i := 0 to dbg.Columns.Count - 1 do
      begin
        excelWorkSheet.Cells.Item[row,column] := ''''+dbg.DataSource.DataSet.fieldByName(dbg.Columns.Items[i].FieldName).AsString;
        column := column + 1;
      end;
      dbg.DataSource.DataSet.Next;
      row := row + 1;
    end;
    excelApplication.Visible[0] := True;
    excelApplication.Connect;
    excelApplication.Free;
    excelWorkSheet.Free;
    excelWorkBook.Free;
    Result := Now;
    messagebox(getactivewindow(),'数据导出EXCEL成功！','提示',MB_OK+MB_ICONINFORMATION);
except
    Result := 0;
    MessageDlg('Excel可能尚未安装！',mtError,[mbOK],0);
    Abort;
end;
end;

procedure sendDBGridToExcel(dbg :TDBGrid;sheetName :string);stdcall;
var
i,row,column,icount:integer;
excelApplication:TexcelApplication;
excelWorkBook: TExcelWorkbook;
excelWorkSheet: TExcelWorksheet;
begin
dbg.DataSource.DataSet.Open;

if (dbg.DataSource.DataSet.RecordCount < 1) or ( not dbg.DataSource.DataSet.Active) then
begin
    messagebox(getactivewindow(),'无数据！','提示',MB_OK+MB_ICONINFORMATION);
    exit;
end;

try
    excelApplication := TExcelApplication.Create(nil);
    excelWorkBook := TExcelWorkbook.Create(nil);
    excelWorkSheet := TExcelWorksheet.Create(nil);
    excelApplication.Connect;
    excelApplication.Visible[0] := True; {显示过程}
    excelApplication.Caption := 'Excel Application';
    excelApplication.Workbooks.Add(null,0);
    excelWorkBook.ConnectTo(excelApplication.Workbooks[1]);
    excelWorkSheet.ConnectTo(excelWorkBook.Worksheets[1] as _Worksheet);
    excelWorkSheet.Name := sheetName;

    row := 2;
    dbg.DataSource.DataSet.First;
    for icount := 0 to dbg.Columns.Count - 1 do
    begin
      excelWorkSheet.Cells.Item[1,icount + 1] := dbg.Columns.Items[icount].Title.Caption;
      excelWorkSheet.Cells.Item[1,icount + 1].Font.color := clred;
      excelWorkSheet.Cells.Item[1,icount + 1].Font.Name := '黑体';
    end;
    while not (dbg.DataSource.DataSet.Eof) do
    begin
      column := 1;
      for i := 0 to dbg.Columns.Count - 1 do
      begin
        excelWorkSheet.Cells.Item[row,column] := dbg.DataSource.DataSet.fieldByName(dbg.Columns.Items[i].FieldName).AsString;
        column := column + 1;
      end;
      dbg.DataSource.DataSet.Next;
      row := row + 1;
    end;
    excelApplication.Visible[0] := True;
    excelApplication.Connect;
    excelApplication.Free;
    excelWorkSheet.Free;
    excelWorkBook.Free;
    messagebox(getactivewindow(),'数据导入EXCEL成功！','提示',MB_OK+MB_ICONINFORMATION);
except
    MessageDlg('Excel尚未安装！',mtError,[mbOK],0);
    Abort;
end;
end;

exports
sendDBGridToExcel,sendDBGridToExcel_F;

begin
end.