一般做oa类管理系统，经常涉及到“组织架构”的概念，那么像这种有上下层级关系的数据一般会做成树形菜单的方式显示，底层代码必定会用到递归算法。这篇随笔的目的就是要谈谈除了用树形菜单来显示这种上下层级关系的数据，还有其他的显示方式吗？答案是有的，例如即将要谈到的二维表显示方式，同时也是本随笔的核心内容。
首先来看二维表的显示效果图：
如果看到这里，你觉得这就是你想要的显示效果，或者对此比较感兴趣。请接着往下看的实现步骤：
1.取出所有的数据临时保存到datatable中,即内存中,拼html时直接查datatable中的数据,不用去反复读取数据库,提高效率；
2.根据节点编号获取该节点下所有的末端子节点编号,因为末端子节点的个数就决定了
的行数； 3.将查到的末端子节点编号的所有父节点编号也查出来,拼接起来,就知道了
的每行的列数； 4.对节点的编号进行排序,这样可以把每列下的相同行的节点编号集中在一起,方便后面的合并单元格；
5.遍历行和列,合并每列相同行的单元格；
6.最后一步，拼接空白的列。
如下是具体代码实现过程：
1 using system; 2 using system.collections.generic; 3 using system.linq; 4 using system.web; 5 using system.data; 6 using system.text; 7 8 /// 9 ///kpitable 的摘要说明 10 /// 11 public class kpitable 12 { 13 dbutility.sqlhelper sqlhelper = new dbutility.sqlhelper(); 14 public string getkpitable(string kpino, string businessno, string tenderno) 15 { 16 //1.取出所有的数据临时保存到dt2,即内存中,拼html时直接查dt2中的数据,不用去反复读取数据库,提高效率 17 datatable dt2 = new datatable(); 18 { 19 dataset ds = new dataset(); 20 int i = sqlhelper.runsql(string.format(select count(1) from sys.objects where name = 'kpivalue{0}', businessno)); 21 if (string.isnullorempty(tenderno) || i -1) 66 { 67 row[kpino] = row[kpino].tostring().substring(index); 68 } 69 } 70 //6.遍历行和列 71 int maxcount = getmaxcount(drarray); 72 stringbuilder sbjson = new stringbuilder(); 73 for (int i = 0; i 0)125 {126 return true;127 }128 else129 {130 return false;131 }132 }133 134 private string recursion(datatable dt, object parentid)135 {136 stringbuilder sbjson = new stringbuilder();137 138 datarow[] rows = dt.select(kpino = ' + parentid + ');139 if (rows.length > 0)140 {141 if (rows[0][kpiparentno].tostring() == 0 || rows[0][kpiparentno].tostring() == )142 {143 sbjson.append(0,);144 }145 else146 {147 sbjson.append(recursion(dt, rows[0][kpiparentno]));148 }149 }150 sbjson.append(parentid.tostring() + ,);151 return sbjson.tostring();152 }153 private int getmaxcount(datarow[] drarray)154 {155 int temp = 0;156 foreach (datarow row in drarray)157 {158 int count = row[kpino].tostring().trim(',').split(',').length;159 if (count > temp)160 {161 temp = count;162 }163 }164 return temp;165 }166 private bool equalupcolumnvalue(int rowindex, int colindex, datarow[] drarray)167 {168 if (rowindex == 0)169 {170 return false;171 }172 173 string[] kpinoarray = drarray[rowindex - 1][kpino].tostring().trim(',').split(',');174 if (kpinoarray.length > colindex)175 {176 string upcolumnvalue = drarray[rowindex][kpino].tostring().trim(',').split(',')[colindex];177 if (upcolumnvalue == kpinoarray[colindex])178 {179 return true;180 }181 else182 {183 return false;184 }185 }186 else187 {188 return false;189 }190 }191 private int getcolspan(int rowindex, int colindex, datarow[] drarray)192 {193 int colspan = 1;194 string[] kpinoarray = drarray[rowindex][kpino].tostring().trim(',').split(',');195 196 while (rowindex colindex)200 {201 if (kpinoarray[colindex] == kpinoarray2[colindex])202 {203 colspan++;204 }205 else206 {207 break;208 }209 }210 else211 {212 break;213 }214 rowindex++;215 }216 return colspan;217 }218 private double getkpiweight(datatable dt, string kpino)219 {220 double kpiweight = 0;221 var drarray = dt.select(kpino=' + kpino + ');222 if (drarray.length > 0)223 {224 string kpiparentno = drarray[0][kpiparentno].tostring();225 double kpino_kpiweight = convert.todouble(drarray[0][kpiweight]);226 drarray = dt.select(kpiparentno=' + kpiparentno + ');227 if (drarray.length > 0)228 {229 double result = 0;230 foreach (datarow row in drarray)231 {232 result += convert.todouble(row[kpiweight]);233 }234 kpiweight = (kpino_kpiweight / result);235 }236 }237 return kpiweight;238 }239 private double getkpivalue(datatable dt, string kpino)240 {241 var drarray = dt.select(string.format(kpino='{0}',kpino));242 if (drarray.length>0)243 {244 return convert.todouble(drarray[0][kpivalue1]);245 }246 return 0;247 }248 private string getkpides(datatable dt, string kpino)249 {250 string des = string.empty;251 var drarray = dt.select(string.format(kpino='{0}',kpino));252 if (drarray.length>0)253 {254 string kpimethod = drarray[0][kpimethod].tostring();255 string kpisampletype = drarray[0][kpisampletype].tostring();256 string kpirule = drarray[0][kpirule].tostring();257 string kpicriterion = drarray[0][kpicriterion].tostring();258 string kpiarearule = drarray[0][kpiarearule].tostring();259 string kpisamplerule = drarray[0][kpisamplerule].tostring();260 261 //表格样式262 if (!string.isnullorempty(kpimethod))263 {264 //des += ;265 //des += 计算方法: + kpimethod +
;266 //des += 采样类别: + kpisampletype +
;267 //des += 评价标准: + kpirule +
;268 //des += 规范要点: + kpicriterion +
;269 //des += 测区规则: + kpiarearule +
;270 //des += 测点规则: + kpisamplerule +
;271 //des +=
;272 }273 274 //换行样式 275 //if (!string.isnullorempty(kpimethod)) { des +=
计算方法: + kpimethod; }276 //if (!string.isnullorempty(kpisampletype)) { des +=
采样类别: + kpisampletype; }277 //if (!string.isnullorempty(kpirule)) { des +=
评价标准: + kpirule; }278 //if (!string.isnullorempty(kpicriterion)) { des +=
规范要点: + kpicriterion; }279 //if (!string.isnullorempty(kpiarearule)) { des +=
测区规则: + kpiarearule; }280 //if (!string.isnullorempty(kpisamplerule)) { des +=
测点规则: + kpisamplerule; } 281 }282 return des;283 }284 285 286 287 288 }
以及需要用到的表（sql脚本）：
1 use [evaluation] 2 go 3 /****** object: table [dbo].[kpi] script date: 2016/3/25 16:06:04 ******/ 4 set ansi_nulls on 5 go 6 set quoted_identifier on 7 go 8 create table [dbo].[kpi]( 9 [kpino] [nvarchar](50) not null, 10 [kpiname] [nvarchar](50) null, 11 [kpiinfo] [nvarchar](100) null, 12 [kpiparentno] [nvarchar](50) null, 13 [kpimethod] [nvarchar](50) null, 14 [kpiweight] [decimal](18, 2) null, 15 [kpirule] [nvarchar](100) null, 16 [kpicriterion] [nvarchar](100) null, 17 [kpiarearule] [nvarchar](100) null, 18 [kpisamplerule] [nvarchar](100) null, 19 [kpiareanum] [int] not null, 20 [kpisamplenum] [int] not null, 21 [kpiminvalue] [decimal](18, 2) null, 22 [kpimaxvalue] [decimal](18, 2) null, 23 [kpioffset] [decimal](18, 2) null, 24 [kpireferenceval] [decimal](18, 2) null, 25 [kpivaluetype] [nvarchar](50) null, 26 [kpiformula] [nvarchar](50) null, 27 [kpiformularule] [nvarchar](100) null, 28 [kpimemo] [nvarchar](100) null, 29 [kpigoodminvalue] [decimal](18, 2) null, 30 [kpigoodmaxvalue] [decimal](18, 2) null, 31 [kpigoodmethod] [nvarchar](50) null, 32 [kpigoodoffset] [decimal](18, 2) null, 33 [kpisampletype] [nvarchar](50) null, 34 [isdlt] [smallint] null, 35 [crtdate] [datetime] null, 36 [crtuser] [nvarchar](50) null, 37 [upddate] [datetime] null, 38 [upduser] [nvarchar](50) null, 39 constraint [pk_evkpiinfo] primary key clustered 40 ( 41 [kpino] asc 42 )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] 43 ) on [primary] 44 45 go 46 /****** object: table [dbo].[kpi1447055501128] script date: 2016/3/25 16:06:04 ******/ 47 set ansi_nulls on 48 go 49 set quoted_identifier on 50 go 51 create table [dbo].[kpi1447055501128]( 52 [kpino] [nvarchar](50) not null, 53 [kpiname] [nvarchar](50) null, 54 [kpiinfo] [nvarchar](100) null, 55 [kpiparentno] [nvarchar](50) null, 56 [kpimethod] [nvarchar](50) null, 57 [kpiweight] [decimal](18, 2) null, 58 [kpirule] [nvarchar](100) null, 59 [kpicriterion] [nvarchar](100) null, 60 [kpiarearule] [nvarchar](100) null, 61 [kpisamplerule] [nvarchar](100) null, 62 [kpiareanum] [int] not null, 63 [kpisamplenum] [int] not null, 64 [kpiminvalue] [decimal](18, 2) null, 65 [kpimaxvalue] [decimal](18, 2) null, 66 [kpioffset] [decimal](18, 2) null, 67 [kpireferenceval] [decimal](18, 2) null, 68 [kpivaluetype] [nvarchar](50) null, 69 [kpiformula] [nvarchar](50) null, 70 [kpiformularule] [nvarchar](100) null, 71 [kpimemo] [nvarchar](100) null, 72 [kpigoodminvalue] [decimal](18, 2) null, 73 [kpigoodmaxvalue] [decimal](18, 2) null, 74 [kpigoodmethod] [nvarchar](50) null, 75 [kpigoodoffset] [decimal](18, 2) null, 76 [kpisampletype] [nvarchar](50) null 77 ) on [primary] 78 79 go 80 /****** object: table [dbo].[kpi20150002] script date: 2016/3/25 16:06:04 ******/ 81 set ansi_nulls on 82 go 83 set quoted_identifier on 84 go 85 create table [dbo].[kpi20150002]( 86 [kpino] [nvarchar](50) not null, 87 [kpiname] [nvarchar](50) null, 88 [kpiinfo] [nvarchar](100) null, 89 [kpiparentno] [nvarchar](50) null, 90 [kpimethod] [nvarchar](50) null, 91 [kpiweight] [decimal](18, 2) null, 92 [kpirule] [nvarchar](100) null, 93 [kpicriterion] [nvarchar](100) null, 94 [kpiarearule] [nvarchar](100) null, 95 [kpisamplerule] [nvarchar](100) null, 96 [kpiareanum] [int] not null, 97 [kpisamplenum] [int] not null, 98 [kpiminvalue] [decimal](18, 2) null, 99 [kpimaxvalue] [decimal](18, 2) null,100 [kpioffset] [decimal](18, 2) null,101 [kpireferenceval] [decimal](18, 2) null,102 [kpivaluetype] [nvarchar](50) null,103 [kpiformula] [nvarchar](50) null,104 [kpiformularule] [nvarchar](100) null,105 [kpimemo] [nvarchar](100) null,106 [kpigoodminvalue] [decimal](18, 2) null,107 [kpigoodmaxvalue] [decimal](18, 2) null,108 [kpigoodmethod] [nvarchar](50) null,109 [kpigoodoffset] [decimal](18, 2) null,110 [kpisampletype] [nvarchar](50) null111 ) on [primary]112 113 go114 /****** object: table [dbo].[kpitree] script date: 2016/3/25 16:06:04 ******/115 set ansi_nulls on116 go117 set quoted_identifier on118 go119 create table [dbo].[kpitree](120 [kpino] [nvarchar](50) not null,121 [kpiname] [nvarchar](50) null,122 [kpiinfo] [nvarchar](50) null,123 [kpiparentno] [nvarchar](50) null,124 [kpicollectmethod] [nvarchar](50) null,125 [kpiweight] [decimal](18, 2) null,126 [kpimemo] [nvarchar](100) null,127 [kpiindex] [int] null,128 [isdlt] [smallint] null,129 [crtdate] [datetime] null,130 [crtuser] [nvarchar](50) null,131 [upddate] [datetime] null,132 [upduser] [nvarchar](50) null,133 constraint [pk_kpitree] primary key clustered 134 (135 [kpino] asc136 )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]137 ) on [primary]138 139 go140 /****** object: table [dbo].[kpitree1447055501128] script date: 2016/3/25 16:06:04 ******/141 set ansi_nulls on142 go143 set quoted_identifier on144 go145 create table [dbo].[kpitree1447055501128](146 [kpino] [nvarchar](50) not null,147 [kpiname] [nvarchar](50) null,148 [kpiinfo] [nvarchar](50) null,149 [kpiparentno] [nvarchar](50) null,150 [kpicollectmethod] [nvarchar](50) null,151 [kpiweight] [decimal](18, 2) null,152 [kpimemo] [nvarchar](100) null,153 [kpiindex] [int] null154 ) on [primary]155 156 go157 /****** object: table [dbo].[kpitree20150002] script date: 2016/3/25 16:06:04 ******/158 set ansi_nulls on159 go160 set quoted_identifier on161 go162 create table [dbo].[kpitree20150002](163 [kpino] [nvarchar](50) not null,164 [kpiname] [nvarchar](50) null,165 [kpiinfo] [nvarchar](50) null,166 [kpiparentno] [nvarchar](50) null,167 [kpicollectmethod] [nvarchar](50) null,168 [kpiweight] [decimal](18, 2) null,169 [kpimemo] [nvarchar](100) null,170 [kpiindex] [int] null171 ) on [primary]172 173 go174 /****** object: table [dbo].[kpivalue] script date: 2016/3/25 16:06:04 ******/175 set ansi_nulls on176 go177 set quoted_identifier on178 go179 create table [dbo].[kpivalue](180 [tenderno] [nvarchar](50) not null,181 [kpino] [nvarchar](50) not null,182 [kpivalue1] [decimal](18, 2) null,183 [kpivalue2] [decimal](18, 2) null,184 [samplenumall] [int] null,185 [samplenum1] [int] null,186 [samplenum2] [int] null187 ) on [primary]188 189 go190 /****** object: table [dbo].[kpivalue1447055501128] script date: 2016/3/25 16:06:04 ******/191 set ansi_nulls on192 go193 set quoted_identifier on194 go195 create table [dbo].[kpivalue1447055501128](196 [tenderno] [nvarchar](50) not null,197 [kpino] [nvarchar](50) not null,198 [kpivalue1] [decimal](18, 2) null,199 [kpivalue2] [decimal](18, 2) null,200 [samplenumall] [int] null,201 [samplenum1] [int] null,202 [samplenum2] [int] null203 ) on [primary]204 205 go206 /****** object: table [dbo].[kpivalue20150002] script date: 2016/3/25 16:06:04 ******/207 set ansi_nulls on208 go209 set quoted_identifier on210 go211 create table [dbo].[kpivalue20150002](212 [tenderno] [nvarchar](50) not null,213 [kpino] [nvarchar](50) not null,214 [kpivalue1] [decimal](18, 2) null,215 [kpivalue2] [decimal](18, 2) null,216 [samplenumall] [int] null,217 [samplenum1] [int] null,218 [samplenum2] [int] null219 ) on [primary]220 221 go222 alter table [dbo].[kpi] add constraint [df_kpi_kpiweight] default ((1)) for [kpiweight]223 go224 alter table [dbo].[kpi] add constraint [df_kpi_kpiareanum] default ((2)) for [kpiareanum]225 go226 alter table [dbo].[kpi] add constraint [df_kpi_kpisamplenum] default ((10)) for [kpisamplenum]227 go228 alter table [dbo].[kpi] add constraint [df_kpi_isdlt] default ((0)) for [isdlt]229 go230 alter table [dbo].[kpi20150002] add constraint [df_kpi20150002_kpiweight] default ((1)) for [kpiweight]231 go232 alter table [dbo].[kpitree] add constraint [df_kpitree_kpiweight] default ((1)) for [kpiweight]233 go234 alter table [dbo].[kpitree] add constraint [df_kpitree_isdlt] default ((0)) for [isdlt]235 go236 alter table [dbo].[kpitree20150002] add constraint [df_kpitree20150002_kpiweight] default ((1)) for [kpiweight]237 go238 exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'指标编号' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'kpi', @level2type=n'column',@level2name=n'kpino'239 go240 exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'指标名称' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'kpi', @level2type=n'column',@level2name=n'kpiname'241 go242 exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'上级指标编号' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'kpi', @level2type=n'column',@level2name=n'kpiparentno'243 go244 exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'权重' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'kpi', @level2type=n'column',@level2name=n'kpiweight'245 go246 exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'上级指标编号' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'kpi', @level2type=n'column',@level2name=n'kpirule'247 go248 exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'权重' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'kpi', @level2type=n'column',@level2name=n'kpicriterion'249 go250 exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'指标配置表' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'kpi'251 go