Function DataSetToTXT(DataSet:TDataSet;FileName:String):Boolean; var s:TStringList; str:string; i:integer; SaveDialog:TSaveDialog; BK:TBookMark; begin s:=TStringList.Create; BK:=DataSet.GetBookMark; DataSet.DisableControls; DataSet.First; while not DataSet.Eof do begin str:=''; for i:=0 to DataSet.FieldCount-1 do begin if str='' then str:=str+DataSet.Fields[i].AsString else str:=str+'|'+DataSet.Fields[i].AsString; end; s.Add(str); DataSet.Next; end; DataSet.GotoBookMark(BK); DataSet.EnableControls; SaveDialog:=TSaveDialog.Create(Nil); SaveDialog.Filter:='文本文件(*.txt)|*.txt'; SaveDialog.Title:='将'+FileName+'数据表导出文本文件'; SaveDialog.FileName:=FileName+'.txt'; Result:=SaveDialog.Execute; UpdateWindow(GetActiveWindow); if Result then s.SaveToFile(SaveDialog.FileName); SaveDialog.Free; s.Free; end;
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='';FileName: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)|*.xls'; SaveDialog.Title:='将'+FileName+'数据表导出Excel文件'; SaveDialog.FileName:=FileName+'.xls'; Result:=SaveDialog.Execute; UpdateWindow(GetActiveWindow); if Result then WorkBook.SaveAs(FileName:=SaveDialog.FileName); SaveDialog.Free; end; Excel.Quit; end; Screen.Cursor:=OldCursor; end;
> 全面控制 Excel
> 首先?建 Excel ?象,使用ComObj:
> var ExcelID: Variant;
> ExcelID := CreateOleObject( 'Excel.Application' );
> 1) ?示?前窗口:
> ExcelID.Visible := True;
> 2) 更改 Excel ???:
> ExcelID.Caption := '?用程序?用 Microsoft Excel';
> 3) 添加新工作簿:
> ExcelID.WorkBooks.Add;
> 4) 打?已存在的工作簿:
> ExcelID.WorkBooks.Open( 'C:ExcelDemo.xls' );
> 5) ?置第2?工作表?活?工作表:
> ExcelID.WorkSheets[2].Activate;
> 或
> ExcelID.WorksSheets[ 'Sheet2' ].Activate;
> 6) ??元格?值:
> ExcelID.Cells[1,4].Value := '第一行第四列';
> 7) ?置指定列的?度(?位:字符??),以第一列?例:
> ExcelID.ActiveSheet.Columns[1].ColumnsWidth := 5;
> 8) ?置指定行的高度(?位:磅)(1磅=0.035厘米),以第二行?例:
> ExcelID.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
> 9) 在第8行之前插入分?符:
> ExcelID.WorkSheets[1].Rows[8].PageBreak := 1;
> 10) 在第8列之前?除分?符:
> ExcelID.ActiveSheet.Columns[4].PageBreak := 0;
> 11) 指定?框??度:
> ExcelID.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
> 1-左 2-右 3-? 4-底 5-斜( ) 6-斜( / )
> 12) 清除第一行第四列?元格公式:
> ExcelID.ActiveSheet.Cells[1,4].ClearContents;
> 13) ?置第一行字体?性:
> ExcelID.ActiveSheet.Rows[1].Font.Name := '??';
> ExcelID.ActiveSheet.Rows[1].Font.Color := clBlue;
> ExcelID.ActiveSheet.Rows[1].Font.Bold := True;
> ExcelID.ActiveSheet.Rows[1].Font.UnderLine := True;
> 14) ?行?面?置:
> a.?眉:
> ExcelID.ActiveSheet.PageSetup.CenterHeader := '?表演示';
> b.??:
> ExcelID.ActiveSheet.PageSetup.CenterFooter := '第&P?';
> c.?眉到?端?距2cm:
> ExcelID.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
> d.??到底端?距3cm:
> ExcelID.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
> e.??距2cm:
> ExcelID.ActiveSheet.PageSetup.TopMargin := 2/0.035;
> f.底?距2cm:
> ExcelID.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
> g.左?距2cm:
> ExcelID.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
> h.右?距2cm:
> ExcelID.ActiveSheet.PageSetup.RightMargin := 2/0.035;
> i.?面水平居中:
> ExcelID.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
> j.?面垂直居中:
> ExcelID.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
> k.打印?元格网?:
> ExcelID.ActiveSheet.PageSetup.PrintGridLines := True;
> 15) 拷?操作:
> a.拷?整?工作表:
> ExcelID.ActiveSheet.Used.Range.Copy;
> b.拷?指定?域:
> ExcelID.ActiveSheet.Range[ 'A1:E2' ].Copy;
> c.?A1位置?始粘?:
> ExcelID.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
> d.?文件尾部?始粘?:
> ExcelID.ActiveSheet.Range.PasteSpecial;
> 16) 插入一行或一列:
> a. ExcelID.ActiveSheet.Rows[2].Insert;
> b. ExcelID.ActiveSheet.Columns[1].Insert;
> 17) ?除一行或一列:
> a. ExcelID.ActiveSheet.Rows[2].Delete;
> b. ExcelID.ActiveSheet.Columns[1].Delete;
> 18) 打印??工作表:
> ExcelID.ActiveSheet.PrintPreview;
> 19) 打印?出工作表:
> ExcelID.ActiveSheet.PrintOut;
> 20) 工作表保存:
> if not ExcelID.ActiveWorkBook.Saved then
> ExcelID.ActiveSheet.PrintPreview;
> 21) 工作表另存?:
> ExcelID.SaveAs( 'C:ExcelDemo1.xls' );
> 22) 放?存?:
> ExcelID.ActiveWorkBook.Saved := True;
> 23) ??工作簿:
> ExcelID.WorkBooks.Close;
> 24) 退出 Excel:
> ExcelID.Quit;
>
>
var
s:TStringList;
str:string;
i:integer;
SaveDialog:TSaveDialog;
BK:TBookMark;
begin
s:=TStringList.Create;
BK:=DataSet.GetBookMark;
DataSet.DisableControls;
DataSet.First;
while not DataSet.Eof do
begin
str:='';
for i:=0 to DataSet.FieldCount-1 do
begin
if str='' then
str:=str+DataSet.Fields[i].AsString
else str:=str+'|'+DataSet.Fields[i].AsString;
end;
s.Add(str);
DataSet.Next;
end;
DataSet.GotoBookMark(BK);
DataSet.EnableControls;
SaveDialog:=TSaveDialog.Create(Nil);
SaveDialog.Filter:='文本文件(*.txt)|*.txt';
SaveDialog.Title:='将'+FileName+'数据表导出文本文件';
SaveDialog.FileName:=FileName+'.txt';
Result:=SaveDialog.Execute;
UpdateWindow(GetActiveWindow);
if Result then
s.SaveToFile(SaveDialog.FileName);
SaveDialog.Free;
s.Free;
end;
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='';FileName: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)|*.xls';
SaveDialog.Title:='将'+FileName+'数据表导出Excel文件';
SaveDialog.FileName:=FileName+'.xls';
Result:=SaveDialog.Execute;
UpdateWindow(GetActiveWindow);
if Result then
WorkBook.SaveAs(FileName:=SaveDialog.FileName);
SaveDialog.Free;
end;
Excel.Quit;
end;
Screen.Cursor:=OldCursor;
end;