DBGridEh导出excel(备忘)

最新推荐文章于 2023-04-04 22:53:59 发布

原创最新推荐文章于 2023-04-04 22:53:59 发布 · 602 阅读

0 ·

CC 4.0 BY-SA版权

DELPHI 专栏收录该内容

15 篇文章

订阅专栏

本文介绍了一种使用 Delphi 将 TDBGridEh 控件中的数据导出到 Excel 文件的方法。过程包括创建 Excel 对象、设置工作表、写入表头及数据，并对表格进行格式化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

uses comobj

procedure ExportToExcel(OutGrid:TDBGridEh;AFileName:string);
var
XLApp: Variant;
Sheet: Variant;
s1, s2: string;
Caption: String;
Row, Col: integer;
iCount, jCount: Integer;
FBookMark: TBookmark;
FileName: String;
SaveDialog1: TSaveDialog;
TitleName: string;
begin
    //如果数据集为空或没有打开则退出
    if not OutGrid.DataSource.DataSet.Active then Exit;
    SaveDialog1 := TSaveDialog.Create(Nil);
    SaveDialog1.FileName := AFileName + '_' + FormatDateTime('YYYYMMDD', Date);
    SaveDialog1.Filter := 'Excel文件|*.xls';
    if SaveDialog1.Execute then
        FileName := SaveDialog1.FileName;
    SaveDialog1.Free;
    if FileName = '' then Exit;
    Application.ProcessMessages;
    Screen.Cursor := crHourGlass;
    if not VarIsEmpty(XLApp) then
    begin
        XLApp.DisplayAlerts := False;
        XLApp.Quit;
        VarClear(XLApp);
    end;
    //通过ole创建Excel对象
    try
        XLApp := CreateOleObject('Excel.Application');
    except
        MessageDlg('创建Excel对象失败，请检查你的系统是否正确安装了Excel软件！', mtError, [mbOk], 0);
        Screen.Cursor := crDefault;
        Exit;
    end;
    //生成sheet工作页
    XLApp.WorkBooks.Add[xlWBatWorkSheet];
    XLApp.WorkBooks[1].WorkSheets[1].Name := AFileName;
    Sheet := XLApp.Workbooks[1].WorkSheets[AFileName];
    //写表格标题
    //sheet.cells[1, 1] := AFileName;
    //sheet.range[sheet.cells[1, 1], sheet.cells[1, OutGrid.Columns.Count]].Select; //选择该列
    //XLApp.selection.HorizontalAlignment := $FFFFEFF4;                               //居中
    //XLApp.selection.MergeCells := True;
                                               //合并
    //写表头
    Row := 1;
    jCount := 3;
    for iCount := 0 to OutGrid.Columns.Count - 1 do
    begin
        Col := 2;
        Row := iCount+1;
        Caption := OutGrid.Columns[iCount].Title.Caption;
        while POS('|', Caption) > 0 do
        begin
            jCount := 4;
            s1 := Copy(Caption, 1, Pos('|',Caption)-1);
            if s2 = s1 then
            begin
                sheet.range[sheet.cells[Col, Row-1],sheet.cells[Col, Row]].Select;
                XLApp.selection.HorizontalAlignment := $FFFFEFF4;
                XLApp.selection.MergeCells := True;
            end
            else
                Sheet.cells[Col,Row] := Copy(Caption, 1, Pos('|',Caption)-1);
            Caption := Copy(Caption,Pos('|', Caption)+1, Length(Caption));
            Inc(Col);
            s2 := s1;
        end;
        Sheet.cells[Col, Row] := Caption;
        Inc(Row);
    end;
    //合并表头并居中
    if jCount = 4 then
        for iCount := 1 to OutGrid.Columns.Count do
            if Sheet.cells[3, iCount].Value = '' then
            begin
                sheet.range[sheet.cells[2, iCount],sheet.cells[3, iCount]].Select;
                XLApp.selection.HorizontalAlignment := $FFFFEFF4;
                XLApp.selection.MergeCells := True;
            end
            else begin
                sheet.cells[3, iCount].Select;
                XLApp.selection.HorizontalAlignment := $FFFFEFF4;
            end;
    //读取数据
    OutGrid.DataSource.DataSet.DisableControls;
    FBookMark := OutGrid.DataSource.DataSet.GetBookmark;
    OutGrid.DataSource.DataSet.First;
    while not OutGrid.DataSource.DataSet.Eof do
    begin
        for iCount := 1 to OutGrid.Columns.Count do
          Sheet.cells[jCount, iCount] := OutGrid.Columns.Items[iCount-1].Field.AsString;
        Inc(jCount);
        OutGrid.DataSource.DataSet.Next;
    end;
    if OutGrid.DataSource.DataSet.BookmarkValid(FBookMark) then
        OutGrid.DataSource.DataSet.GotoBookmark(FBookMark);
    OutGrid.DataSource.DataSet.EnableControls;
    //读取表脚
    if OutGrid.FooterRowCount > 0 then
    begin
        for Row := 0 to OutGrid.FooterRowCount-1 do
        begin
            for Col := 0 to OutGrid.Columns.Count-1 do
                Sheet.cells[jCount, Col+1] := OutGrid.GetFooterValue(Row,OutGrid.Columns[Col]);
            Inc(jCount);
        end;
    end;
    //调整列宽
    for iCount := 1 to OutGrid.Columns.Count do
        Sheet.Columns[iCount].EntireColumn.AutoFit;
    sheet.cells[1, 1].Select;
    XlApp.Workbooks[1].SaveAs(FileName);
    XlApp.Visible := True;
    XlApp := Unassigned;
    Screen.Cursor := crDefault;
    Application.MessageBox('导出Excel完成','提示信息',0);
end;