Read Excel file and add data into dataset

Question

I want to read excel file and insert data into data set. I can read some files and it work properly but in some cases it is not working properly. I have attached screen shot of error and file also. When we unblock that file and open it once and save it then code works. Below is my code:

try
{
    startTime = DateTime.Now;
    System.IO.FileStream fs = new System.IO.FileStream(FullfilePath, System.IO.FileMode.Open, FileAccess.ReadWrite);
    IExcelDataReader excelReader2007 = ExcelReaderFactory.CreateOpenXmlReader(fs);
    excelReader2007.IsFirstRowAsColumnNames = false;
    DataSet result = excelReader2007.AsDataSet();
    if (result.Tables.Count > 0)
    {
        ds = result;
    }
    fs.Close();
    fs.Dispose();
    InsertExecLogDetails(startTime, DateTime.Now, Convert.ToString(Common.EventNames.GenerateDataTableFromExcel), Convert.ToString(Common.StatusEnum.Success), "Table generated from excel");
}
catch (Exception ex)
{
    InsertExecLogDetails(startTime, DateTime.Now, Convert.ToString(Common.EventNames.GenerateDataTableFromExcel), Convert.ToString(Common.StatusEnum.Failure), Convert.ToString(ex.Message));
}

Error link

At which line does the null pointer error occur? For those who can't see the link: http://i.stack.imgur.com/kLt7F.png — Draken, Apr 14 '16 at 06:36
OK, on further analysis it appears to happen at this point: `InsertExecLogDetails()` What does that method do? Can you post an example? — Draken, Apr 14 '16 at 06:38
IExcelDataReader excelReader2007 = ExcelReaderFactory.CreateOpenXmlReader(fs); here fs remain null — Fakhruddin Bharmal, Apr 14 '16 at 06:38
InsertExecLogDetails() that method just insert a log. you can ignore that method — Fakhruddin Bharmal, Apr 14 '16 at 06:39
If FS is null, then it can't open a FileStream to the specified file. You might need to move your close and dispose into a finally block, code incoming — Draken, Apr 14 '16 at 07:06

score 0 · Answer 1 · edited May 23 '17 at 12:31

Try this:

System.IO.FileStream fs;
try
{
    startTime = DateTime.Now;
    fs = new System.IO.FileStream(FullfilePath, System.IO.FileMode.Open, FileAccess.ReadWrite);
    IExcelDataReader excelReader2007 = ExcelReaderFactory.CreateOpenXmlReader(fs);
    excelReader2007.IsFirstRowAsColumnNames = false;
    DataSet result = excelReader2007.AsDataSet();
    if (result.Tables.Count > 0)
    {
        ds = result;
    }

    InsertExecLogDetails(startTime, DateTime.Now, Convert.ToString(Common.EventNames.GenerateDataTableFromExcel), Convert.ToString(Common.StatusEnum.Success), "Table generated from excel");
}
catch (Exception ex)
{
    InsertExecLogDetails(startTime, DateTime.Now, Convert.ToString(Common.EventNames.GenerateDataTableFromExcel), Convert.ToString(Common.StatusEnum.Failure), Convert.ToString(ex.Message));
}
finally
{
    if (fs != null){

        try{
            fs.Close();
            fs.Dispose();
        }
        catch(Exception ex){
            //Error handling for being unable to close file
        }
    }
}

It sounds like you are leaving the file open by a previous execution run and haven't closed it properly, this should hopefully solve your issue. If not, check to see if something else is still using the file

Or you could use the using function so that it closes off the file use for you, see here

