uexcel.~pas

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

unit Uexcel;

interface
uses dbtables,Sysutils,Excel97,OleServer,ComObj;
const
       SUCCESS = 1;
       FAILED_CONNECTXLS  = -1;
       FAILED_ATTACH      = -2;
       FAILED_DISCONNECT  = -3;

  procedure CreateReport;
  procedure CreateReportpackage;
  procedure CreateReportquality;
  procedure DrawFrame;
  procedure DrawFramepackage;
  procedure DrawFramequality;
  procedure FillTable;
  procedure FillTablepackage;
  procedure FillTablequality;

//  procedure FreeReport;

  function XLS_Create :integer;
  function XLS_SetWorkSheetFontSize(wordname:string;wordsize:integer):integer;
  function XLS_SetWidth(Column :integer;Width : integer) :integer;
  function XLS_CoordinateX(pos : integer) : string;
  function XLS_FillCell(X1,Y1 :integer;Text :string;Align:integer):integer;
  function XLS_FillRange(X1,Y1,X2,Y2 : integer ; Text :string) :integer;
  function XLS_MergeEx(X1,Y1,X2,Y2 : integer; bWraped : boolean) : integer;
  function XLS_SetRangeFontSize(X1,Y1,X2,Y2 : integer;wordname:string;wordsize:integer):integer;
  function XLS_Show :integer;
var
  XlsObject : OLEVariant;

implementation
uses UnitDataModul,mainform,ustockquery,upackagequery,uqualityquery;


procedure CreateReport;
begin
  XLS_Create;
  XLS_SetWorkSheetFontSize('宋体',10);
  DrawFrame;
  FillTable;
  XLS_Show;
end;

procedure CreateReportpackage;
begin
  XLS_Create;
  XLS_SetWorkSheetFontSize('宋体',10);
  DrawFramepackage;
  FillTablepackage;
  XLS_Show;
end;

procedure CreateReportquality;
begin
  XLS_Create;
  XLS_SetWorkSheetFontSize('宋体',10);
  DrawFramequality;
  FillTablequality;
  XLS_Show;
end;

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

procedure DrawFrame;
begin
  XLS_MergeEx(1,1,15,1,True);
  XLS_FillRange(1,1,15,1,'库存查询结果表');
  XLS_SetRangeFontSize(1,1,15,1,'黑体',20);
  XLS_FillCell(1,2,'储备单位',0);
  XLS_FillCell(2,2,'库点',0);
  XLS_FillCell(3,2,'仓库',0);
  XLS_FillCell(4,2,'货位',0);
  XLS_FillCell(5,2,'货物',0);
  XLS_FillCell(6,2,'入库时间',0);
  XLS_FillCell(7,2,'收获年份',0);
  XLS_FillCell(8,2,'储备性质',0);
  XLS_FillCell(9,2,'归属',0);
  XLS_FillCell(10,2,'库存数量',0);
  XLS_FillCell(11,2,'单价',0);
  XLS_FillCell(12,2,'产地',0);
  XLS_FillCell(13,2,'等级',0);
  XLS_FillCell(14,2,'水分',0);
  XLS_FillCell(15,2,'杂度',0);
end;

procedure DrawFramepackage;
begin
  XLS_MergeEx(1,1,7,1,True);
  XLS_FillRange(1,1,7,1,'包装物查询结果表');
  XLS_SetRangeFontSize(1,1,7,1,'黑体',20);
  XLS_FillCell(1,2,'储备单位',0);
  XLS_FillCell(2,2,'库点',0);
  XLS_FillCell(3,2,'仓库',0);
  XLS_FillCell(4,2,'货位',0);
  XLS_FillCell(5,2,'新包装',0);
  XLS_FillCell(6,2,'旧包装',0);
  XLS_FillCell(7,2,'杂包装',0);
end;

