Tuesday, June 07, 2011

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:



using System;
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

}
}

Share:

4 comments:

Anonymous said...

Hi Amit,

I am looking for similar solution. how to handle - importing excel data to choice and lookup columns.

Deepali said...

nice post...
i want to add data into list where differnr types of columns (Lookup ,choice, calculated)are exist.Please let me know how to import data in such columns.

Anonymous said...

{"Could not find installable ISAM."}

I m stuck in this...If i give staitc path its working like"C:\hi.xls"
but if i give dynamic path like "+ path +" in the connection string its throwing error.

zzoeller said...

Interesting post. I'm having an issue though.

When I click the 'Upload File' button I receive this error:

The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

About Me

My photo
Based in Bangalore, INDIA, Amit is a MCC. He has an MCA from the U.P.T.U., INDIA and holds Microsoft certifications including SharePoint 2007/2010/2013. He is an contributor to MSDN and http://amitkumarmca04.blogspot.com/. Amit have experience in SharePoint,ASP.Net with C#, Classic ASP, VB,Macros,HTML,JavaScript,JSON,BootStrap and AngularJS.

Followers

Labels