doexcel.~pas

导出数据

unit doexcel;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, excel2000, OleServer,ComObj, DB, ADODB;

type
  TForm1 = class(TForm)
    Button1: TButton;
    ExcelOLEObject1: TExcelOLEObject;
    Workbook: TExcelWorkbook;
    ExcelApp: TExcelApplication;
    Button2: TButton;
    Lbl_Title: TLabel;
    AdoQy_GongGong: TADOQuery;
    Button3: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
   // procedure FormCreate(Sender: TObject);
  //  procedure importData;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

function createExcel:variant;
var
v:variant;
sheet:variant;
begin
v:=createoleobject('Excel.Application');//创建OLE对象
v.visible:=true;
v.workbooks.add(-4167); //添加工作表
v.workbooks[1].sheets[1].name:='test';
sheet:=v.workbooks[1].sheets['test'];
//return v;
end;

procedure importData;
var
I,j,maxcolumn,maxrow:integer;
v:variant;
begin
v:=createExcel; //创建Excel文件test
for I:=0 to maxcolumn do
begin
for j:=0 to maxrow do
v.workbooks[1].sheets[1].cells[I,j]:=I*j; //导入数据
end;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
SaveDialog : TSaveDialog ;
filename:string;
ExcelApp: TExcelApplication;
wkBook: _WorkBook;
wkSheet: _WorkSheet;
LCID: Integer;
begin
ExcelApp := TExcelApplication.Create(nil);
ExcelApp.Visible[0]:=true; //是否可视
ExcelApp.Connect;
LCID := GetUserDefaultLCID();
wkBook := ExcelApp.WorkBooks.Add(EmptyParam,LCID);
wkSheet := wkBook.Sheets[1] as _WorkSheet;
wkSheet.Activate(LCID);
with wkSheet.Range[wkSheet.Cells.Item[1, 3], wkSheet.Cells.Item[3, 3]] do
begin
ColumnWidth := 30; // 宽度
MergeCells := True; //合并单元格
WrapText := True; //自动换行
HorizontalAlignment := xlCenter; //对齐方式 xlleft,xlright
VerticalAlignment := xlCenter; //对齐方式 xltop,xlbottom
font.Bold:=true; //字体加重
font.Italic := True ; //斜体
font.Colorindex:=5; // 颜色
font.Name:='宋体'; //字体
font.FontStyle := '常规' ;
font.Size:=33; //大小
end;
wkSheet.Cells.Item[1, 3].value:='显示的字符串'; //在该单元格显示的字符串
SaveDialog := TSaveDialog.Create(nil);
try
SaveDialog.Title := '输入文件名';
SaveDialog.Filter := 'Microsoft Excel 工作薄|*.xls';
if SaveDialog.Execute then
FileName := SaveDialog.FileName
else
Exit;
finally
SaveDialog.Free;
end;
wkBook.Close(True, FileName, EmptyParam, LCID);//关闭并保存
ExcelApp.Quit;
ExcelApp.Disconnect;
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
importData;
end;

{procedure TForm1.FormCreate(Sender: TObject);
var
i,j,F:integer;
s:string;
ExcelApp,Workbook:variant;
begin
try
ExcelApp:=CreateOleObject('Excel.Application');
Workbook:=CreateOleobject('Excel.Sheet');
except
Application.MessageBox('无法运行Excel,请确认是否已安装 Office 软件！','提示',Mb_OK+MB_IconWarning);
end;
ExcelApp.Visible := true;
//增加一个工作表
Workbook:=ExcelApp.workBooks.Add;
//单元格合并
Workbook.worksheets[1].range['A1:K1'].Merge(True);
//单元格居中
Workbook.worksheets[1].range['A1:K2'].HorizontalAlignment := $FFFFEFF4;
//报表头
Workbook.WorkSheets[1].Cells[1,1].Value :=Lbl_Title.Caption;
//表头
J := 2;
with AdoQy_GongGong do
begin
for I:=1 to Fields.Count do
Workbook.worksheets[1].cells[J,I].value:=Fields[I-1].DisplayName;
// Workbook.worksheets[1].Range['A1:J2'].Font.Color := clBlue;
//字体
Workbook.worksheets[1].Range['A1:K1'].Font.Name := '隶书';
//字号
Workbook.worksheets[1].Range['A1:K1'].Font.Size := 18;
Inc(J);
open;
First;
while not eof do
begin
for I:=1 to fields.Count do
begin
Workbook.worksheets[1].cells.Item[j,i]:=Fields[i-1].AsString;
end;
Next;
Inc(J);
end;
J:=FieldCount;
s := 'A3:I'+ IntToStr(j);
s := 'A1:L'+ IntToStr(j+2);
Try
//For F:=0 to J-1 do
//Workbook.worksheets[1].Columns[F+1].ColumnWidth := DBGD_GongGongWS.Columns[F].Width/5;
Except
end;
Workbook.worksheets[1].Rows[1].RowHeight := 30;
//报表头居中
// Workbook.worksheets[1].Rows[1].VerticalAlignment := $FFFFEFF4;
//设置数据行字体
// s:=‘A1:L‘+inttostr(UdQuery1.RecordCount-2);
Workbook.worksheets[1].Range[s].Font.Name := '仿宋';
//划表线
case J of
9:s := 'A2:I'+ IntToStr(AdoQY_gongGong.recordcount+2);
10:s := 'A2:J'+ IntToStr(AdoQY_gongGong.recordcount+2);
11:s :='A2:K'+ IntToStr(AdoQY_gongGong.recordcount+2);
15:s := 'A2:O'+ IntToStr(AdoQY_gongGong.recordcount+2);
End;
Workbook.worksheets[1].Range[s].Borders.LineStyle := 1;
//设置页面
// Workbook.WorkSheets[1].PageSetup.CenterHorizontally := true;
// Workbook.worksheets[1].pagesetup.PrintTitleRows := ‘A1‘;
//Workbook.WorkSheets[1].PageSetup.PaperSize := $9;
end;
end;   }




procedure TForm1.Button3Click(Sender: TObject);
var
  arr : OleVariant;
 // ExcelApp: TExcelApplication;
  shs : sheets;
begin
  arr := varArrayCreate([0,1],varVariant);
  arr[0] := 'Sheet1';
  arr[1] := 'Sheet2';
  ExcelApp.Connect;
  ExcelApp.Visible[0] := True;
  ExcelApp.AlertBeforeOverwriting[0] := False;
  Workbook.ConnectTo(ExcelApp.Workbooks.Add(NULL, 0));
  Workbook.Sheets.Item[arr].QueryInterface(IID_Sheets,shs);
 //Workbook.ConnectTo(Workbook.Sheets[1]   as   _WorkSheet);
  shs.Select(False,0);
    //ExcelApp.Disconnect;
   //ExcelApp.Quit;
end;


end.