C# Excel search and combine

Question

Hello I have an excel file I generate through a C# program. The file is a combination of data from two database tables organized by Product Part Number and Month. I am trying to find an elegant way to combine fields by searching by Part Number and Month and adding the necessary fields together. The below orange highlighted fields are what I need to find and the yellow is what needs to be added together. Essentially take these two sections and combine them into one. If someone could point me in a good direction that would be awesome! PRODUCT COMMODITY REPORT_DATE FRUPN YYYYMM MONTHYR FRUCNT DIF REPL TARGET FRU_FF_ALL_USD DD 201309 5048998 201210 12-Oct 4076 125684 2 525000 FRU_FF_ALL_USD DD 201309 5048998 201211 12-Nov 4034 120365 0 525000 FRU_FF_ALL_USD DD 201309 5048998 201212 12-Dec 4022 124232 2 525000 FRU_FF_ALL_USD DD 201309 5048998 201301 13-Jan 4018 124381 1 525000 FRU_FF_ALL_USD DD 201309 5048998 201302 13-Feb 4026 112327 0 525000 FRU_FF_ALL_USD DD 201309 5048998 201303 13-Mar 4023 124603 4 525000 FRU_FF_ALL_USD DD 201309 5048998 201304 13-Apr 4022 120471 2 525000 FRU_FF_ALL_USD DD 201309 5048998 201305 13-May 4013 124166 0 525000 FRU_FF_ALL_USD DD 201309 5048998 201306 13-Jun 3979 118924 2 525000 FRU_FF_ALL_USD DD 201309 5048998 201307 13-Jul 3937 121816 3 525000 FRU_FF_ALL_USD DD 201309 5048998 201308 13-Aug 3928 121572 2 525000 FRU_FF_ALL_USD DD 201309 5048998 201309 13-Sep 3918 117142 3 525000 PRODUCT COMMODITY REPORT_DATE FRUPN YYYYMM MONTHYR FRUCNT DIF REPL TARGET FRU_FF_ALL_ESD DD 201309 5048998 201210 12-Oct 16456 507419 8 525000 FRU_FF_ALL_ESD DD 201309 5048998 201211 12-Nov 16426 492407 12 525000 FRU_FF_ALL_ESD DD 201309 5048998 201212 12-Dec 16499 508916 7 525000 FRU_FF_ALL_ESD DD 201309 5048998 201301 13-Jan 16612 510790 145 525000 FRU_FF_ALL_ESD DD 201309 5048998 201302 13-Feb 16501 461255 12 525000 FRU_FF_ALL_ESD DD 201309 5048998 201303 13-Mar 16577 510622 13 525000 FRU_FF_ALL_ESD DD 201309 5048998 201304 13-Apr 16715 498952 11 525000 FRU_FF_ALL_ESD DD 201309 5048998 201305 13-May 16730 516832 25 525000 FRU_FF_ALL_ESD DD 201309 5048998 201306 13-Jun 16790 500242 44 525000 FRU_FF_ALL_ESD DD 201309 5048998 201307 13-Jul 16720 515942 18 525000 FRU_FF_ALL_ESD DD 201309 5048998 201308 13-Aug 16849 517897 13 525000 FRU_FF_ALL_ESD DD 201309 5048998 201309 13-Sep 16855 502933 13 525000

Vulpes · Answer

As there's no direct connection between the rows in an Excel worksheet and the DataRows in a DataTable you need a method to import one to the other. A method which is often used is to create an OleDb connection. There's some sample code in this link: http://getcodesnippet.com/2013/06/24/how-to-import-data-from-excel-to-datatable-c/ Once you've filled the DataTable and processed it I'd then write the DataTable back to Excel using your original code as we know that works.

John Luciani · Answer

