mystringgrid.pas

重新改写的stringgrid,可以实现把stringgrid的内容填写到数据库中

unit MystringGrid;

interface

uses
  SysUtils, Classes, Controls, Grids,db,adodb,forms;

type
  myinteger = 0..255;
  ColInteger = set of myinteger;
  TMystringGrid = class(TStringGrid)
  private
    { Private declarations }
  protected
    { Protected declarations }
  public
    { Public declarations }
    procedure clear(icol,iRow:integer);
      //清空.
    procedure Delete(iCol,iRow:integer);
      //删除某一行.
    procedure AddRow();
      //添加 行
    procedure AddCol();
       //添加列
    procedure Insert(iCol:integer=0);
      //插入一行.
    procedure AddColNumber(NumberCol,iRow:integer;startNumber:integer=1);
      //给某列添加序号.(从NumBerCol,Irow开始)
    procedure AddRowNumber(iCol,NumberRow:integer;startNumber:integer=1);
      //给某行添加序号.（从Icol,NumberRow开始添加序号）
    function FillGrid(Icol,Irow,reLivecol,reLiveRow:integer;notfindCol: ColInteger;dataset:Tdataset):boolean;overload;
    function FillGrid(Icol,Irow,ReliveCol,reLiveRow,width:Integer;Dataset:Tdataset):boolean;overload;
    function FillGrid(Icol,Irow,reLivecol,reLiveRow:integer;dataset:Tdataset):boolean;overload;
    //把DataSet中的数据填充到stringgrid（从行irow列Icol开始填充,reLiveCol,reLiveRow是表示留下的填充后多余的列和行,width是要留下的列和行的宽度）
    function InputDataTable(ICols:array of integer;list,typelist:Tstringlist;con:TAdoconnection;Tablename:string):integer;
    //ICols数组中保存要添加到数据库某表中的某些列号，list用来保存对应列号字段名
    //typelist用来保存对应列号的字段类型(必须是sql中存在的数据类型)
    //con:指定通过哪个连接把数据添加到数据库
    //tablename:指定添加到数据库哪个表中
    //该函数返回所影响的行数
    procedure FillGrid(Icol,Irow,reLiveRow: integer;List: TstringLIst;Num: integer);overload;
    //用List填充stringgird，Icol说明开始填充的列，Irow开始填充的行，ReLiveCol为填充之后要留下的列，ReliveRow为 要留下的行
    //List为填充的数据源，Num为List数据源中填充一行数据的数据的个数
    function GetAColSum(ICol,IRow:integer):single;//统计某一列对应的数字的和,ICol指定列,IRow指定从哪行开始
    function FindValue(v: string): boolean;overload;//查找某个值是否在stringgrid中存在
    function findvalue(V: string;Icolarray: ColInteger): boolean;overload;//查找某些列的某个值是否存在
    function findvalue(V: string;Icol: integer): boolean;overload;//查找某一列的某个值是否存在
    procedure DistinctRow(DistinctCol: integer);//以某一列为基准，该列不可以重复，所有该列重复的全部删除
    published
    { Published declarations }
  end;

procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('chen', [TMystringGrid]);
end;

{ TMystringGrid }

procedure TMystringGrid.AddRow;
begin
  self.RowCount:=self.RowCount+1;
end;

procedure TMystringGrid.AddColNumber(NumberCol, iRow: integer;startNumber:integer);
var
  i:integer;
begin
  for i:=irow to self.RowCount-1 do
    begin
        self.Cells[Numbercol,i]:=inttostr(startNumber);
        inc(StartNumber);
    end;

end;

procedure TMystringGrid.AddRowNumber(iCol, NumberRow: integer; startNumber:integer);
var
  i:integer;
begin
  for i:=iCol to self.ColCount-1 do
    begin
         self.Cells[i,NumberRow]:=inttostr(startNumber);
         inc(startnumber);
    end;
end;

procedure TMystringGrid.clear(iCol,iRow:integer);
var
  i,j:integer;
begin   i:=self.ColCount-1;
while i>=icol do
begin
  if i>=self.FixedCols-1 then
  begin
     for j:=iRow to self.RowCount-1 do
       if j>=self.FixedRows-1 then
           self.Cells[i,j]:='';
  end;
  i:=i-1;
end;
  self.RowCount:=irow+1;
end;

