首页 > 代码库 > 导出Excel2 - 项目分解篇
导出Excel2 - 项目分解篇
接上一篇《导出Excel1 - 项目分解篇》，介绍3个子过程。
1. treeADO
说明：把数据表字段转化为Tree结构
声明：procedure treeADO(adoDataSetX:TADODataSet;tvTemplate1:TTreeView);
实现：
procedure treeADO(adoDataSetX:TADODataSet;tvTemplate1:TTreeView);
var
i,j,k,n,pre_p,a_p:integer;
s,pre_s,tepstr: string;
// aft_s cur_s prn_s,
//
exportMemo:TStringList;
begin
//create mylist
exportMemo:=TStringList.Create;
//
pre_s:='';
for i:=0 to adoDataSetX.FieldDefList.Count-1 do
begin
s:= adoDataSetX.FieldDefList[i].Name;
if pos('_',s)=0 then
begin
exportMemo.Add(s);
end
else
begin
pre_p:=0;
a_p:=0;
n:=0;
for j:=1 to length(s) do
if s[j]='_' then
begin
a_p:=j;
tepstr:='';
for k:=1 to n do
begin
tepstr:=tepstr+#9;
end;
if (copy(s,pre_p+1,a_p-pre_p-1)<> copy(pre_s,pre_p+1,a_p-pre_p-1)) then
begin
exportMemo.Add(tepstr+copy(s,pre_p+1,a_p-pre_p-1));
end;
pre_p:= a_p;
n:=n+1;
end;
exportMemo.Add(tepstr+#9+copy(s,a_p+1,length(s)-pre_p));
end;
pre_s:= s;
end;
exportMemo.SaveToFile(findSysTempDir+'temp.txt');
TVtemplate1.LoadFromFile(findSysTempDir+'temp.txt');
DeleteFile(findSysTempDir+'temp.txt');
//free
FreeAndNil(exportMemo);
end;2. WriteheaderADO
说明：把Tree结构的数据，输出到Excel中。
声明：procedure WriteheaderADO(adoDataSetX:TADODataSet;titlehead:string;
ColWidthInt:Integer;tvTemplate3:TTreeView);
实现：
procedure WriteheaderADO(adoDataSetX:TADODataSet;titlehead:string;
ColWidthInt:Integer;tvTemplate3:TTreeView);
var
colnum,i,titlnum,Depnum:integer;
colname,temcaption:string;
colmix,colmax:string;
treeno,treecount,colnums,treenotemp:integer;
childcount:integer;
Trode:Ttreenode;
rownum:integer;
begin
colnum:=adoDataSetX.FieldDefList.Count;
rownum:=adoDataSetX.RecordCount;
for i:=1 to colnum do
begin
//colwidth
worksheet.Columns[i].ColumnWidth:=ColWidthInt;
//
worksheet.Columns[i].Font.size:=9;
worksheet.Columns[i].Font.Name := '宋体';
end;
if colnum>26 then
colname:=char(colnum div 26 +64)+ char(colnum mod 26 +64)
else
colname:=char(colnum +64);
if rownum=0 then rownum:=Maxnum;
worksheet.Range['A1:'+colname+inttostr(2+rownum+Maxnum-1)].Font.Name := '宋体';
worksheet.Range['A1:'+colname+inttostr(2+rownum+Maxnum-1)].Font.Size := 9;
worksheet.range['A1:'+colname+inttostr(2+rownum+Maxnum-1)].HorizontalAlignment := $FFFFEFF4;
worksheet.range['A1:'+colname+inttostr(2+rownum+Maxnum-1)].VerticalAlignment := $FFFFEFF4;
worksheet.range['A2:'+colname+inttostr(2+rownum+Maxnum-1)].Borders[1].Weight := 2;
worksheet.range['A2:'+colname+inttostr(2+rownum+Maxnum-1)].Borders[2].Weight := 2;
worksheet.range['A2:'+colname+inttostr(2+rownum+Maxnum-1)].Borders[3].Weight := 2;
worksheet.range['A2:'+colname+inttostr(2+rownum+Maxnum-1)].Borders[4].Weight := 2;
worksheet.Range['A1',colname+'1'].Merge('false');
worksheet.Range['A1:'+colname+'1'].Font.Name := '宋体';
worksheet.Range['A1:'+colname+'1'].Font.Size := 14;
worksheet.Range['A1:'+colname+'1'].Font.Bold := True;
worksheet.range['A1:'+colname+'1'].HorizontalAlignment := $FFFFEFF4;
worksheet.Rows[1].VerticalAlignment := $FFFFEFF4;
worksheet.range['A1:'+colname+'1'].Value:=titlehead;
treecount:=tvTemplate3.Items.count;
treeno:=0 ;
colnums:=0 ;
while treeno <= treecount-1 do
begin
//取当前头有几层
titlnum:=1;
temcaption:=adoDataSetX.FieldDefList[colnums].Name;
while Pos('_',temcaption) > 0 do
begin
Delete(temcaption, 1, Pos('_', temcaption));
titlnum:=titlnum+1;
end;
///////////////////////
if (tvTemplate3.Items[treeno].Parent=nil) and (tvTemplate3.Items[treeno].Count=0) then
//无child的第一层节点
begin
{2004 if (colnums+1) >26 then
colname:=char((colnums+1) div 26 +64)+ char((colnums+1) mod 26 +64) //colnum 改为 colnums
else
colname:=char((colnums+1) +64);
}
if colnums div 26 = 0 then
colname := char(colnums MOD 26 + 1 +64)
else
colname := char(colnums div 26 +64) + char(colnums MOD 26 + 1 +64) ;
worksheet.Range[colname+'2',colname+inttostr(Maxnum+1)].Merge('false');
worksheet.Range[colname+'2',colname+inttostr(Maxnum+1)].Value:= tvTemplate3.Items[treeno].text;
treeno:=treeno+1;
colnums:=colnums+1;
end
else //下级结点
begin
if (tvTemplate3.Items[treeno].Parent=nil) and (tvTemplate3.Items[treeno].Count<>0) then
//有child的第一层节点
begin
numberNode(tvTemplate3.Items[treeno],tvTemplate3);
childcount :=childNum;
Depnum:=Depthnum;//本结点的深度
// Depnum:=titlnum;
// childcount := TreeView1.Items[treeno].Count;
//xia加 if treeno >26 then
if colnums div 26 = 0 then
colmix := char(colnums MOD 26 + 1 +64)
else
colmix := char(colnums div 26 +64) + char(colnums MOD 26 + 1 +64) ;
{ 2004 if (colnums+1) >26 then // xia加
colmix:=char((colnums+1) div 26 +64)+ char((colnums+1) mod 26 +64)
else
colmix:=char((colnums+1) +64);
}
if (colnums + childcount -1) div 26 = 0 then
colmax := char((colnums + childcount -1) MOD 26 + 1 +64)
else
colmax := char((colnums + childcount -1) div 26 +64)
+ char((colnums + childcount -1) MOD 26 + 1 +64) ;
{ 2004
if (colnums+1+childcount-1)>26 then
colmax:=char((colnums+1+childcount-1) div 26 +64)+ char((colnums+1+childcount-1) mod 26 +64)
else
colmax:=char((colnums+1+childcount-1) +64);
}
worksheet.Range[colmix+'2',colmax+'2'].Merge('false');
worksheet.Range[colmix+'2',colmax+'2'].Value:= tvTemplate3.Items[treeno].text;
colnums:=colnums-1;
// if colnums =-1 then colnums:=0;
end
else if (tvTemplate3.Items[treeno].Parent<>nil) and (tvTemplate3.Items[treeno].Count<>0) then
//有child的层节点
begin
numberNode(tvTemplate3.Items[treeno],tvTemplate3);
childcount :=childNum;
Depnum:=Depthnum;//本结点的深度
// childcount :=TreeView1.Items[treeno].Count;
// Depnum:=titlnum;
//xia加 if treeno >26 then
if colnums div 26 = 0 then
colmix := char(colnums MOD 26 + 1 +64)
else
colmix := char(colnums div 26 +64) + char(colnums MOD 26 + 1 +64) ;
{2004 if (colnums+1) >26 then // xia加
colmix:=char((colnums+1) div 26 +64)+ char((colnums+1) mod 26 +64)
else
colmix:=char((colnums+1) +64);
}
if (colnums + childcount -1) div 26 = 0 then
colmax := char((colnums + childcount -1) MOD 26 + 1 +64)
else
colmax := char((colnums + childcount -1) div 26 +64)
+ char((colnums + childcount -1) MOD 26 + 1 +64) ;
{2004 if (colnums+1+childcount-1)>26 then
colmax:=char((colnums+1+childcount-1) div 26 +64)+ char((colnums+1+childcount-1) mod 26 +64)
else
colmax:=char((colnums+1+childcount-1) +64);
}
treenotemp:=0;
Trode:=tvTemplate3.Items[treeno].Parent;
while Trode <> nil do
begin
treenotemp:=treenotemp+1;
Trode:=Trode.Parent ;
end;
worksheet.Range[colmix+inttostr(2+treenotemp),colmax+inttostr(2+treenotemp)].Merge('false');
worksheet.Range[colmix+inttostr(2+treenotemp),colmax+inttostr(2+treenotemp)].Value:= tvTemplate3.Items[treeno].text;
colnums:=colnums-1;
// if colnums =-1 then colnums:=0;
end
else //最低层结点
begin
if colnums div 26 = 0 then
colname := char(colnums MOD 26 + 1 +64)
else
colname := char(colnums div 26 +64) + char(colnums MOD 26 + 1 +64) ;
{2004 if colnums+1>26 then
colname:=char((colnums+1) div 26 +64)+ char((colnums+1) mod 26 +64)
else
colname:=char((colnums+1) +64);
}
if Maxnum-titlnum=0 then
worksheet.cells.item[Maxnum+1,colnums+1]:=tvTemplate3.Items[treeno].text
else
begin
treenotemp:=0;
Trode:=tvTemplate3.Items[treeno].Parent;
while Trode <> nil do
begin
treenotemp:=treenotemp+1;
Trode:=Trode.Parent ;
end;
worksheet.Range[colname+inttostr(2+titlnum-1),colname+inttostr(2+Maxnum-1)].Merge('false');
worksheet.Range[colname+inttostr(2+titlnum-1),colname+inttostr(2+Maxnum-1)].Value:= tvTemplate3.Items[treeno].text;
end;
end;
treeno:=treeno+1;
colnums:=colnums+1;
end; //else
end; //while
//procedure WriteExcelData(adoDataSetX:TADODataSet);
end;2.1 numberNode
说明：计算节点数
声明：procedure numberNode(TNode:TTreenode;tvTemplate2:TTreeView);
实现：
procedure numberNode(TNode:TTreenode;tvTemplate2:TTreeView);
type
Nodesum=record
Node:TTreenode; //结点
Layer:Integer; //所在层
end;
var
a:array of Nodesum;
Node:TTreenode;
Depth,i,j,num:Integer;
begin
Depth:=0;
i:=0;
j:=0;
num:=0;
Setlength(a,tvTemplate2.Items.Count);
Node:=TNode; //使顶结点为您所选择的结点
a[i].Node:=Node;
a[i].Layer:=1;
while a[i].Node<> nil do
begin
Node:=a[i].Node.Getfirstchild;//取其第一个子结点
while Node <> nil do
begin
j:=j+1;
a[j].Node:=Node;
a[j].Layer:=a[i].Layer+1; //此结点所在的层数
Node:=Node.GetNextSibling;
end;//如此循环，以取出此层所有结点
i:=i+1;
end;
i:=0;
while (a[i].node<>nil) do
begin
if a[i].Layer>Depth then
Depth:=a[i].Layer;//求最大的层数，即子树深度
IF (a[i].Node.count=0) THEN
num:=num+1;
i:=i+1;
end;
If (a[0].Node<>nil) then
begin
childNum :=num;
DepthNum:=Depth;
end;
end;3. WriteExcelData
说明：输出数据表数据到Excel中。
声明：procedure WriteExcelData(adoDataSetX:TADODataSet);
实现：
procedure WriteExcelData(adoDataSetX:TADODataSet);
var
i1:Integer;
Tabrow:integer;
Valuestr:string;
begin
adoDataSetX.First ;
try
For i1:=0 To adoDataSetX.Recordcount -1 do
begin
for Tabrow:=0 to adoDataSetX.FieldDefList.Count-1 do
begin
if not adoDataSetX.Fields[Tabrow].IsNull then
begin
Valuestr:= adoDataSetX.Fields[Tabrow].AsString;
worksheet.Cells.item[2+Maxnum+i1,Tabrow+1]:=''''+ Valuestr ;
end;
end;
adoDataSetX.Next;
end;
except
end;
end;
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。