WorkAround: Hybrid Approach to Create and Update SharePoint 2013 List from Excel Sheets for large volume of data.

Introduction

In many organizations the management people in any department use Excel to maintain their records, data, history etc. And now a days SharePoint based applications are often used to share and collaborate these files. Many organizations use SharePoint sites as their portal sites for the purpose of sharing information, data within department or organization.

Problem

So now let’s say your manager or client has an Excel sheet with valuable records and now he/she wants to put it on the SharePoint as a list, and asked you to do that. Now what will you do? You cannot use Excel services nor upload excel sheet as it is (even though you can  ) because manager wants SharePoint list only. As a SharePoint developer you will think : first create a SharePoint List and then write one tool which will read Excel sheet records one by one and add it to the SharePoint list. Right? But is it really needed if SharePoint and Office provides it itself? 

Solutions

1. From Excel Sheet

Yes you can quickly create SharePoint List out of Excel sheet without writing any single line of code. Let's see how we can do. Note: I have performed these steps on SharePoint 2013 with Microsoft office 2010.

Steps:

1. Open the Excel 2010 application.
2. Insert/create the table. 

     

3. Pressing OK button will generate the below scenario:

4. Pressing the above link will give you the below screen where you need to put the value of a SharePoint application running at your environment, name of the list and description of the list(optional) as shown below:

5. Pressing Next will cause the following Login screen to appear:

6. Press OK and  On the next screen you will see columns with data types which are going to create in SharePoint list.

        

    7. Now click finish and wait until the operation gets finished. You will see that list gets created in SharePoint site with the records.    

        

That's it. 

   

2. Using Import SpreadSheet App

SharePoint 2013 provides 'Import SpreadSheet App' to do the same thing. Follow below steps to import SpreadSheet.

1. Go to Site Contents -> add an app, and search for 'Import SpreadSheet' app.

            

  

2.  Add 'Import SpreadSheet' app and provide information as shown in below screen. You need to select the excel file. [Before that you need to save the excel file in table format as shown in Approach 1.]

        

3. Click on the import, wait for few seconds and your spreadsheet gets imported in list.

        

That is also fine.

But the problem is in both of the above cases, the generated list is completely new with the data on the excel sheet. But how to handle the scenario when there is an old list and you need to update the list from an external excel sheet without loosing the previously stored data. For this reason, we need to programmatically read the newly created list and insert into our target list which can be done by a simple visual web part and on a button click. The click event and the function written  for this purpose is given below which I have applied as a workaround to solve the problem. Below are the source code samples:

 protected void btnUpload_Click(object sender, EventArgs e)

        {

       

            SPSecurity.RunWithElevatedPrivileges(delegate

                {

                    try

                    {

                        

                        LoadData();

                    }

                    catch(Exception ex)

                    {

                        Logger.Current.Log("Error: " + ex.Message);

                    }

                });

        }

        public void LoadData()

        {

            try

            {

                SPWeb spWeb = SPContext.Current.Site.RootWeb;

                SPList spList = spWeb.Lists.TryGetList("MyList");

                SPQuery qry = new SPQuery();

                string camlquery = "<OrderBy><FieldRef Name='Created' /></OrderBy>";

                qry.Query = camlquery;

                DataTable listItemsTable = spList.GetItems(qry).GetDataTable();

                SPList oList = spWeb.Lists["Employee"];

               

                foreach (DataRow dr in listItemsTable.Rows)

                {

                    SPListItem oSPListItem = oList.Items.Add();

                    oSPListItem["Title"] = dr["Title"].ToString();

                    oSPListItem["Designation"] = dr["Designation"].ToString();                  

                    oSPListItem["Address"] = dr["Address"].ToString();

                    oSPListItem["Salary"] = Conver.ToInt32(dr["Salary"].ToString());

                    oSPListItem["Email"] = dr["Email"].ToString();

                    oSPListItem["PhoneNumber"] = dr["PhoneNumber"].ToString();

                    oSPListItem["Organization"] = dr["Organization"].ToString();

                    

                    oSPListItem.Update();

                    

                }

                lblMessage.Text = "Data inserted successfully";

            }

            catch (Exception ex)

            {

                lblMessage.Text = ex.ToString();

            }

        }

Finally create a page  in your web application and add the visual web part to have a visual interface to click on and update SharePoint list from excel sheets dynamically.

Happy Share Pointing...!!!