议：如何将树形菜单形式的数据转化成HTML的二维表（相同内容需合并单元格）

　　一般做OA类管理系统，经常涉及到“组织架构”的概念，那么像这种有上下层级关系的数据一般会做成树形菜单的方式显示，底层代码必定会用到递归算法。这篇随笔的目的就是要谈谈除了用树形菜单来显示这种上下层级关系的数据，还有其他的显示方式吗？答案是有的，例如即将要谈到的二维表显示方式，同时也是本随笔的核心内容。

　　首先来看二维表的显示效果图：

如果看到这里，你觉得这就是你想要的显示效果，或者对此比较感兴趣。请接着往下看的实现步骤：

1.取出所有的数据临时保存到DataTable中,即内存中,拼html时直接查DataTable中的数据,不用去反复读取数据库,提高效率；

2.根据节点编号获取该节点下所有的末端子节点编号,因为末端子节点的个数就决定了<table>的行数；

3.将查到的末端子节点编号的所有父节点编号也查出来,拼接起来,就知道了<table>的每行的列数；

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 /// <summary> 9 ///KpiTable 的摘要说明 10 /// </summary> 11 public class KpiTable 12 { 13   DBUtility.SQLHelper sqlhelper = new DBUtility.SQLHelper(); 14   public get='_blank'>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)//如果有标段编号就要把KpiValueXXX表里的KpiValue1查出来显示 22       { 23         sqlhelper.RunSQL(string.Format(@"select KpiNo,KpiName,KpiInfo,ISNULL(KpiParentNo,0) KpiParentNo,KpiWeight,0 KpiValue1,'' KpiMethod,'' KpiSampleType,'' KpiRule,'' KpiCriterion,'' KpiAreaRule,'' KpiSampleRule from KpiTree{0} 24 union all select KpiNo,KpiName,KpiInfo,ISNULL(KpiParentNo,0) KpiParentNo,KpiWeight,0 KpiValue1,KpiMethod,KpiSampleType,KpiRule,KpiCriterion,KpiAreaRule,KpiSampleRule from Kpi{0}",businessno), ref ds); 25       } 26       else 27       { 28         sqlhelper.RunSQL(string.Format(@"select * from ( 29 select KpiNo,KpiName,KpiInfo,ISNULL(KpiParentNo,0) KpiParentNo,KpiWeight,0 KpiValue1,'' KpiMethod,'' KpiSampleType,'' KpiRule,'' KpiCriterion,'' KpiAreaRule,'' KpiSampleRule from KpiTree{0} 30 union all select KpiNo,KpiName,KpiInfo,ISNULL(KpiParentNo,0) KpiParentNo,KpiWeight,0 KpiValue1,KpiMethod,KpiSampleType,KpiRule,KpiCriterion,KpiAreaRule,KpiSampleRule from Kpi{0} 31 ) a left join KpiValue{0} b on a.kpino = b.kpino and TenderNo='{1}'", businessno, tenderno), ref ds); 32       } 33       dt2 = ds.Tables[0]; 34     } 35     //2.根据节点编号获取该节点下所有的末端子节点编号,因为末端子节点的个数就决定了table的行数 36     DataTable dt = new DataTable(); 37     { 38       DataSet ds = new DataSet(); 39       if (string.IsNullOrEmpty(kpino) || kpino == "0") 40       { 41         sqlhelper.RunSQL(string.Format(@"select kpino from (select kpino from kpitree{0} union all select kpino from Kpi{0}) t where kpino not in (select isnull(KpiParentNo,0) from (select KpiParentNo from kpitree{0} union all select KpiParentNo from Kpi{0}) t)", businessno), ref ds); 42       } 43       else 44       { 45         string endKpiNo = RecursionEndKpiNo(dt2, kpino).Trim(','); 46         endKpiNo = endKpiNo == "" ? "0" : endKpiNo; 47         string kpinos = string.Empty; 48         foreach (string str in endKpiNo.Split(',')) { kpinos += "'" + str + "',"; } 49         kpinos = kpinos.Trim(','); 50         sqlhelper.RunSQL(string.Format(@"select kpino from (select kpino from kpitree{0} union all select kpino from Kpi{0}) t where kpino not in (select isnull(KpiParentNo,0) from (select KpiParentNo from kpitree{0} union all select KpiParentNo from Kpi{0}) t) and kpino in ({1})", businessno, kpinos), ref ds); 51       } 52       dt = ds.Tables[0]; 53     } 54     //3.将查到的末端子节点编号的所有父节点编号也查出来,拼接起来,就知道了table的每行的列数 55     foreach (DataRow row in dt.Rows) 56     { 57       row["kpino"] = Recursion(dt2, row["kpino"]); 58     } 59     //4.对编号进行排序,这样可以把每列下的相同行的节点编号集中在一起,方便后面的合并单元格 60     var drArray = dt.Select("1=1", "kpino"); 61     //5.限制输出kpino之前的父节点信息 62     foreach (DataRow row in drArray) 63     { 64       int index = row["kpino"].ToString().IndexOf(kpino); 65       if (index > -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 < drArray.Length; i++) 74     { 75       DataRow row = drArray[i]; 76       sbJson.Append("<tr>"); 77       var kpinoArray = row["kpino"].ToString().Trim(',').Split(','); 78       int kpinoArrayLenth = kpinoArray.Length; 79       for (int j = 0; j < kpinoArrayLenth; j++) 80       { 81         string str = kpinoArray[j]; 82         if (str != "0") 83         { 84           var dr = dt2.Select("kpino='" + str + "'"); 85           //合并每列相同行的单元格 86           if (dr.Length > 0 && !EqualUpColumnValue(i, j, drArray)) 87           { 88             double kpiWeight = GetKpiWeight(dt2, str); 89             double kpiValue = GetKpiValue(dt2, str); 90             string kpiValueStr = string.IsNullOrEmpty(tenderno) ? "" : "[" + (kpiValue * kpiWeight).ToString("0.00") + "]"; 91             string kpiDes = GetKpiDes(dt2, str); 92             sbJson.Append(string.Format("<td rowspan='{0}'>{1}({2}%){3}{4}</td>", GetColspan(i, j, drArray), dr[0]["kpiname"], (kpiWeight * 100).ToString("0.00"), kpiValueStr, kpiDes)); 93           } 94         } 95       } 96       //拼接空白的列 97       for (int j = 0; j < maxCount - kpinoArrayLenth; j++) 98       { 99         sbJson.Append("<td></td>");100       }101       sbJson.Append("</tr>");102     }103     return "<table id='kpitable' border='1px'>" + sbJson.ToString() + "</table>";104   }105 106   private string RecursionEndKpiNo(DataTable dt, object parentId)107   {108     StringBuilder sbJson = new StringBuilder();109 110     DataRow[] rows = dt.Select(string.Format("KpiParentNo = '" + parentId + "'"));111     if (rows.Length > 0)112     {113       foreach (DataRow row in rows)114       {115         string str = RecursionEndKpiNo(dt, row["kpino"]);116         sbJson.Append("" + row["kpino"] + "," + str);117       }118     }119     return sbJson.ToString();120   }121   private bool IsChild(DataTable dt, string parentId)122   {123     DataRow[] rows = dt.Select(string.Format("KpiParentNo = '" + parentId + "'"));124     if (rows.Length > 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 < drArray.Length - 1)197     {198       string[] kpinoArray2 = drArray[rowIndex + 1]["kpino"].ToString().Trim(',').Split(',');199       if (kpinoArray2.Length > 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 += "<table id='kpitabledes'>";265         //des += "<tr><td>计算方法:</td><td>" + KpiMethod   +"</td></tr>";266         //des += "<tr><td>采样类别:</td><td>" + KpiSampleType + "</td></tr>";267         //des += "<tr><td>评价标准:</td><td>" + KpiRule + "</td></tr>";268         //des += "<tr><td>规范要点:</td><td>" + KpiCriterion + "</td></tr>";269         //des += "<tr><td>测区规则:</td><td>" + KpiAreaRule + "</td></tr>";270         //des += "<tr><td>测点规则:</td><td>" + KpiSampleRule + "</td></tr>";271         //des += "</table>";272       }273                   274       //换行样式        275       //if (!string.IsNullOrEmpty(KpiMethod)) { des += "<br />计算方法:" + KpiMethod; }276       //if (!string.IsNullOrEmpty(KpiSampleType)) { des += "<br />采样类别:" + KpiSampleType; }277       //if (!string.IsNullOrEmpty(KpiRule)) { des += "<br />评价标准:" + KpiRule; }278       //if (!string.IsNullOrEmpty(KpiCriterion)) { des += "<br />规范要点:" + KpiCriterion; }279       //if (!string.IsNullOrEmpty(KpiAreaRule)) { des += "<br />测区规则:" + KpiAreaRule; }280       //if (!string.IsNullOrEmpty(KpiSampleRule)) { des += "<br />测点规则:" + KpiSampleRule; } 281     }282     return des;283   }284 285 286 287   288 }

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：议：如何将树形菜单形式的数据转化成HTML的二维表（相同内容需合并单元格）

关键词：HTML