下面是我写的一个过程将数据库的数据导到EXCEL中,你可以借鉴一下
PROCEDURE getdatefromdbintoexcel(dataset:tdataset;dbgrid:tdbgrid;title:string);
var i,row,column:integer;
CELLESRANGE,X1:OLEVARIANT;
ASCCOLS:CHAR;
begin
Try
ExcelApplication1.Connect;
Except
MessageDlg('Excel 可能没有安装!',
mtError, [mbOk], 0);
Abort;
end;
ExcelApplication1.Visible[0]:=True;
ExcelApplication1.Caption:='Excel Application';
ExcelApplication1.Workbooks.Add(Null,0);
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo
(ExcelWorkbook1.Worksheets[1] as _Worksheet);
row:=1;
ExcelWorksheet1.Cells.Item[row,1]:=title;
//一共有多少列
ASCCOLS:=CHAR(dbgrid.Columns.Count+64);
ExcelWorksheet1.Range['A1',ASCCOLS+'1'].Merge(X1);
ExcelWorksheet1.Range['A1',ASCCOLS+'1'].HorizontalAlignment:=xlCenter;
row:=2;
for i:=0 to dbgrid.Columns.Count-1 do
ExcelWorksheet1.Cells.Item[row,i+1]:=dbgrid.Columns.Title.Caption;
row:=3;
While Not(dataset.Eof) do
begin
column:=1;
for i:=1 to dataset.FieldCount do
begin
ExcelWorksheet1.Cells.Item[row,column]:=dataset.fields[i-1].AsString;
column:=column+1;
end;
dataset.Next;
row:=row+1;
end;
//设置自动适应列宽
with ExcelWorksheet1.Range['A2',ASCCOLS+INTTOSTR(2+DATASET.RECORDCOUNT)] do
begin
Columns.AutoFit;
//设置边线
Borders.LineStyle:=xlcontinuous;
Borders.weight:=xlthin;
Borders.colorindex:=xlautomatic;
end;
//设置TITLE行高 AND FONTS
ExcelWorksheet1.Range['A1','A1'].RowHeight:=28;
ExcelWorksheet1.Cells.Item[1,1].FONT.NAME:='黑体';
ExcelWorksheet1.Cells.Item[1,1].FONT.size:=20;
ExcelWorksheet1.PageSetup.RightFooter:='&9第&P页 共&N页';
ExcelWorksheet1.PageSetup.PrintTitleRows:='$1:$2';
// 设置单元格格式
for i:=1 to dataset.FieldCount do
begin
ASCCOLS:=CHAR(I+64);
if dataset.fields[i-1].DataType in [ftUnknown,ftstring] then
ExcelWorksheet1.Range[ASCCOLS+'3',ASCCOLS+INTTOSTR(2+dataset.RecordCount)].NumberFormatLocal:='000000';
// if dataset.fields[i-1].fieldtype in [ftUnknown,ftstring] then
// ExcelWorksheet1.Range[ASCCOLS+'3',ASCCOLS+INTTOSTR(2+dataset.RecordCount)].NumberFormatLocal:='000000';
end;
ExcelWorksheet1.Disconnect;
ExcelWorkbook1.Disconnect;
ExcelApplication1.Disconnect;
end;