导出EXCEL

//函数声明function ExportExcel(aAdoquery: TADOQuery;aTitle: string): Boolean;overload;function ExportExcel(DBGridEh1: TDBGridEh;aTitle: string): Boolean;overload;//函数实现function ExportExcel(DBGridEh1: TDBGridEh;aTitle: string): Boolean;var  Book: IXLSWorkbook;  ws: IXLSWorksheet;  iSel,iCol,iCol1, iRow: Integer;  SaveDialog1: TSaveDialog;  function GetDbgridEHColumnsCount: Integer;  var    i: Integer;  begin    Result := 0;    for i := 0 to DBGridEh1.Columns.Count - 1 do    begin      if DBGridEh1.Columns.Items[i].Visible then      begin        Inc(Result);      end;    end;  end;begin  SaveDialog1 := TSaveDialog.Create(nil);  SaveDialog1.Filter := 'Excel文件(*.xls)|*.xls';  try    DBGridEh1.DataSource.DataSet.DisableControls;    iSel := DBGridEh1.DataSource.DataSet.RecNo;    if SaveDialog1.Execute then    begin      try        Book := TXLSWorkbook.Create;        ws := Book.Sheets.Add;        ws.Name := '导出数据';        iCol := GetDbgridEHColumnsCount;        iRow := DBGridEh1.DataSource.DataSet.RecordCount;        //单据头        with ws.RCRange[2, 2, 2, iCol + 1] do        begin          Interior.Color := clSkyBlue;          Font.Color := clBlack;          VerticalAlignment := xlVAlignCenter;          HorizontalAlignment := xlHAlignCenter;          RowHeight := 22;         end;        //表格样式        with ws.RCRange[2, 2,iRow+2, iCol +1] do        begin          Borders.Color := 9;        end;        //标题        with ws.RCRange[1, 2, 1, iCol+ 1] do        begin          Merge(false);          HorizontalAlignment := xlHAlignCenter;          VerticalAlignment := xlVAlignCenter;          RowHeight := 30;          Font.Bold := True;          Font.Size := Font.Size + 3;        end;        with DBGridEh1 do        begin          iCol1 := 0;          for iCol := 0 to DBGridEh1.Columns.Count - 1 do          begin            if DBGridEh1.Columns.Items[iCol].Visible then            begin              ws.Cells.Item[2, iCol1+2].Value := DBGridEh1.Columns.Items[iCol].Title.Caption;              Inc(iCol1);            end;          end;        end;        iCol := 0;        DBGridEh1.DataSource.DataSet.First;        with DBGridEh1.DataSource.DataSet do        begin          for iRow := 0 to RecordCount - 1 do          begin            iCol1 := 0;            for iCol := 0 to DBGridEh1.Columns.Count  - 1 do            begin              if DBGridEh1.Columns.Items[iCol].Visible then              begin                 ws.Cells.Item[iRow+3, iCol1+2].Value := FieldByName(DBGridEh1.Columns.Items[iCol].FieldName).Text;                Inc(iCol1);              end;             end;            Next;          end;        end;        ws.Cells.Item[1, 2].Value := aTitle;        Book.SaveAs(SaveDialog1.FileName + '.xls');        Book := nil;         if MessageBox(0,'导出成功，是否要打开此文档？','询问',MB_YESNO + MB_ICONQUESTION) = IDYES then        begin          ShellExecute(0,'open',PAnsiChar(SaveDialog1.FileName + '.xls'),nil,nil,SW_NORMAL);        end;      except       end;    end;  finally    DBGridEh1.DataSource.DataSet.RecNo := iSel;    DBGridEh1.DataSource.DataSet.EnableControls;    FreeAndNil(SaveDialog1);  end;end;function ExportExcel(aAdoquery: TADOQuery;aTitle: string): Boolean;var  Book: IXLSWorkbook;  ws: IXLSWorksheet;  iCol, iRow: Integer;  SaveDialog1: TSaveDialog;begin  SaveDialog1 := TSaveDialog.Create(nil);  SaveDialog1.Filter := 'Excel文件(*.xls)|*.xls';  try    if SaveDialog1.Execute then    begin      Result := False;      try        Book := TXLSWorkbook.Create;        ws := Book.Sheets.Add;        ws.Name := '导出数据';        iCol := aAdoquery.FieldCount;        iRow := aAdoquery.RecordCount;        //单据头        with ws.RCRange[2, 2, 2, iCol + 1] do        begin          Interior.ColorIndex := 9;          Font.ColorIndex := 2;          VerticalAlignment := xlVAlignCenter;          HorizontalAlignment := xlHAlignCenter;          RowHeight := 22;         end;        //表格样式        with ws.RCRange[2, 2,iRow+2, iCol +1] do        begin          Borders.Color := 9;        end;        //标题        with ws.RCRange[1, 2, 1, iCol+ 1] do        begin          Merge(false);          HorizontalAlignment := xlHAlignCenter;          VerticalAlignment := xlVAlignCenter;          RowHeight := 30;          Font.Bold := True;          Font.Size := Font.Size + 3;        end;        with aAdoquery do        begin          for iCol := 0 to FieldCount - 1 do          begin            ws.Cells.Item[2, iCol+2].Value := Fields[iCol].DisplayName;          end;        end;        with aAdoquery do        begin          for iRow := 0 to RecordCount - 1 do          begin            for iCol := 0 to FieldCount - 1 do            begin              ws.Cells.Item[iRow+3, iCol+2].Value :=Fields[iCol].Text;            end;            Next;          end;        end;        ws.Cells.Item[1, 2].Value := aTitle;        Book.SaveAs(SaveDialog1.FileName + '.xls');        Book := nil;        Result := True;        if MessageBox(0,'导出成功，是否要打开此文档？','询问',MB_YESNO + MB_ICONQUESTION) = IDYES then        begin          ShellExecute(0,'open',PAnsiChar(SaveDialog1.FileName + '.xls'),nil,nil,SW_NORMAL);        end;      except       end;    end;  finally    FreeAndNil(SaveDialog1);  end;end;