Bulk upload using ProcessBatchData in SharePoint Server 2010
Excel Template Format
aspx page code
<script type="text/javascript">
function timedRefresh(timeoutPeriod) {
setTimeout(function () { _spFormOnSubmitCalled = false; }, 2000);
return true;
}
</script>
<div class="speedbar">
<div class="speedbar-nav">
IAA > Audit Upload
</div>
</div>
<div class="contentWrapper">
<div class="grid-full">
<div class="title-grid">
Audit Upload</div>
<div class="content-gird">
<div align="center">
<asp:Label ID="lblExcelError" runat="server"></asp:Label><br />
<asp:Label ID="lblMessCon" runat="server"></asp:Label></div>
<div class="control">
<label class="long">
<span>File Upload</span></label>
<asp:FileUpload ID="AuditExcelUpload" runat="server" />
</div>
<div class="control">
<asp:Button ID="btnAuditUpload" runat="server" Text="Upload Excel Data" CssClass="button-a dark-blue"
ValidationGroup="None" OnClientClick="javascript: timedRefresh();" OnClick="btnAuditUpload_Click" />
<asp:Button ID="btnDownload" runat="server" Text="Download Audit Template" OnClientClick="javascript: timedRefresh(2000)"
CssClass="button-a dark-blue btnMarginLeft" OnClick="btnDownload_Click" />
</div>
<div class="control" align="center">
<asp:Label ID="lblAuditID" runat="server" Visible="false" ForeColor="Red" ></asp:Label>
</div>
<asp:Label ID="AuditUploadmsg" runat="server" Text="" EnableViewState="false" ForeColor="Red"></asp:Label>
</div>
</div>
</div>
.... code End
aspx.cs code
using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web;
using System.IO;
using OfficeOpenXml;
using Microsoft.SharePoint;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.OleDb;
using Microsoft.SharePoint.Utilities;
namespace EY.IAA.WebParts.AuditUpload
{
public partial class AuditUploadUserControl : UserControl
{
bool IsAuditID = false;
string AuditList = "Audit";
//string AuditList = "Auditss";
private readonly string BatchXMLBanner = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><ows:Batch OnError=\"Return\">{0}</ows:Batch>";
private readonly string BatchXMLMethod = "<Method ID=\"{0}\"><SetList>{1}</SetList><SetVar Name=\"ID\">New</SetVar><SetVar Name=\"Cmd\">Save</SetVar>{2}</Method>";
private readonly string BatchXMLSetVar = "<SetVar Name=\"urn:schemas-microsoft-com:office:office#{0}\">{1}</SetVar>";
protected void Page_Load(object sender, EventArgs e)
{
}
#region Upload Method and events
protected void btnAuditUpload_Click(object sender, EventArgs e)
{
try
{
//bulkupload();
UploadExcelFileToSharePointList();
}
catch(Exception ex)
{
lblExcelError.Text = ex.Message.ToString();
}
}
private void UploadExcelFileToSharePointList()
{
if (!AuditExcelUpload.HasFile)
{
lblExcelError.Visible = true;
lblExcelError.Text = "Please select a valid Excel File";
return;
}
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
spWeb.AllowUnsafeUpdates = true;
SPList list = spWeb.Lists[AuditList];
try
{
byte[] fileData = AuditExcelUpload.FileBytes;
using (MemoryStream memStream = new MemoryStream(fileData))
{
memStream.Flush();
using (ExcelPackage pck = new ExcelPackage(memStream))
{
if (pck != null)
{
//CreateListItem(pck, list, spWeb);
CreateListItemTest(pck, list, spWeb);
}
}
}
}
catch (Exception Ex1)
{
lblExcelError.Text = "Error Occured <br/>" + Ex1.Message;
}
}
}
}
catch (Exception Ex)
{
lblExcelError.Text = "Error Occured <br/>" + Ex.Message;
}
}
private void CreateListItem(ExcelPackage pck, SPList list, SPWeb web)
{
Dictionary<int, string> column = GetColumnMapping();
ExcelWorksheet ws = pck.Workbook.Worksheets[1];
ws.Column(200).Hidden = false;
int rowCount = ws.Dimension.End.Row + 1;
int colCount = ws.Dimension.End.Column + 1;
string Errorline = string.Empty;
Int32 Errorlinenumber;
string collistid = string.Empty;
for (int i = 7; i < rowCount; i++) // Row index starts from 7, as the first row is Title
{
try
{
SPListItem item = list.Items.Add();
web.AllowUnsafeUpdates = true;
if (ws.Cells[i, 200].Value == null)
{
for (int j = 1; j <= 14; j++)
{
string v = Convert.ToString(ws.Cells[i, j].Value);
switch (j)
{
case 1:
//collistid = getlist("Audit", v.Trim(), "AuditID");
//item[column[j]] = collistid + ";#" + v.Trim();
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = Convert.ToString(ws.Cells[i, j].Value);
}
break;
case 2:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = Convert.ToString(ws.Cells[i, j].Value);
}
break;
case 3:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 4:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 5:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 6:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 7:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = Convert.ToDateTime(ws.Cells[i, j].Value);
}
break;
case 8:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = Convert.ToDateTime(ws.Cells[i, j].Value);
}
break;
case 9:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
// item[column[j]] = ws.Cells[i, j].Value;
User userApprover = null;
string Approver = string.Empty;
Approver = Convert.ToString(ws.Cells[i, j].Value);
userApprover = GetSiteUser(SPContext.Current.Web.Url, Approver);
item[column[j]] = userApprover;
}
break;
case 10:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 11:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 12:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 13:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
collistid = getlist("AuditRating", ws.Cells[i, j].Value.ToString().Trim(), "Rating");
item[column[j]] = collistid + ";#" + ws.Cells[i, j].Value;
// item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 14:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
collistid = getlist("AuditProcess", ws.Cells[i, j].Value.ToString().Trim(), "Process Name");
item[column[j]] = collistid + ";#" + ws.Cells[i, j].Value;
//item[column[j]] = ws.Cells[i, j].Value;
}
break;
}
}
item.Update();
web.AllowUnsafeUpdates = false;
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Green;
lblExcelError.Text = "Audit uploaded sucessfully.";
}
else
{
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Green;
lblExcelError.Text = "Audit uploaded sucessfully.";
}
}
catch (Exception)
{
Errorlinenumber = i - 6;
Errorline += Errorlinenumber + ",";
}
}
//InsertBatchData(web, list);
lblExcelError.Visible = true;
if (Errorline != string.Empty)
{
Errorline = Errorline.Remove(Errorline.Length - 1);
lblExcelError.Text = Errorline + " items is not uploaded successfully.";
}
else
{
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Green;
lblExcelError.Text = "Audit uploaded sucessfully";
}
}
private Dictionary<int, string> GetColumnMapping()
{
Dictionary<int, string> map = new Dictionary<int, string>();
//map.Add(1, "Audit ID");
//map.Add(2, "Audit Name"); // First parameter is the column Index in Excel Sheet and Second Param is SharePoint List's Column Name (Display Name )
//map.Add(3, "Entity");
//map.Add(4, "Locations");
//map.Add(5, "Audit Status");
//map.Add(6, "Audit Description");
//map.Add(7, "Audit Start Date");
//map.Add(8, "Audit End Date");
//map.Add(9, "Audit Owner");
//map.Add(10, "Audit Team");
//map.Add(11, "Audit Lead");
//map.Add(12, "Audit Manager");
//map.Add(13, "Rating");
//map.Add(14, "Process");
map.Add(1, "AuditID");
map.Add(2, "Title");
map.Add(3, "Entity");
map.Add(4, "Locations");
map.Add(5, "AuditStatus");
map.Add(6, "AuditDescription");
map.Add(7, "AuditStartDate");
map.Add(8, "AuditEndDate");
map.Add(9, "AuditOwner");
map.Add(10, "AuditTeam");
map.Add(11, "AuditLead");
map.Add(12, "AuditManager");
map.Add(13, "Rating");
map.Add(14, "AuditScope");
return map;
}
private string getlist(string listname, string fieldname, string listfieldTitle)
{
SPSite oSite = new SPSite(SPContext.Current.Web.Url);
SPWeb oweb = oSite.OpenWeb();
SPList olist = oweb.Lists[listname];
string fieldTitle = fieldname;
Regex rgx = new Regex("/n");
string result = rgx.Replace(fieldname, "");
int count = olist.ItemCount;
SPQuery objQuery = new SPQuery();
{
objQuery.Query = @"<Where><Eq><FieldRef Name=" + listfieldTitle + " /><Value Type='Text'>" + result.Trim() + "</Value></Eq></Where>";
};
DataTable dt = olist.GetItems(objQuery).GetDataTable();
string colid = string.Empty;
if (dt != null)
{
SPListItemCollection colItems = olist.GetItems(objQuery);
colid = colItems[0]["ID"].ToString();
}
return colid;
}
private SPListItemCollection getsplistitemcoll(SPWeb oweb, string listnme)
{
SPList olist = oweb.Lists[listnme];
DataTable sdqf = olist.GetItems().GetDataTable();
SPListItemCollection colItems = olist.GetItems();
return colItems;
}
public List<Listval> GetListvalue(SPListItemCollection lisititemcoll, string fieldTitle)
{
var count = lisititemcoll.Count;
List<Listval> lst = new List<Listval>();
Listval obj = null;
for (int i = 0; i < count; i++)
{
string colid = lisititemcoll[i][fieldTitle].ToString();
obj = new Listval();
obj.val = colid;
lst.Add(obj);
}
return lst;
}
public void InsertBatchData123(SPWeb web, SPList listdata)
{
try
{
SPListItemCollection listItem = listdata.GetItems();
StringBuilder sbadd = new StringBuilder();
string xmlFormat = @"<?xml version=1.0 encoding=UTF-8?\>";
sbadd.Append(xmlFormat);
sbadd.Append("<Batch>");
sbadd.Append(@"<Method ID='1' Cmd='New'>");
string buildFields = "<Field Name='Audit ID'>{0}</Field>";
buildFields = buildFields + "<Field Name='Audit Name'>{1}</Field>";
buildFields = buildFields + "<Field Name='Entity'>{2}</Field>";
buildFields = buildFields + "<Field Name='Locations'>{3}</Field>";
buildFields = buildFields + "<Field Name='Audit Status'>{4}</Field>";
buildFields = buildFields + "<Field Name='Audit Description'>{5}</Field>";
buildFields = buildFields + "<Field Name='Audit Start Date'>{6}</Field>";
buildFields = buildFields + "<Field Name='Audit End Date'>{7}</Field>";
buildFields = buildFields + "<Field Name='Audit Owner'>{8}</Field>";
buildFields = buildFields + "<Field Name='Audit Team'>{9}</Field>";
buildFields = buildFields + "<Field Name='Audit Lead'>{10}</Field>";
buildFields = buildFields + "<Field Name='Audit Manager'>{11}</Field>";
buildFields = buildFields + "<Field Name='Rating'>{12}</Field>";
buildFields = buildFields + "<Field Name='Process'>{13}</Field>";
foreach (SPListItem item in listItem)
{
sbadd.Append(string.Format
(buildFields, Convert.ToString(item["Audit ID"]).Trim(),
Convert.ToString(item["Audit Name"]).Trim(),
Convert.ToString(item["Entity"]).Trim(),
Convert.ToString(item["Locations"]).Trim(),
Convert.ToString(item["Audit Status"]).Trim(),
Convert.ToString(item["Audit Description"]).Trim(),
Convert.ToDateTime(item["Audit Start Date"]),
Convert.ToDateTime(item["Audit End Date"]),
Convert.ToString(item["Audit Owner"]).Trim(),
Convert.ToString(item["Audit Team"]).Trim(),
Convert.ToString(item["Audit Lead"]).Trim(),
Convert.ToString(item["Audit Manager"]).Trim(),
Convert.ToString(item["Rating"]).Trim(),
Convert.ToString(item["Process"]).Trim()
));
}
sbadd.Append(@"</Method>");
sbadd.Append("</Batch>");
web.ProcessBatchData(sbadd.ToString());
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message.ToString();
}
}
private string InsertBatchData(SPWeb web, SPList listdata)
{
StringBuilder XMLSetVarLines = new StringBuilder();
StringBuilder XMLSetMethods = new StringBuilder();
string guid;
int methodID = 0;
Dictionary<int, string> listFieldName = GetColumnMapping();
SPListItemCollection listItem = listdata.GetItems();
foreach (SPListItem item in listItem)
{
guid = Convert.ToString(item["ID"]);
for (int i = 1; i <= listFieldName.Count; i++)
{
string values;
listFieldName.TryGetValue(i, out values);
var fieldname = values;
var fieldvalue = Convert.ToString(item[fieldname]);
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, fieldname, fieldvalue));
}
XMLSetMethods.Append(String.Format(BatchXMLMethod, methodID, guid, XMLSetVarLines));
methodID++;
XMLSetVarLines.Length = 0;
}
return web.ProcessBatchData(String.Format(BatchXMLBanner, XMLSetMethods));
}
private void CreateListItemTest(ExcelPackage pck, SPList list, SPWeb web)
{
Dictionary<string, User> userDic = new Dictionary<string, User>();
Dictionary<int, string> column = GetColumnMapping();
ExcelWorksheet ws = pck.Workbook.Worksheets[1];
StringBuilder XMLSetVarLines = new StringBuilder();
StringBuilder XMLSetMethods = new StringBuilder();
int methodID = 0;
ws.Column(200).Hidden = false;
int rowCount = ws.Dimension.End.Row + 1;
int colCount = ws.Dimension.End.Column + 1;
string Errorline = string.Empty;
Int32 Errorlinenumber;
string collistid = string.Empty;
string Url = SPContext.Current.Web.Url;
SPSite site = new SPSite(Url);
SPWeb CurrentSite = SPContext.Current.Web;
SPList Auditlists = CurrentSite.Lists[AuditList];
string guid = Auditlists.ID.ToString();
web.AllowUnsafeUpdates = true;
bool AuditIDCheck = false;
string AuditIDValue = string.Empty;
for (int i = 7; i < rowCount; i++) // Row index starts from 7, as the first row is Title
{
try
{
//SPListItem item = list.Items.Add();
if (ws.Cells[i, 200].Value == null)
{
for (int j = 1; j <= 14; j++)
{
string values;
column.TryGetValue(j, out values);
string v = Convert.ToString(ws.Cells[i, j].Value);
switch (j)
{
case 1:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
AuditIDCheck = IsAuditIDExist(Convert.ToString(ws.Cells[i, j].Value));
if (AuditIDCheck)
{
AuditIDValue += Convert.ToString(ws.Cells[i, j].Value) + ",";
IsAuditID = AuditIDCheck;
}
}
break;
case 2:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
}
break;
case 3:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
// below code running
//XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
string EntityData = string.Empty;
EntityData = GetEntitiesParentsChild(Convert.ToString(ws.Cells[i, j].Value));
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(EntityData)));
}
break;
case 4:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
// below code running
//XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
string LocationsData = string.Empty;
LocationsData = GetLocationsParentsChild(Convert.ToString(ws.Cells[i, j].Value));
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(LocationsData)));
}
break;
case 5:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
}
break;
case 6:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
}
break;
case 7:
// if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
string AuditStartDate = (SPUtility.CreateISO8601DateTimeFromSystemDateTime(Convert.ToDateTime(ws.Cells[i, j].Value.ToString())));
//DateTime ccc = Convert.ToDateTime(ws.Cells[i, j].Value).Date;
// if (!string.IsNullOrEmpty(Convert.ToDateTime(Convert.ToString(ws.Cells[i, j].Value)).ToString("dd-MMM-yyyy")))
if (!string.IsNullOrEmpty(AuditStartDate))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, AuditStartDate));//Convert.ToDateTime(ws.Cells[i, j].Value).Date));// Convert.ToDateTime(Convert.ToString(ws.Cells[i, j].Value)).ToString("dd-MMM-yyyy")));
}
break;
case 8:
string AuditEndDate = (SPUtility.CreateISO8601DateTimeFromSystemDateTime(Convert.ToDateTime(ws.Cells[i, j].Value.ToString())));
if (!string.IsNullOrEmpty(AuditEndDate))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, AuditEndDate));//Convert.ToDateTime(ws.Cells[i, j].Value).Date));// Convert.ToDateTime(Convert.ToString(ws.Cells[i, j].Value)).ToString("dd-MMM-yyyy")));
}
break;
case 9:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
SPUser userOwner = EnsureUser(web, Convert.ToString(ws.Cells[i, j].Value));
string userOwners = Convert.ToString(userOwner.ID);
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, userOwners));
}
break;
case 10:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
SPUser userTeam = EnsureUser(web, Convert.ToString(ws.Cells[i, j].Value));
string userTeams = Convert.ToString(userTeam.ID);
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, userTeams));
}
break;
case 11:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
SPUser userLead = EnsureUser(web, Convert.ToString(ws.Cells[i, j].Value));
string userLeands = Convert.ToString(userLead.ID);
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, userLeands));
// XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value).Trim()));
}
break;
case 12:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
SPUser userManager = EnsureUser(web, Convert.ToString(ws.Cells[i, j].Value));
string userManagers = Convert.ToString(userManager.ID);
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, userManagers));
//XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, userss));//Convert.ToString(ws.Cells[i, j].Value).Trim()));
}
break;
case 13:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
SPList olist = web.Lists["AuditRating"];
string fieldTitle = ws.Cells[i, j].Value.ToString();
SPQuery objQuery = new SPQuery();
{
objQuery.Query = @"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>" + fieldTitle + "</Value></Eq></Where>";
};
SPListItemCollection colItems = olist.GetItems(objQuery);
string colid = colItems[0]["ID"].ToString();
String fieldValue = colid + ";#" + fieldTitle;
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, fieldValue));
}
break;
case 14:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
//SPList olist = web.Lists["Scopes"];
//string fieldTitle = ws.Cells[i, j].Value.ToString();
//SPQuery objQuery = new SPQuery();
//{
// objQuery.Query = @"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>" + fieldTitle + "</Value></Eq></Where>";
//};
//SPListItemCollection colItems = olist.GetItems(objQuery);
//string colid = colItems[0]["ID"].ToString();
//String fieldValue = colItems[0]["Title"].ToString();
//String fieldValue = colid + ";#" + fieldTitle;
//String fieldValue = ";#" + fieldTitle;
// below code running
//XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, ws.Cells[i, j].Value));
string AuditScopeData = string.Empty;
AuditScopeData = GetAuditScopeParentsChild(Convert.ToString(ws.Cells[i, j].Value));
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(AuditScopeData)));
}
break;
}
}
// item.Update();
}
else
{
// updatelistitem(i, list, pck);
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Green;
lblExcelError.Text = "Audit upload sucessfully.";
}
}
catch (Exception)
{
Errorlinenumber = i - 6;
Errorline += Errorlinenumber + ",";
}
methodID++;
XMLSetMethods.Append(String.Format(BatchXMLMethod, methodID, guid, XMLSetVarLines));
}
XMLSetVarLines.Length = 0;
if (IsAuditID)
{
lblAuditID.Text = String.Format("AuditID {0} already exists in Audit list.Please rename and upload again.", AuditIDValue.TrimEnd(','));
lblAuditID.Visible = true;
}
else
{
string result = web.ProcessBatchData(String.Format(BatchXMLBanner, XMLSetMethods));
lblAuditID.Visible = false;
}
web.AllowUnsafeUpdates = false;
//bool IsSave = false;
//if (!string.IsNullOrEmpty(result))
//{
// try
// {
// Int64 i = Convert.ToInt64(result);
// if (i > 0)
// {
// IsSave = true;
// }
// }
// catch (Exception)
// {
// IsSave = false;
// }
//}
if (!IsAuditID)
{
if (methodID > 0)
{
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Green;
lblExcelError.Text = "Audit upload sucessfully.";
}
else
{
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Red;
lblExcelError.Text = "File is not upload sucessfully"; //result;
}
}
}
public class Listval
{
public string val { set; get; }
}
public class User
{
public int UserId
{
get;
set;
}
public string LoginName
{
get;
set;
}
public string Email
{
get;
set;
}
public string Name { get; set; }
}
public static User GetSiteUser(string siteUrl, string userName)
{
User user = null;
//SPSecurity.RunWithElevatedPrivileges(delegate()
//{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.OpenWeb())
{
SPUser spUser = web.EnsureUser(userName);
if (spUser != null)
{
user = new User();
user.UserId = spUser.ID;
user.Email = spUser.Email;
user.LoginName = spUser.LoginName;
user.Name = spUser.Name;
}
}
}
//});
return user;
}
public void bulkupload()
{
try
{
string Url = SPContext.Current.Web.Url;
SPSite site = new SPSite(Url);
StringBuilder methodBuilder = new StringBuilder();
string batch = string.Empty;
DateTime currentDate = DateTime.Now;
string formattedDate = DateTime.Now.ToString();
string methodFormat = string.Empty;
SPWeb CurrentSite = SPContext.Current.Web;
// Get the list containing the items to update.
SPList list = CurrentSite.Lists[AuditList];
string listGuid = list.ID.ToString();
for (int i = 0; i < 1000; i++)
{
int itemID = i;
string strPost = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<ows:Batch OnError=\"Return\">" +
"<Method ID=\"A1\"><SetList>" + listGuid + "</SetList>" +
"<SetVar Name=\"ID\">New</SetVar>" +
"<SetVar Name=\"Cmd\">Save</SetVar>" +
"<SetVar Name=" + "\"urn:schemas-microsoft-com:office:office#Title\">" + "New</SetVar>" +
"<SetVar Name=" + "\"urn:schemas-microsoft-com:office:office#EmployeeAccount\">" + CurrentSite.CurrentUser.LoginName.ToString() + "</SetVar>" +
"<SetVar Name=" + "\"urn:schemas-microsoft-com:office:office#AssignmentID\">" + "ASN" + i + "</SetVar>" +
"</Method>" + "</ows:Batch>";
string strProcessBatch = SPContext.Current.Web.ProcessBatchData(strPost);
}
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
}
}
public bool IsAuditIDExist(string AuditID)
{
DataTable dtable = null;
bool IsAudit = false;
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
SPList oSpList = spWeb.Lists.TryGetList(AuditList);
if (oSpList != null)
{
dtable = new DataTable();
SPQuery oSpQuery = new SPQuery();
oSpQuery.Query = "<Where><Eq><FieldRef Name='AuditID' /><Value Type='Text'>" + AuditID + "</Value></Eq></Where>";
oSpQuery.ViewFields = "<FieldRef Name='AuditID' />";
oSpQuery.ViewFieldsOnly = true;
dtable = oSpList.GetItems(oSpQuery).GetDataTable();
if (dtable != null && dtable.Rows.Count > 0)
{
IsAudit = true;
}
else
{
IsAudit = false;
}
}
}
}
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
}
return IsAudit;
}
public DataTable GetEntitiesData(string SiteUrl, string ListName)
{
DataTable dtobservationdata = new DataTable();
using (SPSite site = new SPSite(SiteUrl))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists.TryGetList(ListName);
dtobservationdata = list.GetItems().GetDataTable();
return dtobservationdata;
}
}
}
public string GetEntitiesParentsChild(string EntitiesValue)
{
string EntityTitle = string.Empty;
string strEntityfinal = string.Empty;
DataTable dtable = null;
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
SPList oSpList = spWeb.Lists.TryGetList("Entities");
if (oSpList != null)
{
dtable = new DataTable();
SPQuery oSpQuery = new SPQuery();
oSpQuery.Query = "<Where><And><Eq><FieldRef Name='Title' /><Value Type='Text'>" + EntitiesValue + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
oSpQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='EntityId' /><FieldRef Name='ParentEntityID' />";
oSpQuery.ViewFieldsOnly = true;
dtable = oSpList.GetItems(oSpQuery).GetDataTable();
if (dtable != null && dtable.Rows.Count > 0)
{
bool IsParentEntity = false;
int IsParent = 0;
int EntityID = 0;
foreach (DataRow dritems in dtable.Rows)
{
if (IsParent == Convert.ToInt32(dritems["ParentEntityID"]))
{
IsParentEntity = true;
}
EntityID = Convert.ToInt32(dritems["EntityId"]);
}
if (IsParentEntity)
{
SPList oSPLISTs = spWeb.Lists.TryGetList("Entities");
if (oSPLISTs != null)
{
DataTable dtParents = new DataTable();
SPQuery strQuery = new SPQuery();
//<Where><And><Eq><FieldRef Name="ParentEntityID" /><Value Type="Number">1</Value></Eq><Eq><FieldRef Name="IsActive" /><Value Type="Boolean">1</Value></Eq></And></Where>
strQuery.Query = "<Where><And><Eq><FieldRef Name='ParentEntityID' /><Value Type='Number'>" + EntityID + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
strQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='EntityId' /><FieldRef Name='ParentEntityID' />";
strQuery.ViewFieldsOnly = true;
dtParents = oSPLISTs.GetItems(strQuery).GetDataTable();
if (dtParents != null && dtParents.Rows.Count > 0)
{
foreach (DataRow drParentsitems in dtParents.Rows)
{
EntityTitle += Convert.ToString(drParentsitems["Title"]) + ",";
}
strEntityfinal = EntitiesValue + "," + EntityTitle;
EntityTitle = strEntityfinal;
}
}
}
else
{
EntityTitle = EntitiesValue;
}
}
}
}
}
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
}
return EntityTitle.Trim(',');
}
public string GetLocationsParentsChild(string LocationsValue)
{
string LocationTitle = string.Empty;
string strLocationfinal = string.Empty;
DataTable dtable2 = null;
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
SPList oSpList = spWeb.Lists.TryGetList("Locations");
if (oSpList != null)
{
dtable2 = new DataTable();
SPQuery oSpQuery = new SPQuery();
oSpQuery.Query = "<Where><And><Eq><FieldRef Name='Title' /><Value Type='Text'>" + LocationsValue.Trim() + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
oSpQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='LocationId' /><FieldRef Name='ParentLocationID' />";
oSpQuery.ViewFieldsOnly = true;
dtable2 = oSpList.GetItems(oSpQuery).GetDataTable();
if (dtable2 != null && dtable2.Rows.Count > 0)
{
bool IsParentLocation = false;
int IsParent = 0;
int LocationID = 0;
foreach (DataRow dritems in dtable2.Rows)
{
if (IsParent == Convert.ToInt32(dritems["ParentLocationID"]))
{
IsParentLocation = true;
}
LocationID = Convert.ToInt32(dritems["LocationId"]);
}
if (IsParentLocation)
{
SPList oSPLISTs = spWeb.Lists.TryGetList("Locations");
if (oSPLISTs != null)
{
DataTable dtLocation = new DataTable();
SPQuery strQuery = new SPQuery();
//<Where><And><Eq><FieldRef Name="ParentEntityID" /><Value Type="Number">1</Value></Eq><Eq><FieldRef Name="IsActive" /><Value Type="Boolean">1</Value></Eq></And></Where>
strQuery.Query = "<Where><And><Eq><FieldRef Name='ParentLocationID' /><Value Type='Number'>" + LocationID + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
strQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='LocationId' /><FieldRef Name='ParentLocationID' />";
strQuery.ViewFieldsOnly = true;
dtLocation = oSPLISTs.GetItems(strQuery).GetDataTable();
if (dtLocation != null && dtLocation.Rows.Count > 0)
{
foreach (DataRow drParentsitems in dtLocation.Rows)
{
LocationTitle += Convert.ToString(drParentsitems["Title"]) + ",";
}
strLocationfinal = LocationsValue + "," + LocationTitle;
LocationTitle = strLocationfinal;
}
}
}
else
{
LocationTitle = LocationsValue;
}
}
}
}
}
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
}
return LocationTitle.Trim(',');
}
public string GetAuditScopeParentsChild(string AuditScopeValue)
{
string AuditScopeTitle = string.Empty;
string strAuditScopefinal = string.Empty;
DataTable dtable1 = null;
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
SPList oSpList = spWeb.Lists.TryGetList("Scopes");
if (oSpList != null)
{
dtable1 = new DataTable();
SPQuery oSpQuery = new SPQuery();
oSpQuery.Query = "<Where><And><Eq><FieldRef Name='Title' /><Value Type='Text'>" + AuditScopeValue + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
oSpQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='Scope_Id' /><FieldRef Name='ParentScopeId' />";
oSpQuery.ViewFieldsOnly = true;
dtable1 = oSpList.GetItems(oSpQuery).GetDataTable();
if (dtable1 != null && dtable1.Rows.Count > 0)
{
bool IsParentLocation = false;
int IsParent = 0;
int ScopeID = 0;
foreach (DataRow dritems in dtable1.Rows)
{
if (IsParent == Convert.ToInt32(dritems["ParentScopeId"]))
{
IsParentLocation = true;
}
ScopeID = Convert.ToInt32(dritems["Scope_Id"]);
}
if (IsParentLocation)
{
SPList oSPLISTs = spWeb.Lists.TryGetList("Scopes");
if (oSPLISTs != null)
{
DataTable dtScopes = new DataTable();
SPQuery strQuery = new SPQuery();
//<Where><And><Eq><FieldRef Name="ParentEntityID" /><Value Type="Number">1</Value></Eq><Eq><FieldRef Name="IsActive" /><Value Type="Boolean">1</Value></Eq></And></Where>
strQuery.Query = "<Where><And><Eq><FieldRef Name='ParentScopeId' /><Value Type='Number'>" + ScopeID + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
strQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='Scope_Id' /><FieldRef Name='ParentScopeId' />";
strQuery.ViewFieldsOnly = true;
dtScopes = oSPLISTs.GetItems(strQuery).GetDataTable();
if (dtScopes != null && dtScopes.Rows.Count > 0)
{
foreach (DataRow drParentsitems in dtScopes.Rows)
{
AuditScopeTitle += Convert.ToString(drParentsitems["Title"]) + ",";
}
strAuditScopefinal = AuditScopeValue + "," + AuditScopeTitle;
AuditScopeTitle = strAuditScopefinal;
}
}
}
else
{
AuditScopeTitle = AuditScopeValue;
}
}
}
}
}
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
}
return AuditScopeTitle.Trim(',');
}
#endregion
#region Download Method and events
protected void btnDownload_Click(object sender, EventArgs e)
{
try
{
lblExcelError.Text = "";
lblExcelError.Text = "";
DownloadListItemsAsExcelFile();
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Red;
}
}
protected void DownloadListItemsAsExcelFile()
{
try
{
string Url = SPContext.Current.Web.Url;
// int rowindex = 7;
//using (SPSite spSite = new SPSite(Url))
//{
// using (SPWeb spWeb = spSite.OpenWeb())
// {
SPSecurity.RunWithElevatedPrivileges(delegate()
{
string strFileLocationPath = "~/_layouts/AuditTemplate/IAAAuditTemplate.xlsx";
string FileLocation = HttpContext.Current.Server.MapPath(strFileLocationPath);
string FileName = "AuditTemplate" + DateTime.Now.ToString("dd_MMM_yyyy_HH_mm_ss") + ".xlsx";
string strDestPath = "~/_layouts/AuditTemplate/";
string DestPath = HttpContext.Current.Server.MapPath(strDestPath + FileName);
System.IO.DirectoryInfo myDirInfo = new DirectoryInfo(HttpContext.Current.Server.MapPath(strDestPath));
File.Copy(FileLocation, DestPath);
string ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileLocation + "; Extended Properties='Excel 12.0 Macro;HDR=NO';";
var file = new FileInfo(DestPath);
using (var pck = new ExcelPackage(file))
{
ExcelWorksheet ws = pck.Workbook.Worksheets[1];
ExcelWorksheet masterdataAuditRating = pck.Workbook.Worksheets["AuditRating"];
ExcelWorksheet masterdataAuditScope = pck.Workbook.Worksheets["AuditScope"];
ExcelWorksheet masterdataLocations = pck.Workbook.Worksheets["Locations"];
ExcelWorksheet masterdataEntities = pck.Workbook.Worksheets["Entities"];
ExcelWorksheet masterdataAuditOwner = pck.Workbook.Worksheets["AuditOwner"];
ExcelWorksheet masterdataAuditTeam = pck.Workbook.Worksheets["AuditTeam"];
ExcelWorksheet masterdataAuditLead = pck.Workbook.Worksheets["AuditLead"];
ExcelWorksheet masterdataAuditManager = pck.Workbook.Worksheets["AuditManager"];
ws.Cells[4, 1].Value = SPContext.Current.Web.CurrentUser.Name;
ws.Cells[4, 4].Value = DateTime.Now.ToString("MM/dd/yyyy");
SPListItemCollection AuditRatings = getsplistitemcoll(SPContext.Current.Web, "AuditRating");
var objAuditRatingslist = GetListvalue(AuditRatings, "Title");
int count0 = 1;
foreach (var objAuditRatings in objAuditRatingslist)
{
masterdataAuditRating.Cells[count0, 1].Value = objAuditRatings.val.Trim();
count0 = count0 + 1;
}
SPListItemCollection scopes = getsplistitemcoll(SPContext.Current.Web, "Scopes");
var objScopelist = GetListvalue(scopes, "Title");
int count = 1;
foreach (var objScope in objScopelist)
{
masterdataAuditScope.Cells[count, 1].Value = objScope.val.Trim();
count = count + 1;
}
SPListItemCollection SPLocationsColl = getsplistitemcoll(SPContext.Current.Web, "Locations");
int count1 = 1;
var listLocationsvalue = GetListvalue(SPLocationsColl, "Title");
foreach (var bbLocations in listLocationsvalue)
{
masterdataLocations.Cells[count1, 1].Value = bbLocations.val.Trim();
count1 = count1 + 1;
}
SPListItemCollection SPEntitiesColl = getsplistitemcoll(SPContext.Current.Web, "Entities");
int count2 = 1;
var listEntitiesvalue = GetListvalue(SPEntitiesColl, "Title");
foreach (var bbEntities in listEntitiesvalue)
{
masterdataEntities.Cells[count2, 1].Value = bbEntities.val.Trim();
count2 = count2 + 1;
}
int count3 = 1;
var listAuditOwnervalue = GetOwner(SPContext.Current.Web, "Business User");
foreach (var bbAuditOwner in listAuditOwnervalue)
{
masterdataAuditOwner.Cells[count3, 1].Value = bbAuditOwner.ToString();
count3 = count3 + 1;
}
int count4 = 1;
var listAuditTeamvalue = GetOwner(SPContext.Current.Web, "Audit User");
foreach (var bbAuditTeam in listAuditTeamvalue)
{
masterdataAuditTeam.Cells[count4, 1].Value = bbAuditTeam.ToString();
count4 = count4 + 1;
}
int count5 = 1;
var listAuditLeadvalue = GetOwner(SPContext.Current.Web, "Audit User");
foreach (var bbAuditLead in listAuditTeamvalue)
{
masterdataAuditLead.Cells[count5, 1].Value = bbAuditLead.ToString();
count5 = count5 + 1;
}
int count6 = 1;
var listAuditManagervalue = GetOwner(SPContext.Current.Web, "Audit User");
foreach (var bbAuditManager in listAuditManagervalue)
{
masterdataAuditManager.Cells[count6, 1].Value = bbAuditManager.ToString();
count6 = count6 + 1;
}
ws.Cells.AutoFitColumns();
ws.Column(200).Hidden = true;
pck.SaveAs(this.Page.Response.OutputStream);
lblExcelError.Visible = true;
lblExcelError.Text = "File Download Successfully";
lblExcelError.ForeColor = System.Drawing.Color.Green;
}
this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
this.Page.Response.AddHeader("content-disposition", "attachment; filename=IAAAuditTemplate_on_" + DateTime.Now.ToString("dd_MMM_yyyy_HH_mm_ss") + ".xlsx");
this.Page.Response.Flush();
this.Page.Response.End();
this.Page.Response.Clear();
this.Page.Response.ClearContent();
//this.Page.Response.ClearHeaders();
});
// }
//}
}
catch (Exception ex)
{
lblExcelError.Text = "Error in download file" + ex.Message.ToString();
}
}
private List<String> GetOwner(SPWeb web, string groupName)
{
try
{
List<String> lstuser = getGroupUsers(groupName, web);
return lstuser;
}
catch (Exception)
{
throw;
}
}
public static List<String> getGroupUsers(string groupName, SPWeb web)
{
List<String> groupMembers = new List<String>();
SPSecurity.RunWithElevatedPrivileges(delegate()
{
SPGroup group = web.SiteGroups[groupName];
foreach (SPUser user in group.Users)
{
groupMembers.Add(user.Name);
}
});
return groupMembers;
}
public SPUser EnsureUser(SPWeb web, string username)
{
SPUser user = null;
SPSecurity.RunWithElevatedPrivileges(
delegate()
{
using (var site = new SPSite(web.Site.ID))
{
using (SPWeb elevatedWeb = site.OpenWeb(web.ID))
{
elevatedWeb.AllowUnsafeUpdates = true;
user = elevatedWeb.EnsureUser(username);
}
}
});
return user;
}
#endregion
}
}
Excel Template Format
| Audit Details | |||||||||||||
| Generated By | Generated on | ||||||||||||
| Audit ID(*) | Audit Name(*) | Entity(*) | Locations(*) | Audit Status(*) | Audit Description | Audit Start Date(mm/dd/yyyy)(*) | Audit End Date(mm/dd/yyyy) | Audit Owner(*) | Audit Team(*) | Audit Lead(*) | Audit Manager(*) | Rating | Audit Scope(*) |
aspx page code
<script type="text/javascript">
function timedRefresh(timeoutPeriod) {
setTimeout(function () { _spFormOnSubmitCalled = false; }, 2000);
return true;
}
</script>
<div class="speedbar">
<div class="speedbar-nav">
IAA > Audit Upload
</div>
</div>
<div class="contentWrapper">
<div class="grid-full">
<div class="title-grid">
Audit Upload</div>
<div class="content-gird">
<div align="center">
<asp:Label ID="lblExcelError" runat="server"></asp:Label><br />
<asp:Label ID="lblMessCon" runat="server"></asp:Label></div>
<div class="control">
<label class="long">
<span>File Upload</span></label>
<asp:FileUpload ID="AuditExcelUpload" runat="server" />
</div>
<div class="control">
<asp:Button ID="btnAuditUpload" runat="server" Text="Upload Excel Data" CssClass="button-a dark-blue"
ValidationGroup="None" OnClientClick="javascript: timedRefresh();" OnClick="btnAuditUpload_Click" />
<asp:Button ID="btnDownload" runat="server" Text="Download Audit Template" OnClientClick="javascript: timedRefresh(2000)"
CssClass="button-a dark-blue btnMarginLeft" OnClick="btnDownload_Click" />
</div>
<div class="control" align="center">
<asp:Label ID="lblAuditID" runat="server" Visible="false" ForeColor="Red" ></asp:Label>
</div>
<asp:Label ID="AuditUploadmsg" runat="server" Text="" EnableViewState="false" ForeColor="Red"></asp:Label>
</div>
</div>
</div>
.... code End
aspx.cs code
using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web;
using System.IO;
using OfficeOpenXml;
using Microsoft.SharePoint;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.OleDb;
using Microsoft.SharePoint.Utilities;
namespace EY.IAA.WebParts.AuditUpload
{
public partial class AuditUploadUserControl : UserControl
{
bool IsAuditID = false;
string AuditList = "Audit";
//string AuditList = "Auditss";
private readonly string BatchXMLBanner = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><ows:Batch OnError=\"Return\">{0}</ows:Batch>";
private readonly string BatchXMLMethod = "<Method ID=\"{0}\"><SetList>{1}</SetList><SetVar Name=\"ID\">New</SetVar><SetVar Name=\"Cmd\">Save</SetVar>{2}</Method>";
private readonly string BatchXMLSetVar = "<SetVar Name=\"urn:schemas-microsoft-com:office:office#{0}\">{1}</SetVar>";
protected void Page_Load(object sender, EventArgs e)
{
}
#region Upload Method and events
protected void btnAuditUpload_Click(object sender, EventArgs e)
{
try
{
//bulkupload();
UploadExcelFileToSharePointList();
}
catch(Exception ex)
{
lblExcelError.Text = ex.Message.ToString();
}
}
private void UploadExcelFileToSharePointList()
{
if (!AuditExcelUpload.HasFile)
{
lblExcelError.Visible = true;
lblExcelError.Text = "Please select a valid Excel File";
return;
}
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
spWeb.AllowUnsafeUpdates = true;
SPList list = spWeb.Lists[AuditList];
try
{
byte[] fileData = AuditExcelUpload.FileBytes;
using (MemoryStream memStream = new MemoryStream(fileData))
{
memStream.Flush();
using (ExcelPackage pck = new ExcelPackage(memStream))
{
if (pck != null)
{
//CreateListItem(pck, list, spWeb);
CreateListItemTest(pck, list, spWeb);
}
}
}
}
catch (Exception Ex1)
{
lblExcelError.Text = "Error Occured <br/>" + Ex1.Message;
}
}
}
}
catch (Exception Ex)
{
lblExcelError.Text = "Error Occured <br/>" + Ex.Message;
}
}
private void CreateListItem(ExcelPackage pck, SPList list, SPWeb web)
{
Dictionary<int, string> column = GetColumnMapping();
ExcelWorksheet ws = pck.Workbook.Worksheets[1];
ws.Column(200).Hidden = false;
int rowCount = ws.Dimension.End.Row + 1;
int colCount = ws.Dimension.End.Column + 1;
string Errorline = string.Empty;
Int32 Errorlinenumber;
string collistid = string.Empty;
for (int i = 7; i < rowCount; i++) // Row index starts from 7, as the first row is Title
{
try
{
SPListItem item = list.Items.Add();
web.AllowUnsafeUpdates = true;
if (ws.Cells[i, 200].Value == null)
{
for (int j = 1; j <= 14; j++)
{
string v = Convert.ToString(ws.Cells[i, j].Value);
switch (j)
{
case 1:
//collistid = getlist("Audit", v.Trim(), "AuditID");
//item[column[j]] = collistid + ";#" + v.Trim();
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = Convert.ToString(ws.Cells[i, j].Value);
}
break;
case 2:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = Convert.ToString(ws.Cells[i, j].Value);
}
break;
case 3:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 4:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 5:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 6:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 7:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = Convert.ToDateTime(ws.Cells[i, j].Value);
}
break;
case 8:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = Convert.ToDateTime(ws.Cells[i, j].Value);
}
break;
case 9:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
// item[column[j]] = ws.Cells[i, j].Value;
User userApprover = null;
string Approver = string.Empty;
Approver = Convert.ToString(ws.Cells[i, j].Value);
userApprover = GetSiteUser(SPContext.Current.Web.Url, Approver);
item[column[j]] = userApprover;
}
break;
case 10:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 11:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 12:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 13:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
collistid = getlist("AuditRating", ws.Cells[i, j].Value.ToString().Trim(), "Rating");
item[column[j]] = collistid + ";#" + ws.Cells[i, j].Value;
// item[column[j]] = ws.Cells[i, j].Value;
}
break;
case 14:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
collistid = getlist("AuditProcess", ws.Cells[i, j].Value.ToString().Trim(), "Process Name");
item[column[j]] = collistid + ";#" + ws.Cells[i, j].Value;
//item[column[j]] = ws.Cells[i, j].Value;
}
break;
}
}
item.Update();
web.AllowUnsafeUpdates = false;
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Green;
lblExcelError.Text = "Audit uploaded sucessfully.";
}
else
{
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Green;
lblExcelError.Text = "Audit uploaded sucessfully.";
}
}
catch (Exception)
{
Errorlinenumber = i - 6;
Errorline += Errorlinenumber + ",";
}
}
//InsertBatchData(web, list);
lblExcelError.Visible = true;
if (Errorline != string.Empty)
{
Errorline = Errorline.Remove(Errorline.Length - 1);
lblExcelError.Text = Errorline + " items is not uploaded successfully.";
}
else
{
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Green;
lblExcelError.Text = "Audit uploaded sucessfully";
}
}
private Dictionary<int, string> GetColumnMapping()
{
Dictionary<int, string> map = new Dictionary<int, string>();
//map.Add(1, "Audit ID");
//map.Add(2, "Audit Name"); // First parameter is the column Index in Excel Sheet and Second Param is SharePoint List's Column Name (Display Name )
//map.Add(3, "Entity");
//map.Add(4, "Locations");
//map.Add(5, "Audit Status");
//map.Add(6, "Audit Description");
//map.Add(7, "Audit Start Date");
//map.Add(8, "Audit End Date");
//map.Add(9, "Audit Owner");
//map.Add(10, "Audit Team");
//map.Add(11, "Audit Lead");
//map.Add(12, "Audit Manager");
//map.Add(13, "Rating");
//map.Add(14, "Process");
map.Add(1, "AuditID");
map.Add(2, "Title");
map.Add(3, "Entity");
map.Add(4, "Locations");
map.Add(5, "AuditStatus");
map.Add(6, "AuditDescription");
map.Add(7, "AuditStartDate");
map.Add(8, "AuditEndDate");
map.Add(9, "AuditOwner");
map.Add(10, "AuditTeam");
map.Add(11, "AuditLead");
map.Add(12, "AuditManager");
map.Add(13, "Rating");
map.Add(14, "AuditScope");
return map;
}
private string getlist(string listname, string fieldname, string listfieldTitle)
{
SPSite oSite = new SPSite(SPContext.Current.Web.Url);
SPWeb oweb = oSite.OpenWeb();
SPList olist = oweb.Lists[listname];
string fieldTitle = fieldname;
Regex rgx = new Regex("/n");
string result = rgx.Replace(fieldname, "");
int count = olist.ItemCount;
SPQuery objQuery = new SPQuery();
{
objQuery.Query = @"<Where><Eq><FieldRef Name=" + listfieldTitle + " /><Value Type='Text'>" + result.Trim() + "</Value></Eq></Where>";
};
DataTable dt = olist.GetItems(objQuery).GetDataTable();
string colid = string.Empty;
if (dt != null)
{
SPListItemCollection colItems = olist.GetItems(objQuery);
colid = colItems[0]["ID"].ToString();
}
return colid;
}
private SPListItemCollection getsplistitemcoll(SPWeb oweb, string listnme)
{
SPList olist = oweb.Lists[listnme];
DataTable sdqf = olist.GetItems().GetDataTable();
SPListItemCollection colItems = olist.GetItems();
return colItems;
}
public List<Listval> GetListvalue(SPListItemCollection lisititemcoll, string fieldTitle)
{
var count = lisititemcoll.Count;
List<Listval> lst = new List<Listval>();
Listval obj = null;
for (int i = 0; i < count; i++)
{
string colid = lisititemcoll[i][fieldTitle].ToString();
obj = new Listval();
obj.val = colid;
lst.Add(obj);
}
return lst;
}
public void InsertBatchData123(SPWeb web, SPList listdata)
{
try
{
SPListItemCollection listItem = listdata.GetItems();
StringBuilder sbadd = new StringBuilder();
string xmlFormat = @"<?xml version=1.0 encoding=UTF-8?\>";
sbadd.Append(xmlFormat);
sbadd.Append("<Batch>");
sbadd.Append(@"<Method ID='1' Cmd='New'>");
string buildFields = "<Field Name='Audit ID'>{0}</Field>";
buildFields = buildFields + "<Field Name='Audit Name'>{1}</Field>";
buildFields = buildFields + "<Field Name='Entity'>{2}</Field>";
buildFields = buildFields + "<Field Name='Locations'>{3}</Field>";
buildFields = buildFields + "<Field Name='Audit Status'>{4}</Field>";
buildFields = buildFields + "<Field Name='Audit Description'>{5}</Field>";
buildFields = buildFields + "<Field Name='Audit Start Date'>{6}</Field>";
buildFields = buildFields + "<Field Name='Audit End Date'>{7}</Field>";
buildFields = buildFields + "<Field Name='Audit Owner'>{8}</Field>";
buildFields = buildFields + "<Field Name='Audit Team'>{9}</Field>";
buildFields = buildFields + "<Field Name='Audit Lead'>{10}</Field>";
buildFields = buildFields + "<Field Name='Audit Manager'>{11}</Field>";
buildFields = buildFields + "<Field Name='Rating'>{12}</Field>";
buildFields = buildFields + "<Field Name='Process'>{13}</Field>";
foreach (SPListItem item in listItem)
{
sbadd.Append(string.Format
(buildFields, Convert.ToString(item["Audit ID"]).Trim(),
Convert.ToString(item["Audit Name"]).Trim(),
Convert.ToString(item["Entity"]).Trim(),
Convert.ToString(item["Locations"]).Trim(),
Convert.ToString(item["Audit Status"]).Trim(),
Convert.ToString(item["Audit Description"]).Trim(),
Convert.ToDateTime(item["Audit Start Date"]),
Convert.ToDateTime(item["Audit End Date"]),
Convert.ToString(item["Audit Owner"]).Trim(),
Convert.ToString(item["Audit Team"]).Trim(),
Convert.ToString(item["Audit Lead"]).Trim(),
Convert.ToString(item["Audit Manager"]).Trim(),
Convert.ToString(item["Rating"]).Trim(),
Convert.ToString(item["Process"]).Trim()
));
}
sbadd.Append(@"</Method>");
sbadd.Append("</Batch>");
web.ProcessBatchData(sbadd.ToString());
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message.ToString();
}
}
private string InsertBatchData(SPWeb web, SPList listdata)
{
StringBuilder XMLSetVarLines = new StringBuilder();
StringBuilder XMLSetMethods = new StringBuilder();
string guid;
int methodID = 0;
Dictionary<int, string> listFieldName = GetColumnMapping();
SPListItemCollection listItem = listdata.GetItems();
foreach (SPListItem item in listItem)
{
guid = Convert.ToString(item["ID"]);
for (int i = 1; i <= listFieldName.Count; i++)
{
string values;
listFieldName.TryGetValue(i, out values);
var fieldname = values;
var fieldvalue = Convert.ToString(item[fieldname]);
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, fieldname, fieldvalue));
}
XMLSetMethods.Append(String.Format(BatchXMLMethod, methodID, guid, XMLSetVarLines));
methodID++;
XMLSetVarLines.Length = 0;
}
return web.ProcessBatchData(String.Format(BatchXMLBanner, XMLSetMethods));
}
private void CreateListItemTest(ExcelPackage pck, SPList list, SPWeb web)
{
Dictionary<string, User> userDic = new Dictionary<string, User>();
Dictionary<int, string> column = GetColumnMapping();
ExcelWorksheet ws = pck.Workbook.Worksheets[1];
StringBuilder XMLSetVarLines = new StringBuilder();
StringBuilder XMLSetMethods = new StringBuilder();
int methodID = 0;
ws.Column(200).Hidden = false;
int rowCount = ws.Dimension.End.Row + 1;
int colCount = ws.Dimension.End.Column + 1;
string Errorline = string.Empty;
Int32 Errorlinenumber;
string collistid = string.Empty;
string Url = SPContext.Current.Web.Url;
SPSite site = new SPSite(Url);
SPWeb CurrentSite = SPContext.Current.Web;
SPList Auditlists = CurrentSite.Lists[AuditList];
string guid = Auditlists.ID.ToString();
web.AllowUnsafeUpdates = true;
bool AuditIDCheck = false;
string AuditIDValue = string.Empty;
for (int i = 7; i < rowCount; i++) // Row index starts from 7, as the first row is Title
{
try
{
//SPListItem item = list.Items.Add();
if (ws.Cells[i, 200].Value == null)
{
for (int j = 1; j <= 14; j++)
{
string values;
column.TryGetValue(j, out values);
string v = Convert.ToString(ws.Cells[i, j].Value);
switch (j)
{
case 1:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
AuditIDCheck = IsAuditIDExist(Convert.ToString(ws.Cells[i, j].Value));
if (AuditIDCheck)
{
AuditIDValue += Convert.ToString(ws.Cells[i, j].Value) + ",";
IsAuditID = AuditIDCheck;
}
}
break;
case 2:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
}
break;
case 3:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
// below code running
//XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
string EntityData = string.Empty;
EntityData = GetEntitiesParentsChild(Convert.ToString(ws.Cells[i, j].Value));
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(EntityData)));
}
break;
case 4:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
// below code running
//XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
string LocationsData = string.Empty;
LocationsData = GetLocationsParentsChild(Convert.ToString(ws.Cells[i, j].Value));
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(LocationsData)));
}
break;
case 5:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
}
break;
case 6:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value)));
}
break;
case 7:
// if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
string AuditStartDate = (SPUtility.CreateISO8601DateTimeFromSystemDateTime(Convert.ToDateTime(ws.Cells[i, j].Value.ToString())));
//DateTime ccc = Convert.ToDateTime(ws.Cells[i, j].Value).Date;
// if (!string.IsNullOrEmpty(Convert.ToDateTime(Convert.ToString(ws.Cells[i, j].Value)).ToString("dd-MMM-yyyy")))
if (!string.IsNullOrEmpty(AuditStartDate))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, AuditStartDate));//Convert.ToDateTime(ws.Cells[i, j].Value).Date));// Convert.ToDateTime(Convert.ToString(ws.Cells[i, j].Value)).ToString("dd-MMM-yyyy")));
}
break;
case 8:
string AuditEndDate = (SPUtility.CreateISO8601DateTimeFromSystemDateTime(Convert.ToDateTime(ws.Cells[i, j].Value.ToString())));
if (!string.IsNullOrEmpty(AuditEndDate))
{
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, AuditEndDate));//Convert.ToDateTime(ws.Cells[i, j].Value).Date));// Convert.ToDateTime(Convert.ToString(ws.Cells[i, j].Value)).ToString("dd-MMM-yyyy")));
}
break;
case 9:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
SPUser userOwner = EnsureUser(web, Convert.ToString(ws.Cells[i, j].Value));
string userOwners = Convert.ToString(userOwner.ID);
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, userOwners));
}
break;
case 10:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
SPUser userTeam = EnsureUser(web, Convert.ToString(ws.Cells[i, j].Value));
string userTeams = Convert.ToString(userTeam.ID);
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, userTeams));
}
break;
case 11:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
SPUser userLead = EnsureUser(web, Convert.ToString(ws.Cells[i, j].Value));
string userLeands = Convert.ToString(userLead.ID);
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, userLeands));
// XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(ws.Cells[i, j].Value).Trim()));
}
break;
case 12:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
SPUser userManager = EnsureUser(web, Convert.ToString(ws.Cells[i, j].Value));
string userManagers = Convert.ToString(userManager.ID);
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, userManagers));
//XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, userss));//Convert.ToString(ws.Cells[i, j].Value).Trim()));
}
break;
case 13:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
SPList olist = web.Lists["AuditRating"];
string fieldTitle = ws.Cells[i, j].Value.ToString();
SPQuery objQuery = new SPQuery();
{
objQuery.Query = @"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>" + fieldTitle + "</Value></Eq></Where>";
};
SPListItemCollection colItems = olist.GetItems(objQuery);
string colid = colItems[0]["ID"].ToString();
String fieldValue = colid + ";#" + fieldTitle;
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, fieldValue));
}
break;
case 14:
if (!string.IsNullOrEmpty(Convert.ToString(ws.Cells[i, j].Value)))
{
//SPList olist = web.Lists["Scopes"];
//string fieldTitle = ws.Cells[i, j].Value.ToString();
//SPQuery objQuery = new SPQuery();
//{
// objQuery.Query = @"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>" + fieldTitle + "</Value></Eq></Where>";
//};
//SPListItemCollection colItems = olist.GetItems(objQuery);
//string colid = colItems[0]["ID"].ToString();
//String fieldValue = colItems[0]["Title"].ToString();
//String fieldValue = colid + ";#" + fieldTitle;
//String fieldValue = ";#" + fieldTitle;
// below code running
//XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, ws.Cells[i, j].Value));
string AuditScopeData = string.Empty;
AuditScopeData = GetAuditScopeParentsChild(Convert.ToString(ws.Cells[i, j].Value));
XMLSetVarLines.Append(String.Format(BatchXMLSetVar, values, Convert.ToString(AuditScopeData)));
}
break;
}
}
// item.Update();
}
else
{
// updatelistitem(i, list, pck);
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Green;
lblExcelError.Text = "Audit upload sucessfully.";
}
}
catch (Exception)
{
Errorlinenumber = i - 6;
Errorline += Errorlinenumber + ",";
}
methodID++;
XMLSetMethods.Append(String.Format(BatchXMLMethod, methodID, guid, XMLSetVarLines));
}
XMLSetVarLines.Length = 0;
if (IsAuditID)
{
lblAuditID.Text = String.Format("AuditID {0} already exists in Audit list.Please rename and upload again.", AuditIDValue.TrimEnd(','));
lblAuditID.Visible = true;
}
else
{
string result = web.ProcessBatchData(String.Format(BatchXMLBanner, XMLSetMethods));
lblAuditID.Visible = false;
}
web.AllowUnsafeUpdates = false;
//bool IsSave = false;
//if (!string.IsNullOrEmpty(result))
//{
// try
// {
// Int64 i = Convert.ToInt64(result);
// if (i > 0)
// {
// IsSave = true;
// }
// }
// catch (Exception)
// {
// IsSave = false;
// }
//}
if (!IsAuditID)
{
if (methodID > 0)
{
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Green;
lblExcelError.Text = "Audit upload sucessfully.";
}
else
{
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Red;
lblExcelError.Text = "File is not upload sucessfully"; //result;
}
}
}
public class Listval
{
public string val { set; get; }
}
public class User
{
public int UserId
{
get;
set;
}
public string LoginName
{
get;
set;
}
public string Email
{
get;
set;
}
public string Name { get; set; }
}
public static User GetSiteUser(string siteUrl, string userName)
{
User user = null;
//SPSecurity.RunWithElevatedPrivileges(delegate()
//{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.OpenWeb())
{
SPUser spUser = web.EnsureUser(userName);
if (spUser != null)
{
user = new User();
user.UserId = spUser.ID;
user.Email = spUser.Email;
user.LoginName = spUser.LoginName;
user.Name = spUser.Name;
}
}
}
//});
return user;
}
public void bulkupload()
{
try
{
string Url = SPContext.Current.Web.Url;
SPSite site = new SPSite(Url);
StringBuilder methodBuilder = new StringBuilder();
string batch = string.Empty;
DateTime currentDate = DateTime.Now;
string formattedDate = DateTime.Now.ToString();
string methodFormat = string.Empty;
SPWeb CurrentSite = SPContext.Current.Web;
// Get the list containing the items to update.
SPList list = CurrentSite.Lists[AuditList];
string listGuid = list.ID.ToString();
for (int i = 0; i < 1000; i++)
{
int itemID = i;
string strPost = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<ows:Batch OnError=\"Return\">" +
"<Method ID=\"A1\"><SetList>" + listGuid + "</SetList>" +
"<SetVar Name=\"ID\">New</SetVar>" +
"<SetVar Name=\"Cmd\">Save</SetVar>" +
"<SetVar Name=" + "\"urn:schemas-microsoft-com:office:office#Title\">" + "New</SetVar>" +
"<SetVar Name=" + "\"urn:schemas-microsoft-com:office:office#EmployeeAccount\">" + CurrentSite.CurrentUser.LoginName.ToString() + "</SetVar>" +
"<SetVar Name=" + "\"urn:schemas-microsoft-com:office:office#AssignmentID\">" + "ASN" + i + "</SetVar>" +
"</Method>" + "</ows:Batch>";
string strProcessBatch = SPContext.Current.Web.ProcessBatchData(strPost);
}
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
}
}
public bool IsAuditIDExist(string AuditID)
{
DataTable dtable = null;
bool IsAudit = false;
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
SPList oSpList = spWeb.Lists.TryGetList(AuditList);
if (oSpList != null)
{
dtable = new DataTable();
SPQuery oSpQuery = new SPQuery();
oSpQuery.Query = "<Where><Eq><FieldRef Name='AuditID' /><Value Type='Text'>" + AuditID + "</Value></Eq></Where>";
oSpQuery.ViewFields = "<FieldRef Name='AuditID' />";
oSpQuery.ViewFieldsOnly = true;
dtable = oSpList.GetItems(oSpQuery).GetDataTable();
if (dtable != null && dtable.Rows.Count > 0)
{
IsAudit = true;
}
else
{
IsAudit = false;
}
}
}
}
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
}
return IsAudit;
}
public DataTable GetEntitiesData(string SiteUrl, string ListName)
{
DataTable dtobservationdata = new DataTable();
using (SPSite site = new SPSite(SiteUrl))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists.TryGetList(ListName);
dtobservationdata = list.GetItems().GetDataTable();
return dtobservationdata;
}
}
}
public string GetEntitiesParentsChild(string EntitiesValue)
{
string EntityTitle = string.Empty;
string strEntityfinal = string.Empty;
DataTable dtable = null;
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
SPList oSpList = spWeb.Lists.TryGetList("Entities");
if (oSpList != null)
{
dtable = new DataTable();
SPQuery oSpQuery = new SPQuery();
oSpQuery.Query = "<Where><And><Eq><FieldRef Name='Title' /><Value Type='Text'>" + EntitiesValue + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
oSpQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='EntityId' /><FieldRef Name='ParentEntityID' />";
oSpQuery.ViewFieldsOnly = true;
dtable = oSpList.GetItems(oSpQuery).GetDataTable();
if (dtable != null && dtable.Rows.Count > 0)
{
bool IsParentEntity = false;
int IsParent = 0;
int EntityID = 0;
foreach (DataRow dritems in dtable.Rows)
{
if (IsParent == Convert.ToInt32(dritems["ParentEntityID"]))
{
IsParentEntity = true;
}
EntityID = Convert.ToInt32(dritems["EntityId"]);
}
if (IsParentEntity)
{
SPList oSPLISTs = spWeb.Lists.TryGetList("Entities");
if (oSPLISTs != null)
{
DataTable dtParents = new DataTable();
SPQuery strQuery = new SPQuery();
//<Where><And><Eq><FieldRef Name="ParentEntityID" /><Value Type="Number">1</Value></Eq><Eq><FieldRef Name="IsActive" /><Value Type="Boolean">1</Value></Eq></And></Where>
strQuery.Query = "<Where><And><Eq><FieldRef Name='ParentEntityID' /><Value Type='Number'>" + EntityID + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
strQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='EntityId' /><FieldRef Name='ParentEntityID' />";
strQuery.ViewFieldsOnly = true;
dtParents = oSPLISTs.GetItems(strQuery).GetDataTable();
if (dtParents != null && dtParents.Rows.Count > 0)
{
foreach (DataRow drParentsitems in dtParents.Rows)
{
EntityTitle += Convert.ToString(drParentsitems["Title"]) + ",";
}
strEntityfinal = EntitiesValue + "," + EntityTitle;
EntityTitle = strEntityfinal;
}
}
}
else
{
EntityTitle = EntitiesValue;
}
}
}
}
}
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
}
return EntityTitle.Trim(',');
}
public string GetLocationsParentsChild(string LocationsValue)
{
string LocationTitle = string.Empty;
string strLocationfinal = string.Empty;
DataTable dtable2 = null;
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
SPList oSpList = spWeb.Lists.TryGetList("Locations");
if (oSpList != null)
{
dtable2 = new DataTable();
SPQuery oSpQuery = new SPQuery();
oSpQuery.Query = "<Where><And><Eq><FieldRef Name='Title' /><Value Type='Text'>" + LocationsValue.Trim() + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
oSpQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='LocationId' /><FieldRef Name='ParentLocationID' />";
oSpQuery.ViewFieldsOnly = true;
dtable2 = oSpList.GetItems(oSpQuery).GetDataTable();
if (dtable2 != null && dtable2.Rows.Count > 0)
{
bool IsParentLocation = false;
int IsParent = 0;
int LocationID = 0;
foreach (DataRow dritems in dtable2.Rows)
{
if (IsParent == Convert.ToInt32(dritems["ParentLocationID"]))
{
IsParentLocation = true;
}
LocationID = Convert.ToInt32(dritems["LocationId"]);
}
if (IsParentLocation)
{
SPList oSPLISTs = spWeb.Lists.TryGetList("Locations");
if (oSPLISTs != null)
{
DataTable dtLocation = new DataTable();
SPQuery strQuery = new SPQuery();
//<Where><And><Eq><FieldRef Name="ParentEntityID" /><Value Type="Number">1</Value></Eq><Eq><FieldRef Name="IsActive" /><Value Type="Boolean">1</Value></Eq></And></Where>
strQuery.Query = "<Where><And><Eq><FieldRef Name='ParentLocationID' /><Value Type='Number'>" + LocationID + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
strQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='LocationId' /><FieldRef Name='ParentLocationID' />";
strQuery.ViewFieldsOnly = true;
dtLocation = oSPLISTs.GetItems(strQuery).GetDataTable();
if (dtLocation != null && dtLocation.Rows.Count > 0)
{
foreach (DataRow drParentsitems in dtLocation.Rows)
{
LocationTitle += Convert.ToString(drParentsitems["Title"]) + ",";
}
strLocationfinal = LocationsValue + "," + LocationTitle;
LocationTitle = strLocationfinal;
}
}
}
else
{
LocationTitle = LocationsValue;
}
}
}
}
}
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
}
return LocationTitle.Trim(',');
}
public string GetAuditScopeParentsChild(string AuditScopeValue)
{
string AuditScopeTitle = string.Empty;
string strAuditScopefinal = string.Empty;
DataTable dtable1 = null;
try
{
string Url = SPContext.Current.Web.Url;
using (SPSite spSite = new SPSite(Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
SPList oSpList = spWeb.Lists.TryGetList("Scopes");
if (oSpList != null)
{
dtable1 = new DataTable();
SPQuery oSpQuery = new SPQuery();
oSpQuery.Query = "<Where><And><Eq><FieldRef Name='Title' /><Value Type='Text'>" + AuditScopeValue + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
oSpQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='Scope_Id' /><FieldRef Name='ParentScopeId' />";
oSpQuery.ViewFieldsOnly = true;
dtable1 = oSpList.GetItems(oSpQuery).GetDataTable();
if (dtable1 != null && dtable1.Rows.Count > 0)
{
bool IsParentLocation = false;
int IsParent = 0;
int ScopeID = 0;
foreach (DataRow dritems in dtable1.Rows)
{
if (IsParent == Convert.ToInt32(dritems["ParentScopeId"]))
{
IsParentLocation = true;
}
ScopeID = Convert.ToInt32(dritems["Scope_Id"]);
}
if (IsParentLocation)
{
SPList oSPLISTs = spWeb.Lists.TryGetList("Scopes");
if (oSPLISTs != null)
{
DataTable dtScopes = new DataTable();
SPQuery strQuery = new SPQuery();
//<Where><And><Eq><FieldRef Name="ParentEntityID" /><Value Type="Number">1</Value></Eq><Eq><FieldRef Name="IsActive" /><Value Type="Boolean">1</Value></Eq></And></Where>
strQuery.Query = "<Where><And><Eq><FieldRef Name='ParentScopeId' /><Value Type='Number'>" + ScopeID + "</Value></Eq><Eq><FieldRef Name='IsActive' /><Value Type='Boolean'>1</Value></Eq></And></Where>";
strQuery.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='Scope_Id' /><FieldRef Name='ParentScopeId' />";
strQuery.ViewFieldsOnly = true;
dtScopes = oSPLISTs.GetItems(strQuery).GetDataTable();
if (dtScopes != null && dtScopes.Rows.Count > 0)
{
foreach (DataRow drParentsitems in dtScopes.Rows)
{
AuditScopeTitle += Convert.ToString(drParentsitems["Title"]) + ",";
}
strAuditScopefinal = AuditScopeValue + "," + AuditScopeTitle;
AuditScopeTitle = strAuditScopefinal;
}
}
}
else
{
AuditScopeTitle = AuditScopeValue;
}
}
}
}
}
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
}
return AuditScopeTitle.Trim(',');
}
#endregion
#region Download Method and events
protected void btnDownload_Click(object sender, EventArgs e)
{
try
{
lblExcelError.Text = "";
lblExcelError.Text = "";
DownloadListItemsAsExcelFile();
}
catch (Exception ex)
{
lblExcelError.Text = ex.Message;
lblExcelError.Visible = true;
lblExcelError.ForeColor = System.Drawing.Color.Red;
}
}
protected void DownloadListItemsAsExcelFile()
{
try
{
string Url = SPContext.Current.Web.Url;
// int rowindex = 7;
//using (SPSite spSite = new SPSite(Url))
//{
// using (SPWeb spWeb = spSite.OpenWeb())
// {
SPSecurity.RunWithElevatedPrivileges(delegate()
{
string strFileLocationPath = "~/_layouts/AuditTemplate/IAAAuditTemplate.xlsx";
string FileLocation = HttpContext.Current.Server.MapPath(strFileLocationPath);
string FileName = "AuditTemplate" + DateTime.Now.ToString("dd_MMM_yyyy_HH_mm_ss") + ".xlsx";
string strDestPath = "~/_layouts/AuditTemplate/";
string DestPath = HttpContext.Current.Server.MapPath(strDestPath + FileName);
System.IO.DirectoryInfo myDirInfo = new DirectoryInfo(HttpContext.Current.Server.MapPath(strDestPath));
File.Copy(FileLocation, DestPath);
string ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileLocation + "; Extended Properties='Excel 12.0 Macro;HDR=NO';";
var file = new FileInfo(DestPath);
using (var pck = new ExcelPackage(file))
{
ExcelWorksheet ws = pck.Workbook.Worksheets[1];
ExcelWorksheet masterdataAuditRating = pck.Workbook.Worksheets["AuditRating"];
ExcelWorksheet masterdataAuditScope = pck.Workbook.Worksheets["AuditScope"];
ExcelWorksheet masterdataLocations = pck.Workbook.Worksheets["Locations"];
ExcelWorksheet masterdataEntities = pck.Workbook.Worksheets["Entities"];
ExcelWorksheet masterdataAuditOwner = pck.Workbook.Worksheets["AuditOwner"];
ExcelWorksheet masterdataAuditTeam = pck.Workbook.Worksheets["AuditTeam"];
ExcelWorksheet masterdataAuditLead = pck.Workbook.Worksheets["AuditLead"];
ExcelWorksheet masterdataAuditManager = pck.Workbook.Worksheets["AuditManager"];
ws.Cells[4, 1].Value = SPContext.Current.Web.CurrentUser.Name;
ws.Cells[4, 4].Value = DateTime.Now.ToString("MM/dd/yyyy");
SPListItemCollection AuditRatings = getsplistitemcoll(SPContext.Current.Web, "AuditRating");
var objAuditRatingslist = GetListvalue(AuditRatings, "Title");
int count0 = 1;
foreach (var objAuditRatings in objAuditRatingslist)
{
masterdataAuditRating.Cells[count0, 1].Value = objAuditRatings.val.Trim();
count0 = count0 + 1;
}
SPListItemCollection scopes = getsplistitemcoll(SPContext.Current.Web, "Scopes");
var objScopelist = GetListvalue(scopes, "Title");
int count = 1;
foreach (var objScope in objScopelist)
{
masterdataAuditScope.Cells[count, 1].Value = objScope.val.Trim();
count = count + 1;
}
SPListItemCollection SPLocationsColl = getsplistitemcoll(SPContext.Current.Web, "Locations");
int count1 = 1;
var listLocationsvalue = GetListvalue(SPLocationsColl, "Title");
foreach (var bbLocations in listLocationsvalue)
{
masterdataLocations.Cells[count1, 1].Value = bbLocations.val.Trim();
count1 = count1 + 1;
}
SPListItemCollection SPEntitiesColl = getsplistitemcoll(SPContext.Current.Web, "Entities");
int count2 = 1;
var listEntitiesvalue = GetListvalue(SPEntitiesColl, "Title");
foreach (var bbEntities in listEntitiesvalue)
{
masterdataEntities.Cells[count2, 1].Value = bbEntities.val.Trim();
count2 = count2 + 1;
}
int count3 = 1;
var listAuditOwnervalue = GetOwner(SPContext.Current.Web, "Business User");
foreach (var bbAuditOwner in listAuditOwnervalue)
{
masterdataAuditOwner.Cells[count3, 1].Value = bbAuditOwner.ToString();
count3 = count3 + 1;
}
int count4 = 1;
var listAuditTeamvalue = GetOwner(SPContext.Current.Web, "Audit User");
foreach (var bbAuditTeam in listAuditTeamvalue)
{
masterdataAuditTeam.Cells[count4, 1].Value = bbAuditTeam.ToString();
count4 = count4 + 1;
}
int count5 = 1;
var listAuditLeadvalue = GetOwner(SPContext.Current.Web, "Audit User");
foreach (var bbAuditLead in listAuditTeamvalue)
{
masterdataAuditLead.Cells[count5, 1].Value = bbAuditLead.ToString();
count5 = count5 + 1;
}
int count6 = 1;
var listAuditManagervalue = GetOwner(SPContext.Current.Web, "Audit User");
foreach (var bbAuditManager in listAuditManagervalue)
{
masterdataAuditManager.Cells[count6, 1].Value = bbAuditManager.ToString();
count6 = count6 + 1;
}
ws.Cells.AutoFitColumns();
ws.Column(200).Hidden = true;
pck.SaveAs(this.Page.Response.OutputStream);
lblExcelError.Visible = true;
lblExcelError.Text = "File Download Successfully";
lblExcelError.ForeColor = System.Drawing.Color.Green;
}
this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
this.Page.Response.AddHeader("content-disposition", "attachment; filename=IAAAuditTemplate_on_" + DateTime.Now.ToString("dd_MMM_yyyy_HH_mm_ss") + ".xlsx");
this.Page.Response.Flush();
this.Page.Response.End();
this.Page.Response.Clear();
this.Page.Response.ClearContent();
//this.Page.Response.ClearHeaders();
});
// }
//}
}
catch (Exception ex)
{
lblExcelError.Text = "Error in download file" + ex.Message.ToString();
}
}
private List<String> GetOwner(SPWeb web, string groupName)
{
try
{
List<String> lstuser = getGroupUsers(groupName, web);
return lstuser;
}
catch (Exception)
{
throw;
}
}
public static List<String> getGroupUsers(string groupName, SPWeb web)
{
List<String> groupMembers = new List<String>();
SPSecurity.RunWithElevatedPrivileges(delegate()
{
SPGroup group = web.SiteGroups[groupName];
foreach (SPUser user in group.Users)
{
groupMembers.Add(user.Name);
}
});
return groupMembers;
}
public SPUser EnsureUser(SPWeb web, string username)
{
SPUser user = null;
SPSecurity.RunWithElevatedPrivileges(
delegate()
{
using (var site = new SPSite(web.Site.ID))
{
using (SPWeb elevatedWeb = site.OpenWeb(web.ID))
{
elevatedWeb.AllowUnsafeUpdates = true;
user = elevatedWeb.EnsureUser(username);
}
}
});
return user;
}
#endregion
}
}
.... code End
No comments:
Post a Comment