数据集DataSet导出到Excel
来源:互联网 发布:淘宝宝贝主图尺寸多大 编辑:程序博客网 时间:2024/04/30 22:06
{ 背景:今天要把DataSet导入Excel,查询以前的贴子,一部分用Ole,速度太慢,
一部分用ADO连接到Excel文件,也很慢,一气之下把DBGrigEh的导出部分改了出来,
欢迎大家指教、改进。
功能:将数据集的数据导入Excel;
用法:With TDS2Excel.Create(TDataSet(ADOQuery1)) do
Try
Save2File(SaveDialog1.FileName, True);
finally
Free;
end;
作者:Caidao (核心代码来自Ehlib)
时间:2003-04-09
地点:汕头
}
unit UntObject;
interface
Uses
DB, Classes;
var
CXlsBof: array[0..5] of Word = ($809, 8, 0, $10, 0, 0);
CXlsEof: array[0..1] of Word = ($0A, 00);
CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
CXlsNumber: array[0..4] of Word = ($203, 14, 0, 0, 0);
CXlsRk: array[0..4] of Word = ($27E, 10, 0, 0, 0);
CXlsBlank: array[0..4] of Word = ($201, 6, 0, 0, $17);
Type
TDS2Excel = Class(TObject)
Private
FCol: word;
FRow: word;
FDataSet: TDataSet;
Stream: TStream;
FWillWriteHead: boolean;
FBookMark: TBookmark;
procedure IncColRow;
procedure WriteBlankCell;
procedure WriteFloatCell(const AValue: Double);
procedure WriteIntegerCell(const AValue: Integer);
procedure WriteStringCell(const AValue: string);
procedure WritePrefix;
procedure WriteSuffix;
procedure WriteTitle;
procedure WriteDataCell;
procedure Save2Stream(aStream: TStream);
Public
procedure Save2File(FileName: string; WillWriteHead: Boolean);
Constructor Create(aDataSet: TDataSet);
end;
implementation
uses SysUtils;
Constructor TDS2Excel.Create(aDataSet: TDataSet);
begin
inherited Create;
FDataSet := aDataSet;
end;
procedure TDS2Excel.IncColRow;
begin
if FCol = FDataSet.FieldCount - 1 then
begin
Inc(FRow);
FCol :=0;
end
else
Inc(FCol);
end;
procedure TDS2Excel.WriteBlankCell;
begin
CXlsBlank[2] := FRow;
CXlsBlank[3] := FCol;
Stream.WriteBuffer(CXlsBlank, SizeOf(CXlsBlank));
IncColRow;
end;
procedure TDS2Excel.WriteFloatCell(const AValue: Double);
begin
CXlsNumber[2] := FRow;
CXlsNumber[3] := FCol;
Stream.WriteBuffer(CXlsNumber, SizeOf(CXlsNumber));
Stream.WriteBuffer(AValue, 8);
IncColRow;
end;
procedure TDS2Excel.WriteIntegerCell(const AValue: Integer);
var
V: Integer;
begin
CXlsRk[2] := FRow;
CXlsRk[3] := FCol;
Stream.WriteBuffer(CXlsRk, SizeOf(CXlsRk));
V := (AValue shl 2) or 2;
Stream.WriteBuffer(V, 4);
IncColRow;
end;
procedure TDS2Excel.WriteStringCell(const AValue: string);
var
L: Word;
begin
L := Length(AValue);
CXlsLabel[1] := 8 + L;
CXlsLabel[2] := FRow;
CXlsLabel[3] := FCol;
CXlsLabel[5] := L;
Stream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel));
Stream.WriteBuffer(Pointer(AValue)^, L);
IncColRow;
end;
procedure TDS2Excel.WritePrefix;
begin
Stream.WriteBuffer(CXlsBof, SizeOf(CXlsBof));
end;
procedure TDS2Excel.WriteSuffix;
begin
Stream.WriteBuffer(CXlsEof, SizeOf(CXlsEof));
end;
procedure TDS2Excel.WriteTitle;
var
n: word;
begin
for n := 0 to FDataSet.FieldCount - 1 do
WriteStringCell(FDataSet.Fields[n].FieldName);
end;
procedure TDS2Excel.WriteDataCell;
var
n: word;
begin
WritePrefix;
if FWillWriteHead then WriteTitle;
FDataSet.DisableControls;
FBookMark := FDataSet.GetBookmark;
FDataSet.First;
while not FDataSet.Eof do
begin
for n := 0 to FDataSet.FieldCount - 1 do
begin
if FDataSet.Fields[n].IsNull then
WriteBlankCell
else begin
case FDataSet.Fields[n].DataType of
ftSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:
WriteIntegerCell(FDataSet.Fields[n].AsInteger);
ftFloat, ftCurrency, ftBCD:
WriteFloatCell(FDataSet.Fields[n].AsFloat);
else
WriteStringCell(FDataSet.Fields[n].AsString);
end;
end;
end;
FDataSet.Next;
end;
WriteSuffix;
if FDataSet.BookmarkValid(FBookMark) then FDataSet.GotoBookmark(FBookMark);
FDataSet.EnableControls;
end;
procedure TDS2Excel.Save2Stream(aStream: TStream);
begin
FCol := 0;
FRow := 0;
Stream := aStream;
WriteDataCell;
end;
procedure TDS2Excel.Save2File(FileName: string; WillWriteHead: Boolean);
var
aFileStream: TFileStream;
begin
FWillWriteHead := WillWriteHead;
if FileExists(FileName) then DeleteFile(FileName);
aFileStream := TFileStream.Create(FileName, fmCreate);
Try
Save2Stream(aFileStream);
Finally
aFileStream.Free;
end;
end;
end.
增加一个过程,用起来要方便一些
procedure TDS2Excel.Save2File(WillWriteHead: Boolean);
var
SaveDialog1: TSaveDialog;
begin
SaveDialog1 := TSaveDialog.Create(nil);
Try
SaveDialog1.Filter := 'Excel文档|*.xls';
SaveDialog1.InitialDir := 'D:/';
if not SaveDialog1.Execute then exit;
Save2File(SaveDialog1.FileName, WillWriteHead);
Finally
SaveDialog1.Free;
end;
end;
- 数据集DataSet导出到Excel
- 从DataSet导出数据到excel!!
- 导出DataSet的数据到Excel中
- Dataset导出到excel
- DataSet导出到Excel
- dataset 导出到 excel
- 把dataset数据保存到excel,GridView导出到excel
- [C#]网格数据导出到Excel,Excel转DataSet
- C#将数据集DataSet中的数据导出到EXCEL文件的几种方法
- C#将数据集DataSet中的数据导出到EXCEL文件的几种方法
- 将DataSet中的数据导出到Excel表中
- 导出DataSet数据到excel的多个sheet
- 将Excel或CSV文件中的数据导出到DataSet
- GridView(DataSet)导出数据到Excel文件代码C#
- ASP.NET(C#) DataSet数据导出到Excel
- 快速DataSet导出6万条数据到EXCEL!
- 将Dataset数据导出到Excel中(ASP.NET/C#)
- 将Dataset数据导出到Excel中(ASP.NET/C#)
- 公司换股吸收合并 换股价格如何确定?
- C#将文件保存到数据库中或者从数据库中读取文件
- 美刊称人脑可能成为未来黑客攻击对象
- JavaScript学习笔记SEC(1) 变量的生命周期
- 随感
- 数据集DataSet导出到Excel
- 学习"使用 Eclipse BPEL 插件开发和执行 WS-BPEL V2.0 业务流程"碰到的问题
- webBrowser使用
- 早餐吃燕麦鸡蛋可排毒养颜改善便秘
- 认识Gzip及其相关涉及
- ASP.NET中web.config中加密连接字符串 [转自9who]
- 关于批处理以及批处理常用命令的总结
- with xxx as () select xxx where rowno between 10 and 20 分页查询语句
- C# WINFORM 强制让窗体获得焦点(转)