LinqToExcel System.ArgumentException in c#

Question

I am using LinqToExcel in my .net framework project to read some excel files. Sometimes I receive a file with a date error, and when I try to convert my ExcelQueryable to a list of rows using excel.Worksheet(worksheetName).ToList(); or foreach (var row in worksheet) { var teste = row; } I get a System.ArgumentException error and I cannot proceed.

Is there some way to ignore the error and deal manually with the error Rows?

Screenshot of the error Row

My code:

var excel = new ExcelQueryFactory(path);

var worksheetNames = excel.GetWorksheetNames();
var worksheetName = worksheetNames.FirstOrDefault();
var worksheet = excel.Worksheet(worksheetName);
List<Row> itens = excel.Worksheet(worksheetName).ToList();

I expect to convert the ExcelQueryable to a List with no errors

Answer 1

you can use ClosedXML lib.

this is a function to read excel to DataTable

var dt = Export_Excel_TO_DataTable(file, "Sheet1");

and function itself

public static DataTable Export_Excel_TO_DataTable(string filePath, string sheetName)
{
    // Open the Excel file using ClosedXML.
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        //Read the first Sheet from Excel file.
        IXLWorksheet workSheet = workBook.Worksheet(1);
        //Create a new DataTable.
        DataTable dt = new DataTable();
        //Loop through the Worksheet rows.
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows().Where(r=> !r.IsEmpty()))
        {
            //Use the first row to add columns to DataTable.
            if (firstRow)
            {
                row.Cells()
                    .ToList()
                    .ForEach(cell =>
                    {
                        dt.Columns.Add(cell.Value.ToString().Trim());
                    });
                firstRow = false;
            }
            else
            {
                //Add rows to DataTable.
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }
        return dt;
    }
}