在Delphi中自己建立交叉表

2019-11-18 18:32:32

字體：大中小

來源：轉載

供稿：網友

經常在CSDN上查閱名位大俠的文章，得益不少，近期因做一個項目，需要用到交叉表，報表上倒是有，但客戶要求在Grid上能操作，沒有辦法，只好自己寫了一段代碼用于普通查詢到交叉表的實現，不敢獨享，故上傳，望能拋磚引玉，請名位大俠不吝指教。

function CreateTmptab(const AFieldDefs:TFieldDefs):TDataSet;
var
TempTable:TatClientDataSet;
begin
TempTable:=nil;
Result:=nil;
if AFieldDefs<>nil then
begin
    try
        TempTable:=TatClientDataSet.Create(application);
        TempTable.FieldDefs.Assign(AFieldDefs);
        TempTable.CreateDataSet;
        Result:=(TempTable as TDataSet);
    Except
    if TempTable<>nil then
        TempTable.Free;
        Result:=nil;
        raise;
    end
end;
end;
{
SouDataset源數據集
ColField交叉表動態列字段
RowField交叉表行字段
DataField數據字段
}
function GenCrossTable(SouDataset:tdataset;ColField,RowField,DataField:string):tdataset;
var
Vdataset:tdataset;
tmpdataset:tatclientdataset;
DataSource:tdatasource;
tmpstrs:tstrings;
rowval,colval,dataval:string;
i,j:integer;
datatype:TFieldType;
DataSize:integer;
begin
result:=nil;
if (ColField='') or(RowField='')or(DataField='') then
showmessage('All Field not be NULL!')
else
begin
if (ColField=RowField)
      or(ColField=DataField)
      or(RowField=DataField) then
    showmessage('All Field not be Equ!')
else
if (self.SouDataSet.FieldByName(ColField).DataType=ftString)
    or (self.SouDataSet.FieldByName(ColField).DataType<>ftWideString)
    or (self.SouDataSet.FieldByName(ColField).DataType<>ftFixedChar)
    or (self.SouDataSet.FieldByName(ColField).DataType<>ftMemo)
    or (self.SouDataSet.FieldByName(ColField).DataType<>ftFmtMemo) then
begin
try
    tmpstrs:=tstringlist.Create;
    Vdataset:=SouDataSet;
    Vdataset.First;
    for i:=0 to Vdataset.RecordCount-1 do
    begin
      if (varisnull(SouDataSet.FieldValues[colfield])=false) and (SouDataSet.FieldValues[colfield]<>'') then
        if tmpstrs.IndexOf(SouDataSet.FieldValues[colfield])=-1 then
        begin
          tmpstrs.Add(SouDataSet.FieldValues[colfield]);
        end;
      Vdataset.Next;
    end;
    //生成動態列標題
    tmpdataset:=TClientDataSet.Create(Self);
    tmpdataset.FieldDefs.Add(rowfield,ftstring,50,False);
    for i:=0 to tmpstrs.Count-1 do
    begin
      with tmpdataset.FieldDefs do
      begin
        Add(tmpstrs.Strings[i],ftInteger,0,False);
      end;
    end;
    tmpdataset.FieldDefs.Add('Sum',ftInteger,0,False);
    DataSource:=tdatasource.Create(self);
    DataSource.DataSet:=tmpdataset;
    with DataSource do
    begin
      dataset:=Createtmptab(tmpdataset.FieldDefs);
      dataset.Open;
    end;
    //建立臨時表
    Vdataset.First;
    for i:=0 to Vdataset.RecordCount-1 do
    begin
      rowval:=SouDataSet.fieldbyname(rowfield).AsString;
      colval:=SouDataSet.fieldbyname(colfield).AsString;
      dataval:=SouDataSet.fieldbyname(datafield).AsString;
      if dataval='' then dataval:='0';
      if DataSource.DataSet.Locate(rowfield,rowval,[loPartialKey]) then
      begin
        DataSource.DataSet.Edit;
        DataSource.DataSet.FieldByName(colval).AsString:=dataval;
        DataSource.DataSet.FieldByName('Sum').AsInteger:=
          DataSource.DataSet.FieldByName('Sum').AsInteger+strtoint(dataval);
        DataSource.DataSet.Post;
      end
      else
      begin
        DataSource.DataSet.Append;
        DataSource.DataSet.FieldByName(rowfield).AsString:=rowval;
        for j:=1 to DataSource.DataSet.Fields.Count-1 do
          DataSource.DataSet.Fields[j].AsCurrency:=0;
        DataSource.DataSet.FieldByName(colval).AsString:=dataval;
        DataSource.DataSet.FieldByName('Sum').AsString:=dataval;
        DataSource.DataSet.Post;
      end;
      Vdataset.Next;
    end;
    result:=DataSource.DataSet;
    //生成交叉表數據集
    tmpstrs.Free;
except
end;
end
else
    showmessage('ColField Must be of Type String!') ;
end;
end;