Excel automation query( need linq code as the data is huge )

1=>I have an E1 file having s1 sheet (c1-c10) columns and E2 file having s2 sheet and (cA-cZ)columns. Now If c1 matches cA and also c2 matches cB then i need c3-cC=a, c4-cD=b, c5-cE=c and write the values of a,b,c in new columns .

2=>If c1=cA then c3+cC=x and write the value in a new column

Hi @Suthari_RohithReddy ,

Could you maybe provide us with the Example data and the Expected Output for that data, so that we can analyse and properly provide the suggestions to your query.

For Each CurrentRow As DataRow In Argument1.AsEnumerable
For Each Currentline As DataRow In Argument2.AsEnumerable
If CurrentRow(“GSTIN”).ToString=Currentline(“Bill To GSTIN”).ToString Then
x = CInt(CurrentRow(“CGST”)) - CInt(Currentline(“Total CGST Tax Amount”))
y = CInt(CurrentRow(“IGST”)) - CInt(Currentline(“Total IGST Tax Amount”))
z = CInt(CurrentRow(“SGST”)) - CInt(Currentline(“Total SGST Tax Amount”))

End If
Next Currentline
Next CurrentRow
I constructed the above code for 1st scenario but it’s throwing an exception saying “Invoke Code: Exception has been thrown by the target of an invocation.” I don’t understand where i am going wrong.


when the exception occur

click on locals panel and click on the exception then it will explain where the exception is happened

share the exception screenshot



As per error one of the values are null values as DBNull and you are tryin to convert to string or integer and it is failing there

You can check like this if it is DBNull or no

CurrentRow("Column") Is DBNull.Value this gives true if dbnull is found