如何把SQL Server 数据库中查询得到的数据导入excel表中(100分)

如何把SQL Server 数据库中查询得到的数据导入excel表中

如果数据使用sql得到，你可以使用cxgrid之类的直接输出到excel.

有没有大虾知道扩展名为PDG的文件怎么打开？

可用advstringgrid控件直接输出到EXCEL

adobe reader 打开PDF

没试过，供参考：
1.
sExcelConnect = 'Provider=Microsoft.Jet.OLEDB.4.0; '
+ 'User ID=Admin; '
+ 'Data Source=%s; '
+ 'Mode=Share Deny None; '
+ 'Extended Properties="%s;HDR=%s;IMEX=%s"; ';
xlsCnt := format(sExcelConnect, [tmpXlsFileName, 'excel 8.0', 'yes', '1']);
2.
ODBC_SqlStr =
' IN [ODBC] [ODBC;Driver=SQL Server;UID=%s;PWD=%s;Server=%s;DataBase=%s] ';
cntStr := format(ODBC_sqlStr, [UserID, Password, DBServer, DBString]);
3.
with dm.acTmp do
begin
close;
connectionstring := XlsCnt;
s := 'select * into [' + sSheet + '] from tmp ' + cntStr +' where ...' ;
Execute(s);
end;

用ehlib

好多代码

//以下是一个通用的DataSet To Excel 的代码

//窗体文件
object frmDataToExcel: TfrmDataToExcel
Left = 265
Top = 167
Width = 418
Height = 403
BorderIcons = [biSystemMenu]
Caption = '导出数据'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
OnShow = FormShow
PixelsPerInch = 96
TextHeight = 13
object ProgressBar1: TProgressBar
Left = 0
Top = 362
Width = 410
Height = 14
Align = alBottom
TabOrder = 0
end
object Panel1: TPanel
Left = 0
Top = 0
Width = 410
Height = 362
Align = alClient
TabOrder = 1
object Panel2: TPanel
Left = 1
Top = 307
Width = 408
Height = 54
Align = alBottom
BevelOuter = bvNone
TabOrder = 0
object btnOK: TBitBtn
Left = 72
Top = 16
Width = 75
Height = 25
Caption = '确定'
TabOrder = 0
OnClick = btnOKClick
end
object btnCancel: TBitBtn
Left = 280
Top = 16
Width = 75
Height = 25
Cancel = True
Caption = '取消'
TabOrder = 1
OnClick = btnCancelClick
end
end
object Panel3: TPanel
Left = 1
Top = 1
Width = 408
Height = 32
Align = alTop
BevelOuter = bvNone
TabOrder = 1
object Panel5: TPanel
Left = 0
Top = 0
Width = 203
Height = 32
Align = alLeft
BevelOuter = bvNone
Caption = '数据字段'
TabOrder = 0
end
object Panel6: TPanel
Left = 205
Top = 0
Width = 203
Height = 32
Align = alRight
BevelOuter = bvNone
Caption = '导出字段'
TabOrder = 1
end
end
object Panel4: TPanel
Left = 183
Top = 33
Width = 44
Height = 274
Align = alClient
BevelOuter = bvNone
TabOrder = 2
object btnExportAll: TSpeedButton
Left = 2
Top = 96
Width = 40
Height = 22
Caption = '=>'
OnClick = btnExportAllClick
end
object btnExportOne: TSpeedButton
Left = 2
Top = 66
Width = 40
Height = 22
Caption = '->'
OnClick = btnExportOneClick
end
object btnCancelAll: TSpeedButton
Left = 2
Top = 157
Width = 40
Height = 22
Caption = '<='
OnClick = btnCancelAllClick
end
object btnCancelOne: TSpeedButton
Left = 2
Top = 128
Width = 40
Height = 22
Caption = '<-'
OnClick = btnCancelOneClick
end
object SpeedButton1: TSpeedButton
Left = 3
Top = 200
Width = 39
Height = 22
OnClick = SpeedButton1Click
end
end
object lstIn: TCheckListBox
Left = 1
Top = 33
Width = 182
Height = 274
Align = alLeft
ImeName = '极品五笔输入法'
ItemHeight = 13
TabOrder = 3
end
object lstOut: TCheckListBox
Left = 227
Top = 33
Width = 182
Height = 274
Align = alRight
ImeName = '极品五笔输入法'
ItemHeight = 13
TabOrder = 4
end
end
end

//窗体单元文件
unit DataToExcelFrm;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComCtrls, StdCtrls, ExtCtrls, CheckLst,DB, Buttons,ComObj,Math;

