Updating Excel data to Share Point List Items based on Id
Loading Excel Data
public void LoadExcelData()
{
string fileName = "D:/Employee.xlsx";
string fileExtension = Path.GetExtension(fileName).ToUpper();
string connectionString = "";
if (fileExtension == ".XLS")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "'; Extended Properties='Excel 8.0;HDR=YES;'";
}
else if (fileExtension == ".XLSX")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties='Excel 8.0;'";
//string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data.xls;Extended Properties=Excel 8.0;HDR=YES;";
}
if (!(string.IsNullOrEmpty(connectionString)))
{
string[] sheetNames = GetExcelSheetNames(connectionString);
string SheetName = sheetNames[0].ToString().Replace("'", "");
if ((sheetNames != null) && (sheetNames.Length > 0))
{
DataSet ds = new DataSet();
DataTable dt = null;
OleDbConnection con = new OleDbConnection(connectionString);
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + sheetNames[0] + "]", con);
dt = new DataTable();
da.Fill(dt);
ds.Tables.Add(dt);
InsertIntoList(dt, "ProjectList");
}
}
}
To Get Excel Sheet Names
private string[] GetExcelSheetNames(string strConnection)
{
var connectionString = strConnection;
String[] excelSheets;
using (var connection = new OleDbConnection(connectionString))
{
connection.Open();
DataTable Sheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (Sheets == null)
{
return null;
}
excelSheets = new String[Sheets.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in Sheets.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
}
return excelSheets;
}
Insert Excel Data to Share point List
private void InsertIntoList(DataTable listTable, string ProjectsListName)
{
SPWeb myweb = null;
//Country List Name
string CountryList = "Countries";
string Title = "Title";
string States = "States";
try
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite site = new SPSite("sharepointpelsiteurl"))
{
using (myweb = site.OpenWeb())
{
#region for
for (int iRow = 0; iRow < listTable.Rows.Count; iRow++)
{
//GetExcelSheetNames Values
string ProjectNumberExcel = Convert.ToString(listTable.Rows[iRow][0]);
string CountryExcel = Convert.ToString(listTable.Rows[iRow][1]);
string StateExcel = Convert.ToString(listTable.Rows[iRow][2]);
string CityExcel = Convert.ToString(listTable.Rows[iRow][3]);
myweb.AllowUnsafeUpdates = true;
SPList ProjectsList = myweb.Lists["Employee"];
SPQuery Projectsquery = new SPQuery();
Projectsquery.Query = "<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>" + ProjectNumberExcel + "</Value></Eq></Where>";
SPListItemCollection itemsCollection = ProjectsList.GetItems(Projectsquery);
foreach (SPListItem listItem in itemsCollection)
{
string ListIntId=Convert.ToString(listItem["ID"]);
string strstrEmpId = Convert.ToString(listItem["EmpId"]);
string strCountry = Convert.ToString(listItem["Country"]);
string strState = Convert.ToString(listItem["State"]);
string strCity = Convert.ToString(listItem["City"]);
int id = int.Parse(ListIntId);
int PrjId = int.Parse(strEmpId);
//SPListItem itemtoUpdate = ProjectsList.GetItemById(id);
#region if ProjectNumber
if (strProjectNumber == ProjectNumberExcel)
{
if (strCountry == string.Empty)
{
SPListItem itemtoUpdateCountry = ProjectsList.GetItemById(id);
itemtoUpdateCountry["Country"] = GetLookFieldIDS(Title, CountryExcel, CountryList, myweb);
itemtoUpdateCountry.Update();
}
if (strState == string.Empty)
{
SPListItem itemtoUpdateState = ProjectsList.GetItemById(id);
itemtoUpdateState["State"] = StateExcel;
//GetLookFieldIDS(States, StateExcel, CountryList, myweb);
itemtoUpdateState.Update();
}
if (strCity == string.Empty)
{
SPListItem itemtoUpdateCity = ProjectsList.GetItemById(id);
itemtoUpdateCity["City"] = CityExcel;
itemtoUpdateCity.Update();
}
}
#endregion
}
}
#endregion
}
myweb.AllowUnsafeUpdates = false;
}
});
}
catch (Exception ex)
{
//log exception
}
finally
{
if (myweb != null) //don't dispose if the site is from SPContext
{
myweb.AllowUnsafeUpdates = false;
}
}
}
Get the Lookup Value ID Values for Country Drop Down
public static SPFieldLookupValueCollection GetLookFieldIDS(String coloumnIntName, String lookupValues, String lookupSourceList, SPWeb web)
{
SPFieldLookupValueCollection lookupIds = new SPFieldLookupValueCollection();
if (!String.IsNullOrEmpty(lookupValues))
{
SPList lookupList = web.Lists[lookupSourceList];
SPQuery query = new Microsoft.SharePoint.SPQuery();
query.Query = String.Format("<Where><Eq><FieldRef Name='{0}'/><Value Type='Text'> {1}</Value></Eq></Where>", coloumnIntName, lookupValues);
SPListItemCollection listItems = lookupList.GetItems(query);
foreach (Microsoft.SharePoint.SPListItem item in listItems)
{
SPFieldLookupValue value = new SPFieldLookupValue(item.ID, item.Title);
lookupIds.Add(value);
break;
}
}
return lookupIds;
}
public void LoadExcelData()
{
string fileName = "D:/Employee.xlsx";
string fileExtension = Path.GetExtension(fileName).ToUpper();
string connectionString = "";
if (fileExtension == ".XLS")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "'; Extended Properties='Excel 8.0;HDR=YES;'";
}
else if (fileExtension == ".XLSX")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties='Excel 8.0;'";
//string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data.xls;Extended Properties=Excel 8.0;HDR=YES;";
}
if (!(string.IsNullOrEmpty(connectionString)))
{
string[] sheetNames = GetExcelSheetNames(connectionString);
string SheetName = sheetNames[0].ToString().Replace("'", "");
if ((sheetNames != null) && (sheetNames.Length > 0))
{
DataSet ds = new DataSet();
DataTable dt = null;
OleDbConnection con = new OleDbConnection(connectionString);
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + sheetNames[0] + "]", con);
dt = new DataTable();
da.Fill(dt);
ds.Tables.Add(dt);
InsertIntoList(dt, "ProjectList");
}
}
}
To Get Excel Sheet Names
private string[] GetExcelSheetNames(string strConnection)
{
var connectionString = strConnection;
String[] excelSheets;
using (var connection = new OleDbConnection(connectionString))
{
connection.Open();
DataTable Sheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (Sheets == null)
{
return null;
}
excelSheets = new String[Sheets.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in Sheets.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
}
return excelSheets;
}
Insert Excel Data to Share point List
private void InsertIntoList(DataTable listTable, string ProjectsListName)
{
SPWeb myweb = null;
//Country List Name
string CountryList = "Countries";
string Title = "Title";
string States = "States";
try
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite site = new SPSite("sharepointpelsiteurl"))
{
using (myweb = site.OpenWeb())
{
#region for
for (int iRow = 0; iRow < listTable.Rows.Count; iRow++)
{
//GetExcelSheetNames Values
string ProjectNumberExcel = Convert.ToString(listTable.Rows[iRow][0]);
string CountryExcel = Convert.ToString(listTable.Rows[iRow][1]);
string StateExcel = Convert.ToString(listTable.Rows[iRow][2]);
string CityExcel = Convert.ToString(listTable.Rows[iRow][3]);
myweb.AllowUnsafeUpdates = true;
SPList ProjectsList = myweb.Lists["Employee"];
SPQuery Projectsquery = new SPQuery();
Projectsquery.Query = "<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>" + ProjectNumberExcel + "</Value></Eq></Where>";
SPListItemCollection itemsCollection = ProjectsList.GetItems(Projectsquery);
foreach (SPListItem listItem in itemsCollection)
{
string ListIntId=Convert.ToString(listItem["ID"]);
string strstrEmpId = Convert.ToString(listItem["EmpId"]);
string strCountry = Convert.ToString(listItem["Country"]);
string strState = Convert.ToString(listItem["State"]);
string strCity = Convert.ToString(listItem["City"]);
int id = int.Parse(ListIntId);
int PrjId = int.Parse(strEmpId);
//SPListItem itemtoUpdate = ProjectsList.GetItemById(id);
#region if ProjectNumber
if (strProjectNumber == ProjectNumberExcel)
{
if (strCountry == string.Empty)
{
SPListItem itemtoUpdateCountry = ProjectsList.GetItemById(id);
itemtoUpdateCountry["Country"] = GetLookFieldIDS(Title, CountryExcel, CountryList, myweb);
itemtoUpdateCountry.Update();
}
if (strState == string.Empty)
{
SPListItem itemtoUpdateState = ProjectsList.GetItemById(id);
itemtoUpdateState["State"] = StateExcel;
//GetLookFieldIDS(States, StateExcel, CountryList, myweb);
itemtoUpdateState.Update();
}
if (strCity == string.Empty)
{
SPListItem itemtoUpdateCity = ProjectsList.GetItemById(id);
itemtoUpdateCity["City"] = CityExcel;
itemtoUpdateCity.Update();
}
}
#endregion
}
}
#endregion
}
myweb.AllowUnsafeUpdates = false;
}
});
}
catch (Exception ex)
{
//log exception
}
finally
{
if (myweb != null) //don't dispose if the site is from SPContext
{
myweb.AllowUnsafeUpdates = false;
}
}
}
public static SPFieldLookupValueCollection GetLookFieldIDS(String coloumnIntName, String lookupValues, String lookupSourceList, SPWeb web)
{
SPFieldLookupValueCollection lookupIds = new SPFieldLookupValueCollection();
if (!String.IsNullOrEmpty(lookupValues))
{
SPList lookupList = web.Lists[lookupSourceList];
SPQuery query = new Microsoft.SharePoint.SPQuery();
query.Query = String.Format("<Where><Eq><FieldRef Name='{0}'/><Value Type='Text'> {1}</Value></Eq></Where>", coloumnIntName, lookupValues);
SPListItemCollection listItems = lookupList.GetItems(query);
foreach (Microsoft.SharePoint.SPListItem item in listItems)
{
SPFieldLookupValue value = new SPFieldLookupValue(item.ID, item.Title);
lookupIds.Add(value);
break;
}
}
return lookupIds;
}
Comments
Post a Comment