[Edit] Example of using function (You may also need to use using on your IExcelDataReader, but I haven't checked the API):

try
{
    using(System.IO.FileStream fs = new System.IO.FileStream(FullfilePath, System.IO.FileMode.Open, FileAccess.ReadWrite))
    {
        startTime = DateTime.Now;
        IExcelDataReader excelReader2007 = ExcelReaderFactory.CreateOpenXmlReader(fs);
        excelReader2007.IsFirstRowAsColumnNames = false;
        DataSet result = excelReader2007.AsDataSet();
        if (result.Tables.Count > 0)
        {
            ds = result;
        }

        InsertExecLogDetails(startTime, DateTime.Now, Convert.ToString(Common.EventNames.GenerateDataTableFromExcel), Convert.ToString(Common.StatusEnum.Success), "Table generated from excel");
    }
}
catch (Exception ex)
{
    InsertExecLogDetails(startTime, DateTime.Now, Convert.ToString(Common.EventNames.GenerateDataTableFromExcel), Convert.ToString(Common.StatusEnum.Failure), Convert.ToString(ex.Message));
}

[Edit2]

Issue is being caused by NTFS systems setting a flag saying it's unsafe, you need to use the following code and call it before accessing the file to unblock it:

public class FileUnblocker {

    [DllImport("kernel32", CharSet = CharSet.Unicode, SetLastError = true)]
    [return: MarshalAs(UnmanagedType.Bool)]
    public static extern bool DeleteFile(string name );

    public bool Unblock(string fileName) {
        return DeleteFile(fileName+ ":Zone.Identifier");
    }
}

Source

So your resulting code would be:

try
{

    if (new FileUnblocker().Unblock(FullfilePath))
    {
        using(System.IO.FileStream fs = new System.IO.FileStream(FullfilePath, System.IO.FileMode.Open, FileAccess.ReadWrite))
        {
            startTime = DateTime.Now;
            IExcelDataReader excelReader2007 = ExcelReaderFactory.CreateOpenXmlReader(fs);
            excelReader2007.IsFirstRowAsColumnNames = false;
            DataSet result = excelReader2007.AsDataSet();
            if (result.Tables.Count > 0)
            {
                ds = result;
            }

            InsertExecLogDetails(startTime, DateTime.Now, Convert.ToString(Common.EventNames.GenerateDataTableFromExcel), Convert.ToString(Common.StatusEnum.Success), "Table generated from excel");
        }
    }
    else{
        //Issue with unblocking, deal with it here
    }
}
catch (Exception ex)
{
    InsertExecLogDetails(startTime, DateTime.Now, Convert.ToString(Common.EventNames.GenerateDataTableFromExcel), Convert.ToString(Common.StatusEnum.Failure), Convert.ToString(ex.Message));
}

Hello, I tried both solution but it not works. problem is that when I click on properties of excel file and click on unblock then open it and save then this code works — Fakhruddin Bharmal, Apr 14 '16 at 08:40
Ah, security permissions, I understand now. Have a look at this: http://stackoverflow.com/questions/6374673/unblock-file-from-within-net-4-c-sharp — Draken, Apr 14 '16 at 09:12
Hello, Thank you so much I tried your update code but it always goes in "Else" condition. What code should be in else condition because I want to read excel file anyhow weather it is unlock or not. — Fakhruddin Bharmal, Apr 14 '16 at 09:52
Does it do that for all files or just files that were already unblocked? If all files, something else is going wrong and that I'm not sure one — Draken, Apr 14 '16 at 10:00
This issue comes only files which are blocked. for other files it is working properly. if you want I can send you files which are causing issue . — Fakhruddin Bharmal, Apr 14 '16 at 10:05
Sadly I won't be able to test it as I don't have access to an IDE for C# at the moment. The only thing I could suggest is try and debug further and find out why the files aren't being unblocked — Draken, Apr 14 '16 at 10:38
Files are being unblocked but still always else part of code is executed. — Fakhruddin Bharmal, Apr 14 '16 at 11:32
Then remove the if check and just call: `new FileUnblocker().Unblock(FullfilePath);` instead, followed by your usual code for opening the file. I've possibly misunderstood what the Boolean is actually returning — Draken, Apr 14 '16 at 12:08
Then they're either not being unblocked or something else is causing the File to not be read. You'll need to do some further investigation into the issue yourself and once you know the problem further, create a new question. — Draken, Apr 14 '16 at 12:30
When I open that file and saving it and then it is processed. I have to unblock file and open it once and save then it is working. — Fakhruddin Bharmal, Apr 14 '16 at 13:10

Read Excel file and add data into dataset

1 Answers1