type
TfrmDataToExcel = class(TForm)
ProgressBar1: TProgressBar;
Panel1: TPanel;
Panel2: TPanel;
Panel3: TPanel;
Panel4: TPanel;
lstIn: TCheckListBox;
lstOut: TCheckListBox;
Panel5: TPanel;
Panel6: TPanel;
btnOK: TBitBtn;
btnCancel: TBitBtn;
btnExportAll: TSpeedButton;
btnExportOne: TSpeedButton;
btnCancelAll: TSpeedButton;
btnCancelOne: TSpeedButton;
SpeedButton1: TSpeedButton;
procedure btnCancelClick(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure btnExportOneClick(Sender: TObject);
procedure btnExportAllClick(Sender: TObject);
procedure btnCancelOneClick(Sender: TObject);
procedure btnCancelAllClick(Sender: TObject);
procedure SpeedButton1Click(Sender: TObject);
procedure btnOKClick(Sender: TObject);
private
{ Private declarations }
procedure InickbxIn;
//Operation:0 Source -> Darget; 1 Source => Darget ; 2 Source <- Darget ; 3 Source <= Darget
procedure MoveListNode(var Source, Target: TCheckListBox;Operation:Byte);

public
{ Public declarations }
end;


var
frmDataToExcel: TfrmDataToExcel;

FDataSet: TDataSet;


implementation

{$R *.dfm}

{ TfrmDataToExcel }


procedure TfrmDataToExcel.InickbxIn;
var i:integer;
begin
lstIn.Clear;
with FDataSet do
begin
for i:=0 to FieldCount-1 do
if Fields.Visible = True then
lstIn.Items.Add(Fields.DisplayLabel);
end;
end;


procedure TfrmDataToExcel.btnCancelClick(Sender: TObject);
begin
close;
end;

//Operation:0 Source -> Darget; 1 Source => Darget ; 2 Source <- Darget ; 3 Source <= Darget
procedure TfrmDataToExcel.MoveListNode( var Source, Target: TCheckListBox; Operation: Byte);
var i:integer;
begin
case Operation of
0:
begin
Target.Clear;
for i:=0 to Source.Items.Count -1 do
if Source.Checked = True then
Target.Items.Add(Source.Items);
end;
1:
begin
Target.Clear;
for i:=0 to Source.Items.Count -1 do
Target.Items.Add(Source.Items);
end;
2:
begin
for i:=Target.Items.Count-1 downto 0 do
if Target.Checked then Target.Items.Delete(i);
end;
3:
begin
Target.Clear;
end;
end;
end;

procedure TfrmDataToExcel.FormShow(Sender: TObject);
begin
lstIn.MultiSelect:=True;
lstOut.MultiSelect:=True;
InickbxIn;
end;

procedure TfrmDataToExcel.btnExportOneClick(Sender: TObject);
begin
MoveListNode(lstIn,lstOut,0);
end;

procedure TfrmDataToExcel.btnExportAllClick(Sender: TObject);
begin
MoveListNode(lstIn,lstOut,1);
end;

procedure TfrmDataToExcel.btnCancelOneClick(Sender: TObject);
begin
MoveListNode(lstIn,lstOut,2);
end;

procedure TfrmDataToExcel.btnCancelAllClick(Sender: TObject);
begin
MoveListNode(lstIn,lstOut,3);
end;

procedure TfrmDataToExcel.SpeedButton1Click(Sender: TObject);
begin
InickbxIn;
end;

procedure TfrmDataToExcel.btnOKClick(Sender: TObject);
const
TextRow = 2;
TextCol = 2;
xlHAlignLeft =-4131;
xlHAlignRight =-4152;
xlHAlignCenter =-4108;
xlInsideHorizontal = 12;
xlInsideVertical = 11;
xlEdgeBottom = 9;
xlEdgeLeft = 7;
xlEdgeRight = 10;
xlEdgeTop = 8;
xlThin =2;
xlThick =4;
xlContinuous = 1;
xlLineStyleNone= 6;
var
VExcelApp: Variant;
VExcelWorkBook: Variant;
VExcelWorkSheet1: Variant;
i, vCellRow: Integer;
FieldNumber: Integer;
S: String;
FieldType1: TFieldType;
CurrentRecordBookMark: TBookMark;
begin
with FDataSet do
begin
if (not Active) or (RecordCount=0) then Exit;
if lstOut.Items.count=0 then
begin
Application.MessageBox('没有选择打印的字段','信息框',
MB_OK+MB_DEFBUTTON1+MB_ICONEXCLAMATION+MB_SYSTEMMODAL);
exit;
end;
ProgressBar1.Min := 0;
ProgressBar1.Max := RecordCount+1;
ProgressBar1.Step := 1;
ProgressBar1.Visible:=True;
ProgressBar1.StepIt;

DisableControls;

CurrentRecordBookMark := GetBookMark;
VExcelApp := CreateOleObject('Excel.Application');
VExcelApp.Caption:='Microsoft Excel(软件提供——XuXianjin)';
VExcelApp.Visible := False;
VExcelApp.SheetsInNewWorkbook := 1;
VExcelWorkBook := VExcelApp.WorkBooks.Add;
VExcelWorkSheet1 := VExcelWorkBook.Sheets[1];
VExcelWorkSheet1.Cells[1,1].Value := '序号';
VExcelWorkSheet1.Columns[1].ColumnWidth := 6;

FieldNumber:=0;
for i := 0 to lstOut.Items.Count -1 do
begin
FieldType1 := FieldByName(lstOut.Items).DataType;
VExcelWorkSheet1.Columns[TextCol+FieldNumber].ColumnWidth :=
Max(FieldByName(lstOut.Items).DisplayWidth,
length(FieldByName(lstOut.Items).DisplayLabel));
VExcelWorkSheet1.Cells[1,TextCol+FieldNumber].Value := FieldByName(lstOut.Items).DisplayLabel;
case FieldType1 of
ftBoolean ,
ftMemo ,
ftFmtMemo ,
ftWideString ,
ftString ,
ftTime ,
ftDate ,
ftDateTime :
begin
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[TextRow, TextCol+FieldNumber],
VExcelWorkSheet1.Cells[TextRow+RecordCount-1, TextCol+FieldNumber]].NumberFormat := '@';
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[TextRow, TextCol+FieldNumber],
VExcelWorkSheet1.Cells[TextRow+RecordCount-1, TextCol+FieldNumber]].HorizontalAlignment := xlHAlignLeft;
end;
ftFloat :
begin
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[TextRow, TextCol+FieldNumber],
VExcelWorkSheet1.Cells[TextRow+RecordCount-1, TextCol+FieldNumber]].NumberFormat
:= '0.00';
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[TextRow, TextCol+FieldNumber],
VExcelWorkSheet1.Cells[TextRow+RecordCount-1, TextCol+FieldNumber]].HorizontalAlignment
:= xlHAlignRight;
end;
ftCurrency :
begin
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[TextRow, TextCol+FieldNumber],
VExcelWorkSheet1.Cells[TextRow+RecordCount-1, TextCol+FieldNumber]].NumberFormat
:= '￥#,##0.00;[红色]￥-#,##0.00';
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[TextRow, TextCol+FieldNumber],
VExcelWorkSheet1.Cells[TextRow+RecordCount-1, TextCol+FieldNumber]].HorizontalAlignment
:= xlHAlignRight;
end;
ftSmallint ,
ftWord ,
ftLargeint ,
ftAutoInc ,
ftInteger :
begin
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[TextRow, TextCol+FieldNumber],
VExcelWorkSheet1.Cells[TextRow+RecordCount-1, TextCol+FieldNumber]].NumberFormat
:= '0_ ;[红色]-0';
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[TextRow, TextCol+FieldNumber],
VExcelWorkSheet1.Cells[TextRow+RecordCount-1, TextCol+FieldNumber]].HorizontalAlignment
:= xlHAlignRight;
end;
end;
FieldNumber:=FieldNumber+1;
end;

