时间:2021-07-01 10:21:17 帮助过:30人阅读
首先来看二维表的显示效果图:
如果看到这里,你觉得这就是你想要的显示效果,或者对此比较感兴趣。请接着往下看的实现步骤:
1.取出所有的数据临时保存到DataTable中,即内存中,拼html时直接查DataTable中的数据,不用去反复读取数据库,提高效率;
2.根据节点编号获取该节点下所有的末端子节点编号,因为末端子节点的个数就决定了
的行数;3.将查到的末端子节点编号的所有父节点编号也查出来,拼接起来,就知道了
4.对节点的编号进行排序,这样可以把每列下的相同行的节点编号集中在一起,方便后面的合并单元格;
5.遍历行和列,合并每列相同行的单元格;
6.最后一步,拼接空白的列。
如下是具体代码实现过程:
输出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(""); 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("", 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("");100 }101 sbJson.Append("");102 }103 return "
- 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.限制
{1}({2}%){3}{4} |
计算方法: | " + KpiMethod +" |
采样类别: | " + KpiSampleType + " |
评价标准: | " + KpiRule + " |
规范要点: | " + KpiCriterion + " |
测区规则: | " + KpiAreaRule + " |
测点规则: | " + KpiSampleRule + " |
以及需要用到的表(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