你有使用dbgrid吗？有的话参考这段源码
Question/Problem/Abstract:
I've been asked a quite a few times by the users if it would be possible to see the contents of a grid in excel because they want todo
additional operations and not mess with the DB.
Answer:
The example dbgrid (DBGrid1) has a popup menu connected that allows to select the options "Send to Excel" and "Copy"
// NOTE: this method must include the COMObj, Excel97 units
// UPDATE: if you use Delphi 4 you can replace xlWBatWorkSheet with 1 (one)
//-----------------------------------------------------------
// if toExcel = false, export dbgrid contents to the Clipboard
// if toExcel = true, export dbgrid to Microsoft Excel
procedure ExportDBGrid(toExcel: Boolean);
var
bm: TBookmark;
col, row: Integer;
sline: String;
mem: TMemo;
ExcelApp: Variant;
begin
Screen.Cursor := crHourglass;
DBGrid1.DataSource.DataSet.DisableControls;
bm := DBGrid1.DataSource.DataSet.GetBookmark;
DBGrid1.DataSource.DataSet.First;
// create the Excel object
if toExcel then
begin
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.WorkBooks.Add(xlWBatWorkSheet);
ExcelApp.WorkBooks[1].WorkSheets[1].Name := 'Grid Data';
end;
// First we send the data to a memo
// works faster thando
ing it directly to Excel
mem := TMemo.Create(Self);
mem.Visible := false;
mem.Parent := MainForm;
mem.Clear;
sline := '';
// add the info for the column names
for col := 0 to DBGrid1.FieldCount-1do
sline := sline + DBGrid1.Fields[col].DisplayLabel + #9;
mem.Lines.Add(sline);
// get the data into the memo
for row := 0 to DBGrid1.DataSource.DataSet.RecordCount-1do
begin
sline := '';
for col := 0 to DBGrid1.FieldCount-1do
sline := sline + DBGrid1.Fields[col].AsString + #9;
mem.Lines.Add(sline);
DBGrid1.DataSource.DataSet.Next;
end;
// we copy the data to the clipboard
mem.SelectAll;
mem.CopyToClipboard;
// if needed, send it to Excel
// if not, we already have it in the clipboard
if toExcel then
begin
ExcelApp.Workbooks[1].WorkSheets['Grid Data'].Paste;
ExcelApp.Visible := true;
end;
FreeAndNil(ExcelApp);
DBGrid1.DataSource.DataSet.GotoBookmark(bm);
DBGrid1.DataSource.DataSet.FreeBookmark(bm);
DBGrid1.DataSource.DataSet.EnableControls;
Screen.Cursor := crDefault;
end;
well those are my $2c.
please post if you have comments or it could be better written.
thnks..
如果你使用的是ole（不好意思，这个俺不熟，如果不明白需要另外找人帮忙）可以参考这段，不过呢，这段代码实现的是导出到xls文件。
Question/Problem/Abstract:
How can I export data into MS Excel workbook?
Answer:
Anyone who worked with OLE automation, know that OLE is very slowly.
Especially if you work using late binding (which have a lot of other
advantages which early binding haven't)
A reason of bad performance is the next:
every command (method or property) which you access (no matter in read or
write mode) will be interpretated (a-la script). I mean that this command
must be found in table of available methods/properties by string name and
only if found, a physical memory address for execution will be calculated.
So if your code contain a lot of access to methods/properties, your code
will be slow.
For example, you need transfer some data from Delphi application into
xls-spreadsheet.
You can solve a task in two different ways (now I describe only late binding
for OLE automation anddo
n't describe another methods):
- to navigate thru own data and export every data in required cell
- to prepare a variant array with copied data and apply this array with data
into desired range of cells
I must say that second method will be faster than first because you'll call
less commands from OLE object and main code will be executed without OLE
automation.
Small sample: to export some StringGrid into xls-file.
var
xls, wb, Range: OLEVariant;
arrData: Variant;
begin
{create variant array where we'll copy our data}
arrData := VarArrayCreate([1, yourStringGrid.RowCount, 1,
yourStringGrid.ColCount], varVariant);
{fill array}
for i := 1 to yourStringGrid.RowCountdo
for j := 1 to yourStringGrid.ColCountdo
arrData[i, j] := yourStringGrid.Cells[j-1, i-1];
{initialize an instance of Excel}
xls := CreateOLEObject('Excel.Application');
{create workbook}
wb := xls.Workbooks.Add;
{retrieve a range where data must be placed}
Range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[1, 1],
wb.WorkSheets[1].Cells[yourStringGrid.RowCount, yourStringGrid.ColCount]];
{copy data from allocated variant array}
Range.Value := arrData;
{show Excel with our data}
xls.Visible := True;
end;
Of course, you must understand that such method is not good for large data
arrays because to allocate in memory large array is not easy task. You must
find some optimal size for data transfer (for example, to copy every 10
rows) and as result you'll receive an optimal code both for memory use and
performance.
Anyway more faster way to transfer data is not use OLE at all
You can use
my TSMExportToXLS component from SMExport suite
(http://www.scalabium.com/sme) for this task. There is implemented a direct
xls-file creation whichdo
esn't require installed MS Excel at all..
With best regards, Mike Shkolnik
EMail: mshkolnik@scalabium.com
http://www.scalabium.com
!]
]