Read Data from Excel file in SharePoint web Part C#

Question

I need to read Excel Data in C# DataTable in my Visual web Part.

for this purpose I've uploaded the excel file in a temporary location in a document library, the file upload is successful, now I want to read the contents of Excel file in C# DataTable

I've tried the same using OLEDB but it's throwing 'Unspecified Exception'

Here is my code:

  protected void btn_UploadItems_Click(object sender, EventArgs e)
        {
            Stream fStream = flu_items.PostedFile.InputStream;  
            byte[] contents = new byte[fStream.Length];  
            fStream.Read(contents, 0, (int)fStream.Length);  


            SPSecurity.CatchAccessDeniedException = false;  
            try  
            {

                string extension = Path.GetExtension(flu_items.PostedFile.FileName);
                string filename = ViewState["UserID"].ToString().Replace(".","_") + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + extension;
                obj_tempdoc.UploadFile(filename, fStream);

            }  
            catch (UnauthorizedAccessException)  
            {  
               // Controls.Add(new LiteralControl("You are not authorized to upload files to the document library"));  
            }  

            fStream.Close(); 
        }


 public bool UploadFile(string filename, Stream fStream)
        {
            try
            {
                using (SPSite site = new SPSite(SPContext.Current.Web.Url))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPFolder myLibrary = web.Folders["GatePassTemporaryDocuments"];


                        // Prepare to upload
                        Boolean replaceExistingFiles = true;                      

                        // Upload document
                        SPFile spfile = myLibrary.Files.Add(filename, fStream, replaceExistingFiles);

                        // Commit 
                        myLibrary.Update();

                        //
                        ReadExcelFile(spfile.ServerRelativeUrl);
                        //
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;

            }

        }
        public DataTable ReadExcelFile(string fileName)
        {

            try
            {
                using (SPSite site = new SPSite(SPContext.Current.Web.Url))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPFolder myLibrary = web.Folders["GatePassTemporaryDocuments"];

                        string fileExtension = Path.GetExtension(fileName).ToUpper();
                        string connectionString = "";

                        if (fileExtension == ".XLS")
                        {
                            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "'; Extended Properties='Excel 8.0;HDR=YES;'";
                        }
                        else if (fileExtension == ".XLSX")
                        {
                            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                        }
                        if (!(string.IsNullOrEmpty(connectionString)))
                        {
                            string[] sheetNames = GetExcelSheetNames(connectionString);
                            if ((sheetNames != null) && (sheetNames.Length > 0))
                            {
                                DataTable dt = null;
                                OleDbConnection con = new OleDbConnection(connectionString);
                                OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + sheetNames[0] + "]", con);
                                dt = new DataTable();
                                da.Fill(dt);
                                return dt;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {


            }
            return null;

        }
        private string[] GetExcelSheetNames(string strConnection)
        {
            var connectionString = strConnection;
            String[] excelSheets;
            using (var connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                var dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }
            }
            return excelSheets;
        }

The Exception is thrown at the time of Opening Connection

Please Help

I've also tried the following but still the Exception is same:

https://www.c-sharpcorner.com/blogs/visual-web-part-for-read-data-from-excel-sheet-and-insert-into-data-base1

Answer 1

Use Microsoft's Office OpenXml library to programmatically read or modify Excel files in memory or on disk. If you are developing in Visual Studio, the easiest way to add a reference is using Nuget Install-Package Open-XML-SDK

Also see How to Open a Spreadsheet from a Stream and How to read a large spreadsheet using Office OpenXML.

Fetch the Sheet:

public DataTable FetchSheet(string ExcelFilePath, string SheetName)
        {
            DataTable dt = new DataTable();
            try
            {
                string FrameNumbers = string.Empty;

                SPFile file = null;
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    file = SPContext.Current.Site.RootWeb.GetFile(ExcelFilePath);
                });

                using (Stream dataStream = file.OpenBinaryStream())
                {
                    using (SpreadsheetDocument document = SpreadsheetDocument.Open(dataStream, false))
                    {
                        Workbook workbook = document.WorkbookPart.Workbook;

                        foreach (Sheet s in document.WorkbookPart.Workbook.Descendants<Sheet>())
                        {
                            if (s.Name.HasValue && s.Name.Value == SheetName)
                            {
                                WorksheetPart wsPart = document.WorkbookPart.GetPartById(s.Id) as WorksheetPart;

                                if (wsPart == null)
                                {
                                    continue;
                                }
                                IEnumerable<Row> rows = wsPart.Worksheet.Descendants<Row>();

                                foreach (Cell cell in rows.ElementAt(0))
                                {                                    
                                    dt.Columns.Add(GetCellValue(cell));
                                }

                                int rowCount = 0;


                                    foreach (Row row in rows) //this will also include your header row...
                                    {
                                        if (rowCount != 0)//if this is not the first row i.e. column row                                     
                                        {
                                            //i=2 is Description column, i=8 is Expected return date column
                                            string itemDescription = GetCellValue(row.Descendants<Cell>().ElementAt(2));


                                            if(itemDescription.Trim()!="")
                                            {
                                                DataRow tempRow = dt.NewRow();
                                                for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                                                {
                                                    tempRow[i] = GetCellValue(row.Descendants<Cell>().ElementAt(i));
                                                }
                                                dt.Rows.Add(tempRow);
                                            }

                                        }
                                        rowCount++;

                                    }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                logger.InsertLog("GatePassSystem", ex.ToString());
            }
            return dt;
        }

namespaces:

using Microsoft.SharePoint;
using DocumentFormat.OpenXml;
using System.Data.OleDb;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet; 

Add DocumentFormat.OpenXML dll

Answer 2

The below approach is using Microsoft.Office.Interop.Excel, to the function ProcessProjectPlannerExcel I have passed the excel file url from my document library and I have read the data from excel sheet according to my need. Kindly change the for loop i values,

using Excel = Microsoft.Office.Interop.Excel;

 public void ProcessProjectPlannerExcel(string excelFileURL)
        {   

            System.Data.DataTable DtSampleDatatable = new System.Data.DataTable();
            Excel.Application app = null;
            Excel.Workbooks books = null;
            Excel.Workbook book = null;
            Excel.Sheets sheets = null;
            Excel.Range range = null;

            string siteurl = "YourSiteURL";
            SPSite tempSite = new SPSite(siteurl);
            SPUserToken systoken = tempSite.SystemAccount.UserToken;
            SPSecurity.RunWithElevatedPrivileges(delegate ()
            {
                using (SPSite site = new SPSite(siteurl, systoken))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        string projectName = string.Empty;
                        try
                        {                            
                            app = new Excel.Application();
                            books = app.Workbooks;
                            book = books.Open(excelFileURL, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                            sheets = book.Sheets;


                            foreach (Excel.Worksheet sheet in sheets)
                            {
                                if (sheet.Name.Equals("YourSheetName"))
                                {
                                    range = sheet.UsedRange;
                                    int rowCount = range.Rows.Count;
                                    int colCount = range.Columns.Count;

                                    //Reading the excel sheet headers and creating it as my datatable column names.
                                    for (int columnStart = 1; columnStart < 15; columnStart++)
                                    {
                                        if (range.Cells[2, columnStart] != null && range.Cells[2, columnStart].Value2 != null)
                                        {
                                            //Adding Datatable column
                                            DtSampleDatatable.Columns.Add(range.Cells[2, columnStart].Value2.ToString(), typeof(System.String));
                                        }
                                    }
                                    //Reading excel sheet data from row 3
                                    for (int i = 3; i <= rowCount; i++)
                                    {
                                        DataRow dr = DtSampleDatatable.NewRow();
                                        int columnStartValue = 0;
                                        bool flag = false;
                                        for (int j = 1; j <= DtSampleDatatable.Columns.Count; j++)
                                        {
                                            if (range.Cells[i, j] != null && range.Cells[i, j].Value2 != null)
                                            {
                                                string value = range.Cells[i, j].Value2.ToString();
                                                if (columnStartValue == 0 && string.IsNullOrEmpty(value))
                                                {
                                                    break;
                                                }
                                                if (!string.IsNullOrEmpty(value))
                                                {
                                                    flag = true;
                                                    dr[columnStartValue] = value;
                                                    columnStartValue++;
                                                }
                                                else
                                                {
                                                    flag = true;
                                                    dr[columnStartValue] = "";
                                                    columnStartValue++;
                                                }
                                            }
                                        }
                                        if (flag)
                                        {
                                            DtSampleDatatable.Rows.Add(dr);
                                        }
                                    }

                                    Marshal.ReleaseComObject(sheet);
                                    Marshal.ReleaseComObject(range);
                                }                              
                            }
                            book.Close();
                            app.Quit();
                        }
                        finally
                        {
                            if (sheets != null) Marshal.ReleaseComObject(sheets);
                            if (book != null) Marshal.ReleaseComObject(book);
                            if (books != null) Marshal.ReleaseComObject(books);
                            if (app != null) Marshal.ReleaseComObject(app);
                            System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("Excel");
                            foreach (System.Diagnostics.Process p in process)
                            {
                                if (!string.IsNullOrEmpty(p.ProcessName))
                                {
                                    try
                                    {
                                        p.Kill();
                                    }
                                    catch { }
                                }
                            }
                        }
                    }
                }
            });
        }