进销存管理软件版1楼: 有什麼方法可以提高導入Excel的速度？

2楼: 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. 如速达财务软件官网

3楼: 謝謝,delfier!
我還想問一下,你給的例子基本上能解決速度問題,但是關於Excel的格式控制
好像很難,客戶一般都是看到報表裡面是什麼格式在Excel就要是什麼格式

4楼: 直接使用ADO操纵Excel，将excel当然数据库。

5楼: to late_autumn_zp：
有个没测试过的办法，就是还利用delfier的方法建立这个结构，然后在没个元素中间添加#9
把这个内容贴到剪贴板里面，最后拷贝出来，在execl粘贴（通过com的方式，网上代码很多很多），但是也有个缺点就是可能不快，优点就是自己还可以是指表头等和数据集无关的信息，比如合并单元格等等

6楼: to delfier,
我不太明白你的這段代碼
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);
這些定義有什麼用處及
Stream.WriteBuffer(CXlsBlank, SizeOf(CXlsBlank))？
望高手指點

进销存管理软件版7楼: to chenybin,
我現就是用com操作的,
var
xlapp:variant;
xlapp:=createoleobject(＇＇excel.application＇＇),感覺慢

8楼: to ChaseSun,
用Ado操作像報表裡面的線條及數據的分組,匯總等等,
數據集難建

9楼: 高手們,幫幫忙,還有沒有好的方法

10楼: 千万不要用createoleobject(＇＇excel.application＇＇)这样不但慢，而且一旦程序意外中断这个object会驻留系统内存你的excel文件就有可能会暂时打不开，我建议你用f1book的read的功能把excel文件整个读到程序里头来再做处理。

11楼: 多人接受答案了。