Upload Excel data into SharePiont Custom List using Interop

Upload Excel data into SharePiont Custom List using Interop
1 vote, 5.00 avg. rating (95% score)

Introduction

In this post we will see how to upload excel data (using office.interop.excel) into SharePoint 2007 / SharePoint 2010 Custom List from object model(c#)
You can also check the article how to upload excel data using oledb provider

Upload excel data

The following code has method LoadExcelData() which reads data from ‘contacts.xls’ and inserts into SharePoint Custom List ‘ContactList’.
According to the schema of the ‘ContactList’ that I have created, the method ‘InsertIntoList’ in the following code has relevant code.
You can modify according to the schema of your Custom List and Excel file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Data;
using System.Data.OleDb;
using Microsoft.SharePoint;

namespace TestSharepointProject
{
    public class UploadExcelData
    {
        public void LoadExcelData()
        {
            string filePath = "@"C:\AdisGroup\Contacts\contacts.xls";
			//if you are using file upload control in sharepoint get the full path as follows assuming fileUpload1 is control instance
			//string filePath = fileUpload1.PostedFile.FileName
			
             if (!(string.IsNullOrEmpty(fileName)))
            {
				DataTable excelData = GetExcelDataTable(filePath);
                if (excelData != null)
                {
                    InsertIntoList(excelData,"ContactList");
                }
			}
        }

        private DataTable GetExcelDataTable(string filePath)
        {
            DataTable dt = new DataTable();
            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.DisplayAlerts = false;
            if (ExcelObj == null)
            {
                return null;
            }
            string filead = filePath;
            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(filead, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
            true, false, 0, true, false, false);


            Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
            int index = 0;
            object rowIndex = 2;
            
            dt.Columns.Add("FirstName");
            dt.Columns.Add("LastName");
            dt.Columns.Add("FullName");
            dt.Columns.Add("LoginID");
            dt.Columns.Add("EmailAddress");
            dt.Columns.Add("PhoneNumberBusiness");
            dt.Columns.Add("Company");

            DataRow row;
            while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null)
            {
                rowIndex = 2 + index;
                row = dt.NewRow();
                row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
                row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
                row[3] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 4]).Value2);
                row[4] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 5]).Value2);
                row[5] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2);
                row[6] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 7]).Value2);

                index++;
                dt.Rows.Add(row);
            }
            ExcelObj.Workbooks.Close();
            return dt;
 
        }
		
		 private void InsertIntoList(DataTable listTable, string contactListName)
        {

            SPWeb mySite = null;
            try
            {

                mySite = SPContext.Current.Web; //create web object if context is null
                mySite.AllowUnsafeUpdates = true;
                SPList contactList = mySite.Lists[contactListName];
                for (int iRow = 0; iRow < listTable.Rows.Count; iRow++)
                {
                    SPListItem newContact = contactList.Items.Add();
                    newContact["FirstName"] = Convert.ToString(listTable.Rows[iRow][0]);
                    newContact["LastName"] = Convert.ToString(listTable.Rows[iRow][1]);
                    newContact["FullName"] = Convert.ToString(listTable.Rows[iRow][2]);
                    newContact["LoginID"] = Convert.ToString(listTable.Rows[iRow][3]);
                    newContact["EmailAddress"] = Convert.ToString(listTable.Rows[iRow][4]);
                    newContact["PhoneNumber"] = Convert.ToString(listTable.Rows[iRow][5]);
                    newContact["Company"] = Convert.ToString(listTable.Rows[iRow][6]);
                    newContact.Update();
                }
                mySite.AllowUnsafeUpdates = false;
            }
            catch (Exception ex)
            {
                //log exception
            }
            finally
            {
                if (mySite != null) //don't dispose if the site is from SPContext
                {
                    mySite.AllowUnsafeUpdates = false;
                }
            }
                  
        }

       

    }
}

Remarks

In the above code
‘GetExcelDataTable’ method takes data from first sheet name i.e get_Item(1). Change this if you want to load from another sheetName
‘InsertIntoList’ method uses SPContext to get the current web object. If you are using the above code where SPContext is not available then
you have create SPWeb object and dispose it in finally block

Conclusion

Hope you understand how to load excel data with interop services into Custom List in SharePoint using object model.

AdiCodesTags
Export an Excel table to a SharePoint list
Export Excel Data into SharePoint List
Import Excel spread sheet to SharePoint list
Upload excel data into SharePoint list
Upload Spreadsheets into SharePoint Lists

June 21, 2012 В· Adi В· One Comment
Tags:  В· Posted in: Sharepoint 2007, Sharepoint 2010

One Response

  1. Navoneel - October 8, 2012

    Very good work.helped really.Keep up good work.

Leave a Reply

What is 8 + 7 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)