How can I store a bunch of exceptions and return then as the response of an web API?

Solution 1:

Most .NET parts that can return multiple exceptions use AggregateException to achieve that. A generic template for that would be:

var exceptions = new List<Exception>();

foreach (var w in work)
{
    try
    {
        DoWork(w);
    }
    catch (Exception e)
    {
        exceptions.Add(e);
    }
}

if (exceptions.Any())
{
    throw new AggregateException(exceptions);
}

Here's the docs for AggregateException.

Solution 2:

the problem is, every time I hit a line with bad information from the excel table my code stops running and the API returns the exception.

Going on with a process after an exception has never been a good idea. At best the thing you are working with is now in a invalid state and will throw more exceptions "InvalidState" every further access attempt. At worst, it is utterly broken and will cause utterly unpredictbale behavior because it asumes you would not do something so bad. Such behavior can go all the way into your process being kicked out by windows, due to Memory Access Violations.

There is basic classification I use for Exceptions. While this exception is at worst a Exogenous Exception for your code, it should be considered a fatal one for the parser and this Documet. Going on after it, is not going to add any value. At best, it adds more useless error messages wich hide the real error.

90% of the times you get a compiler error, you get a message because the Syntax is wrong. And often it is so wrong, the Source Code parser can not make heads or tails of what it is seeing anymore. It stop being able to tell you where the issue even is.

You should expose/log each exception, stop the parsing after the first one and have the user deal with the issue (wich is clearly outside your job).