Reading locked Excel (.xlsx) file, Using C#

Question

Here is my code to read uploaded Excel file. Which is working absolutely fine for past 3 months.

var connectionString = GetOleDbConnectionString(file);

using (var dataAdapter = new OleDbDataAdapter("select * from [Sheet1$]",         connectionString))
{
    dataAdapter.Fill(ds, tableCount.ToString());
}

private static string GetOleDbConnectionString(string file)
{
    var fileExtension = Path.GetExtension(file);

    if (fileExtension.EqualsCCIC(".xlsx"))
    {
        return @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;".F(file);
    }
}

Problem: Uploaded excel file got "StartDate" as first column. But this column also has employee name along with dates (I need to read this employee name to process this sheet).

I came across one new excel file (Excel2007 .xlsx). When I upload new file (Which has both employee name and dates) it is only reading dates from the column and ignoring employee names. my dataset showing (While debugging) those cells in data table as empty strings. According to business logic I need to know which employee these dates belongs to. I removed locks for entire sheet(cell formatting>>Protection>> lock) but still no use. How can I solve this problem? I have no clue...

It is successfully reading old files (2007 .xlsx) I didn't understand what is it that makes OLEDB to hide strings in Date column?

Answer 1

So you're saying that the new excel file is the issue? If so...

• check the data in the file (particularly the first 8 rows in the employee name column)
• copy the data from the new file to a file that is known to work

Accessing Excel Spreadsheet with C# occasionally returns blank value for some cells Check out ABHI's answer in the above link (in particular points 1. and 2.)