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

C# Excel search and combine

Answers (7)