Conversion from type 'DBNull' to type 'Integer' is not valid in Excel

Good Day.

I am trying to get the sum of “Games Played” column. However, if there is a word “Total” at cell A10, it seems that the word “Total” is being included in the rows being processed.

May I know what can be the possible solutions for this? Kindly see attached files for the actual excel file and xaml file. Thank you.

Test1.xlsx (8.8 KB)
Main.xaml (9.5 KB)

Best regards,
Robert Monsalud


can you try the following?

SampleDT.AsEnumerable.Where(Function(r) Not String.IsNullOrEmpty(r("Games Played").ToString)).CopyToDataTable.AsEnumerable.Sum(Function(x) CDbl(x("Games Played").ToString))


1 Like


You have two options here…

Use excel formula using write cell …first read the datatable which will give you the rowcount = dt.Rowcount

Now use write cell with values as "= Sum(B2:B" + rowcount.ToString + ")" and cell as “B” + (RowCount+1).ToString


Read the datatable then use assign activity as below

Sum = dt.AsEnumerable.Sum(function(x) if(IsNothing(x("Games Played")) OrElse String.IsNullOrEmpty(x("GamesPlayed").ToString.Trim,0,cdbl(x("Games Played").ToString)))

Is of type double …write back using same mething as above

Hope this helps


1 Like

Hi @fernando_zuluaga and @Anil_G! Thank you so much for your help. If there is only a way to mark as a solution both of your responses, I would have done it.

Best regards,
Robert Monsalud


This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.