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
}
}
Comments
I am looking for similar solution. how to handle - importing excel data to choice and lookup columns.
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.
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.
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.