u_operateexcel.pas

Micorsoft Excel 操作单元

procedure TExcel.rowhigh(H:Integer);
var
  rowrange:variant;
begin
  rowrange:=xlapp.Activesheet.rows;
  rowrange.rows.rowheight:=H;
end;

procedure TExcel.allcellsalignment(V_Value,H_Value:Integer);
begin
  xlapp.cells.verticalalignment:=V_Value;
  xlapp.cells.horizontalalignment:=H_Value;
end;

procedure TExcel.ColumnsAlignment(Col,V,H:Integer);
var
  columnrange:variant;
begin
  columnrange:=xlapp.Activesheet.columns;
  columnrange.columns[Col].verticalalignment:=V;
  columnrange.columns[Col].horizontalalignment:=H;
end;

procedure TExcel.RowsAlignment(Row,V,H:integer);
var
  rowrange:variant;
begin
  rowrange:=xlapp.Activesheet.rows;
  rowrange.rows[Row].verticalalignment:=V;
  rowrange.rows[Row].horizontalalignment:=H;
end;

procedure TExcel.EveryCellsAlignment(Row1,Col1,Row2,Col2,V_Value,H_Value:integer);
begin
  xlapp.range[xlapp.Activesheet.cells[Row1,Col1],xlapp.Activesheet.cells[Row2,Col2]].verticalalignment:=V_Value;
  xlapp.range[xlapp.Activesheet.cells[Row1,Col1],xlapp.Activesheet.cells[Row2,Col2]].horizontalalignment:=H_Value;
end;

procedure TExcel.borderstyle(Row1,Col1,Row2,Col2:integer);
begin
  xlapp.range[xlapp.Activesheet.cells[Row1,Col1],xlapp.Activesheet.cells[Row2,Col2]].borders.linestyle:=1;
end;

procedure TExcel.CellValue(Row,Col:integer;StrValue:string);
begin
  xlapp.activesheet.cells[Row,Col].value:=StrValue;
end;

procedure TExcel.smallpagesetup;
begin
  xlapp.activesheet.pagesteup.printarea:='';
  xlapp.activesheet.pagesetup.centerhorizontally:=true;
  xlapp.activesheet.pagesetup.centervertically:=true;
  xlapp.activesheet.pagesetup.orientation:=2;
  xlapp.activesheet.pagesetup.fittopageswidth:=1;
  xlapp.activesheet.pagesetup.fittopagestall:=1;
  xlapp.activesheet.pagesetup.papersize:=45;
end;

procedure TExcel.handlerange;
var
  range:variant;
begin
  range:=xlapp.Activesheet.range['c1:f25'];
  range.formula:='=rand()';
  range.columns.interior.colorindex:=3;
  //range.borders.linestyle:=xlcontinuous;
end;

procedure TExcel.excelvisible(Visible:Boolean);
begin
  xlapp.application.visible:=Visible;
  xlapp.activesheet.visible:=Visible;
end;

procedure TExcel.SheetPrintPreview;
begin
  xlapp.Activesheet.printpreview;
end;

procedure TExcel.SheetPrint;
begin
  xlapp.activesheet.printout;
end;

procedure TExcel.CloseExcel;
begin
  try
    xlapp.application.Quit;
    xlapp.displayalerts:=true;
  except
  end;
end;

function TExcel.GetCellValue(Row,Col:integer):string;
begin
  Result:=xlapp.Activesheet.cells[Row,Col].value;
end;

procedure TExcel.FormulaCell(row1,col1,row2,col2,row,col:integer;tag:string);
var
  strFunction:string;
begin
  if (row1=row2) and (col1=col2) then
    strFunction:='='+tag+'('+chr(64+col1)+inttostr(row1)+')'
  else
    strFunction:='='+tag+'('+chr(64+col1)+inttostr(row1)+','+chr(64+col1)+inttostr(row1)+')';
  xlapp.Activesheet.cells[Row,Col].Formula:=strFunction;
  xlapp.Activesheet.cells[Row,Col].select;
end;

Function TExcel.Borderno(row,col:integer):boolean;
begin
  result:=false;
  if  xlapp.Activesheet.cells[Row+1,Col].LineStyle =0 then
    result:=true;
end;

Function TExcel.GetCellLeftstyle(Row1,Col1,Row2,Col2:integer):integer;
begin
  result:=xlapp.Range[xlapp.Activesheet.cells[Row1,Col2],xlapp.Activesheet.Cells[Row2,Col2]].borders.item[7].LineStyle;
end;

Function TExcel.GetCellTopStyle(Row1,Col1,Row2,Col2:integer):integer;
begin
  result:=xlapp.Range[xlapp.Activesheet.cells[Row1,Col2],xlapp.Activesheet.Cells[Row2,Col2]].borders.item[8].LineStyle;
end;

Function TExcel.GetCellRightStyle(Row1,Col1,Row2,Col2:integer):integer;
begin
  result:=xlapp.Range[xlapp.Activesheet.cells[Row1,Col2],xlapp.Activesheet.Cells[Row2,Col2]].borders.item[10].LineStyle;
end;

Function TExcel.GetCellBottomStyle(Row1,Col1,Row2,Col2:integer):integer;
begin
  result:=xlapp.Range[xlapp.Activesheet.cells[Row1,Col2],xlapp.Activesheet.Cells[Row2,Col2]].borders.item[9].LineStyle;
end;

Procedure TExcel.SetCellTopStyle(Row1,Col1,Row2,Col2,value:integer);
begin
  Xlapp.Range[Xlapp.ActiveSheet.Cells[Row1,Col1],Xlapp.ActiveSheet.Cells[Row2,Col2]].borders.item[8].LineStyle:=value;
end;

Procedure TExcel.SetCellBottomStyle(Row1,Col1,Row2,Col2,value:integer);
begin
  Xlapp.Range[Xlapp.ActiveSheet.Cells[Row1,Col1],Xlapp.ActiveSheet.Cells[Row2,Col2]].borders.item[9].LineStyle:=value;
end;

Procedure TExcel.SetCellLeftStyle(Row1,Col1,Row2,Col2,value:integer);
begin
  Xlapp.Range[Xlapp.ActiveSheet.Cells[Row1,Col1],Xlapp.ActiveSheet.Cells[Row2,Col2]].borders.item[7].LineStyle:=value;
end;

Procedure TExcel.SetCellRightStyle(Row1,Col1,Row2,Col2,value:integer);
begin
  Xlapp.Range[Xlapp.ActiveSheet.Cells[Row1,Col1],Xlapp.ActiveSheet.Cells[Row2,Col2]].borders.item[10].LineStyle:=value;
end;

Procedure TExcel.SheetPrintSome(Page1,Page2:integer);
begin
  xlapp.activesheet.printout(page1,page2);
end;

Function TExcel.NowOpenFile:string;
begin
  result:=Xlapp.Activeworkbook.Fullname;
end;

function TExcel.InputDataToExcel(ServerName:string;DataBaseName:string;UserName:string;Pwd:string;QueryStr:string):boolean;
{参数说明
 ServerName:数据库服务器名称； 如：Hello
 DataBaseName:数据库名称；     如：Telcount_test
 QueryStr:查询SQL 语句；      如：Select * from yhdang
}
var
  Con:variant;
  Constr:string;                                                                       //    WSID=MYPC;
begin
  try
    result:=true;
    Constr:='ODBC;DRIVER=SQL Server;SERVER='+ServerName+
            ';UID='+UserName+';PWD='+Pwd+';APP=Microsoft? Query;DATABASE='+DatabaseName;
    Xlapp.visible:=true;
    Xlapp.Caption:='数据导出文件';
    Xlapp.WorkBooks.add;
    con:=Xlapp.Activesheet.QueryTables.add(Constr, Xlapp.Range[Xlapp.Activesheet.cells[1,1],Xlapp.Activesheet.cells[1,1]]);
    con.commandtext:=Querystr;
    Xlapp.Activesheet.Name:='SQL数据批导入';
    con.FieldNames:=True;
    con.RowNumbers:= False;
    con.FillAdjacentFormulas := False;
    con.PreserveFormatting := True;
    con.RefreshOnFileOpen := False;
    con.BackgroundQuery := True;
    con.RefreshStyle :=2;
    con.SavePassword := True;
    con.SaveData := True;
    con.AdjustColumnWidth := True;
    con.RefreshPeriod := 0;
    con.PreserveColumnInfo := True;
    con.Refresh(False);
  except
    result:=false;
    showmessage('系统未安装Office Microsoft Query 功能');
  end;
end;

function TExcel.InputQueryData(Qry:TQuery):boolean;
var
  i,j:integer;
begin
  if not Qry.Active then Qry.Open;
  for i:=0 to  Qry.RecordCount do  //控制数据源的行数
  begin
    for J:=0 to  Qry.FieldCount-1 do   //控制数据源的列数
    begin
      if i =0   then
        CellValue(i+1,j+1,Qry.Fields[j].FieldName)
      else
        CellValue(i+1,j+1,trim(Qry.Fields[j].Asstring));
    end;
    Qry.next;
  end;
end;

end.