procedure DrawFramequality;
begin
  XLS_MergeEx(1,1,21,1,True);
  XLS_FillRange(1,1,21,1,'质量查询结果表');
  XLS_SetRangeFontSize(1,1,21,1,'黑体',20);
  XLS_FillCell(1,2,'储备单位',0);
  XLS_FillCell(2,2,'库点',0);
  XLS_FillCell(3,2,'仓库',0);
  XLS_FillCell(4,2,'货位',0);
  XLS_FillCell(5,2,'货物类别',0);
  XLS_FillCell(6,2,'货物',0);
  XLS_FillCell(7,2,'入库时间',0);
  XLS_FillCell(8,2,'收获年份',0);
  XLS_FillCell(9,2,'储备性质',0);
  XLS_FillCell(10,2,'归属',0);
  XLS_FillCell(11,2,'库存数量',0);
  XLS_FillCell(12,2,'单价',0);
  XLS_FillCell(13,2,'产地',0);
  XLS_FillCell(14,2,'等级',0);
  XLS_FillCell(15,2,'水分',0);
  XLS_FillCell(16,2,'杂度',0);

  XLS_FillCell(17,2,'纯粮率/黄粒米',0);
  XLS_FillCell(18,2,'不完善率',0);
  XLS_FillCell(19,2,'整精米率',0);
  XLS_FillCell(20,2,'谷外糙米',0);
  XLS_FillCell(21,2,'容重,出糙率,稻谷粒, 高过氧化值',0);
end;

procedure FillTable;
var i:integer;
    fc:integer;
begin
    fc:=fstockquery.stockQuery.RecordCount ;
    fstockquery.stockQuery.First ;
    //fstockquery.stockQuery.DisableControls;
    for i:=0 to fc-1 do
     begin
       XLS_FillCell(1,i+3,fstockquery.stockQuery.fieldbyname('储备单位').AsString,-1);
       XLS_FillCell(2,i+3,fstockquery.stockQuery.fieldbyname('库点').AsString,0);
       XLS_FillCell(3,i+3,fstockquery.stockQuery.fieldbyname('仓库').AsString,0);
       XLS_FillCell(4,i+3,fstockquery.stockQuery.fieldbyname('货位').AsString,0);
       XLS_FillCell(5,i+3,fstockquery.stockQuery.fieldbyname('货物').AsString,0);
       XLS_FillCell(6,i+3,fstockquery.stockQuery.fieldbyname('入库时间').AsString,0);
       XLS_FillCell(7,i+3,fstockquery.stockQuery.fieldbyname('收获年份').AsString,0);
       XLS_FillCell(8,i+3,fstockquery.stockQuery.fieldbyname('储备性质').AsString,0);
       XLS_FillCell(9,i+3,fstockquery.stockQuery.fieldbyname('归属').AsString,0);
       XLS_FillCell(10,i+3,fstockquery.stockQuery.fieldbyname('库存数量').AsString,0);
       XLS_FillCell(11,i+3,fstockquery.stockQuery.fieldbyname('单价').AsString,0);
       XLS_FillCell(12,i+3,fstockquery.stockQuery.fieldbyname('产地').AsString,0);
       XLS_FillCell(13,i+3,fstockquery.stockQuery.fieldbyname('等级').AsString,0);
       XLS_FillCell(14,i+3,fstockquery.stockQuery.fieldbyname('水分').AsString,0);
       XLS_FillCell(15,i+3,fstockquery.stockQuery.fieldbyname('杂度').AsString,0);
//       XLS_FillCell(16,i+3,inttostr(fc),0);
       fstockquery.stockQuery.Next;
    end;
    //fstockquery.stockQuery.EnableControls;
end;

procedure FillTablepackage;
var i:integer;
    fc:integer;
begin
    fc:=fpackagequery.Query1.RecordCount ;
    fpackagequery.Query1.First ;
    //fstockquery.stockQuery.DisableControls;
    for i:=0 to fc-1 do
     begin
       XLS_FillCell(1,i+3,fpackagequery.query1.fieldbyname('储备单位').AsString,-1);
       XLS_FillCell(2,i+3,fpackagequery.query1.fieldbyname('库点').AsString,0);
       XLS_FillCell(3,i+3,fpackagequery.query1.fieldbyname('仓库').AsString,0);
       XLS_FillCell(4,i+3,fpackagequery.query1.fieldbyname('货位').AsString,0);
       XLS_FillCell(5,i+3,fpackagequery.query1.fieldbyname('新包装').AsString,0);
       XLS_FillCell(6,i+3,fpackagequery.query1.fieldbyname('旧包装').AsString,0);
       XLS_FillCell(7,i+3,fpackagequery.query1.fieldbyname('杂包装').AsString,0);
