private void SortRecursiveTable(ref string output, ref MainDataSet.ArticleDataTable input, Guid?parentArticleId) { OrderedEnumerableRowCollection <MainDataSet.ArticleRow> query = null; if (parentArticleId.HasValue) { query = input.Where(w => w.IsParentArticleGuidNull() ? false : w.ParentArticleGuid.Equals(parentArticleId.Value)).OrderBy(o => o.Subject); } else { query = input.Where(w => w.IsParentArticleGuidNull()).OrderBy(o => o.Subject); } int cnt = query.Count(); if (cnt > 0) { output += "<ul>"; } foreach (MainDataSet.ArticleRow row in query) { output += string.Format(CultureInfo.CurrentCulture, "<li><a style=\"vertical-align:top\" href=\"Default.aspx?i={0}&t={1}\">{2}</a>", this.InstanceGuid.ToString("N"), row.ArticleGuid.ToString("N"), row.Subject); SortRecursiveTable(ref output, ref input, row.ArticleGuid); } if (cnt > 0) { output += "</ul>"; } }
/// <summary> /// 删除一篇文章 /// </summary> /// <param name="wz"></param> public static void DeleteWz(ScienceResearchDataSetNew.文章Row wz) { if (MessageBox.Show("确认删除该文章及其所有关键词和语段", "重要", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { //删除所有关键词 List <ScienceResearchDataSetNew.文章_关键词Row> wz_gjc_list = GetWzGjc(wz); foreach (ScienceResearchDataSetNew.文章_关键词Row wz_gjc in wz_gjc_list) { wz_gjc.Delete(); } MainWindow.wz_gjc_ta.Update(MainWindow.wz_gjc_dt); //删除所有语段 OrderedEnumerableRowCollection <ScienceResearchDataSetNew.语段Row> ydCollection = GetYd(wz); foreach (ScienceResearchDataSetNew.语段Row yd in ydCollection) { DeleteYd(yd); } //删除该文章 wz.Delete(); MainWindow.wz_ta.Update(MainWindow.wz_dt); } else { return; } }
private static void DispTable(OrderedEnumerableRowCollection <DataRow> list) { foreach (var item in list) { Console.WriteLine("{0,-15} \t{1,-15} \t{2,-15}", item.Field <Int32>("Id"), item.Field <Int32>("ownerId"), item.Field <String>("key")); } }
private void ProcessDataChitiet(ref DataTable m_dtReport) { try { var lstAdded = new List <DataRow>(); int startDate = Utility.Int32Dbnull(Utility.GetYYYYMMDD(dtFromDate.Value), 0); int EndDate = Utility.Int32Dbnull(Utility.GetYYYYMMDD(dtToDate.Value), 0); int iFound = 0; int idx = 0; foreach (DataRow dr in m_dtReport.Rows) { DateTime _ngaybiendong = Convert.ToDateTime(dr[TBiendongThuoc.Columns.NgayBiendong]); long _myId = Utility.Int64Dbnull(dr[TBiendongThuoc.Columns.IdBiendong], 0); iFound++; if (iFound == 1) //Tính tồn cuối dòng đầu tiên tìm thấy { dr["Toncuoi"] = Utility.Int32Dbnull(dr["Tondau"], 0) + Utility.Int32Dbnull(dr["SoLuongNhap"], 0) - Utility.Int32Dbnull(dr["SoLuongXuat"], 0); } else { OrderedEnumerableRowCollection <DataRow> q = from p in m_dtReport.AsEnumerable() where Convert.ToDateTime( p[TBiendongThuoc.Columns.NgayBiendong]) <= _ngaybiendong && Utility.Int64Dbnull( p[TBiendongThuoc.Columns.IdBiendong], 0) != _myId && Utility.Int32Dbnull(p["processed"], 0) > 0 orderby Utility.Int32Dbnull(p["processed"], 0) descending //Convert.ToDateTime(p[TBiendongThuoc.Columns.NgayBiendong]) descending select p; if (q.Count() > 0) { DataRow drPrevious = q.FirstOrDefault(); dr["Tondau"] = Utility.Int32Dbnull(drPrevious["Toncuoi"], 0); dr["Toncuoi"] = Utility.Int32Dbnull(dr["Tondau"], 0) + Utility.Int32Dbnull(dr["SoLuongNhap"], 0) - Utility.Int32Dbnull(dr["SoLuongXuat"], 0); } } idx++; dr["processed"] = idx; m_dtReport.AcceptChanges(); } } catch { } }
/// <summary> /// Make ea xml from data table. The xml is ready for out put by 'repository.RunModelSearch("", "", "", xml);' /// If DataTable is empty it returns the empty EA xml string. /// </summary> /// <param name="dt"></param> /// <returns></returns> public static string MakeXmlFromDataTable(DataTable dt) { if (dt == null) { return(MakeEmptyXml()); } // Make EA xml OrderedEnumerableRowCollection <DataRow> rowsDt = from row in dt.AsEnumerable() orderby row.Field <string>(dt.Columns[0].Caption) select row; return(MakeXml(dt, rowsDt)); }
/// <summary> /// Test Query to show making EA xml from a Data table by using MakeXml. It queries the data table, orders the content according to Name columen and outputs it in EA xml format /// </summary> /// <param name="dt"></param> /// <returns></returns> public static string QueryAndMakeXmlFromTable(DataTable dt) { try { // Make a LINQ query (WHERE, JOIN, ORDER,) OrderedEnumerableRowCollection <DataRow> rows = from row in dt.AsEnumerable() orderby row.Field <string>("Name") descending select row; return(Xml.MakeXml(dt, rows)); } catch (Exception e) { MessageBox.Show($@"{e}", @"Error LINQ query Test query to show Table to EA xml format"); return(""); } }
private void SortRecursiveTable(ref MainDataSet.ArticleDataTable output, ref MainDataSet.ArticleDataTable input, Guid?parentArticleId) { OrderedEnumerableRowCollection <MainDataSet.ArticleRow> query = null; if (parentArticleId.HasValue) { query = input.Where(w => w.IsParentArticleGuidNull() ? false : w.ParentArticleGuid.Equals(parentArticleId.Value)).OrderBy(o => o.Subject); } else { query = input.Where(w => w.IsParentArticleGuidNull()).OrderBy(o => o.Subject); } foreach (MainDataSet.ArticleRow row in query) { output.ImportRow(row); SortRecursiveTable(ref output, ref input, row.ArticleGuid); } }
public string[] GetPermissionsList() { string[] result; this.permissionsTableAdapter.Fill(this.detroit.Permissions); OrderedEnumerableRowCollection <Detroit.PermissionsRow> rowCollection = this.detroit.Permissions.OrderBy(p => p.PermissionKey); result = new string[this.detroit.Permissions.Rows.Count]; int i = 0; foreach (DataRow row in rowCollection) //this.detroit.Permissions.Rows) { result[i] = row["PermissionKey"].ToString(); i++; } result.OrderBy(p => p.ToString()); return(result); }
/// <summary> /// Make EA xml from a DataTable (for column names) and the ordered Enumeration provided by LINQ. Set the Captions in DataTable to ensure column names. /// /// </summary> /// <param name="dt"></param> /// <param name="rows"></param> /// <returns></returns> public static string MakeXml(DataTable dt, OrderedEnumerableRowCollection <DataRow> rows) { XElement xFields = new XElement("Fields"); foreach (DataColumn col in dt.Columns) { XElement xField = new XElement("Field"); xField.Add(new XAttribute("name", col.Caption)); xFields.Add(xField); } try { XElement xRows = new XElement("Rows"); foreach (var row in rows) { XElement xRow = new XElement("Row"); int i = 0; foreach (DataColumn col in dt.Columns) { XElement xField = new XElement("Field"); xField.Add(new XAttribute("value", row[i].ToString())); xField.Add(new XAttribute("name", col.Caption)); xRow.Add(xField); i = i + 1; } xRows.Add(xRow); } XElement xDoc = new XElement("ReportViewData"); xDoc.Add(xFields); xDoc.Add(xRows); return(xDoc.ToString()); } catch (Exception e) { MessageBox.Show($"{e}", "Error enumerating through LINQ query"); return(""); } }
/// <summary> /// Called when user makes a selection in the menu. /// This is your main exit point to the rest of your Add-in /// </summary> /// <param name="repository">the repository</param> /// <param name="location">the location of the menu</param> /// <param name="menuName">the name of the menu</param> /// <param name="itemName">the name of the selected menu item</param> public override void EA_MenuClick(EA.Repository repository, string location, string menuName, string itemName) { string xml; DataTable dt; // for LINQ to SQL IDataProvider provider; // the provider to connect to database like Access, .. string connectionString; // The connection string to connect to database string parametersToPassToQuery; string linqQueryFileName; string linqQueryFilePath; bool result; LinqPad linqPad; DataTable dtHtml; switch (itemName) { // user has clicked the menuHello menu option case MenuHello: this.SayHello(); break; // user has clicked the menuGoodbye menu option case MenuGoodbye: this.SayGoodbye(); break; case MenuOpenProperties: this.TestPropertiesDialog(repository); break; // Test the Search and output the results to EA Search Window case MenuRunDemoSearch: // 1. Collect data dt = SetTable(); // 2. Order, Filter, Join, Format to XML xml = QueryAndMakeXmlFromTable(dt); // 3. Out put to EA repository.RunModelSearch("", "", "", xml); break; case MenuRunDemoPackageContent: // 1. Collect data into a data table dt = SetTableFromContext(repository); // 2. Order, Filter, Join, Format to XML xml = QueryAndMakeXmlFromTable(dt); // 3. Out put to EA repository.RunModelSearch("", "", "", xml); break; // Example to run SQL, convert to DataTable and output in EA Search Window case MenuRunDemoSqlToDataTable: // 1. Run SQL string sql = "select ea_guid AS CLASSGUID, object_type AS CLASSTYPE, name, stereotype, object_type from t_object order by name"; xml = repository.SQLQuery(sql); // 2. Convert to DataTable dt = Xml.MakeDataTableFromSqlXml(xml); // 2. Order, Filter, Join, Format to XML xml = QueryAndMakeXmlFromTable(dt); // 3. Out put to EA repository.RunModelSearch("", "", "", xml); break; // Read connection string from EA and try an ADODB Connection // Copy connection string to clipboard case MenuShowConnectionString: string eaConnectionString = repository.ConnectionString; if (eaConnectionString != null) { connectionString = LinqUtil.GetConnectionString(repository, out provider); string lConnectionString = $"{eaConnectionString}\r\n\r\nProvider for Linq for SQL:\r\n'{provider}\r\n{connectionString}"; Clipboard.SetText(lConnectionString); MessageBox.Show($@"{lConnectionString}", @"Connection string (EA+LINQ + SQL) copied to clipboard"); if (connectionString == "") { return; } ADODB.Connection conn = new ADODB.Connection(); try { conn.Open(connectionString, "", "", -1); // connection Open synchronously //conn.Open(connectionString, "", "", -1); // connection Open synchronously MessageBox.Show($@"EA ConnectionString: '{eaConnectionString}' ConnectionString: - '{connectionString}' Provider: - '{provider}' Mode: - '{conn.Mode}' State: - '{conn.State}'", @"ODBC Connection established "); } catch (Exception e) { MessageBox.Show($@"EA ConnectionString: '{eaConnectionString}' ConnectionString: - '{connectionString}' Mode: - '{conn.Mode}' State: - '{conn.State}' { e}", @"ODBC Connection error "); } } break; // Basis LINQ to SQL example case MenuShowRunLinq2Db: // get connection string of repository connectionString = LinqUtil.GetConnectionString(repository, out provider); // Run LINQ query to dataTable dt = LinqUtil.RunLinq2Db(provider, connectionString); // Make EA xml OrderedEnumerableRowCollection <DataRow> rows = from row in dt.AsEnumerable() orderby row.Field <string>(dt.Columns[0].Caption) select row; xml = Xml.MakeXml(dt, rows); // Output to EA repository.RunModelSearch("", "", "", xml); break; // Advanced LINQ to SQL example case MenuShowRunLinq2DbAdvanced: // get connection string of repository connectionString = LinqUtil.GetConnectionString(repository, out provider); // Run LINQ query to dataTable dt = LinqUtil.RunLinq2DbAdvanced(provider, connectionString); // Make EA xml xml = Xml.MakeXmlFromDataTable(dt); // Output to EA repository.RunModelSearch("", "", "", xml); break; // run LINQPad query to HTML,csv, text (uses lprun.exe) // - !!!!You need a LINQPad license to run!!!!!! // - lprun installed at standard location (c:\Program Files (x86)\LINQPad5\lprun.exe) // - output to 'c:\temp\EaBasicQuery.html' // - EA standard installation (used for EAExample database) case MenuShowRunLinqPadToHtml: // Run query with lprun.exe parametersToPassToQuery = @"""Test query EaBasicQuery.linq"""; linqQueryFileName = "EaBasicQuery.linq"; linqQueryFilePath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\" + linqQueryFileName; linqPad = new LinqPad(repository); // Output as html with read back table and out put to EA Search Window result = linqPad.Run(linqQueryFilePath, @"html", parametersToPassToQuery); if (!result) { return; } dtHtml = linqPad.ReadHtml(); // Make EA xml xml = Xml.MakeXmlFromDataTable(dtHtml); // Output to EA repository.RunModelSearch("", "", "", xml); linqPad.Show(); // csv result = linqPad.Run(linqQueryFilePath, @"csv", parametersToPassToQuery); if (!result) { return; } linqPad.Show(); // text result = linqPad.Run(linqQueryFilePath, @"text", parametersToPassToQuery); if (!result) { return; } linqPad.Show(); break; // Run LINQPad and output EA context information case MenuShowRunLinqPadEaContext: linqQueryFileName = "TestCallLinqWithParameter.linq"; linqQueryFilePath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\" + linqQueryFileName; linqPad = new LinqPad(repository); // Output as html with read back table and out put to EA Search Window result = linqPad.Run(linqQueryFilePath, @"html", linqPad.GetArg(repository, "mySearchTerm")); if (!result) { return; } dtHtml = linqPad.ReadHtml(); // Make EA xml xml = Xml.MakeXmlFromDataTable(dtHtml); // Output to EA repository.RunModelSearch("", "", "", xml); linqPad.Show(); break; // run LINQ XML query for own EA queries which are stored in *.xml case MenuShowRunLinqXml: // Make DataTable with LINQ Search/Query dt = EaSearches(); // Make xml = Xml.MakeXmlFromDataTable(dt); // Output to EA repository.RunModelSearch("", "", "", xml); //linqPad.Show(); break; case MenuShowShowLinqPadConnections: linqQueryFileName = "LinqPadConnections.linq"; linqQueryFilePath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\" + linqQueryFileName; linqPad = new LinqPad(repository); // Output as html with read back table and out put to EA Search Window, don't use a connection result = linqPad.Run(linqQueryFilePath, @"html", linqPad.GetArg(repository, ""), noConnection: true); if (!result) { return; } dtHtml = linqPad.ReadHtml(); // Make EA xml xml = Xml.MakeXmlFromDataTable(dtHtml); // Output to EA repository.RunModelSearch("", "", "", xml); linqPad.Show(); break; // run LINQ XML query for own EA queries which are stored in *.xml case MenuAbout: // get product version Assembly assembly = Assembly.GetExecutingAssembly(); FileVersionInfo fileVersionInfo = FileVersionInfo.GetVersionInfo(assembly.Location); // Get file-version of dll string pathRoot = Assembly.GetExecutingAssembly().Location; pathRoot = Path.GetDirectoryName(pathRoot); string productVersion = $"{fileVersionInfo.ProductVersion}{Environment.NewLine}"; string pathAddInSimpleVersion = Path.Combine(new[] { pathRoot, "AddInSimple.dll" }); string pathHoLinqToSql = Path.Combine(new[] { pathRoot, "hoLinqToSql.dll" }); string pathLinq2Db = Path.Combine(new[] { pathRoot, "linq2db.dll" }); MessageBox.Show($@"Product version: {productVersion} AddInSimple.dll {FileVersionInfo.GetVersionInfo(pathAddInSimpleVersion).FileVersion} hoLinqToSql.dll {FileVersionInfo.GetVersionInfo(pathHoLinqToSql).FileVersion} linq2db.dll {FileVersionInfo.GetVersionInfo(pathLinq2Db).FileVersion} hoTools [email protected] +49 172 51 79 16 7 ", @"AddInSimple, the example Add-In"); break; } }
private void ProcessDataThethuoc(ref DataTable m_dtReport) { DataTable dtTemp = m_dtReport.Clone(); try { var lstAdded = new List <DataRow>(); int startDate = Utility.Int32Dbnull(Utility.GetYYYYMMDD(dtFromDate.Value), 0); int EndDate = Utility.Int32Dbnull(Utility.GetYYYYMMDD(dtToDate.Value), 0); if (m_dtReport.Rows.Count == 1 && Utility.sDbnull(m_dtReport.Rows[0]["YYYYMMDD"], "") == "") { m_dtReport.Rows[0]["YYYYMMDD"] = startDate.ToString(); m_dtReport.Rows[0][TBiendongThuoc.Columns.NgayBiendong] = dtFromDate.Value.Date.ToString("dd/MM/yyyy"); } int iFound = 0; DataRow rowdata = m_dtReport.Rows[0]; DateTime _dtmStartDate = dtFromDate.Value.Date;// Utility.FromYYYYMMDD2Datetime(startDate.ToString()); if (THU_VIEN_CHUNG.Laygiatrithamsohethong("THUOC_THETHUOC_TUDONGTHEMDULIEU_TUONGLAI", "0", false) == "1") { while (_dtmStartDate <= dtToDate.Value.Date) { DataRow[] arrDr = m_dtReport.Select("YYYYMMDD=" + Utility.GetYYYYMMDD(_dtmStartDate)); if (arrDr.Length > 0) { dtTemp.ImportRow(arrDr[0]); } else { DataRow newDr = dtTemp.NewRow(); Utility.CopyData(rowdata, ref newDr); newDr["YYYYMMDD"] = Utility.GetYYYYMMDD(_dtmStartDate); newDr[TBiendongThuoc.Columns.NgayBiendong] = _dtmStartDate.ToString("dd/MM/yyyy"); newDr["Xuat"] = 0; newDr["Tondau"] = 0; newDr["Toncuoi"] = 0; newDr["Nhap"] = 0; dtTemp.Rows.Add(newDr); } _dtmStartDate = _dtmStartDate.AddDays(1); } } else { dtTemp = m_dtReport.Copy(); } _dtmStartDate = dtFromDate.Value.Date; while (_dtmStartDate <= dtToDate.Value.Date) { DataRow[] arrDr = dtTemp.Select("YYYYMMDD=" + Utility.GetYYYYMMDD(_dtmStartDate)); if (arrDr.Length > 0) { iFound++; if (iFound == 1) //Tính tồn cuối dòng đầu tiên tìm thấy { arrDr[0]["Toncuoi"] = Utility.Int32Dbnull(arrDr[0]["Tondau"], 0) + Utility.Int32Dbnull(arrDr[0]["Nhap"], 0) - Utility.Int32Dbnull(arrDr[0]["Xuat"], 0); } else { OrderedEnumerableRowCollection <DataRow> q = from p in dtTemp.AsEnumerable() where Utility.Int32Dbnull(p["YYYYMMDD"], 0) < Utility.Int32Dbnull(Utility.GetYYYYMMDD(_dtmStartDate)) orderby p["YYYYMMDD"] descending select p; if (q.Count() > 0) { DataRow drPrevious = q.FirstOrDefault(); arrDr[0]["Tondau"] = Utility.Int32Dbnull(drPrevious["Toncuoi"], 0); arrDr[0]["Toncuoi"] = Utility.Int32Dbnull(arrDr[0]["Tondau"], 0) + Utility.Int32Dbnull(arrDr[0]["Nhap"], 0) - Utility.Int32Dbnull(arrDr[0]["Xuat"], 0); } } dtTemp.AcceptChanges(); } else { } _dtmStartDate = _dtmStartDate.AddDays(1); } } catch (Exception ex) { //Utility.CatchException(ex); } finally{ m_dtReport = dtTemp.Copy(); } }
public int GetCurves(string primID, int crvSeqNo, string worklistID, int worklistNumber) { ResultDataSet.Tables.Clear(); OrderedEnumerableRowCollection <DataRow> selectedRows = null; if (crvSeqNo > 0) { //var condition = new ParadoxCondition.Compare(ParadoxCompareOperator.Equal, mainTestID, 8, 0); //QueryArgs q = new QueryArgs("HRRuns", condition, "CrvSeqNum", ""); //return GetSimpleRowInfos(q, false); } if (!String.IsNullOrEmpty(primID) && worklistNumber > 0) { selectedRows = from qs in _dataSet.Tables["HRRuns"].AsEnumerable() where qs.Field <string>("PrimID") == primID && qs.Field <int>("WrkLstNum") == worklistNumber orderby qs.Field <int>("ChnlNum") ascending select qs; } if (!String.IsNullOrEmpty(worklistID) && worklistNumber > 0) { selectedRows = from qs in _dataSet.Tables["HRRuns"].AsEnumerable() where qs.Field <int>("WrkLstNum") == worklistNumber && qs.Field <string>("WrkLstID") == worklistID orderby qs.Field <int>("ChnlNum") ascending select qs; } if (selectedRows != null && selectedRows.Count() > 0) { // 创建结果报告集 DataTable reportTable = CreateReportTable(); ResultDataSet.Tables.Add(reportTable); // 创建病人信息结果集 DataTable patientTable = CreatePatientTable(); ResultDataSet.Tables.Add(patientTable); foreach (var sr in selectedRows) { // 创建曲线点信息 int lblChnNum = sr.Field <int>("ChnlNum") + 1; string label = "Channel " + lblChnNum.ToString(); AggRamCurve crv = new AggRamCurve( label, (int)sr["InitialMax"], (int)sr["InitialMin"], (int)sr["ScaleSet1Rd"], (int)sr["MaxPCPoint"], (double)sr["ScaleSetResult"], (int)sr["DataPoints"], (byte[])sr["Data"]); // Add Curve if (OnAddCurve != null) { OnAddCurve(label, crv); //OnAddCurve(label, CreateCurve((int)rdr["DataPoints"], (byte[])rdr["Data"])); } int selectedProcTag = sr.Field <int>("ProcTag"); var procs = from qs in _dataSet.Tables["HRTestParms"].AsEnumerable() where qs.Field <int>("ProcTag") == selectedProcTag select qs; // Add Report Row DataRow reportRow = reportTable.NewRow(); reportRow["ChnlNum"] = lblChnNum; reportRow["StartTime"] = sr.Field <DateTime>("StartTime"); if (procs.Count() > 0) { var procInfo = procs.First(); reportRow["ProcID"] = procInfo.Field <string>("ProcID"); reportRow["Abbrev"] = procInfo.Field <string>("Abbrev"); } reportRow["Unit"] = sr.Field <string>("Unit"); reportRow["Conc"] = sr.Field <double>("Conc"); reportRow["PRP"] = FloatPS(crv.PRP, 3); reportRow["PPP"] = FloatPS(crv.PPP, 3); reportRow["MaxPercent"] = FloatPS(crv.MaxPercent * 100, 1); reportRow["MaxPCTime"] = FloatPS((double)sr.Field <int>("MaxPCTime") / 1000.0, 0); reportRow["LagTime"] = FloatPS((double)sr.Field <int>("LagTime") / 1000.0, 1); reportRow["Slope"] = FloatPS(sr.Field <double>("Slope"), 1); reportTable.Rows.Add(reportRow); // Add Patient Row DataRow row = patientTable.NewRow(); row[0] = lblChnNum; string selectedPrimID = sr.Field <string>("PrimID"); var patientInfos = from qs in _dataSet.Tables["Patients"].AsEnumerable() where qs.Field <string>("PrimID") == selectedPrimID select qs; if (patientInfos.Count() > 0) { var patientInfo = patientInfos.First(); object[] array = (patientInfo.ItemArray.Take(patientTable.Columns.Count - 1)).ToArray(); for (int i = 0; i < patientTable.Columns.Count - 1; i++) { row[i + 1] = array[i]; } } else { row[1] = selectedPrimID; } patientTable.Rows.Add(row); } return(selectedRows.Count()); } return(0); }
void ExportAll(object filePath) { this.Invoke(new Action(() => { })); lock (obj) { DataTable tables_1 = SqlHelper_1.GetTables(); DataTable tables_2 = SqlHelper_2.GetTables(); OrderedEnumerableRowCollection <DataRow> dataRows_1 = tables_1.AsEnumerable().OrderBy(a => a.Field <string>("name")); OrderedEnumerableRowCollection <DataRow> dataRows_2 = tables_2.AsEnumerable().OrderBy(a => a.Field <string>("name")); //创建Excel文件的对象 IWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); #region 设置表头样式 //创建样式对象 ICellStyle style = book.CreateCellStyle(); //创建一个字体样式对象 IFont font = book.CreateFont(); font.Boldweight = short.MaxValue; font.FontHeightInPoints = 16; //水平居中 style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //垂直居中 style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CenterSelection; //将字体样式赋给样式对象 style.SetFont(font); #endregion #region 设置内容样式 //创建样式对象 ICellStyle style2 = book.CreateCellStyle(); //水平居中 style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //垂直居中 style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CenterSelection; #endregion foreach (DataRow row in dataRows_1) { string tablename = row["name"].ToString().Trim(); DataTable tableInfo_1 = SqlHelper_1.GetTableInfo(tablename); DataTable tableInfo_2 = SqlHelper_2.GetTableInfo(tablename); IEnumerable <DataRow> query = tableInfo_1.AsEnumerable().Except(tableInfo_2.AsEnumerable(), DataRowComparer.Default); if (query.Count() > 0) { DataTable changesTable = query.CopyToDataTable(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(tablename); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); int c = 0; foreach (DataColumn item in changesTable.Columns) { //设置宽度 sheet1.SetColumnWidth(c, 40 * 150); ICell cell = row1.CreateCell(c); cell.SetCellValue(item.Caption); cell.CellStyle = style; c++; } //将数据逐步写入sheet1各个行 int k = 0; foreach (DataRow item in changesTable.Rows) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(k + 1); for (int i = 0; i < changesTable.Columns.Count; i++) { ICell cell = rowtemp.CreateCell(i); cell.SetCellValue(item[i].ToString()); cell.CellStyle = style2; } k++; } } } //写入文件 FileStream xlsfile = new FileStream(filePath.ToString(), FileMode.Create); book.Write(xlsfile); xlsfile.Dispose(); this.Invoke(new Action(() => { MessageBox.Show("导出成功,文件存放于桌面", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.None); })); isFinish = true; } }
internal static string GetHtmlTableString(string[] ExtendedFields, Dictionary <string, string> content, List <string> subtotal, List <string> subavg, List <string> SubCount, List <string> Count, DataTable dt, List <string> sum, List <string> avg, List <string> group, string rptTitle = null, string comment = null, string subcountLabel = "Sub Count", List <string> sortonCols = null, List <bool> isAscending = null, List <int> seq = null, List <string> hideRows = null) { bool showChangeOnly = ExtendedFields[CUSTOMRP.Model.REPORT.EXTENDFIELDs.ReportType] == "1"; bool hideCriteria = ExtendedFields[CUSTOMRP.Model.REPORT.EXTENDFIELDs.HideCriteria] == "1"; StringBuilder sb = new StringBuilder(); int columnsCount = dt.Columns.Count; if (rptTitle != null) { sb.AppendFormat("<div data-type=\"rptTitle\">{0}</div>", rptTitle); } if (comment != null) { sb.Append(comment); } //start set value to each cell. int[] Subsum_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(subtotal.ToArray(), dt.Columns); int[] Subavg_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(subavg.ToArray(), dt.Columns); int[] Subcount_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(SubCount.ToArray(), dt.Columns); int[] sum_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(sum.ToArray(), dt.Columns); int[] avg_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(avg.ToArray(), dt.Columns); int[] count_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(Count.ToArray(), dt.Columns); int[] groupIndex = WebHelper.getColumnIndexByColumnName(group.ToArray(), dt.Columns); int[] sortIndex = WebHelper.getColumnIndexByColumnName(sortonCols.ToArray(), dt.Columns); int[] hideIndex = WebHelper.getColumnIndexByColumnName(hideRows.ToArray(), dt.Columns); Type[] numeric = new Type[3] { typeof(Double), typeof(Decimal), typeof(Int32) }; subTotal[] subTotalInfo = new subTotal[Subsum_Index.Length]; subAvg[] subAvginfo = new subAvg[Subavg_Index.Length]; subCount[] subcountinfo = new subCount[Subcount_Index.Length]; sb.Append("<table data-tblname=\"custom\">"); sb.AppendLine("<tr data-rowtype=\"header\">"); for (int m = 0; m < columnsCount; m++) { if (hideIndex.Contains(m)) { continue; } DataColumn dc = dt.Columns[m]; //v1.0.0 - Cheong - 2016/03/21 - Align right for column headers of numeric fields if ((dt.Columns[m].DataType == typeof(Double)) || (dt.Columns[m].DataType == typeof(Decimal)) || (dt.Columns[m].DataType == typeof(Int32))) { sb.Append("<th style=\"text-align: right\">" + content[dc.ColumnName] + "</th>"); } else { //v1.0.0 - Cheong - 2015/05/27 - use DisplayName instead //sb.Append("<th>" + dc.ColumnName + "</th>"); sb.Append("<th>" + content[dc.ColumnName] + "</th>"); } } sb.Append("</tr>"); for (int x = 0; x < subTotalInfo.Length; x++) { subTotalInfo[x].ColumnIndex = Subsum_Index[x]; } for (int y = 0; y < subAvginfo.Length; y++) { subAvginfo[y].ColumnIndex = Subavg_Index[y]; } OrderedEnumerableRowCollection <DataRow> tmp = null; //bool grouped = false; // 1. Sort columns which belong to a group (only if the user specified grouping). if (sortIndex.Length > 0) { DateTime tpdatetime; //grouped = true; if (numeric.Contains(dt.Columns[sortIndex[0]].DataType)) { tmp = isAscending[0] ? dt.AsEnumerable().OrderBy(f => Convert.ToDouble(f[sortIndex[0]])) : dt.AsEnumerable().OrderByDescending(f => Convert.ToDouble(f[sortIndex[0]])); } else if (dt.Columns[sortIndex[0]].DataType == typeof(DateTime)) { tmp = isAscending[0] ? dt.AsEnumerable().OrderBy(f => DateTime.TryParse(Convert.ToString(f[sortIndex[0]]), out tpdatetime) ? tpdatetime : DateTime.MinValue) : dt.AsEnumerable().OrderByDescending(f => DateTime.TryParse(Convert.ToString(f[sortIndex[0]]), out tpdatetime) ? tpdatetime : DateTime.MinValue); } else { tmp = isAscending[0] ? dt.AsEnumerable().OrderBy(f => Convert.ToString(f[sortIndex[0]])) : dt.AsEnumerable().OrderByDescending(f => Convert.ToString(f[sortIndex[0]])); } for (int z = 1; z < sortIndex.Count(); z++) { if (isAscending[z]) { var thisI = z; tmp = numeric.Contains(dt.Columns[sortIndex[thisI]].DataType) ? tmp.ThenBy(f => f[sortIndex[thisI]] is DBNull ? 0d : Convert.ToDouble(f[sortIndex[thisI]])) : (dt.Columns[sortIndex[thisI]].DataType == typeof(DateTime) ? tmp.ThenBy(f => DateTime.TryParse(Convert.ToString(f[sortIndex[thisI]]), out tpdatetime) ? tpdatetime : DateTime.MinValue) : tmp.ThenBy(f => Convert.ToString(f[sortIndex[thisI]]))); } else { var thisI = z; tmp = numeric.Contains(dt.Columns[sortIndex[thisI]].DataType) ? tmp.ThenByDescending(f => f[sortIndex[thisI]] is DBNull ? 0d : Convert.ToDouble(f[sortIndex[thisI]])) : (dt.Columns[sortIndex[thisI]].DataType == typeof(DateTime) ? tmp.ThenByDescending(f => DateTime.TryParse(Convert.ToString(f[sortIndex[thisI]]), out tpdatetime) ? tpdatetime : DateTime.MinValue) : tmp.ThenByDescending(f => Convert.ToString(f[sortIndex[thisI]]))); } } //v1.8.8 Ben 2019.10.31 - CopyToDataTable Must have rows otherwise it cause error //dt = tmp.CopyToDataTable(); if (tmp.Any()) { dt = tmp.CopyToDataTable(); } } int tempRowsCount = 0; for (int i = 0; i < dt.Rows.Count; i++) { if (!showChangeOnly) { if (groupIndex.Length > 0) { #region group header bool GroupBegin = false; if (i == 0) { GroupBegin = true; } else { for (int g_i = 0; g_i < groupIndex.Count(); g_i++) { if (dt.Rows[i][groupIndex[g_i]].ToString() != dt.Rows[i - 1][groupIndex[g_i]].ToString()) { GroupBegin = true; break; } } } if (GroupBegin) { tempRowsCount = 0; if (!Convert.IsDBNull(dt.Rows[i][groupIndex[0]]) && !string.IsNullOrEmpty(Convert.ToString(dt.Rows[i][groupIndex[0]]))) { sb.Append("<tr data-rowtype=\"groupheader\">"); sb.AppendFormat("<td{0}>", dt.Columns.Count != 1 ? String.Format(" colspan=\"{0}\"", dt.Columns.Count) : String.Empty); StringBuilder sbgrouptitle = new StringBuilder(); //v1.0.0 - Cheong - 2015/05/27 - Use Displayname instead //sbgrouptitle.AppendFormat("{0} : {1}", group[0], dt.Rows[i][groupIndex[0]]); //sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], dt.Rows[i][groupIndex[0]]); if (!Convert.IsDBNull(dt.Rows[i][groupIndex[0]])) { if (dt.Columns[groupIndex[0]].DataType == typeof(DateTime)) { string l_strDateTime = ((DateTime)dt.Rows[i][groupIndex[0]]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture); l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim(); l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim(); sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], l_strDateTime); } else { sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], dt.Rows[i][groupIndex[0]]); } } else { sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], String.Empty); } for (int g_i = 1; g_i < groupIndex.Length; g_i++) { //v1.0.0 - Cheong - 2015/05/27 - Same as above comment //sbgrouptitle.AppendFormat(" {0} : {1}", group[g_i], dt.Rows[i][groupIndex[g_i]]); //sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], dt.Rows[i][groupIndex[g_i]]); if (!Convert.IsDBNull(dt.Rows[i][groupIndex[0]])) { if (dt.Columns[groupIndex[g_i]].DataType == typeof(DateTime)) { string l_strDateTime = ((DateTime)dt.Rows[i][groupIndex[g_i]]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture); l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim(); l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim(); sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], l_strDateTime); } else { sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], dt.Rows[i][groupIndex[g_i]]); } } else { sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], String.Empty); } } sb.Append(sbgrouptitle.ToString()); sb.Append("</td>"); sb.Append("</tr>"); } } #endregion } sb.Append("<tr>"); for (int j = 0; j < columnsCount; j++) { if (hideIndex.Contains(j)) { continue; } if (!Convert.IsDBNull(dt.Rows[i][j])) { if (dt.Columns[j].DataType == typeof(DateTime)) { string l_strDateTime = ((DateTime)dt.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture); l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim(); l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim(); sb.AppendFormat("<td>{0}</td>", l_strDateTime); } else if (dt.Columns[j].DataType == typeof(string)) { sb.AppendFormat("<td>{0}</td>", ((string)dt.Rows[i][j]).Replace("\r", "").Replace("\n", "<br />")); } else { sb.AppendFormat("<td>{0}</td>", dt.Rows[i][j]); } } else { sb.Append("<td></td>"); } } sb.Append("</tr>"); //weather need sub . if (groupIndex.Length > 0) { #region Grouping bool SubTotal = false; decimal dectemp; if (i == dt.Rows.Count - 1) { SubTotal = true; } else { for (int g_i = 0; g_i < groupIndex.Count(); g_i++) { if (dt.Rows[i][groupIndex[g_i]].ToString() != dt.Rows[i + 1][groupIndex[g_i]].ToString()) { SubTotal = true; break; } } } tempRowsCount++; for (int j = 0; j < subTotalInfo.Length; j++) { //v1.1.0 - Cheong - 2016/05/27 - Do not throw exception when attempt to sum a field that can have null value //subTotalInfo[j].total += !Convert.IsDBNull(dt.Rows[i][subTotalInfo[j].ColumnIndex]) ? Decimal.Parse(dt.Rows[i][subTotalInfo[j].ColumnIndex].ToString()) : 0M; if (Decimal.TryParse(dt.Rows[i][subTotalInfo[j].ColumnIndex].ToString(), out dectemp)) { subTotalInfo[j].total += dectemp; } } for (int j = 0; j < subAvginfo.Length; j++) { //v1.1.0 - Cheong - 2016/05/27 - Do not throw exception when attempt to sum a field that can have null value //subAvginfo[j].total += !Convert.IsDBNull(dt.Rows[i][subAvginfo[j].ColumnIndex]) ? Decimal.Parse(dt.Rows[i][subAvginfo[j].ColumnIndex].ToString()) : 0M; if (Decimal.TryParse(dt.Rows[i][subAvginfo[j].ColumnIndex].ToString(), out dectemp)) { subAvginfo[j].total += dectemp; } } #region sub total if (SubTotal && Subsum_Index.Count() > 0) { sb.Append("<tr>"); for (int j = 0; j < columnsCount; j++) { if (hideIndex.Contains(j)) { continue; } int array_index = Array.IndexOf(Subsum_Index, j); if (array_index >= 0) { if (j == 0) { sb.Append("<td style='font-weight:bold;'>Total: " + subTotalInfo[array_index].total.ToString() + "</td>"); } else { sb.Append("<td style='font-weight:bold;'>" + subTotalInfo[array_index].total.ToString() + "</td>"); } subTotalInfo[array_index].total = 0; } else { if (j == Subsum_Index[0] - 1 && Subsum_Index[0] != 0) { sb.Append("<td style='font-weight:bold;'>Total</td>"); } else { sb.Append("<td></td>"); } } } sb.Append("</tr>"); } #endregion #region sub count if (SubTotal && Subcount_Index.Count() > 0 && !Convert.IsDBNull(dt.Rows[i][groupIndex[0]]) && !string.IsNullOrEmpty(Convert.ToString(dt.Rows[i][groupIndex[0]]))) { sb.Append("<tr>"); for (int j = 0; j < columnsCount; j++) { if (hideIndex.Contains(j)) { continue; } int array_index = Array.IndexOf(Subcount_Index, j); if (array_index >= 0) { if (j == 0) { sb.Append("<td style='font-weight:bold;'>"); sb.Append(subcountLabel); sb.Append(": " + tempRowsCount.ToString() + "</td>"); } else { sb.Append("<td style='font-weight:bold;'>" + tempRowsCount.ToString() + "</td>"); } } else { if (j == Subcount_Index[0] - 1 && Subcount_Index[0] != 0) { sb.Append("<td style='font-weight:bold;'>"); sb.Append(subcountLabel); sb.Append("</td>"); } else { sb.Append("<td></td>"); } } } sb.Append("</tr>"); } #endregion #region sub avg if (SubTotal && Subavg_Index.Count() > 0) { sb.Append("<tr>"); for (int j = 0; j < columnsCount; j++) { if (hideIndex.Contains(j)) { continue; } int array_index = Array.IndexOf(Subavg_Index, j); if (array_index >= 0) { if (j == 0) { if (tempRowsCount > 0) { sb.Append("<td style='font-weight:bold;'>Avg: " + (subAvginfo[array_index].total / tempRowsCount).ToString("#.##") + "</td>"); } } else { if (tempRowsCount > 0) { sb.Append("<td style='font-weight:bold;'>" + (subAvginfo[array_index].total / tempRowsCount).ToString("#.##") + "</td>"); } } subAvginfo[array_index].total = 0; } else { if (j == Subavg_Index[0] - 1 && Subavg_Index[0] != 0) { sb.Append("<td style='font-weight:bold;'>Avg</td>"); } else { sb.Append("<td></td>"); } } } sb.Append("</tr>"); } #endregion #endregion } } else { //showChangeOnly //v1.2.0 Kim 2016.11.16 add grouping logic to show changed only case( hidden flag seem irrelevant) //bool hidden = false; //if (groupIndex.Length > 0 && i > 0) //{ // int sameCount = 0; // for (int g_i = 0; g_i < groupIndex.Count(); g_i++) // { // if (dt.Rows[i][groupIndex[g_i]].ToString() == dt.Rows[i - 1][groupIndex[g_i]].ToString()) // { // sameCount++; // } // } // if (sameCount == groupIndex.Length) // { // hidden = true; // } //} //v1.2.0 Kim 2016.11.16 add grouping logic to show changed only case if (groupIndex.Length > 0) { #region group header bool GroupBegin = false; if (i == 0) { GroupBegin = true; } else { for (int g_i = 0; g_i < groupIndex.Count(); g_i++) { if (dt.Rows[i][groupIndex[g_i]].ToString() != dt.Rows[i - 1][groupIndex[g_i]].ToString()) { GroupBegin = true; break; } } } if (GroupBegin) { tempRowsCount = 0; sb.Append("<tr data-rowtype=\"groupheader\">"); sb.AppendFormat("<td{0}>", dt.Columns.Count != 1 ? String.Format(" colspan=\"{0}\"", dt.Columns.Count) : String.Empty); StringBuilder sbgrouptitle = new StringBuilder(); //v1.0.0 - Cheong - 2015/05/27 - Use Displayname instead //sbgrouptitle.AppendFormat("{0} : {1}", group[0], dt.Rows[i][groupIndex[0]]); //sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], dt.Rows[i][groupIndex[0]]); if (!Convert.IsDBNull(dt.Rows[i][groupIndex[0]])) { if (dt.Columns[groupIndex[0]].DataType == typeof(DateTime)) { string l_strDateTime = ((DateTime)dt.Rows[i][groupIndex[0]]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture); l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim(); l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim(); sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], l_strDateTime); } else { sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], dt.Rows[i][groupIndex[0]]); } } else { sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], String.Empty); } for (int g_i = 1; g_i < groupIndex.Length; g_i++) { //v1.0.0 - Cheong - 2015/05/27 - Same as above comment //sbgrouptitle.AppendFormat(" {0} : {1}", group[g_i], dt.Rows[i][groupIndex[g_i]]); //sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], dt.Rows[i][groupIndex[g_i]]); if (!Convert.IsDBNull(dt.Rows[i][groupIndex[0]])) { if (dt.Columns[groupIndex[g_i]].DataType == typeof(DateTime)) { string l_strDateTime = ((DateTime)dt.Rows[i][groupIndex[g_i]]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture); l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim(); l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim(); sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], l_strDateTime); } else { sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], dt.Rows[i][groupIndex[g_i]]); } } else { sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], String.Empty); } } sb.Append(sbgrouptitle.ToString()); sb.Append("</td>"); sb.Append("</tr>"); } #endregion } sb.Append("<tr>"); for (int j = 0; j < columnsCount; j++) { if (hideIndex.Contains(j)) { continue; } if (!Convert.IsDBNull(dt.Rows[i][j])) { //v1.0.0 - Cheong - 2016/03/17 - In "Show changed data only" mode, if the first column value is different, show it. //if (((i != 0) && (dt.Rows[i][j].Equals(dt.Rows[i - 1][j]))) || hidden) //v1.2.0 Kim 2016.11.16 add grouping logic to show changed only case(hidden flag seem irrelevant) //if (((i != 0) && (dt.Rows[i][j].Equals(dt.Rows[i - 1][j]) && (dt.Rows[i][0].Equals(dt.Rows[i - 1][0])))) || hidden) if (((i != 0) && (dt.Rows[i][j].Equals(dt.Rows[i - 1][j]) && (dt.Rows[i][0].Equals(dt.Rows[i - 1][0]))))) { //v1.1.0 - Cheong - 2016/06/07 - "Show changed data only" will change text color to light grey instead of inserting blank //sb.Append("<td></td>"); if (dt.Columns[j].DataType == typeof(DateTime)) { string l_strDateTime = ((DateTime)dt.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture); l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim(); l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim(); sb.AppendFormat("<td style=\"color: #C0C0C0\">{0}</td>", l_strDateTime); } else if (dt.Columns[j].DataType == typeof(string)) { sb.AppendFormat("<td style=\"color: #C0C0C0\">{0}</td>", ((string)dt.Rows[i][j]).Replace("\r", "").Replace("\n", "<br />")); } else { sb.AppendFormat("<td style=\"color: #C0C0C0\">{0}</td>", dt.Rows[i][j]); } } else { //sb.Append("<td>" + dt.Rows[i][j].ToString() + "</td>"); if (dt.Columns[j].DataType == typeof(DateTime)) { string l_strDateTime = ((DateTime)dt.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture); l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim(); l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim(); sb.AppendFormat("<td>{0}</td>", l_strDateTime); } else if (dt.Columns[j].DataType == typeof(string)) { sb.AppendFormat("<td>{0}</td>", ((string)dt.Rows[i][j]).Replace("\r", "").Replace("\n", "<br />")); } else { sb.AppendFormat("<td>{0}</td>", dt.Rows[i][j]); } } } else { sb.Append("<td></td>"); } } sb.Append("</tr>"); //v1.2.0 Kim 2016.11.16 add grouping logic to show changed only case //weather need sub . if (groupIndex.Length > 0) { #region Grouping bool SubTotal = false; decimal dectemp; if (i == dt.Rows.Count - 1) { SubTotal = true; } else { for (int g_i = 0; g_i < groupIndex.Count(); g_i++) { if (dt.Rows[i][groupIndex[g_i]].ToString() != dt.Rows[i + 1][groupIndex[g_i]].ToString()) { SubTotal = true; break; } } } tempRowsCount++; for (int j = 0; j < subTotalInfo.Length; j++) { //v1.1.0 - Cheong - 2016/05/27 - Do not throw exception when attempt to sum a field that can have null value //subTotalInfo[j].total += !Convert.IsDBNull(dt.Rows[i][subTotalInfo[j].ColumnIndex]) ? Decimal.Parse(dt.Rows[i][subTotalInfo[j].ColumnIndex].ToString()) : 0M; if (Decimal.TryParse(dt.Rows[i][subTotalInfo[j].ColumnIndex].ToString(), out dectemp)) { subTotalInfo[j].total += dectemp; } } for (int j = 0; j < subAvginfo.Length; j++) { //v1.1.0 - Cheong - 2016/05/27 - Do not throw exception when attempt to sum a field that can have null value //subAvginfo[j].total += !Convert.IsDBNull(dt.Rows[i][subAvginfo[j].ColumnIndex]) ? Decimal.Parse(dt.Rows[i][subAvginfo[j].ColumnIndex].ToString()) : 0M; if (Decimal.TryParse(dt.Rows[i][subAvginfo[j].ColumnIndex].ToString(), out dectemp)) { subAvginfo[j].total += dectemp; } } #region sub total if (SubTotal && Subsum_Index.Count() > 0) { sb.Append("<tr>"); for (int j = 0; j < columnsCount; j++) { if (hideIndex.Contains(j)) { continue; } int array_index = Array.IndexOf(Subsum_Index, j); if (array_index >= 0) { if (j == 0) { sb.Append("<td style='font-weight:bold;'>Total: " + subTotalInfo[array_index].total.ToString() + "</td>"); } else { sb.Append("<td style='font-weight:bold;'>" + subTotalInfo[array_index].total.ToString() + "</td>"); } subTotalInfo[array_index].total = 0; } else { if (j == Subsum_Index[0] - 1 && Subsum_Index[0] != 0) { sb.Append("<td style='font-weight:bold;'>Total</td>"); } else { sb.Append("<td></td>"); } } } sb.Append("</tr>"); } #endregion #region sub count if (SubTotal && Subcount_Index.Count() > 0) { sb.Append("<tr>"); for (int j = 0; j < columnsCount; j++) { if (hideIndex.Contains(j)) { continue; } int array_index = Array.IndexOf(Subcount_Index, j); if (array_index >= 0) { if (j == 0) { sb.Append("<td style='font-weight:bold;'>Count: " + tempRowsCount.ToString() + "</td>"); } else { sb.Append("<td style='font-weight:bold;'>" + tempRowsCount.ToString() + "</td>"); } } else { if (j == Subcount_Index[0] - 1 && Subcount_Index[0] != 0) { sb.Append("<td style='font-weight:bold;'>Count</td>"); } else { sb.Append("<td></td>"); } } } sb.Append("</tr>"); } #endregion #region sub avg if (SubTotal && Subavg_Index.Count() > 0) { sb.Append("<tr>"); for (int j = 0; j < columnsCount; j++) { if (hideIndex.Contains(j)) { continue; } int array_index = Array.IndexOf(Subavg_Index, j); if (array_index >= 0) { if (j == 0) { if (tempRowsCount > 0) { sb.Append("<td style='font-weight:bold;'>Avg: " + (subAvginfo[array_index].total / tempRowsCount).ToString("#.##") + "</td>"); } } else { if (tempRowsCount > 0) { sb.Append("<td style='font-weight:bold;'>" + (subAvginfo[array_index].total / tempRowsCount).ToString("#.##") + "</td>"); } } subAvginfo[array_index].total = 0; } else { if (j == Subavg_Index[0] - 1 && Subavg_Index[0] != 0) { sb.Append("<td style='font-weight:bold;'>Avg</td>"); } else { sb.Append("<td></td>"); } } } sb.Append("</tr>"); } #endregion #endregion } } } #region rp sum,avg,count IList <decimal> sum_result = new List <decimal>(sum_Index.Length); IList <decimal> avg_result = new List <decimal>(avg_Index.Length); for (int j = 0; j < sum_Index.Length; j++) { sum_result.Add(0); } for (int j = 0; j < avg_Index.Length; j++) { avg_result.Add(0); } for (int i = 0; i < dt.Rows.Count; i++) { decimal _tmp; for (int j = 0; j < sum_Index.Length; j++) { if (Decimal.TryParse(dt.Rows[i][sum_Index[j]].ToString(), out _tmp)) { sum_result[j] += !Convert.IsDBNull(dt.Rows[i][sum_Index[j]]) ? _tmp : 0M; } } for (int j = 0; j < avg_Index.Length; j++) { if (Decimal.TryParse(dt.Rows[i][avg_Index[j]].ToString(), out _tmp)) { avg_result[j] += !Convert.IsDBNull(dt.Rows[i][avg_Index[j]]) ? _tmp : 0M; } } } #region Total if (sum_Index.Length > 0) { sb.Append("<tr data-rowtype=\"summary\">"); for (int i = 0; i < columnsCount; i++) { if (hideIndex.Contains(i)) { continue; } int contain_index = Array.IndexOf(sum_Index, i); if (contain_index >= 0) { if (sum_Index[0] == 0 && i == 0) { sb.Append("<td style='font-weight:bold;'>Report Total:" + (sum_result[0]).ToString() + "</td>"); } else { sb.Append("<td style='font-weight:bold;'>" + (sum_result[contain_index]).ToString() + "</td>"); } } else { if (sum_Index[0] != 0 && i == sum_Index[0] - 1) { sb.Append("<td style='font-weight:bold;'>Report Total</td>"); } else { sb.Append("<td></td>"); } } } sb.Append("</tr>"); } #endregion Total #region Count if (count_Index.Length > 0) { sb.Append("<tr data-rowtype=\"summary\">"); for (int i = 0; i < columnsCount; i++) { if (hideIndex.Contains(i)) { continue; } int contain_index = Array.IndexOf(count_Index, i); if (contain_index >= 0) { if (count_Index[0] == 0 && i == 0) { sb.Append("<td style='font-weight:bold;'>Report Count:" + dt.Rows.Count.ToString() + "</td>"); } else { sb.Append("<td style='font-weight:bold;'>" + dt.Rows.Count.ToString() + "</td>"); } } else { if (count_Index[0] != 0 && i == count_Index[0] - 1) { sb.Append("<td style='font-weight:bold;'>Report Count</td>"); } else { sb.Append("<td></td>"); } } } sb.Append("</tr>"); } #endregion Count #region Avg if (avg_Index.Length > 0) { sb.Append("<tr data-rowtype=\"summary\">"); for (int i = 0; i < columnsCount; i++) { if (hideIndex.Contains(i)) { continue; } int contain_index = Array.IndexOf(avg_Index, i); if (contain_index >= 0) { if (avg_Index[0] == 0 && i == 0) { if (dt.Rows.Count > 0) { sb.Append("<td style='font-weight:bold;'>Report Avg:" + (avg_result[0] / dt.Rows.Count).ToString("#.##") + "</td>"); } else { sb.Append("<td style='font-weight:bold;'>Report Avg:0</td>"); } } else { if (dt.Rows.Count > 0) { sb.Append("<td style='font-weight:bold;'>" + (avg_result[contain_index] / dt.Rows.Count).ToString("#.##") + "</td>"); } else { sb.Append("<td style='font-weight:bold;'>Report Avg:0</td>"); } } } else { if (avg_Index[0] != 0 && i == avg_Index[0] - 1) { sb.Append("<td style='font-weight:bold;'>Report Avg</td>"); } else { sb.Append("<td></td>"); } } } sb.Append("</tr>"); } #endregion Avg #endregion rp sum,avg,count sb.Append("</table>"); return(sb.ToString()); }