procedure TMystringGrid.Delete(iCol,iRow: integer);
var
  i,j:integer;
begin
 if self.FixedRows = self.RowCount then
   exit;
 if self.RowCount=0 then
    exit;
 for i:=irow to self.RowCount - 1 do
 begin
   for j:=iCol to self.ColCount - 1 do
   begin
     self.Cells[j,i]:=self.Cells[j,i+1];
     if i=self.RowCount-1 then
       self.Cells[j,i]:='';
   end;
 end;
 if (self.Row >= self.FixedRows) or (self.FixedRows =0) then
    self.RowCount := self.RowCount - 1;
end;

procedure TMystringGrid.Insert(icol:integer);
var
  i,j:integer;
begin
self.RowCount:=self.RowCount+1;
 for i:=icol to self.ColCount-1 do
    for  j:=self.RowCount-2  downto self.Row  do
    begin
         self.Cells[i,j+1]:=self.Cells[i,j];
         self.Cells[i,j]:='';
    end;
end;

procedure TMystringGrid.AddCol;
begin
self.ColCount:=self.ColCount+1;
end;

function TMystringGrid.FillGrid(Icol,Irow,reLivecol,reLiveRow:integer;notfindCol: ColInteger;Dataset:Tdataset):boolean;
var
  i,j,temp:integer;
  breakflag: boolean;
begin
temp:=icol;
breakflag := false;
Dataset.First;
if Dataset.RecordCount > 0 then
begin
  self.ColCount := icol+Dataset.FieldCount;
  self.RowCount := irow+Dataset.RecordCount;
  for i := 0 to Dataset.RecordCount-1 do
  begin
    for j := 0 to Dataset.FieldCount-1 do
    begin
      try
           if self.FindValue(dataset.Fields[j].AsVariant,notfindcol) then
           begin
               breakflag := true;
               break;
           end
           else
               self.Cells[icol,irow]:=DataSet.Fields[j].AsVariant;
       except
           self.Cells[icol,irow]:='';
       end;
           inc(icol);
    end;
  icol:=temp;
  Dataset.Next;
  if not breakflag then
     inc(irow)
  else
     reliverow := 0;
  end;
self.ColCount:=self.ColCount+reLiveCol;
self.RowCount:=self.RowCount+reLiveRow;
result:=true;
end
else
  result:=false;
end;

function TMystringGrid.InputDataTable(ICols: array of integer;list,typelist:tstringlist;con:TADOConnection;tablename:string):integer;
var
query:Tadoquery;
i,j,k,count:integer;
sql,temp:string;
begin
k:=0;
count:=0;
for i:=0 to self.RowCount-1 do//先判断stringgrid中有数据的行数
begin
 for j:=0 to self.ColCount-1 do
  if self.Cells[j,i]<>'' then
     break;
  if j>self.ColCount-1 then
     break
  else
    inc(count);
end;

query:=Tadoquery.Create(self);
query.Connection:=con;
sql:='insert '+tablename+'(';
for i:=0 to list.Count-1 do
  if i<list.Count-1 then
      sql:=sql+list.Strings[i]+','
  else
      sql:=sql+list.Strings[i]+')  values(';
temp:=sql;
for i:=1 to count-1 do
begin
 sql:=temp;
 for j:=0 to high(iCols) do
 begin
 if typelist.Strings[j]<>'varchar' then
   if j<high(icols) then
    sql:=sql+'cast('''+self.Cells[icols[j],i]+''' as '+typelist.Strings[j]+'),'
   else
    sql:=sql+'cast('''+self.Cells[icols[j],i]+''' as '+typelist.Strings[j]+'))'
 else
  if j<high(icols) then
   sql:=sql+''''+self.Cells[icols[j],i]+''','
  else
   sql:=sql+''''+self.Cells[icols[j],i]+''')';
 end;
query.SQL.Clear;
query.SQL.Add(sql);
try
   query.ExecSQL;
   k:=k+1;
except
   break;
end;
end;
  result:=k;
end;

function TMystringGrid.GetAColSum(ICol, IRow:integer):single;
var
sum:single;
i:integer;
begin
sum:=0;
for i:=Irow to self.RowCount-1 do//先判断stringgrid中有数据的行数
begin
  if self.Cells[icol,i]<>'' then
   try
      sum:=sum+strtofloat(self.Cells[icol,i]);
   except
   end;
end;
result:=sum;
end;

