一般做OA類管理系統,經常涉及到“組織架構”的概念,那麼像這種有上下層級關係的數據一般會做成樹形菜單的方式顯示,底層代碼必定會用到遞歸演算法。這篇隨筆的目的就是要談談除了用樹形菜單來顯示這種上下層級關係的數據,還有其他的顯示方式嗎?答案是有的,例如即將要談到的二維表顯示方式,同時也是本隨筆的核心內容。 ...
一般做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 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 else 129 { 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 else 146 { 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 else 182 { 183 return false; 184 } 185 } 186 else 187 { 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 else 206 { 207 break; 208 } 209 } 210 else 211 { 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 }View Code
以及需要用到的表(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