C# Reading excel file ignores 1st row

Question

I'm using OLEDB for reading Excel file. After reading I'm saving it into DataSet for future usages.

The folowing code works just nice, but always ignores the first row of Excel file. How could this be fixed? Please, just ignore comments and exception's texts. There's the screens of result(I'm displaying resulted DatatSet's first DataTable to DevExpress GridView.

public static class ExcelHelperFasad
{
    /// <summary>
    /// Прочитать excel'евский файл и вернуть DataSet с заполненными DataTable'ами для каждой страницы.
    /// </summary>
    /// <param name="filename">Имя файла.</param>
    /// <returns></returns>
    public static DataSet OpenExcelFile(string filename)
    {
        if (File.Exists(filename) == false)
            throw new MyException("Файл не существует : " + filename);

        var dataTables = new List<DataTable>();

        var sheets = ListSheetInExcel(filename);

        if (sheets.Count == 0)
            throw new MyException("В Excel файле не найдено ни одной страницы. Проверьте файл на наличие страниц : " + filename);

        foreach (var sheet in sheets)
        {
            var dataTable = LoadWorksheetInDataTable(filename, sheet);

            dataTables.Add(dataTable);
        }

        var dataSet = new DataSet(Path.GetFileName(filename));

        foreach (var dataTable in dataTables)
            dataSet.Tables.Add(dataTable);

        return dataSet;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="sheetName"></param>
    /// <returns></returns>
    private static DataTable LoadWorksheetInDataTable(string fileName, string sheetName)
    {
        var sheetData = new DataTable(sheetName);
        using (var conn = ReturnConnection(fileName))
        {
            conn.Open();
            // retrieve the data using data adapter
            var sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
            sheetAdapter.Fill(sheetData);
        }
        return sheetData;
    }

    /// <summary>
    /// OLEDB коннекшон.
    /// </summary>
    /// <param name="fileName"></param>
    /// <returns></returns>
    private static OleDbConnection ReturnConnection(string fileName)
    {
        return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
    }

    /// <summary>
    /// Список страниц.
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    private static List<string> ListSheetInExcel(string filePath)
    {
        var sbConnection = new OleDbConnectionStringBuilder();
        var strExtendedProperties = string.Empty;
        sbConnection.DataSource = filePath;
        if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
        {
            sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
            strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
        }
        else if (Path.GetExtension(filePath).Equals(".xlsx"))  //for 2007 Excel file
        {
            sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
            strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
        }
        else
            throw new MyException("Ошибка чтения Excel файла. Необходимо сконвертировать Ваш файл в \".xlsx\" или \".xls\" формат.");

        sbConnection.Add("Extended Properties", strExtendedProperties);
        var listSheet = new List<string>();
        using (var conn = new OleDbConnection(sbConnection.ToString()))
        {
            conn.Open();
            var dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            foreach (DataRow drSheet in dtSheet.Rows)
            {
                if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
                    listSheet.Add(drSheet["TABLE_NAME"].ToString());
            }
        }
        return listSheet;
    }
}

Answer 1

Change "HDR" to "No" in strExtendedProperties:

    if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
    {
        sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
        strExtendedProperties = "Excel 8.0;HDR=No;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
    }
    else if (Path.GetExtension(filePath).Equals(".xlsx"))  //for 2007 Excel file
    {
        sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
        strExtendedProperties = "Excel 12.0;HDR=No;IMEX=1";
    }
    else
        throw new MyException("Ошибка чтения Excel файла. Необходимо сконвертировать Ваш файл в \".xlsx\" или \".xls\" формат.");

Otherwise it will always interpret first row as column names.

Answer 2

HDR=1 is the issue, make it HDR=0 - according to the comments on the code the code is assuming a HEADER row, that would be a row that has headings before the data - your data doesnt have this, and thats why thefirst row is being skipped