//       XLS_FillCell(16,i+3,inttostr(fc),0);
       fpackagequery.query1.Next;
    end;
    //fstockquery.stockQuery.EnableControls;
end;

procedure FillTablequality;
var i:integer;
    fc:integer;
begin
    fc:=fqualityquery.Query1.RecordCount ;
    fqualityquery.Query1.First ;
    //fstockquery.stockQuery.DisableControls;
    for i:=0 to fc-1 do
     begin
       XLS_FillCell(1,i+3,fqualityquery.Query1.fieldbyname('储备单位').AsString,-1);
       XLS_FillCell(2,i+3,fqualityquery.Query1.fieldbyname('库点').AsString,0);
       XLS_FillCell(3,i+3,fqualityquery.Query1.fieldbyname('仓库').AsString,0);
       XLS_FillCell(4,i+3,fqualityquery.Query1.fieldbyname('货位').AsString,0);
       XLS_FillCell(5,i+3,fqualityquery.Query1.fieldbyname('货物类别').AsString,0);
       XLS_FillCell(6,i+3,fqualityquery.Query1.fieldbyname('货物').AsString,0);
       XLS_FillCell(7,i+3,fqualityquery.Query1.fieldbyname('入库时间').AsString,0);
       XLS_FillCell(8,i+3,fqualityquery.Query1.fieldbyname('收获年份').AsString,0);
       XLS_FillCell(9,i+3,fqualityquery.Query1.fieldbyname('储备性质').AsString,0);
       XLS_FillCell(10,i+3,fqualityquery.Query1.fieldbyname('归属').AsString,0);
       XLS_FillCell(11,i+3,fqualityquery.Query1.fieldbyname('库存数量').AsString,0);
       XLS_FillCell(12,i+3,fqualityquery.Query1.fieldbyname('单价').AsString,0);
       XLS_FillCell(13,i+3,fqualityquery.Query1.fieldbyname('产地').AsString,0);
       XLS_FillCell(14,i+3,fqualityquery.Query1.fieldbyname('等级').AsString,0);
       XLS_FillCell(15,i+3,fqualityquery.Query1.fieldbyname('水分').AsString,0);
       XLS_FillCell(16,i+3,fqualityquery.Query1.fieldbyname('杂度').AsString,0);

       XLS_FillCell(17,i+3,fqualityquery.Query1.fieldbyname('纯粮率/黄粒米').AsString,0);
       XLS_FillCell(18,i+3,fqualityquery.Query1.fieldbyname('不完善率').AsString,0);
       XLS_FillCell(19,i+3,fqualityquery.Query1.fieldbyname('整精米率').AsString,0);
       XLS_FillCell(20,i+3,fqualityquery.Query1.fieldbyname('谷外糙米').AsString,0);
       XLS_FillCell(21,i+3,fqualityquery.Query1.fieldbyname('容重,出糙率,稻谷粒, 高过氧化值').AsString,0);
//       XLS_FillCell(16,i+3,inttostr(fc),0);
       fqualityquery.Query1.Next;
    end;
    //fstockquery.stockQuery.EnableControls;
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_SetWidth(Column :integer;Width : integer) :integer;
begin
  XlsObject.ActiveSheet.Columns[Column].ColumnWidth :=Width;
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_FillCell(X1,Y1 :integer;Text :string;Align:integer):integer;
var sp1:string;
    text1:string;   //防止'-'导入到excel时变成日期型
    i:integer;
begin
  sP1 :=XLS_CoordinateX(X1)+InttoStr(Y1);
  i:=pos('-',text);
  if i<>0 then
  text1:=copy(text,1,i-1)+'_'+copy(text,i+1,10) else
  text1:=text;
  XlsObject.ActiveSheet.Range[sP1].Value :=Text1;
  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_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_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_Show :integer;
begin
  XlsObject.Visible := True;
end;

end.