如何将DBGrid里查询SQL里的数据结果,通过一个SpeedButton按钮导出到Excel里?

Question

我很菜,在网上搜了很久,也不知道如何将DBGrid里查询出SQL的数据结果,通过一个SpeedButton按钮导出到Excel里?
劳请高手指点一下啥!

Answer 1

var
asheet,range:variant;
i,k,m,n,y:integer;
SaveName:string;
tsList :TStringList;
s :string;
begin
if SaveDialog1.Execute then
begin
Screen.Cursor:=crHourGlass;
SaveName:=SaveDialog1.FileName;
ExcelApplication1.Visible[0]:=False;
ExcelApplication1.Workbooks.Add(xlWBATWorksheet,0);
asheet:=ExcelApplication1.Worksheets.Item[1];
for i:=0 to DBGrid.Columns.Count-1 do
asheet.cells[2,i+1].value:=Trim(DBGrid.Columns.Items[i].Title.Caption);
K:=1;
N:=DBGrid.Columns.count;
I:=DBGrid.DataSource.DataSet.RecordCount;
tsList:=TStringList.Create;
try
DBGrid.DataSource.DataSet.first;
while not DBGrid.DataSource.DataSet.Eof do
begin
s:='';
for y:=0 to n-1 do
begin
s:=s+Trim(DBGrid.Columns.Items[y].Field.DisplayText)+#9;
Application.ProcessMessages;
end;
tsList.Add(s);
DBGrid.DataSource.DataSet.next;
end;
Clipboard.AsText:=tsList.Text;
finally
tsList.Free;
end;
ExcelApplication1.Disconnect;
asheet.cells[3,1].select;
aSheet.Paste;
range:=asheet.range[asheet.cells[2,1],asheet.cells[DBGrid.DataSource.DataSet.RecordCount+2,DBGrid.Columns.Count]];
range.select;
range.borders.linestyle:=1;//画线
for i:=1 to zpGrid.Columns.Count do begin
range:=asheet.range[asheet.cells[1,i],asheet.cells[zpGrid.DataSource.DataSet.RecordCount,i]];
range.EntireColumn.AutoFit;//自动列宽
end;
aSheet.Saveas(SaveName);
MessageBox(Application.Handle,'数据导出完毕！','系统提示',MB_ICONINFORMATION or MB_OK);
Screen.Cursor:=crDefault;
ExcelApplication1.Quit;
ExcelApplication1.Disconnect;
aSheet:=Unassigned; //释放VARIANT变量
DBGrid.DataSource.DataSet.First;
end;

Answer 2

就是运行时在Clipboard.AsText:=tsList.Text处与for i:=1 to zpGrid.Columns.Count do 处有问题,请解释一下这两句是什么意思吧

Answer 3

uses Clipbrd;
Clipboard.AsText:=tsList.Text；//将数据复制到剪贴板
for i:=1 to zpGrid.Columns.Count do 代码只是一个参考，你改成自己的GRID名称即可

Answer 4

我有一列数据超过15位数字,导出变成了科学计数法了,能不能导出后以文本方式显示.另外就是导出的Excel文件会空第一行是什么原因?

Answer 5

http://topic.csdn.net/u/20080602/16/1322ba35-0005-493f-ba40-6c6b59dae057.html

Answer 6

我认为有一个更为简单的方法: 在生成DBGrid的数据之前可以把那个记录集中的数据, 用逗号分隔每个字段值,然后写入一个文本文件并且把文件名定为.cvs，这样的话双击这个文件就会用excel打开了 ,把生成.cvs文件的这个过程放在一个按钮中就可以实现你的需求

Answer 7

我只用国TButton导出，TSpeedbutton没有用过，期待高手

Answer 8

将单元格设为字符：ObjSheet.Columns[1].NumberFormatLocal:= '@';

Answer 9

GRID导EXL格式会很乱的，健议先生民报表再导，如果不允许的话就画EXL模板，然后再往里导，如果楼主真的想省事的话，就用DBGRIDEH吧，还行

Answer 10

RoverX:请问具体哪里修改来实现?

