在DELPHI中如何通过控件(DBGrid控件或其他的)查询SQL数据库中多个表的信息,并导出为EXCEL文件,文件名为"日期.xls
要求每个表信息导出一个EXCEL文件,且每天导出新文件,不会覆盖前一天的文件!!
谢谢 各位大侠帮忙下 !!
要求每个表信息导出一个EXCEL文件,且每天导出新文件,不会覆盖前一天的文件!!
谢谢 各位大侠帮忙下 !!
解决方案 »
- 求教:现在最新的DELPHI是什么版本?我一直用DELPHI7有必要换吗?
- Delphi 有这样的VCL控件吗?
- 程序完成某件工作,后自动激活显示
- 错误异常 不知如何处理 希望大家给予帮助
- 多线程的问题
- 象这样的解压缩怎么做?
- treeview的问题,请各位帮给以解决!
- 请问fastreport是怎么用的?
- (对应姊妹片)中国软件业的程序员认为比较好的公司清单 欢迎加入
- 组件问题:[Error] Nerver-build package 'ourway' requires always-build package 'EhLib'
- wooden954,进来接分
- 为什么我的delphi6里怎么没有webservice页?如何安装?
数据集(dataset)用adoquery,通过adoquery来查询多个表的数据,with adoquery1 do
begin
close;
sql.clear;
sql.add('select * from table1,table2 where table1.bh=table1.bh');
open;
end;
然后用下面的表导出
procedure TForm1.OUTTOEXCEL1Click(Sender: TObject);
var
s:TStringList;
str,fn:string;
i:Integer;
begin
str:='';
fn:=datetostr(now);
dbgrid1.DataSource.DataSet.DisableControls;
for i:=0 to dbgrid1.DataSource.DataSet.FieldCount-1 do
str:=str+dbgrid1.DataSource.DataSet.fields[i].DisplayLabel+char(9);
str:=str+#13;
dbgrid1.DataSource.DataSet.First;
while not(dbgrid1.DataSource.DataSet.eof) do
begin
for i:=0 to dbgrid1.DataSource.DataSet.FieldCount-1 do
str:=str+dbgrid1.DataSource.DataSet.Fields[i].AsString+char(9);
str:=str+#13;
dbgrid1.DataSource.DataSet.next;
end;
dbgrid1.DataSource.DataSet.EnableControls;
s:=TStringList.Create;
s.Add(str);
s.SaveToFile('c:\'+fn+'.xls');//保存到c:\当天日期.xls
showmessage('c:\'+fn+'.xls');
s.Free;
end;
SaveDialog1->InitialDir = "C:\Documents and Settings\bc\桌面\Backup";
SaveDialog1->Filter = "Excel文件(*.xls)|*.XLS";
if(SaveDialog1->Execute())
{
AnsiString filename=SaveDialog1->FileName;
if(filename.Trim().Length()<1)
{
Application->MessageBox("文件名不能为空!","系统提示",MB_OK+64);
return;//可以给个提示
}
filename += ".xls";
SaveDBGridEhToExportFile(__classid(TDBGridEhExportAsText),DBGridEh1,filename,true);
Application->MessageBox("导出为Excel文件已经完成!","系统提示",MB_OK+64);
}这是BCB的一段代码,我比较懒,你先看看会不会转成delphi。
SaveDialog1->InitialDir = "C:\Documents and Settings\bc\桌面\Backup";
SaveDialog1->Filter = "Excel文件(*.xls)|*.XLS";
if(SaveDialog1->Execute())
{
AnsiString filename=SaveDialog1->FileName;
if(filename.Trim().Length()<1)
{
Application->MessageBox("文件名不能为空!","系统提示",MB_OK+64);
return;//可以给个提示
}
filename += ".xls";
SaveDBGridEhToExportFile(__classid(TDBGridEhExportAsText),DBGridEh1,filename,true);
Application->MessageBox("导出为Excel文件已经完成!","系统提示",MB_OK+64);
}这是BCB的一段代码,我比较懒,你先看看会不会转成delphi。
begin
with Query1 do
close;
SQL.clear;
SQL.add('select * from ClientBInfo,ClientWInfo,WaterMeterInfo,SellsInfo
where ClientBInfo.CACC=ClientBInfo.CACC');
open;end;
出现SQL没有定义
怎么回事啊?
begin
with Query1 do
begin
close;
SQL.clear;
SQL.add('select * from ClientBInfo,ClientWInfo,WaterMeterInfo,SellsInfo
where ClientBInfo.CACC=ClientBInfo.CACC');
open;
end;end;是把所有表的信息导出到一个EXCEL文件中
查询的功能没有用了 不能查询了 而且DBGrid控件里面不能显示全部的用户了 只是重复显示了1号(第一个)12次和11号(最后一个)12次
这是为什么?会不会和我表里的字段类型有关系 我表里有很多种类型
查询的功能没有用了 不能查询了 而且DBGrid控件里面不能显示全部的用户了 只是重复显示了1号(第一个)12次和11号(最后一个)12次 而且不能导出数据生成EXCEL文件
这是为什么?会不会和我表里的字段类型有关系 我表里有很多种类型
查询功能还是没有用
但是DBGrid控件里面能显示全部的用户 也 可以 导出EXCEL文件
不 知道 这是上面 原因
麻烦你了谢谢
(*
原作者: iamdream(delphi盒子)
修改: 不得闲
功能: 将DbGrid数据保存到Excel
参数:
Grid指定表格
FileName指定要保存的文件名
MaxPageRowCount指定一页最多的支持行数
ShowProgress 指定是否显示进度条
用法:
SaveDbGridAsExcel(DBGrid1,'C:\2.xls','表测试',2000);
*)procedure SaveDbGridAsExcel(Grid: TDBGrid;const FileName,title: string;
const MaxPageRowCount: Integer = 65535;const ShowProgress: Boolean = True);
const
MAX_VAR_ONCE = 1000; //一次导出的条数
var //返回导出记录条数
Excel, varCells: Variant;
MySheet, MyCells, Cell1, Cell2, Range: OleVariant;
iRow, iCol, iSheetIdx, iVarCount, iCurRow: integer;
CurPos: TBook;
ProgressForm: TForm;
Prompt: TLabel;
progressBar: TProgressBar;
Panel : TPanel;
Button : TButton;
procedure ReSetObjEvent(OldEventAddr: pointer;NewEventValue: pointer;ReSetObject: TObject);
begin
TMethod(OldEventAddr^).Code := NewEventValue;
TMethod(OldEventAddr^).Data := ReSetObject;
end; procedure ButtonClick(BtnObject: TObject;Sender: TObject);
begin
TComponent(BtnObject).Tag := Integer(MessageBox(Application.Handle,
'真的要终止数据的导出吗?','确认',
MB_OKCANCEL + MB_ICONINFORMATION) = IDOK);
end; procedure CreateProgressForm;
begin
ProgressForm := TForm.Create(nil);
With ProgressForm do
begin
Font.Name := '宋体';
Font.Size := 10;
BorderStyle := bsNone;
Width := 280;
Height := 120;
BorderWidth := 1;
Color := clBackground;
Position := poOwnerFormCenter;
end;
Panel := TPanel.Create(ProgressForm);
with Panel do { Create Panel }
begin
Parent := ProgressForm;
Align := alClient;
BevelInner := bvNone;
BevelOuter := bvNone;
Caption := '';
end; Prompt := TLabel.Create(Panel);
with Prompt do { Create Label }
begin
Parent := Panel;
Left := 20;
Top := 25;
Caption := '正在启动Excel,请稍候……';
end; progressBar := TProgressBar.Create(panel);
with ProgressBar do { Create ProgressBar }
begin
Step := 1;
Parent := Panel;
Smooth := true;
Left := 20;
Top := 50;
Height := 18;
Width := 260;
end; Button := TButton.Create(Panel);
with Button do { Create Cancel Button }
begin
Parent := Panel;
Left := 115;
Top := 80;
Caption := '关闭';
end;
ReSetObjEvent(@@Button.OnClick,@ButtonClick,Button);
ProgressForm.FormStyle := fsStayOnTop;
ProgressForm.Show;
ProgressForm.Update;
end;begin
if (Grid.DataSource <> nil) and
(Grid.DataSource.DataSet <> nil) and
Grid.DataSource.DataSet.Active then
begin
Grid.DataSource.DataSet.DisableControls;
CurPos := Grid.DataSource.DataSet.GetBook;
Grid.DataSource.DataSet.First;
try
if ShowProgress then
begin
CreateProgressForm;
Button.Tag := 0;
end;
Excel := CreateOleObject('Excel.Application');
Excel.WorkBooks.Add;
Excel.Visible := False;
except
Application.Messagebox('Excel 没有安装!','操作提示', MB_IConERROR + mb_Ok);
Screen.Cursor := crDefault;
Grid.DataSource.DataSet.GotoBook(CurPos);
Grid.DataSource.DataSet.FreeBook(CurPos);
Grid.DataSource.DataSet.EnableControls;
if ProgressForm <> nil then
ProgressForm.Free;
exit;
end;
if Grid.DataSource.DataSet.RecordCount <= MAX_VAR_ONCE then
iVarCount := Grid.DataSource.DataSet.RecordCount
else iVarCount := MAX_VAR_ONCE;
varCells := VarArrayCreate([1, iVarCount,1,Grid.FieldCount],varVariant); iSheetIdx := 1;
iRow := 0;
if ShowProgress then
begin
ProgressBar.Position := 0;
Prompt.Caption := '请等待,正在导出数据……';
ProgressBar.Max := Grid.DataSource.DataSet.RecordCount;
end;
while (not Grid.DataSource.DataSet.Eof and not ShowProgress) or
(not Grid.DataSource.DataSet.Eof and ShowProgress and (Button.Tag = 0)) do
begin
if (iRow = 0) or (iRow > MaxPageRowCount + 1) then
begin
if iSheetIdx <= Excel.WorkBooks[1].WorkSheets.Count then
MySheet := Excel.WorkBooks[1].WorkSheets[iSheetIdx]
else
MySheet := Excel.WorkBooks[1].WorkSheets.Add(NULL, MySheet);//加在后面
MySheet.Name := Title + IntToStr(iSheetIdx);
MyCells := MySheet.Cells;
Inc(iSheetIdx);
//开始新的数据表
iRow := 1;
//写入表头
for iCol := 1 to Grid.FieldCount do
begin
MySheet.Cells[1, iCol] := Grid.Columns[iCol-1].Title.Caption;
MySheet.Cells[1, iCol].Font.Bold := True;
if (Grid.Fields[iCol - 1].DataType = ftString) or
(Grid.Fields[iCol - 1].DataType = ftWideString) then
//对于“字符串”型数据则设Excel单元格为“文本”型
MySheet.Columns[iCol].NumberFormatLocal := '@';
end;
Inc(iRow);
end;
iCurRow := 1;
while (not Grid.DataSource.DataSet.Eof and not ShowProgress) or
(not Grid.DataSource.DataSet.Eof and ShowProgress and (Button.Tag = 0)) do
begin
for iCol := 1 to Grid.FieldCount do
begin
Application.ProcessMessages;
if Grid.Fields[iCol - 1].IsBlob then
varCells[iCurRow, iCol] := '二进制数据'
else varCells[iCurRow, iCol] := Grid.Fields[iCol-1].AsString;
end;
Inc(iRow);
Inc(iCurRow);
if ShowProgress then
ProgressBar.Position := ProgressBar.Position + 1;
Application.ProcessMessages;
Grid.DataSource.DataSet.Next;
if (iCurRow > iVarCount) or (iRow > MaxPageRowCount + 1) then
begin
Application.ProcessMessages;
Break;
end;
end;
Cell1 := MyCells.Item[iRow - iCurRow + 1, 1];
Cell2 := MyCells.Item[iRow - 1,Grid.FieldCount];
Range := MySheet.Range[Cell1 ,Cell2];
Range.Value := varCells;
MySheet.Columns.AutoFit;
Cell1 := Unassigned;
Cell2 := Unassigned;
Range := Unassigned;
Application.ProcessMessages;
end;
if (ShowProgress and (Button.Tag = 0)) or not ShowProgress then
MySheet.saveas(FileName);
MyCells := Unassigned;
varCells := Unassigned;
Excel.WorkBooks[1].Saved := True;
MySheet.application.quit;
Excel.quit;
Excel := Unassigned;
if CurPos <> nil then
begin
Grid.DataSource.DataSet.GotoBook(CurPos);
Grid.DataSource.DataSet.FreeBook(CurPos);
end;
Grid.DataSource.DataSet.EnableControls;
if ProgressForm <> nil then
ProgressForm.Free;
end;
end;
还有运行上面的第一代码时 显示在DBGrid控件的信息正常 运行第二代码时 显示在DBGrid控件的信息会重复显示几遍
怎么办啊? 第一、procedure TForm1.Button1Click(Sender: TObject);
begin
with Query1 do
begin
SQL.Clear;
SQL.Add('Select * from ClientBInfo where Name like :Name');
Case RadioGroup1.ItemIndex of
0:parambyname('Name').AsString:=edit1.Text;
1:parambyname('Name').AsString:=edit1.Text + '%';
end;
open;
end;
end;二、procedure TForm1.Button1Click(Sender: TObject);
begin
with Query1 do
begin
SQL.Clear;
SQL.Add('Select * from ClientBInfo,ClientWInfo where ClientBInfo.CACC=ClientBInfo.CACC');
Case RadioGroup1.ItemIndex of
0:parambyname('CACC').AsString:=edit1.Text;
1:parambyname('CACC').AsString:=edit1.Text + '%';
end;
open;
end;
end;
还有运行上面的第一代码时 显示在DBGrid控件的信息正常 运行第二代码时 显示在DBGrid控件的信息会重复显示几遍
怎么办啊?是不是不用连接2个表?还是怎么搞? 第一、procedure TForm1.Button1Click(Sender: TObject);
begin
with Query1 do
begin
SQL.Clear;
SQL.Add('Select * from ClientBInfo where Name like :Name');
Case RadioGroup1.ItemIndex of
0:parambyname('Name').AsString:=edit1.Text;
1:parambyname('Name').AsString:=edit1.Text + '%';
end;
open;
end;
end;
二、procedure TForm1.Button1Click(Sender: TObject);
begin
with Query1 do
begin
SQL.Clear;
SQL.Add('Select * from ClientBInfo,ClientWInfo where ClientBInfo.CACC=ClientBInfo.CACC');
Case RadioGroup1.ItemIndex of
0:parambyname('CACC').AsString:=edit1.Text;
1:parambyname('CACC').AsString:=edit1.Text + '%';
end;
open;
end;
end;