Upload (Import) data from excel (spreadsheet) file to SharePoint list using c#

Upload (Import) data from excel (spreadsheet) file to SharePoint list using c#

Upload (Import) data from excel (spreadsheet) file to SharePoint list using c#:

When we have data in excel format and want to import that data to a SharePoint list, then how to do. By default SharePoint will provide us an option to edit in spreadsheet or download list items in spread sheet. But, it doesn't have an option to import the excel data to SharePoint list. To resolve this problem, I have created web part.
In this web part, I am using SharePoint list called “Employee”. The name of columns are Tile and LastName.
With the help of this web part, user will enter the data in the excel and then give the path of the file in the input box present in the page. Now instead of uploading the whole excel file into a document library the code will read the file and update the list with the data present in the excel file.

The web part code is:

view plaincopy to clipboardprint?

1. using System;  
2. using System.Collections.Generic;  

using System.Text;  

using System.Web;  

using System.Data;  

using System.Xml;  

using System.Xml.Serialization;  

using System.ComponentModel;  

using System.Data.OleDb;  

using System.Web.UI.WebControls;  

using Microsoft.SharePoint;  

using Microsoft.SharePoint.WebPartPages;  

using Microsoft.SharePoint.WebControls;  

  

  

  

  

namespace Example1  

{  

    public class wp_ImportXLSFile : WebPart  

    {  

        #region Member Variables  

        System.Web.UI.WebControls.Button btnUpload;  

        FileUpload fleDoc;  

        #endregion  

 

        #region Properties  

        private const string _defaultList = "Employee";  

        private string _listName = "Employee";  

        [DefaultValue(_defaultList)]  

        [WebPartStorage(Storage.Personal)]  

        [XmlElement(ElementName = "List Name", IsNullable = false)]  

        public string InternalList  

        {  

            get { return this._listName; }  

            set { this._listName = value; }  

        }  

 

 

         #endregion  

 

        #region Render Controls and Handle Controls Event  

 

        #region Create Child Controls  

        protected override void CreateChildControls()  

        {  

  

            try  

            {  

                Table tbl = new Table();  

                TableRow tr = new TableRow();  

                TableCell tc = new TableCell();  

  

                tc.Text = "Select file:";  

                tr.Cells.Add(tc);  

                tc = new TableCell();  

                //Create browse object  

                fleDoc = new FileUpload();  

                tc.Controls.Add(fleDoc);  

                tr.Cells.Add(tc);  

                tbl.Rows.Add(tr);  

  

  

                tr = new TableRow();  

                tc = new TableCell();  

                tc.RowSpan = 2;  

                tc.HorizontalAlign = HorizontalAlign.Center;  

                //Create button  

                btnUpload = new System.Web.UI.WebControls.Button();  

                btnUpload.Text = "Upload File";  

                btnUpload.Click += new EventHandler(btnUpload_Click);  

                tc.Controls.Add(btnUpload);  

                tr.Cells.Add(tc);  

                tbl.Rows.Add(tr);  

                this.Controls.Add(tbl);  

  

            }  

            catch (Exception ex)  

            {  

                this.Page.Response.Write(ex.Message);  

            }  

        }  

 

        #endregion  

 

        #region Handle button click event  

        void btnUpload_Click(object sender, EventArgs e)  

