How to get row info of Exception handled row for LinqToExcel?

Question

I have an excel file that has 3 columns. So I created a model like following to cast rows.

public class Point
{
    [ExcelColumn("Lon")]
    public float Lon {get;set;}

    [ExcelColumn("Lat")]
    public float Lat {get;set;}

    [ExcelColumn("Elevation")]
    public float Elevation {get;set;}
}

var excel = new ExcelQueryFactory("points.xlsx");
var ic= from c in excel.Worksheet<Point>();

Sample excel

Lon    Lat    Elevation
---    ---    ---------
11.5   25.6   80.56
12ab   89.87  14.83
1.7    5.8    9.3

But sometimes users may incorrect data entry. For example Lon value is "12ab" for 2nd row. In this stuation throws exception.

How can I understand which row and which cell is failed?

Answer 1

Not really what you wanted but maybe it is enough of a "solution" for you.

Couldn't find a way with the current version of the Library, but I did the following

static void Main(string[] args)
{
    var f = Path.Combine(AppContext.BaseDirectory, "points.xlsx");
    var excel = new ExcelQueryFactory(f);
    excel.AddTransformation<Point>(x => x.Lon, x => float.TryParse(x, out var y) ? y : -1);
    excel.AddTransformation<Point>(x => x.Lat, x => float.TryParse(x, out var y) ? y : -1);
    excel.AddTransformation<Point>(x => x.Elevation, x => float.TryParse(x, out var y) ? y : -1);

    int rowNumber = 0;
    try
    {
        foreach (var row in excel.Worksheet<Point>())
        {
            if (row.Lon == -1 ||
                row.Lat == -1 ||
                row.Elevation == -1)
            {
                throw new FormatException();
            }

            rowNumber++;
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Failed at {rowNumber}");
        Console.WriteLine(ex.Message);
    }

    Console.WriteLine("Done");
    Console.ReadLine();
}

If you don't mind using the "pre-release" version https://www.nuget.org/packages/LinqToExcel/2.0.0-PRERELEASE-2

You could do it like this:

var f = Path.Combine(AppContext.BaseDirectory, "points.xlsx");
var excel = new ExcelQueryFactory(f);
excel.Lazy = true;

int rowNumber = 0;
try
{
    foreach (var row in excel.Worksheet<Point>())
    {
        rowNumber++;
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Failed at {rowNumber}");
    Console.WriteLine(ex.Message);
}

This uses the "Lazy" feature, which got included in October 2018 but up till now there was never a NuGet package released. https://github.com/paulyoder/LinqToExcel/pull/155

You actually don't even need the manual count of rowNumber with the pre-release version since there is a second code change that comes in handy for you, which also never got published in a final version, https://github.com/paulyoder/LinqToExcel/pull/125 (from October 2017)

So all you would need to do is:

var f = Path.Combine(AppContext.BaseDirectory, "points.xlsx");
var excel = new ExcelQueryFactory(f);

try
{
    foreach (var row in excel.Worksheet<Point>())
    {

    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

And you would get the new and improved exception stating something like this:

Error on row 3 and column name 'Lon'.