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

Hey

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))

Regards!

1 Like

@RobertRussell_Monsalud

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

Or

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)))

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

Hope this helps

Cheers

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

2 Likes

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