        {  

            try  

            {  

                if (fleDoc != null && fleDoc.PostedFile != null)  

                {  

                    #region If File exist  

  

                    if (fleDoc.PostedFile.ContentLength > 0)  

                    {  

                        #region File size is greater than 0  

                          

                        string fileExt = System.IO.Path.GetExtension(fleDoc.FileName);  

                        if (string.Equals(fileExt, ".xls", StringComparison.InvariantCultureIgnoreCase) )  

                        {  

                            #region If not xls file  

                            string Path = fleDoc.PostedFile.FileName;  

                            string listname = "Sheet1";  

  

                            OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0");  

                            //OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties=Excel 12.0 Xml;HDR=YES");  

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

                            //Provider=Microsoft.ACE.OLEDB.12.0;Data Source=;Extended Properties="Excel 12.0 Xml;HDR=YES";  

                            con.Open();  

  

  

                            //Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference  

                            DataSet myDataSet = new DataSet();  

                            OleDbCommand myCommand = new OleDbCommand(" SELECT * FROM [" + listname + "$]", con);  

                            System.Data.OleDb.OleDbDataAdapter da = new OleDbDataAdapter(myCommand);  

  

                            da.Fill(myDataSet);  

                            con.Close();  

  

                            if (myDataSet != null && myDataSet.Tables.Count > 0 && myDataSet.Tables[0].Rows.Count > 0 && !string.IsNullOrEmpty(this.InternalList))  

                            {  

                                #region If record found  

  

                                SPWeb web = SPContext.Current.Web;  

  

                                SPSecurity.RunWithElevatedPrivileges(delegate  

                                                    {  

                                                        using (SPSite fullSite = new SPSite(web.Site.ID))  

                                                        {  

                                                            using (SPWeb fullWeb = fullSite.OpenWeb())  

                                                            {  

 

                                                                #region Find list exist or not  

                                                                if (chkListExist(fullWeb))  

                                                                {  

                                                                    fullWeb.AllowUnsafeUpdates = true;  

                                                                    SPList oList = fullWeb.Lists[this.InternalList];  

 

                                                                    #region Travers through each row in the dataset  

                                                                    foreach (DataRow myDataRow in myDataSet.Tables[0].Rows)  

                                                                    {  

                                                                        #region Datarow Loop  

                                                                        Object[] cells = myDataRow.ItemArray;  

                                                                        string strFirstName = string.Empty;  

                                                                        string strLastName = string.Empty;  

                                                                        int cellCount = 1;  

                                                                        foreach (object cellContent in cells)  

                                                                        {  

                                                                            #region Cell Loop  

  

                                                                            string cellText = cellContent.ToString();  

                                                                            cellText = cellText.Replace("\n", "|");  

                                                                            //Read the string and put into Array of characters chars  

                                                                            if (cellCount == 1)  

                                                                            {  

                                                                                strFirstName = cellText;  

                                                                                cellCount++;  

                                                                            }  

                                                                            else if (cellCount == 2)  

                                                                            {  

                                                                                strLastName = cellText;  

                                                                                cellCount = 1;  

                                                                            }  

                                                                            this.Page.Response.Write("\n" + cellText);  

                                                                            #endregion  

                                                                        }  

                                                                        insertRecordInList(oList, strFirstName, strLastName);  

                                                                        #endregion  

                                                                    }  

                                                                    #endregion  

  

                                                                    fullWeb.AllowUnsafeUpdates = false;  

  

                                                                }  

                                                                 

                                                                #endregion  

  

                                                            }  

                                                        }  

                                                    });  

 

                                #endregion  

                            }  

                            else  

                            {  

                                #region If record not found  

                                this.Page.Response.Write("No record found.");  

                                #endregion  

                            }  

                            #endregion  

  

                        }  

                        else  

                        {  

                            #region If not xls file  

                            this.Page.Response.Write("Only .xls files allowed!");  

                            #endregion  

                        }  

 

 

 

                        #endregion  

                    }  

                    else  

                    {  

                        #region File size is 0  

                        this.Page.Response.Write("Uploaded file size is 0.");  

                        #endregion  

                    }  

 

 

                    #endregion  

                }  

                else  

                {  

                    #region If File not exist  

                    this.Page.Response.Write("Uploaded file does not exist.");  

                    #endregion  

                }  

            }  

            catch (Exception ex)  

            {  

                throw ex;  

            }  

        }  

 

        #endregion  

 

        #endregion  

 

        #region Helper  

 

 

        #region Check list exist or not  

        private Boolean chkListExist(SPWeb web)  

        {  

            Boolean blnExist = false;  

            try  

            {  

                #region Try  

                if (!String.IsNullOrEmpty(this.InternalList) && this.InternalList.ToString().Trim().Length != 0)  

                {  

                    try  

                    {  

                        if (web.Lists[this.InternalList] != null)  

                        {  

                            blnExist =  true;  

                        }  

                    }  

                    catch  

                    {  

                        #region If internal news list name is not valid  

                        blnExist = false;  

                        #endregion  

  

                    }  

  

                }  

                else  

                {  

                    #region If internal news list name is empty  

                    blnExist = false;  

                    #endregion  

                }  

 

                #endregion  

  

            }  

            catch (Exception ex)  

            {  

                #region Catch  

                blnExist = false;  

                #endregion  

            }  

            return blnExist;  

        }  

        #endregion  

 

 

        #region Insert record in list  

        private void insertRecordInList(SPList oList,string strFirstName,string strLastName)  

        {  

            try  

            {  

                #region Try  

                SPListItem oListItem = oList.Items.Add();  

                oListItem["Title"] = strFirstName;  

                oListItem["LastName"] = strLastName;  

                oListItem.Update();  

                #endregion  

  

            }  

            catch (Exception ex)  

            {  

                #region Catch  

                throw ex;  

                #endregion  

            }  

        }  

        #endregion  

 

        #endregion  

  

    }  

}