有一简单程序select * from tableA
其结果显示在datagrid控件中,能否将结果导成excel文件
其结果显示在datagrid控件中,能否将结果导成excel文件
解决方案 »
- 咨询delphi的TDateTimePicker问题
- 求Delphi7从入门到精通
- 1.窗体还没有free,就Nil, 如果继续使用此窗体,会不会出错! 2.怎么通过类返回对象名
- Adoquery与Adotable
- 求一个简单的SQL语句
- 各位大哥,谁有烟草生产企业的结构图?
- 早上手机丢了,气氛!!!!!!!!!!!!(散分以散心)
- 程序(界面是一个长条)启动后停靠在桌面最上方,怎么让我的电脑等图标自动往下移动?(
- ActionMainMenuBar,ActionManager和多窗体的问题!
- 用ENTER键实现TAB键的功能
- ★如何让主窗体在桌面上能显示但在任务栏中却没有显示?如何让窗体在按win+m时缩小到系统托盘?
- 我买了大本营的光盘,怎么注册成会员呀?
excel通用,还不用存在excel
var
i,j:Integer;
f:TextFile;
sl:TStringList;
SaveDialog:TSaveDialog;
begin
if (not DataSet.Active) then
Exit;
SaveDialog:=TSaveDialog.create(NIL);
SaveDialog.Filter:='*.csv|*.csv';
SaveDialog.DefaultExt:='*.csv';
if (not SaveDialog.Execute) then
Exit; AssignFile(f,SaveDialog.FileName);
try
Rewrite(f);
except
ShowInfo('保存文件失败!');
CloseFile(f);
Exit;
end; with DataSet do
begin
Screen.Cursor := crHourGlass;
sl := TStringList.Create;
sl.Clear;
for i:=0 to FieldCount-1 do
begin
if (Fields[i].Visible) then
sl.Add(Fields[i].DisplayLabel);
end;
sl.Add(TbName);
Writeln(f,sl.CommaText); DataSet.First;
DataSet.DisableControls;
for j:=0 to RecordCount-1 do
begin
sl.Clear;
for i:=0 to FieldCount-1 do
begin
if (Fields[i].Visible) then
sl.Add(VarToStr(Fields[i].Value));
end;
sl.Add(TbName);
writeln(f,sl.CommaText);
DataSet.Edit;
DataSet['tran_mode']:=0;
DataSet.Post;
DataSet.Next;
end;
DataSet.EnableControls;
sl.Free;
CloseFile(f);
Screen.Cursor := crDefault;
//成功提示
ShowInfo('写文件完成!');
end;
end;
作者: Bear
功能:保存数据集,如TTable,TQuery,TClientDataSet等为Excel文件, 包含标题,可以只将一部分字段导出 这一点通过设置DataSet中要不导出字段的Tag值大于某一个值来处理
原理:调用 Microsoft Excel Ole对象
调用方式:
Function DataSetToExcel(
DataSet:TDataSet;FieldTagMax:Integer;
Visible:Boolean;ExcelFileName:String=''): Boolean;
--------------------------------------------------------------------------------------------------}
unit UExcelTools;
interface
uses
classes, comctrls, stdctrls, windows, Dialogs, controls, SysUtils,
Db,forms,DBClient,ComObj;
//把数据集导入ExcelSheet的核心函数
function DataSetToExcelSheet
(
DataSet :TDataSet;
FieldTagMax :Integer; // 字段的Tag值如果大于这个值,就不导出到Excel
Sheet :OleVariant
): Boolean; //实际使用的函数,内部调用了DataSetToExcelSheet,在外面加入UI接口和错误处理
function DataSetToExcel
(
DataSet :TDataSet; // 要转换的数据集
FieldTagMax :Integer; // 字段的Tag值如果大于这个值,就不导出到Excel
Visible :Boolean; // 是否让做转换工作的Excel可见
ExcelFileName:String='' // Excel文件名,*.xls
): Boolean; implementation Function DataSetToExcelSheet(DataSet:TDataSet;FieldTagMax:Integer;Sheet:OleVariant): Boolean;
var
Row,Col,FieldIndex :Integer;
BK:TBookMark;
begin
Result := False;
if not Dataset.Active then exit;
BK:=DataSet.GetBookMark;
DataSet.DisableControls; Sheet.Activate;
try // 列标题
Row:=1;
Col:=1;
for FieldIndex:=0 to DataSet.FieldCount-1 do
begin
if DataSet.Fields[FieldIndex].Tag <= FieldTagMax then
begin
Sheet.Cells(Row,Col) :=DataSet.Fields[FieldIndex].DisplayLabel;
Inc(Col);
end;
end;
// 表内容
DataSet.First;
while Not DataSet.Eof do
begin
Row:=Row+1;
Col:=1;
for FieldIndex:=0 to DataSet.FieldCount-1 do
begin
if DataSet.Fields[FieldIndex].Tag <= FieldTagMax then
begin
Sheet.Cells(Row,Col):=DataSet.Fields[FieldIndex].AsString;
Inc(Col);
end;
end;
DataSet.Next;
end; Result := True;
finally
DataSet.GotoBookMark(BK);
DataSet.EnableControls;
end;
end;
Function DataSetToExcel(
DataSet:TDataSet;FieldTagMax:Integer;
Visible:Boolean;ExcelFileName:String=''): Boolean;
var
ExcelObj, Excel, WorkBook, Sheet: OleVariant;
OldCursor:TCursor;
SaveDialog:TSaveDialog;
begin
Result := False;
if not Dataset.Active then exit; OldCursor:=Screen.Cursor;
Screen.Cursor:=crHourGlass; try
ExcelObj := CreateOleObject('Excel.Sheet');
Excel := ExcelObj.Application;
Excel.Visible := Visible ;
WorkBook := Excel.Workbooks.Add ;
Sheet:= WorkBook.Sheets[1];
except
MessageBox(GetActiveWindow,'无法调用Mircorsoft Excel! '+chr(13)+chr(10)+
'请检查是否安装了Mircorsoft Excel。','提示',MB_OK+MB_ICONINFORMATION);
Screen.Cursor:=OldCursor;
Exit;
end; Result:=DataSetToExcelSheet(DataSet,FieldTagMax,Sheet) ;
if Result then
if Not Visible then
begin
if ExcelFileName<>''
then WorkBook.SaveAs(FileName:=ExcelFileName)
else begin
SaveDialog:=TSaveDialog.Create(Nil);
SaveDialog.Filter := 'Microsoft Excel 文件¦*.xls';
Result:=SaveDialog.Execute;
UpdateWindow(GetActiveWindow);
if Result then
WorkBook.SaveAs(FileName:=SaveDialog.FileName);
SaveDialog.Free;
end;
Excel.Quit;
end;
Screen.Cursor:=OldCursor;
end; end.
注:
1.在程序中加入UExcelTools单元;
2.在unit1的uses列表中加入UExcelTools;
3.在Form1上放一Table1,连接到“在database desktop中建的表”,打开表:Table1.open;
4.在Button1Click事件中调用函数
DataSetToExcel(Table1,10000,true,'C\:Book1.xls');OK!在Delphi5.0中测试通过