Answer 11

ObjSheet.Columns[1].NumberFormatLocal:= '@';
设定第一列为字符型，可以根据自己需要修改

Answer 12

我一直在用一导出Excel代码,希望对你有帮助:SaveDialog1.Filter属性设置为:'Microsoft Office Excel文件(*.xls)|*.xls'
{最前面加了uses Clipbrd,否则无法通过,剪切板赋值等于明细表的值,如果是DBGridEh直接替换即可}
procedure TForm1.SpeedButton2Click(Sender: TObject);
var
  asheet,range:variant;
  ia,ib,ic:integer;
  SaveName:string;
  tsList :TStringList;
  sa:string;
begin
  if SaveDialog1.Execute then                                                                                                 //保存对话框执行时
     begin
        Screen.Cursor:=crHourGlass;                                                                                           //屏幕光标显砂漏状态
        SaveName:=SaveDialog1.FileName;                                                                                       //保存名称= 保存对话框名
        ExcelApplication1.Visible[0]:=False;                                                                                  //Excel应用程序不可见
        ExcelApplication1.Workbooks.Add(xlWBATWorksheet,0);                                                                   //Excel应用程序添加一个Workbook
        asheet:=ExcelApplication1.Worksheets.Item[1];                                                                         //变量asheet等于工作薄的sheet1
    for ia:=0 to DBGrid1.Columns.Count-1 do                                                                                    //DBGrid1列数
        asheet.cells[1,ia+1].value:=Trim(DBGrid1.Columns.Items[ia].Title.Caption);                                              //sheet1单元格[行1,每列]等于DBGrid1列标题
        ib:=DBGrid1.Columns.count;                                                                                             //N等于列数
        tsList:=TStringList.Create;                                 
      try
      DBGrid1.DataSource.DataSet.first;
      while not DBGrid1.DataSource.DataSet.Eof do
         begin
          sa:='';
         for ic:=0 to ib-1 do                                                                                                    //N等于列数
              begin
              sa:=sa+Trim(DBGrid1.Columns.Items[ic].Field.DisplayText)+#9;
              Application.ProcessMessages;
              end;
          tsList.Add(sa);
          DBGrid1.DataSource.DataSet.next;
       end;
      Clipboard.AsText:=tsList.Text;                                                                                            //最前面加了uses Clipbrd,否则无法通过,剪切板赋值等于明细表的值
      finally                                                                                                                   //最终
      tsList.Free;                                                                                                              //释放
     end;
      ExcelApplication1.Disconnect;                                                                                             //Excel应用程序断开
      asheet.cells[2,1].select;                                                                                                 //从asheet的选中A2单元格
      asheet.cells.NumberFormatLocal:='@';                                                                                      //将Excel单元格设为文本格式
      aSheet.Paste;                                                                                                             //从剪贴板中开始粘贴
      range:=asheet.range[asheet.cells[1,1],asheet.cells[DBGrid1.DataSource.DataSet.RecordCount+1,DBGrid1.Columns.Count]];      // range赋值范围等于asheet顶点单元格：最末单元格
      range.select;                                                                                                             //选中range这个区域
      range.borders.linestyle:=1;                                                                                               //range区域边缘画线
      range.font.Size:=10;                                                                                                      //设置字体大小为10
      for ia:=1 to DBGrid1.Columns.Count do
      begin
         range:=asheet.range[asheet.cells[1,ia],asheet.cells[DBGrid1.DataSource.DataSet.RecordCount,ia]];
         range.EntireColumn.AutoFit;                                                                                            //自动列宽
      end;
         aSheet.Saveas(SaveName);
         MessageBox(Application.Handle,'数据导出完毕！','系统提示',MB_ICONINFORMATION or MB_OK);
         Screen.Cursor:=crDefault;                                                                                              //屏幕光标恢复正常状态
         ExcelApplication1.Quit;
         ExcelApplication1.Disconnect;
         aSheet:=Unassigned;                                                                                                   //释放VARIANT变量
         DBGrid1.DataSource.DataSet.First;
      end;
end;