First;

VCellRow := TextRow;
while not Eof do
begin
VExcelWorkSheet1.Cells[vCellRow,1].Value := vCellRow-TextRow+1;
VExcelWorkSheet1.Cells[vCellRow,1].Formula := '=row(A' + Trim(IntToStr(vCellRow)) + ')-1';
FieldNumber:=0;
for i := 0 to lstOut.Items.Count-1 do
begin
with FieldByName(lstOut.Items) do
begin
if DataType = ftBoolean then
begin
S := AsString;
if S<>'' then
if AsBoolean then
S := '是' else S := '否';
end
else
if Lookup then
begin
if LookupDataset.Locate(LookupKeyFields,FieldByName(KeyFields).AsString,[]) then
S := LookupDataset.FieldByName(LookupResultField).AsString
else
S := '';
end
else
S := AsString;
end;
VExcelWorkSheet1.Cells[VCellRow,TextCol+FieldNumber].Value := S;
FieldNumber:=FieldNumber+1;
end;
Next;
VCellRow := VCellRow+1;
ProgressBar1.Stepit;
end;

// TitleRow
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[1, 1],
VExcelWorkSheet1.Cells[1, FieldNumber+1]].HorizontalAlignment := xlHAlignCenter;
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[1, 1],
VExcelWorkSheet1.Cells[1, FieldNumber+1]].Borders[xlEdgeRight].Weight := xlThick;

VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[TextRow-1, 1],
VExcelWorkSheet1.Cells[VCellRow-1, TextCol+FieldNumber-1]].Font.Size:= 9;
VExcelWorkSheet1.Range[VExcelWorkSheet1.Cells[TextRow-1, 1],
VExcelWorkSheet1.Cells[VCellRow-1, TextCol+FieldNumber-1]].Font.Name:= '宋体';


VExcelWorkSheet1.PageSetup.PrintTitleRows := VExcelWorkSheet1.Rows[1].Address;
VExcelWorkSheet1.PageSetup.PrintTitleColumns := VExcelWorkSheet1.Columns[1].Address;

GotoBookmark(CurrentRecordBookmark);

EnableControls;
ProgressBar1.Position:=0;

FreeBookMark(CurrentRecordBookmark);
VExcelApp.Visible := True;

end;
end;

end.

//调用的过程
with TfrmDataToExcel.Create(Application) do
try
FDataSet := ADOTable1;
ShowModal;
finally
Free;
end;