关于生成有跨行与跨列的excel表,如何提高速度?现在利用的是OLE,速度慢。如果提高速度,可以直接写文件,
但是我的表是有跨行与跨列的,
所以用这种方式不行。大家有啥方法?盼讨论。
解决方案 »
- 如何把RichEdit对齐方式做得象Word一样?!
- 什么是.udl文件?
- 还是rtf转txt文本问题,请各位多多帮忙!
- 关于imagelist的图标大小如何调整????????????????????????????
- 如何在dbgrid中修改了一条记录而不立即保存到数据库中?急,在线等!
- 如何将一个数据库中的某个表中的数据拷贝到另一个数据库中的某个表?
- 问一下,我按钮上放个图标怎么放?比如像上面的 搜索 按钮旁边放个ico的图标?还有哪里可以找到一些比较全面的图标?谢谢了
- 郁闷~~~~没分也散~~~~~~
- 怎样把子窗口提到最前?
- 关于locate的问题?
- 数据库打包的问题,谢谢!
- 用delphi能不能实现将一图片设置为屏幕的背景图片
看来是很少有人搞这块。我只知道有一种办法,
那就是直接生成excel文件。
可是excel文件格式不好控制呀,
从网上找到一个,很长,郁闷。
后来终于找到一个极快的方法,等下贴给你。
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: TBook;
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);
//procedure Save2File2(WillWriteHead: Boolean);
Constructor Create(aDataSet: TDataSet);
end;implementationuses 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.GetBook;
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.BookValid(FBookMark) then FDataSet.GotoBook(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;{
procedure TDS2Excel.Save2File2(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;
}end.
Try
Save2File(SaveDialog1.FileName, True);
finally
Free;
end;
如果是,那么使用 ole 操作excel,可以生成excel之后再createobject打开,进行进一步的处理。
xlQuery.FieldNames := false; //要不要字段名
xlQuery.Refresh;xlQuery,Sheet都声明成OleVariant类型
数量量不大,只几行数据,
用OLE操作,速度很慢呀。好4、5秒。如果数据量再大一点,更慢了。