Asp.Net (c#) - Read Excel Sheet.

Question

I have a requirement to do some imports from an Excel spread sheet and have been looking at various examples on the web, they all seem to use the "Jet" driver which is not compatible with 64Bit.

Now I am fully aware of the workarounds available (in changing how IIS runs etc) however I would like to know if there is replacement for the "Jet" driver so I can read and generate excel sheets from Asp.Net running on a 64Bit server with no IIS modifications.

Answer 1

The last time I researched there isnt an x64 driver. I use this to open xls files. It works on 64bit boxes so long as you compile using x86.

 DataSet myDataset = new DataSet();
                string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + @";Extended Properties=""Excel 12.0 Xml;HDR=YES""";
                OleDbConnection myData = new OleDbConnection(strConn);
                try {
                                      myData.Open();
                }
                catch (OleDbException e) {
                    try {
                        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=Excel 8.0;HDR=YES;";
                        myData = new OleDbConnection(strConn);
                        myData.Open();
                    }
                    catch (Exception e2) {
                        strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + @";Extended Properties=""HTML Import;HDR=YES;IMEX=1"";";
                        myData = new OleDbConnection(strConn);
                        myData.Open();
                    }
                }

                int i = 0;
                foreach (DataRow row in myData.GetSchema("tables").Rows)
                    try {
                        i++;
                        string name = row[2].ToString().Replace("''", "'").TrimEnd('_');
                        DataSet ds = new DataSet();
                        OleDbDataAdapter d = new OleDbDataAdapter("SELECT * from [" + name + "]", strConn);
                        d.Fill(ds);
                        DataTable dt = ds.Tables[0].Copy();
                        dt.Namespace = name;
                        myDataset.Tables.Add(dt);
                    }
                    catch (Exception e) {
                    }
                return myDataset;

Answer 2

SpreadsheetGear for .NET is an Excel compatible spreadsheet component for .NET which works equally well with 32 bit and 64 bit applicaions (including IIS).

You can see live ASP.NET samples here and download the free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC

Answer 3

Have you already thought of giving the Open XML SDK a try?

Answer 4

You can try SmartXLS for .Net,it is a .Net spreadsheet component which support most Excel features like charts,pivots,formula evaluation,cell formattings,decrypt/encrypt(for both xls and xlsx)...etc.

Answer 5

http://npoi.codeplex.com

I use it myself on a 64bit server with IIS 6.0 Not the easiest one to use, but it works fine (fast, reliable, don't need office installed on server, etc).