How can I read the rows of an Excel csv or xls file into an ASP.NET app from a remote location using C#?

Question

Here's my situation. I'm designing a program that takes Excel files (which may be in csv, xls, or xlsx format) from a remote network drive, processes the data, then outputs and stores the results of that process. The program provides a listbox of filenames that are obtained from the remote network drive folder using the method detailed in the accepted answer here. Once the user selects a filename from the listbox, I want the program to find the file and obtain the information from it to do the data processing. I have tried using this method to read the data from the Excel file while in a threaded security context, but that method just fails without giving any kind of error. It seems to not terminate. Am I going about this the wrong way?

Edit - (Final Notes: I have taken out the OleDbDataAdapter and replaced it with EPPlus handling.)

I was able to scrub sensitive data from the code, so here it is:

protected void GetFile(object principalObj)
    {
        if (principalObj == null)
        {
            throw new ArgumentNullException("principalObj");
        }

        IPrincipal principal = (IPrincipal)principalObj;
        Thread.CurrentPrincipal = principal;
        WindowsIdentity identity = principal.Identity as WindowsIdentity;
        WindowsImpersonationContext impersonationContext = null;
        if (identity != null)
        {
            impersonationContext = identity.Impersonate();
        }
        try
        {
            string fileName = string.Format("{0}\\" + Files.SelectedValue, @"RemoteDirectoryHere");
            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.14.0; data source={0}; Extended Properties=Excel 14.0;", fileName);

            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM Sheet1", connectionString);
            DataSet ds = new DataSet();

            adapter.Fill(ds, "Sheet1");

            dataTable = ds.Tables["Sheet1"];
        }
        finally
        {
            if (impersonationContext != null)
            {
                impersonationContext.Undo();
            }
        }
    }

Additional Edit

Now xlsx files have been added to the mix.

Third Party

Third party solutions are not acceptable in this case (unless they allow unrestricted commercial use).

Attempts - (Final Notes: Ultimately I had to abandon OleDb connections.)

I have tried all of the different connection strings offered, and I have tried them with just one file type at a time. None of the connection strings worked with any of the file types.

Permissions

The User does have access to the file and its directory.

Answer 1

Your connection string might be the issue here. As far as I know, there isn't 1 that can read all xls, csv, and xlsx. I think you're using the XLSX connection string.

When I read xls, i use the following connection string:

@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"

Having said that, I recommend using a 3rd party file reader/parser to read XLS and CSV since, from my experience, OleDbDataAdapter is wonky depending on the types of data that's being read (and how mixed they are within each column).

For XLS, try NPOI https://code.google.com/p/npoi/

For CSV, try http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader

For XLSX, try EPPlus http://epplus.codeplex.com/

I've had great success with the above libraries.

Answer 2

Is it really important that you use an OleDb interface for this? I've always done it with Microsoft.Office.Excel.Interop, to wit:

using System;
using Microsoft.Office.Interop.Excel;

namespace StackOverflowExample
{
    class Program
    {
        static void Main(string[] args)
        {
            var app = new Application();
            var wkbk = app.Workbooks.Open(@"c:\data\foo.xls") as Workbook;
            var wksht = wkbk.Sheets[1] as Worksheet; // not zero-based!
            for (int row = 1; row <= 100; row++) // not zero-based!
            {
                Console.WriteLine("This is row #" + row.ToString());
                for (int col = 1; col <= 100; col++)
                {
                    Console.WriteLine("This is col #" + col.ToString());
                    var cell = wksht.Cells[row][col] as Range;
                    if (cell != null)
                    {
                        object val = cell.Value;
                        if (val != null)
                        {
                            Console.WriteLine("The value of the cell is " + val.ToString());
                        }
                    }
                }
            }
        }
    }
}

Answer 3

As you will be dealing with xlsx extension, you should rather opt for the new connection string.

public static string getConnectionString(string fileName, bool HDRValue, bool WriteExcel)
{
    string hdrValue = HDRValue ? "YES" : "NO";
    string writeExcel = WriteExcel ? string.Empty : "IMEX=1";
    return "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + "Extended Properties=\"Excel 12.0 xml;HDR=" + hdrValue + ";" + writeExcel + "\"";
}

Above is the code for getting the connection string. First argument expects the actual path for file location. Second argument will decide whether to consider first row values as column headers or not. Third argument helps decide whether you want to open the connection to create and write the data or simply read the data. To read the data set it to "FALSE"

public static ReadData(string filePath, string sheetName, List<string> fieldsToRead, int startPoint, int endPoint)
{
    DataTable dt = new DataTable();
    try
    {
        string ConnectionString = ProcessFile.getConnectionString(filePath, false, false);
        using (OleDbConnection cn = new OleDbConnection(ConnectionString))
        {
            cn.Open();
            DataTable dbSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dbSchema == null || dbSchema.Rows.Count < 1)
            {
                throw new Exception("Error: Could not determine the name of the first worksheet.");
            }
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT *");
            sb.Append(" FROM [" + sheetName + fieldsToRead[0].ToUpper() + startPoint + ":" + fieldsToRead[1].ToUpper() + endPoint + "] ");
            OleDbDataAdapter da = new OleDbDataAdapter(sb.ToString(), cn);
            dt = new DataTable(sheetName);
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                string i = row[0].ToString();
                }
            }
            cn.Dispose();
            return fileDatas;
        }
    }
    catch (Exception)
    {
    }
}

Answer 4

This is for reading 2007 Excel into dataset

  DataSet ds = new DataSet();
        try
        {


            string myConnStr = "";

                myConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDataSource;Extended Properties=\"Excel 12.0;HDR=YES\"";


            OleDbConnection myConn = new OleDbConnection(myConnStr);
            OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$] ", myConn);
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = cmd;
            myConn.Open();
            adapter.Fill(ds);
            myConn.Close();
        }
        catch
        { }
        return ds;