0

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

0

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

| improve this answer | |
0

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 { }
                                }
                            }
                        }
                    }
                }
            });
        }
| improve this answer | |
  • If my answer had helped you, kindly upvote and Mark it as answer, so that it will be helpful to others too – Karthik Jaganathan Jan 25 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.