uxls.pas

是分布式粮库程序,是采用Delphi实现的

unit uXls;

interface
uses Excel97,OleServer;

const  SUCCESS = 1;
       FAILED_CONNECTXLS  = -1;
       FAILED_ATTACH      = -2;
       FAILED_DISCONNECT  = -3;

       {XLS_HOR_Center     = xlHAlignCenter;
       XLS_HOR_LEFT       = xlHAlignLeft;
       XLS_HOR_RIGHT      = xlHAlignRight;

       XLS_VER_TOP        = xlVAlignTop;
       XLS_VER_CENTER     = xlVAlignCenter;
       XLS_VER_BOTTOM     = xlVAlignBottom;}

var    XlsObject : OLEVariant;
       //XlsBook   : OLEVariant;
       //XLSSheet  : OLEVariant;

{ 创建Excel OLE对象 }
function XLS_Create     : integer;

function XLS_Open(fName : string)     : integer;

{ 关闭Excel OLE对话 }
function XLS_Close      : integer;

{ 显示Excel界面 }
function XLS_Show       : integer;

{ 对Excel OLE对象的格子画边框 }
function XLS_DrawBorder(X1,Y1,X2,Y2: integer) : integer;

{ 向Excel OLE对象的指定格子写入字符串 }
function XLS_FillCell(X1,Y1 : integer ; Text :string;Align:integer) :integer;

function XLS_ReadCell(X1,Y1 : integer) :string;

{ 向Excel OLE对象的格子范围写入字符串 }
function XLS_FillRange(X1,Y1,X2,Y2 : integer ; Text :string) :integer;

{ 合并Excel OLE对象的指定范围格子 }
function XLS_Merge(X1,Y1,X2,Y2 : integer) : integer;

{ 合并Excel OLE对象的指定范围格子，并指定文字是否自动换行 }
function XLS_MergeEx(X1,Y1,X2,Y2 : integer; bWraped : boolean) : integer;

{ 把Excel OLE对象保存为.xls文件 }
function XLS_SaveAs(PathName : string) : integer;

{ 设置列宽度 }
function XLS_SetWidth(Column : integer; Width : integer) :integer;

{ 设置行高度 }
function XLS_SetHeight(Row :integer;Height : integer) :integer;

function XLS_Line(X1,Y1,X2,Y2 : integer):integer;

function XLS_BorderLine(X1,Y1,X2,Y2 : integer):integer;

function XLS_SetSize(X1,Y1,X2,Y2 : integer;wordsize : integer) : integer;

function XLS_SetWorkSheetFontSize(wordname:string;wordsize:integer):integer;

function XLS_SetRangeFontSize(X1,Y1,X2,Y2 : integer;wordname:string;wordsize:integer):integer;

function XLS_CoordinateX(pos : integer) : string;

function XLS_AddPageBreak(Row : integer; Col : integer) : integer;


implementation
uses Sysutils,ComObj;

function XLS_Create : integer;
begin
  Result := SUCCESS;
  Try
    XlsObject := CreateOLEObject('Excel.Application');
  Except
    Result :=FAILED_CONNECTXLS;
    Exit;
  End;
  XlsObject.WorkBooks.Add;
end;

function XLS_Open(fName : string)     : integer;
begin
  Result := SUCCESS;
  Try
    XlsObject := CreateOLEObject('Excel.Application');
  Except
    Result :=FAILED_CONNECTXLS;
    Exit;
  End;
  XlsObject.WorkBooks.Open(fName);
end;

function XLS_Close:Integer;
begin
  Result := SUCCESS;
  Try
    XlsObject.DisplayAlerts:=False;
    XlsObject.Quit;
  Except
    Result := FAILED_DISCONNECT;
    Exit;
  End;
end;

function XLS_Show       : integer;
begin
  XlsObject.Visible := True;
end;

function XLS_DrawBorder(X1,Y1,X2,Y2: integer) : integer;
var sP1,sP2 :string;
begin
  sP1 :=XLS_CoordinateX(X1)+InttoStr(Y1);
  sP2 :=XLS_CoordinateX(X2)+InttoStr(Y2);
  XlsObject.ActiveSheet.Range[sP1+':'+sP2].BorderAround(1,xlThin,0);
end;

function XLS_FillCell(X1,Y1 :integer;Text :string;Align:integer):integer;
var sp1:string;
begin
  sP1 :=XLS_CoordinateX(X1)+InttoStr(Y1);
  XlsObject.ActiveSheet.Range[sP1].Value :=Text;
  case Align of
  0:
  begin
    XlsObject.ActiveSheet.Range[sP1].HorizontalAlignment :=xlHAlignCenter ;
    XlsObject.ActiveSheet.Range[sP1].VerticalAlignment   :=xlVAlignCenter ;
  end;
  -1:
  begin
    XlsObject.ActiveSheet.Range[sP1].HorizontalAlignment :=xlHAlignLeft ;
    XlsObject.ActiveSheet.Range[sP1].VerticalAlignment   :=xlVAlignCenter ;
  end;
  1:
  begin
    XlsObject.ActiveSheet.Range[sP1].HorizontalAlignment :=xlHAlignRight ;
    XlsObject.ActiveSheet.Range[sP1].VerticalAlignment   :=xlVAlignCenter ;
  end;
  end;
end;

function XLS_FillRange(X1,Y1,X2,Y2 : integer ; Text :string) :integer;
var sp1,sP2,s:string;
begin
  sP1 :=XLS_CoordinateX(X1)+InttoStr(Y1);
  sP2 :=XLS_CoordinateX(X2)+InttoStr(Y2);
  s :=sP1+':'+sP2;
  XlsObject.ActiveSheet.Range[s].Value :=Text;
  XlsObject.ActiveSheet.Range[s].HorizontalAlignment :=xlHAlignCenter ;
  XlsObject.ActiveSheet.Range[s].VerticalAlignment   :=xlVAlignCenter ;
end;

function XLS_Merge(X1,Y1,X2,Y2 : integer) : integer;
var sP1,sP2 :string;
begin
  sP1 :=XLS_CoordinateX(X1)+InttoStr(Y1);
  sP2 :=XLS_CoordinateX(X2)+InttoStr(Y2);
  XlsObject.ActiveSheet.Range[sP1+':'+sP2].Merge;
end;

function XLS_MergeEx(X1,Y1,X2,Y2 : integer; bWraped : boolean) : integer;
var sP1,sP2 :string;
begin
  sP1 :=XLS_CoordinateX(X1)+InttoStr(Y1);
  sP2 :=XLS_CoordinateX(X2)+InttoStr(Y2);
  XlsObject.ActiveSheet.Range[sP1+':'+sP2].Merge;
  XlsObject.ActiveSheet.Range[sP1+':'+sP2].WrapText := bWraped;
end;


function XLS_SaveAs(PathName : string) : integer;
begin
  XlsObject.ActiveWorkbook.SaveAs(PathName);
end;

function XLS_SetWidth(Column :integer;Width : integer) :integer;
begin
  XlsObject.ActiveSheet.Columns[Column].ColumnWidth :=Width;
end;

function XLS_SetHeight(Row :integer;Height : integer) :integer;
begin
  XlsObject.ActiveSheet.Rows[Row].RowHeight :=Height;
end;

function XLS_Line(X1,Y1,X2,Y2 : integer):integer;
var BeginX,BeginY,EndX,EndY:Single;
begin
  BeginX:=(X1-1)*(XlsObject.ActiveSheet.Range['A1'].Width);
  BeginY:=(Y1-1)*(XlsObject.ActiveSheet.Range['A1'].Height);
  EndX:=X2*(XlsObject.ActiveSheet.Range['A1'].Width);
  EndY:=Y2*(XlsObject.ActiveSheet.Range['A1'].Height);
  XlsObject.ActiveSheet.Shapes.AddLine(BeginX,BeginY,EndX,EndY);
end;

function XLS_SetSize(X1,Y1,X2,Y2 : integer;wordsize : integer):integer;
var sp1,sp2:string;
begin
  sP1 :=XLS_CoordinateX(X1)+InttoStr(Y1);
  sP2 :=XLS_CoordinateX(X2)+InttoStr(Y2);
  XlsObject.ActiveSheet.Range[sP1+':'+sP2].Font.Size:=wordsize;
end;

function XLS_SetWorkSheetFontSize(wordname:string;wordsize:integer):integer;
begin
  XlsObject.ActiveSheet.Cells.Font.Name:=wordname;
  XlsObject.ActiveSheet.Cells.Font.Size:=wordsize;
end;

function XLS_BorderLine(X1,Y1,X2,Y2 : integer):integer;
var sp1,sp2:string;
begin
  sP1 :=XLS_CoordinateX(X1)+InttoStr(Y1);
  sP2 :=XLS_CoordinateX(X2)+InttoStr(Y2);
  XlsObject.ActiveSheet.Range[sP1+':'+sP2].Borders.LineStyle:=XLContinuous;
end;

function XLS_CoordinateX(pos : integer) : string;
begin
        if pos > 26 then
        begin
                Result := Chr((pos div 26)+Ord('A')-1)+Chr((pos mod 26)+Ord('A')-1)
        end
        else
                Result := Chr(pos+Ord('A')-1)
end;

function XLS_ReadCell(X1,Y1 : integer) :string;
var sP1: string;
begin
  sP1 :=XLS_CoordinateX(X1)+InttoStr(Y1);
  Result := XlsObject.ActiveSheet.Range[sP1];
end;

function XLS_SetRangeFontSize(X1,Y1,X2,Y2 : integer;wordname:string;wordsize:integer):integer;
var sP1,sP2 : string;
begin
  sP1 :=XLS_CoordinateX(X1)+InttoStr(Y1);
  sP2 :=XLS_CoordinateX(X2)+InttoStr(Y2);

  XlsObject.ActiveSheet.Range[sP1+':'+sP2].Font.Size := wordsize;
  XlsObject.ActiveSheet.Range[sP1+':'+sP2].Font.Name := wordname;
end;

function XLS_AddPageBreak(Row : integer; Col : integer) : integer;
var sp1,sp2 : string;
begin
  sP1 :=XLS_CoordinateX(1)+InttoStr(Row);
  sP2 :=XLS_CoordinateX(Col)+InttoStr(Row);
  XlsObject.ActiveSheet.Range[sP1+':'+sP2].PageBreak := 1;
end;


end.