function TMystringGrid.FillGrid(Icol, Irow, ReliveCol, reLiveRow,
  width: Integer;Dataset: Tdataset): boolean;
var
   i,j,temp:integer;
begin
temp:=icol;
Dataset.First;
if Dataset.RecordCount>0 then
begin
  self.ColCount:=icol+Dataset.FieldCount;
  self.RowCount:=irow+Dataset.RecordCount;
  for i:=0 to Dataset.RecordCount-1 do
  begin
    for j:=0 to Dataset.FieldCount-1 do
    begin
      try
           self.Cells[icol,irow]:=DataSet.Fields[j].AsVariant;
       except
           self.Cells[icol,irow]:='';
       end;
           inc(icol);
    end;
  icol:=temp;
  Dataset.Next;
  end;
  j:=self.ColCount;
  for i:=j to self.ColCount+relivecol-1 do
  begin
     if i<self.ColCount+relivecol then
     self.ColCount:=self.ColCount+1;
     self.ColWidths[i]:=width;
  end;
self.RowCount:=self.RowCount+reLiveRow;
result:=true;
end
else
  result:=false;
end;

function TMystringGrid.FindValue(v: string): boolean;
var
  Col,Row: integer;
begin
  Result := false;
  for Row := self.FixedRows to self.RowCount do
  begin
     for Col := self.FixedCols to self.ColCount do
        if (self.Cells[Col,Row] = V) and (v <> '') then
        begin
           Result := true;
           exit;
        end;
  end;
end;

function TMystringGrid.FindValue(V: string; IcolArray: ColInteger): boolean;
var
  Col,row: integer;
begin
   Result := false;
   for Row := self.FixedRows  to self.RowCount do
   begin
      for Col := self.FixedCols to self.ColCount do
        if not(Col in IcolArray) then
         if (self.Cells[col,row] = V) and (V<>'') then
           begin
             Result := true;
             exit;
           end;
   end;
end;

function TMystringGrid.FindValue(V: string; Icol: integer): boolean;
var
    Row: integer;
begin
  Result := false;
  for Row := self.FixedRows to self.RowCount do
  begin
    if (self.Cells[Icol,Row] = v) and (V<>'') then
    begin
      Result := true;
      exit;
    end;
  end;
end;

function TMystringGrid.FillGrid(Icol, Irow, reLivecol, reLiveRow: integer; dataset: Tdataset): boolean;
var
  i,j,temp:integer;
begin
temp:=icol;
Dataset.First;
if Dataset.RecordCount > 0 then
begin
  self.ColCount := icol+Dataset.FieldCount;
  self.RowCount := irow+Dataset.RecordCount;
  for i := 0 to Dataset.RecordCount-1 do
  begin
    for j := 0 to Dataset.FieldCount-1 do
    begin
      try
           self.Cells[icol,irow]:=DataSet.Fields[j].AsVariant;
       except
           self.Cells[icol,irow]:='';
       end;
           inc(icol);
    end;
  icol:=temp;
  Dataset.Next;
  inc(irow);
  end;
self.ColCount:=self.ColCount+reLiveCol;
self.RowCount:=self.RowCount+reLiveRow;
result:=true;
end
else
  result:=false;
end;

procedure TMystringGrid.DistinctRow(DistinctCol: integer);
var
  Row,i: integer;
begin
   for Row := self.Fixedrows to self.RowCount - 1 do
   begin
     for i := Row + 1 to self.RowCount - 1 do
     begin
        if self.Cells[distinctCol,row] = self.Cells[distinctCol,i] then
           self.Delete(0,i);
     end;
   end;
   self.AddColNumber(0,self.FixedRows);
end;

procedure TMystringGrid.FillGrid(Icol, Irow, reLiveRow: integer;
  List: TstringLIst;Num: integer);
var
  temp,i,j,row: integer;
begin
  temp := Icol;
  row := List.Count div Num;
  for i := 1 to Row do
  begin
    for j := (i-1)*Num to List.Count-1-(Num*(Row-i)) do
    begin
      self.Cells[Icol,Irow] := List.Strings[j];
      inc(Icol);
    end;
    Icol := temp;
    Inc(Irow);
    self.RowCount := self.RowCount + 1;
  end;
  self.RowCount := self.RowCount + ReliveRow-1;
  self.AddColNumber(0,1);
end;

end.