I almost have it going... I think. The data is using a Microsoft Excel worksheet as the intermediary step so the error I get is: Cannot implicitly convert type 'Microsoft.Office.Interop.Excel.Range' to 'System.Data.DataTable'. An explicit conversion exists (are you missing a cast?) As well as: Cannot implicitly convert type 'System.Data.DataTable' to 'System.Data.DataRow' I am not sure how to proceed. public void GenerateOtherResults(Worksheet objSheet) { int ct = objSheet.UsedRange.Rows.Count / 14 + 1; for (int i = 0; i { object obj = objSheet.UsedRange.Value2; string str = ((object[])(obj))[2 + i * 14 4].ToString(); if (str.Length { str = 00 + str; } string sqlTarget = SELECT * FROM TBL_TARGET_REL WHERE ITEM_NUMBER = ' + str + '; string strTarget = GetTargetNumber(sqlTarget); targetReader.Close(); //Sort and combine USD & ESD var query = from DataRow dr in objSheet.Rows group dr by new{PartNo = dr[FRUPN] Month = dr[YYYYMM]} into g select g; System.Data.DataTable DataTable1 = new System.Data.DataTable(); DataTable1 = objSheet.Rows; foreach (var group in query) { DataRow da = DataTable1; //DataRow da = DataTable1.Rows.Cast (); da.ItemArray = group.First().ItemArray; da[FRUCNT] = group.Sum(dr => (int)dr[FRUCNT]); da[DIF] = group.Sum(dr => (int)dr[DIF]); da[REPL] = group.Sum(dr => (int)dr[REPL]); DataTable1.Rows.Add(da); }

Vulpes · Answer

OK so in that case you only want to group on the part number and the month as the product code could be different. You'll therefore need to replace: var query = from DataRow dr in dt.Rows group dr by new{Product = dr[PRODUCT] PartNo = dr[FRUPN] Month = dr[YYYYMM]} into g select g; by: var query = from DataRow dr in dt.Rows group dr by new{PartNo = dr[FRUPN] Month = dr[YYYYMM]} into g select g; in the above code. The product code selected for the combined table will be the one in the first (or only) DataRow in the group. This line won't of course now be needed: dt.Merge(dt2);

John Luciani · Answer

So from what I understand these are all in one datatable but the product was split into two products for the same part number. Hence FRU_FF_ALL_ESD vs. FRU_FF_ALL_USD. From what I just learned the information that is ported into this database was becoming too cumbersome so they split the product families up.

Vulpes · Answer

If the two DataTables (dt and dt2 say) have the same schema then here's some code which merges them combines similar rows and adds them to a new DataTable: dt.Merge(dt2); var query = from DataRow dr in dt.Rows group dr by new{Product = dr[PRODUCT] PartNo = dr[FRUPN] Month = dr[YYYYMM]} into g select g; DataTable dt3 = dt.Clone(); foreach(var group in query) { DataRow da = dt3.NewRow(); da.ItemArray = group.First().ItemArray; da[FRUCNT] = group.Sum(dr => (int)dr[FRUCNT]); da[DIF] = group.Sum(dr => (int)dr[DIF]); da[REPL] = group.Sum(dr => (int)dr[REPL]); dt3.Rows.Add(da); } You can now add the contents of dt3 to Excel using the same code as you did previously.

John Luciani · Answer

The only access to the database is to simple query data and export it to excel or access. The two tables were divided from one because the amount of data was too large to allow fast queries. I am not sure adding a key column would be possible. I would like to do this in my C# program after the data has been queried.

Javeed M Shaikh · Answer

Hi John You can either join these two datasets in the database itself by writing a small procedure to combine them and return as one cursor or if you have the two datatables in your code you can create a relation between them with the key column. Here is an article from MSDN on how to create relation: http://msdn.microsoft.com/en-us/library/ms171915.aspx you can also find articles on C# corner on data relation. Regards Javeed

PRODUCT	COMMODITY	REPORT_DATE	FRUPN	YYYYMM	MONTHYR	FRUCNT	DIF	REPL	TARGET
FRU_FF_ALL_USD	DD	201309	5048998	201210	12-Oct	4076	125684	2	525000
FRU_FF_ALL_USD	DD	201309	5048998	201211	12-Nov	4034	120365	0	525000
FRU_FF_ALL_USD	DD	201309	5048998	201212	12-Dec	4022	124232	2	525000
FRU_FF_ALL_USD	DD	201309	5048998	201301	13-Jan	4018	124381	1	525000
FRU_FF_ALL_USD	DD	201309	5048998	201302	13-Feb	4026	112327	0	525000
FRU_FF_ALL_USD	DD	201309	5048998	201303	13-Mar	4023	124603	4	525000
FRU_FF_ALL_USD	DD	201309	5048998	201304	13-Apr	4022	120471	2	525000
FRU_FF_ALL_USD	DD	201309	5048998	201305	13-May	4013	124166	0	525000
FRU_FF_ALL_USD	DD	201309	5048998	201306	13-Jun	3979	118924	2	525000
FRU_FF_ALL_USD	DD	201309	5048998	201307	13-Jul	3937	121816	3	525000
FRU_FF_ALL_USD	DD	201309	5048998	201308	13-Aug	3928	121572	2	525000
FRU_FF_ALL_USD	DD	201309	5048998	201309	13-Sep	3918	117142	3	525000

PRODUCT	COMMODITY	REPORT_DATE	FRUPN	YYYYMM	MONTHYR	FRUCNT	DIF	REPL	TARGET
FRU_FF_ALL_ESD	DD	201309	5048998	201210	12-Oct	16456	507419	8	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201211	12-Nov	16426	492407	12	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201212	12-Dec	16499	508916	7	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201301	13-Jan	16612	510790	145	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201302	13-Feb	16501	461255	12	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201303	13-Mar	16577	510622	13	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201304	13-Apr	16715	498952	11	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201305	13-May	16730	516832	25	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201306	13-Jun	16790	500242	44	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201307	13-Jul	16720	515942	18	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201308	13-Aug	16849	517897	13	525000
FRU_FF_ALL_ESD	DD	201309	5048998	201309	13-Sep	16855	502933	13	525000