Reading Spreadsheet into a DataTable in C#

This is an extremely useful function, which allows you to read an Excel spreadsheet and convert its data into a DataTable in C#. With data in a DataTable, we can perform lots of things to it.

public static DataTable ReadExcelFile(FileUpload File1, string strSheetName)
        {
            string strExtensionName = "";
            string strFileName = System.IO.Path.GetFileName(File1.PostedFile.FileName);
            DataTable dtt = new DataTable();
            if (!string.IsNullOrEmpty(strFileName))
            {
                //get the extension name, check if it's a spreadsheet
                strExtensionName = strFileName.Substring(strFileName.IndexOf(".") + 1);
                if (strExtensionName.Equals("xls") || strExtensionName.Equals("xlsx"))
                {
                        /*Import data*/
                        int FileLength = File1.PostedFile.ContentLength;
                        if (File1.PostedFile != null && File1.HasFile)
                        {

                            //upload the file to server
                            string strServerPath = "~/FolderName"; //create a FolderName in your Sharepoint root
                            FileInfo file = new FileInfo(File1.PostedFile.FileName);
                            string strServerFileName = System.Guid.NewGuid().ToString() + "-" + file.Name;
                            string strFullPath = HttpContext.Current.Server.MapPath(strServerPath).ToLower() + "/" + strServerFileName;
                            File1.PostedFile.SaveAs(strFullPath);
                            
                            //open connection out to read excel
                            string strConnectionString = string.Empty;
                            if (strExtensionName == "xls")
                                strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                                                        + strFullPath
                                                        + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                            else if (strExtensionName == "xlsx")
                                strConnectionString = "Provider=Microsoft.Jet.OLEDB.12.0;Data Source="
                                                        + strFullPath
                                                        + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

                         if (!string.IsNullOrEmpty(strConnectionString))
                            {
                                OleDbConnection objConnection = new OleDbConnection(strConnectionString);
                                objConnection.Open();
                                DataTable oleDbSchemaTable = objConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                                bool blExists = false;
                                foreach (DataRow dtr in oleDbSchemaTable.Rows)
                                {
                                    //reads from the spreadsheet called 'Sheet1'
                                    if (dtr["TABLE_NAME"].ToString() == "" + strSheetName + "$")
                                    {
                                        blExists = true;
                                        break;
                                    }
                                }
                                if (blExists)
                                {
                                    OleDbCommand objCmd = new OleDbCommand(string.Format("Select * from [{0}$]", strSheetName), objConnection);
                                    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
                                    objAdapter1.SelectCommand = objCmd;
                                    DataSet objDataSet = new DataSet();
                                    objAdapter1.Fill(objDataSet);
                                    objConnection.Close();
                                    dtt = objDataSet.Tables[0];
                                }
                                
                            }
                        }
                  }
            }
          return dtt;
        }

Categories: C#
Tags: