unitexcel.pas

本人原创（delphi程序设计提高与应用）源码。《简单的数据库工作平台》其目的是教学生如何用delphi连接数据库、打开数据表

unit UnitExcel;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, OleServer, Excelxp, Excel2000;

type
  TFormexcel = class(TForm)
    ExcelApplication1: TExcelApplication;
    ExcelWorkbook1: TExcelWorkbook;
    ExcelWorksheet1: TExcelWorksheet;
  private
    { Private declarations }
  public
    { Public declarations }
    procedure Excelopen;
    procedure NewXls;
    procedure WText(SText:OleVariant;X,Y:Integer);
    procedure XLSSaveAs(XLSName:OLEVariant);
    procedure ExcelClose;
    procedure Format(format:string;X1,Y1,X2,Y2:Integer);
    procedure ColumnWidth(Col:Integer;ColumnWidth:Real);
    procedure HBCells(X1,Y1,X2,Y2:Integer);
    procedure AutoCol;
    procedure CellsCenter(X1,Y1,X2,Y2:Integer);
    procedure Font(X1,Y1,X2,Y2:Integer;Size,Bold:Integer;Name:STRING);
    procedure LineStyle(X1,Y1,X2,Y2:Integer);
    procedure NewSheet(WorksheetName:String);
    function ConnectToSheet(n:integer):String;
    function FondSheetName(name:string):Boolean;
    procedure OpenXls(filename:string);
  end;

var
  Formexcel: TFormexcel;

implementation

{$R *.dfm}
procedure TFormexcel.Excelopen;
//建立Excel连接
begin
  try
     ExcelApplication1.Connect;
  except
     application.MessageBox('请检查您的电脑上是否安装了Excel2000','警告',mb_ok);
     Abort;
  end;


end;
procedure TFormexcel.NewXls;
//创建Excel文件
begin
    Application.MessageBox('即将开始后台打印，请关闭其它Excel2000应用程序！','提示',mb_ok);
 with ExcelApplication1 do
    begin
      Visible[1] := true;     //将Excel程序置为可见
      ExcelApplication1.WindowState[1]:=xlMinimized;//Excel窗口最小化
      WorkBooks.add(EmptyParam, 0);   //添加一个新的工作簿
      ExcelWorkBook1.ConnectTo(WorkBooks[1]); //将工作簿和ExcelWorkBook组件连接
    end;
end;

procedure TFormexcel.OpenXls(filename:string);
//打开Excel文件
begin
  ExcelApplication1.Workbooks.Open(filename,EmptyParam,
  EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
  EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
  ExcelApplication1.Visible[1]:=true; //将Excel程序置为可见
  ExcelWorkBook1.ConnectTo(ExcelApplication1.WorkBooks[1]);
end;

procedure TFormexcel.NewSheet(WorksheetName:String);
//创建工作表（Sheet）
var
Temp_Worksheet: _WorkSheet;
begin
Temp_Worksheet:=ExcelWorkbook1.
WorkSheets.Add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _WorkSheet;
ExcelWorkSheet1.ConnectTo(Temp_WorkSheet);
ExcelWorksheet1.Name:=WorksheetName;   //设置工作表的名字
End;

Function TFormexcel.FondSheetName(name:string):Boolean;
//查找工作表（Sheet），如果找到名字为name的工作表，锁定该工作表返回True
//否则返回False
var
n,i:Integer;
begin
  result:=False;
  n:=ExcelApplication1.Worksheets.Count;
for i:=1 to n do begin
   if ConnectToSheet(i)=name then begin
      result:=True;
      Break;
   end;
end;
end; 

Function TFormexcel.ConnectToSheet(n:integer):String;
//锁定第n个工作表（Sheet），返回工作表名
begin
ExcelWorksheet1.ConnectTo(ExcelWorkBook1.Sheets[n] as _worksheet);
result:=ExcelWorkSheet1.Name;
End;

procedure TFormexcel.WText(SText:OleVariant;X,Y:Integer);
//将数据填入指定的单元格（X表示行，Y表示列）
begin
  Excelworksheet1.Cells.Item[X,Y]:=SText;
end;

procedure TFormexcel.Format(format:string;X1,Y1,X2,Y2:Integer);
//设置区域数字、分类
BEGIN
  with  ExcelWorksheet1 do
  Range[Cells.Item[X1,Y1],Cells.Item[X2,Y2]].NumberFormatLocal:=format;
END;

procedure TFormexcel.XLSSaveAs(XLSName:OLEVariant);
//保存excel文档
BEGIN
  ExcelWorkBook1.Close(true,XLSName);    //保存文档并关闭工作簿，文档名为XLSName
END;

procedure TFormexcel.ExcelClose;
//退出excel程序
begin
  ExcelApplication1.Quit;        //退出excel
  Excelworksheet1.Disconnect;    //切断工作表的连接
  ExcelWorkBook1.Disconnect;     //切断工作簿连接
  ExcelApplication1.Disconnect;  //切断excel连接
end;

procedure TFormexcel.ColumnWidth(Col:Integer;ColumnWidth:Real);
//设置列宽
begin
Excelworksheet1.Cells.Item[1,Col].ColumnWidth:=ColumnWidth;
end;

procedure TFormexcel.HBCells(X1,Y1,X2,Y2:Integer);
//合并单元格
begin
with  ExcelWorksheet1 do
Range[Cells.Item[X1,Y1],Cells.Item[X2,Y2]].Merge(true);;
end;

procedure TFormexcel.AutoCol;
//自动选择列宽
begin
Excelworksheet1.Columns.EntireColumn.AutoFit;
end;

procedure TFormexcel.CellsCenter(X1,Y1,X2,Y2:Integer);
//居中对齐
begin
with  ExcelWorksheet1 do
Range[Cells.Item[X1,Y1],Cells.Item[X2,Y2]].HorizontalAlignment:=xlcenter;
end;

procedure TFormexcel.Font(X1,Y1,X2,Y2:Integer;Size,Bold:Integer;Name:STRING);
//设置字体
begin
with  ExcelWorksheet1 do
  begin
     Range[Cells.Item[X1,Y1],Cells.Item[X2,Y2]].Font.Size:=18;  //字号
     Range[Cells.Item[X1,Y1],Cells.Item[X2,Y2]].Font.Name:='宋体'; //字体
     Range[Cells.Item[X1,Y1],Cells.Item[X2,Y2]].Font.Bold:=1;   //是否加粗
  end;
end;

procedure TFormexcel.LineStyle(X1,Y1,X2,Y2:Integer);
//画表格
begin
with  ExcelWorksheet1 do
    Range[Cells.Item[X1,Y1],Cells.Item[X2,Y2]].Borders.LineStyle:=xlContinuous;
end;
end.