Click here to Skip to main content
15,904,155 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
Below is my first table in database

CPTCode                            CPT 
80051	                          82374	
                                  82435	     
                                  84132
                                  84295


and this is my second table in database my requirement is if the CPT Code in my first table has the same cpt code in second table then what is should do is i should color that cpt code in my second table after exporting
also i should check all the cpts of that cpt code and if any cpt's match in both the tables i should color them also that means

Billnum              CPT                                                
OCR000967985	     80051		
OCR000967985	     82310		
OCR000967985	     83690	
OCR000967985	     84132		
OCR000967985	     85025	
OCR000967985	     86140

I have CPT Code 80051 in first table and corresponding cpt's for CPT Code 80051 are 82374, 82435, 84132, 84295 -- this is first table data
In second table i have same bill num OCR000967985 with cpt's 80051, 82310, 83690, 84132, 85025, 86140
if we compare above data 80051 in first table matches with 80051 in second table and one of the cpt for the CPT CODE 80051 matches with cpt's in second table which is 84132 the while exporting i should apply color for 80051 and 84132
as both CPT Code and CPT in first table matches with cpt in second table. Can any one help me out in doing this

What I have tried:

i could not find source for doing this
Posted
Updated 11-Dec-17 19:27pm
v4
Comments
jekin77 12-Dec-17 4:45am    
how do you export the data to excel? (OLE ? Other library ?)

anyway ... as a possible solution :
1: cache all match values
2: export you data to excel
3: loop thrue the match values
3.1: search the value in excel table
3.1.1: if found -> mark it
kav@94 12-Dec-17 4:49am    
using interop
jekin77 12-Dec-17 4:58am    
try to use alogotithm abow with snippets below
//get used range
Excel.Range usedRange= WorkSheet.UsedRange;

//find cell that match your search value
Excel.Range findRange= usedRange.Find(YourSearchValue, missing,
        Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
		Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
        missing, missing); 
		
//mark matches
Excel.Range firstFind = null; 
while(findRange!= null)
{         
	// Keep track of the first range you find.
	if (firstFind == null)
	{            
		firstFind = findRange;
	}         
	// If you didn't move to a new range, you are done.        
	else if (findRange.get_Address(Excel.XlReferenceStyle.xlA1)
				== firstFind.get_Address(Excel.XlReferenceStyle.xlA1))        
	{            
		break;        
	}         
	
	//Apply color        
	findRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
	findRange.Font.Bold = true;
	findRange = findRange.FindNext(findRange);     
}
kav@94 12-Dec-17 5:26am    
my requirement is different from the solution you gave me
My table1 data and table2 data are already there in my database and i am trying to compare the table 1 data with table2 data that is i am comparing

CPTCode CPT
80051 82374
82435
84132
84295
with

Billnum CPT
OCR000967985 80051
OCR000967985 82310
OCR000967985 83690
OCR000967985 84132
OCR000967985 85025
OCR000967985 86140

as my second table has both 80051 and 84132 in first table i need to apply colour for my second table while exporting to excel.


i am getting the two tables data into two datatables and i got strucked in comparing the first datatable with second datatable in order to find the matching CPT'S in both the datatables and to apply the colours.

Till now i am able to get two tables data into two datatables from there i got strucked with two tasks they are

1) Finding the matching Cpt's in both the tables and

2) Applying colour to those matching Cpt's while exporting to excel
jekin77 12-Dec-17 6:16am    
ok. you have two DataTables - say DataTable("First") and DataTable("Second")
save CPT values from Tables in two lists and compare it
using System.Linq;
.
.
DataTable table1 = new DataTable("First");
...//fill you first table with data
DataTable table2 = new DataTable("Second");
...//fill you second table with data
 
//loop thrue the first table rows and collect cpt values
List<string> cptValuesList1 = new List<string>();
foreach (DataRow row in table1.Rows)
{
	cptValuesList1.Add(row["CPTCode"].ToString());
	cptValuesList1.Add(row["CPT"].ToString());
}
//remove doubletts 
cptValuesList1 = cptValuesList1.Distinct().ToList();
 
//loop thrue the second table rows and collect cpt values
List<string> cptValuesList2 = new List<string>();
foreach (DataRow row in table2.Rows)
{	
	cptValuesList2.Add(row["CPT"].ToString());
}
//remove doubletts 
cptValuesList2 = cptValuesList2.Distinct().ToList();
 
//get intersected values 
List<string> intersectList = cptValuesList1.Intersect(cptValuesList2);
.
.
//now use the snippets abow to mark you interesected values

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900