// Reading Excel file as datatable function
public DataTable ReadExcelAsDataTable()
{
DataTable dataTable = new DataTable();
string value = string.Empty;
if (resourceFileupload.HasFiles)
{
byte[] filebytes = resourceFileupload.FileBytes;
MemoryStream msFileStream = new MemoryStream(filebytes);
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(msFileStream, true))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
string relationshipId = sheets.ToList()[0].Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Descendants<Row>();
// foreach (Cell cell in rows.ElementAt(0))
foreach (Cell cell in rows.ElementAt(1))
{
string val = GetCellValue(spreadSheetDocument, cell);
dataTable.Columns.Add(val);
}
foreach (var row in rows)
{
//From second row of excel onwards, add data rows to data table.
IEnumerable<Cell> cells = GetCellsFromRowIncludingEmptyCells(row);
DataRow newDataRow = dataTable.NewRow();
int columnCount = 0;
foreach (Cell currentCell in cells)
{
value = GetCellValue(spreadSheetDocument, currentCell);
//There are empty headers which are not added to data table columns. So avoid those.
if (columnCount < dataTable.Columns.Count)
{
newDataRow[columnCount++] = value;
}
}
if (!IsEmpty(newDataRow, dataTable))
dataTable.Rows.Add(newDataRow);
}
dataTable.Rows.RemoveAt(1);
if (dataTable != null && dataTable.Rows.Count > 1)
{
// int projectID = Convert.ToInt32(ViewState["ProjectId"]);
int projectID = Convert.ToInt32(hdnProjectId.Value);
GetResourceMappingTable(dataTable, projectID);
UploadResourceExcel(fromServer); // uploading excel to sharepoint library
}
else
ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "alert2", "alert('Excel does not have any records!!')", true);
}
}
return dataTable;
}
//reading cells including empty cell
private IEnumerable<Cell> GetCellsFromRowIncludingEmptyCells(Row row)
{
int currentCount = 0;
// row is a class level variable representing the current
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in
row.Descendants<DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
string columnName = GetColumnName(cell.CellReference);
int currentColumnIndex = ConvertColumnNameToNumber(columnName);
//Return null for empty cells
for (; currentCount < currentColumnIndex; currentCount++)
{
yield return null;
}
yield return cell;
currentCount++;
}
}
public int ConvertColumnNameToNumber(string columnName)
{
Regex alpha = new Regex("^[A-Z]+$");
if (!alpha.IsMatch(columnName)) throw new ArgumentException();
char[] colLetters = columnName.ToCharArray();
Array.Reverse(colLetters);
int convertedValue = 0;
for (int i = 0; i < colLetters.Length; i++)
{
char letter = colLetters[i];
int current = i == 0 ? letter - 65 : letter - 64; // ASCII 'A' = 65
convertedValue += current * (int)Math.Pow(26, i);
}
return convertedValue;
}
public string GetCellValue(SpreadsheetDocument document, Cell cell)
{
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
if (cell == null || cell.CellValue == null)
{
return "";
}
string value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
}
else
{
return value;
}
}
public string GetColumnName(string cellReference)
{
// Match the column name portion of the cell name.
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellReference);
return match.Value;
}
public bool IsEmpty(DataRow row, DataTable dt)
{
bool isBlank = true;
foreach (DataColumn col in dt.Columns)
{
if (!string.IsNullOrEmpty(Convert.ToString(row[col.ColumnName])))
{
isBlank = false;
return isBlank;
}
}
return isBlank;
}
#endregion
public void GetResourceMappingTable(DataTable sourceExcelDt, int projectId)
{
DataTable allocationDt = new DataTable();
AdminBAL adminbal = new AdminBAL();
string col = "Name";
// sourceExcelDt.Rows.RemoveAt(0);
DataTable excelDt = sourceExcelDt.Copy();
excelDt.Columns.Add("Median", typeof(decimal));
//for (int i = sourceExcelDt.Columns.Count-1; i > 18; i--)
// excelDt.Columns.Remove(excelDt.Columns[i]);
//excelDt.Columns.Remove(col);
DataSet ds = adminbal.GetResourceMasterData();
if (ds != null && ds.Tables.Count > 0)
{
DataTable region = ds.Tables[0];
DataTable function = ds.Tables[1];
DataTable role = ds.Tables[2];
DataTable EngagementPhase = ds.Tables[3];
DataTable org = ds.Tables[4];
DataTable parentTrack = ds.Tables[5];
DataTable si_budget = ds.Tables[6];
DataTable capex_opex = ds.Tables[7];
DataTable resourceType = ds.Tables[8];
DataTable location = ds.Tables[9];
DataTable resource = ds.Tables[10];
DataTable fnlresource = CompareExcelWithExistingDBData(excelDt, resource);
for (int i = 1; i <= excelDt.Rows.Count - 1; i++)
{
excelDt.Rows[i]["PS ID"] = projectId;
excelDt.Rows[i]["Region"] = GetId(Convert.ToString(excelDt.Rows[i]["Region"]), region, col);
excelDt.Rows[i]["Function"] = GetId(Convert.ToString(excelDt.Rows[i]["Function"]), function, col);
excelDt.Rows[i]["Role"] = GetId(Convert.ToString(excelDt.Rows[i]["Role"]), role, col);
excelDt.Rows[i]["Engagement Phase"] = GetId(Convert.ToString(excelDt.Rows[i]["Engagement Phase"]), EngagementPhase, col);
excelDt.Rows[i]["Organization"] = GetId(Convert.ToString(excelDt.Rows[i]["Organization"]), org, col);
excelDt.Rows[i]["Parent Track"] = GetId(Convert.ToString(excelDt.Rows[i]["Parent Track"]), parentTrack, col);
excelDt.Rows[i]["Budget(SI/Non-SI)"] = GetId(Convert.ToString(excelDt.Rows[i]["Budget(SI/Non-SI)"]), si_budget, col);
excelDt.Rows[i]["Capex/Opex"] = GetId(Convert.ToString(excelDt.Rows[i]["Capex/Opex"]), capex_opex, col);
excelDt.Rows[i]["Resource Type"] = GetId(Convert.ToString(excelDt.Rows[i]["Resource Type"]), resourceType, col);
excelDt.Rows[i]["Location"] = GetId(Convert.ToString(excelDt.Rows[i]["Location"]), location, col);
excelDt.Rows[i]["Email"] = GetId(Convert.ToString(excelDt.Rows[i]["Email"]), fnlresource, "Email");
if (!string.IsNullOrEmpty(Convert.ToString(excelDt.Rows[i]["Expected Start Date"])))
{
DateTime dt = GetDateFormat(Convert.ToString(excelDt.Rows[i]["Expected Start Date"]));//.ToString("yyyy/MM/dd");
excelDt.Rows[i]["Expected Start Date"] = dt;
}
if (!string.IsNullOrEmpty(Convert.ToString(excelDt.Rows[i]["Expected Completion Date"])))
{
DateTime dt = GetDateFormat(Convert.ToString(excelDt.Rows[i]["Expected Completion Date"]));//.ToString("yyyy/MM/dd");
excelDt.Rows[i]["Expected Completion Date"] = dt;
}
excelDt.Rows[i]["Median"] = GetMedian(i, excelDt);
}
allocationDt = excelDt.Copy(); // this is table with all ids converted with financial year data at poition 0
}
excelDt.Columns.Remove("ID");
excelDt.Columns.Remove("Name");
excelDt.Columns.Remove("Window ID");
excelDt.Columns.Remove("Manager's Name");
excelDt.Columns["Email"].ColumnName = "ResourceID";
excelDt.Columns.Add("ID", typeof(int)).SetOrdinal(0);
DataTable finalDt = GetFinalDataTable(excelDt);
if (finalDt != null && finalDt.Rows.Count > 0)
{
string projId = Convert.ToString(finalDt.Rows[0]["ProjectId"]);
adminbal.DeleteRecords("ResourceMapping", projId);
adminbal.ResourceBulkInsert(finalDt, "ResourceMapping");
GetAllocationTableData(allocationDt);
}
}
// converting data column as row for saving in DB
public void GetAllocationTableData(DataTable sourceTable)
{
AdminBAL adminbal = new AdminBAL();
DataTable clonedt = sourceTable.Copy();
DataTable newTable = new DataTable();
newTable.Columns.Add("Id", typeof(int));
newTable.Columns.Add("ProjectId", typeof(int));
newTable.Columns.Add("ResourceId", typeof(int));
newTable.Columns.Add("FinancialYear", typeof(string));
newTable.Columns.Add("WeekStartDate", typeof(DateTime));
newTable.Columns.Add("Hours", typeof(int));
for (int i = 18; i >= 0; i--)
{
if (i == 10 || i == 1)
continue;
clonedt.Columns.Remove(clonedt.Columns[i].ColumnName);
}
clonedt.Columns.Remove("Comments");
clonedt.Columns.Remove("Median");
for (int i = 1; i <= clonedt.Rows.Count - 1; i++)
{
for (int j = 2; j < clonedt.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(Convert.ToString(clonedt.Rows[i][j])))
{
DataRow row = newTable.NewRow();
string projectId = Convert.ToString(clonedt.Rows[i][0]);
string resourceId = Convert.ToString(clonedt.Rows[i][1]);
string fy = Convert.ToString(clonedt.Rows[0][j]);
string weekStart = GetDateFormat(clonedt.Columns[j].ColumnName).ToString("dd-MMM-y");
string hour = Convert.ToString(clonedt.Rows[i][j]);
if (!string.IsNullOrEmpty(projectId))
row["ProjectId"] = projectId;
if (!string.IsNullOrEmpty(resourceId))
row["ResourceId"] = resourceId;
if (!string.IsNullOrEmpty(fy))
row["FinancialYear"] = fy;
if (!string.IsNullOrEmpty(weekStart))
row["WeekStartDate"] = weekStart;
if (!string.IsNullOrEmpty(hour))
row["Hours"] = hour;
newTable.Rows.Add(row);
}
}
}
if (newTable != null && newTable.Rows.Count > 0)
{
string projectId = Convert.ToString(newTable.Rows[0]["ProjectId"]);
adminbal.DeleteRecords("ResourceAllocation", projectId);
adminbal.ResourceBulkInsert(newTable, "ResourceAllocation");
}
}
// Comparing the resource excel data to SQL resource data for any new change,
public DataTable CompareExcelWithExistingDBData(DataTable newDt, DataTable dbtable)
{
string col1 = "Name"; string col2 = "Email"; string col3 = "Window ID"; string col4 = "Manager's Name"; string col5 = "ID";
ADMethods attributes = new ADMethods();
AdminBAL adminbal = new AdminBAL();
DataTable excelDt = newDt.Copy();
excelDt.Rows.RemoveAt(0);
DataView view = new DataView(excelDt);
DataTable newExcelTable = view.ToTable(true, col1, col2, col3, col4);
// correcting the display name of resource from email column
for (int i = 0; i < newExcelTable.Rows.Count; i++)
{
DataRow row = newExcelTable.Rows[i];
string email = Convert.ToString(newExcelTable.Rows[i]["Email"]);
if (attributes.IsUserExist(email, ADMethods.AdPrpoertyParameters.mail))
{
ADMethods.ADAttributes attr = attributes.GetEmployeeAttributes(email, ADMethods.AdPrpoertyParameters.mail);
if (attr != null)
{
row[col1] = attr.displayName;
row[col4] = attr.ManagerDisplayName;
}
}
}
DataTable dataTable = new DataTable();
dataTable.Columns.Add(col1);
dataTable.Columns.Add(col2);
dataTable.Columns.Add(col3);
dataTable.Columns.Add(col4);
var query1 = newExcelTable.AsEnumerable().Select(a => new
{
Name = a[col1].ToString().ToLower(),
Email = a[col2].ToString().ToLower(),
WinId = a[col3].ToString().ToLower(),
ManagerName = a[col4].ToString().ToLower()
});
var query2 = dbtable.AsEnumerable().Select(b => new
{
Name = b[col1].ToString().ToLower(),
Email = b[col2].ToString().ToLower(),
WinId = b["WindowId"].ToString().ToLower(),
ManagerName = b[col4].ToString().ToLower()
});
var exceptResultsExceltoDb = query1.Except(query2).ToList(); // checking the differnce between 2 tables
for (int i = 0; i < exceptResultsExceltoDb.Count(); i++)
{
DataRow row = dataTable.NewRow();
row[col1] = Convert.ToString(exceptResultsExceltoDb[i].Name).ToLower();
row[col2] = Convert.ToString(exceptResultsExceltoDb[i].Email).ToLower();
row[col3] = Convert.ToString(exceptResultsExceltoDb[i].WinId).ToLower();
row[col4] = Convert.ToString(exceptResultsExceltoDb[i].ManagerName).ToLower();
dataTable.Rows.Add(row);
}
if (dataTable.Rows.Count > 0)
{
dataTable.Columns.Add(col5, typeof(int)).SetOrdinal(0);
adminbal.ResourceBulkInsert(dataTable, "Resources"); // bulk insert
}
DataTable dt = adminbal.GetResourceDetails(); // get resource data table
return dt;
}
// get final data table
public DataTable GetFinalDataTable(DataTable dt)
{
DataTable sourceDt = dt.Copy();
sourceDt.Rows.RemoveAt(0);
DataTable finalDt = new DataTable();
finalDt.Columns.Add("ID", typeof(int));
finalDt.Columns.Add("ProjectId", typeof(int));
finalDt.Columns.Add("Region", typeof(int));
finalDt.Columns.Add("Location", typeof(int));
finalDt.Columns.Add("Function", typeof(int));
finalDt.Columns.Add("Organization", typeof(int));
finalDt.Columns.Add("Parent Track", typeof(int));
finalDt.Columns.Add("Sub Track", typeof(string));
finalDt.Columns.Add("ResourceID", typeof(int));
finalDt.Columns.Add("Role", typeof(int));
finalDt.Columns.Add("Engagement Phase", typeof(int));
finalDt.Columns.Add("Budget", typeof(int));
finalDt.Columns.Add("Capex", typeof(int));
finalDt.Columns.Add("ResourceType", typeof(int));
finalDt.Columns.Add("Expected Start Date", typeof(DateTime));
finalDt.Columns.Add("Expected Completion Date", typeof(DateTime));
finalDt.Columns.Add("Median", typeof(decimal));
foreach (DataRow row in sourceDt.Rows)
{
DataRow newRow = finalDt.NewRow();
//newRow["ID"] = Convert.ToInt32(row["ID"]);
newRow["ProjectId"] = Convert.ToInt32(row["PS ID"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Region"])))
newRow["Region"] = Convert.ToInt32(row["Region"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Location"])))
newRow["Location"] = Convert.ToInt32(row["Location"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Function"])))
newRow["Function"] = Convert.ToInt32(row["Function"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Organization"])))
newRow["Organization"] = Convert.ToInt32(row["Organization"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Parent Track"])))
newRow["Parent Track"] = Convert.ToInt32(row["Parent Track"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Sub-Track"])))
newRow["Sub Track"] = Convert.ToString(row["Sub-Track"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["ResourceID"])))
newRow["ResourceID"] = Convert.ToInt32(row["ResourceID"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Role"])))
newRow["Role"] = Convert.ToInt32(row["Role"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Engagement Phase"])))
newRow["Engagement Phase"] = Convert.ToInt32(row["Engagement Phase"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Budget(SI/Non-SI)"])))
newRow["Budget"] = Convert.ToInt32(row["Budget(SI/Non-SI)"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Capex/Opex"])))
newRow["Capex"] = Convert.ToInt32(row["Capex/Opex"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Resource Type"])))
newRow["ResourceType"] = Convert.ToInt32(row["Resource Type"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Expected Start Date"])))
newRow["Expected Start Date"] = Convert.ToDateTime(row["Expected Start Date"]);
if (!string.IsNullOrEmpty(Convert.ToString(row["Expected Completion Date"])))
newRow["Expected Completion Date"] = Convert.ToDateTime(row["Expected Completion Date"]);
newRow["Median"] = Convert.ToInt32(row["Median"]);
finalDt.Rows.Add(newRow);
}
return finalDt;
}
public string GetId(string value, DataTable dataTablemaster, string col)
{
string id = string.Empty;
// DataRow[] rows = dataTablemaster.Select("Name='" + value + "'");
DataRow[] rows = dataTablemaster.Select("" + col + "='" + value + "'");
if (rows.Length > 0)
{
id = Convert.ToString(rows[0]["ID"]);
}
return id;
}
public DateTime GetDateFormat(string dateValue)
{
DateTime dt = DateTime.Now;
try
{
if (!string.IsNullOrEmpty(dateValue.Trim()))
{
int dtnum = Convert.ToInt32(dateValue);
dt = DateTime.FromOADate(dtnum);
}
}
catch (Exception ex)
{
throw;
}
return dt;
}
public int GetMedian(int rowIndex, DataTable sourceDt)
{
decimal median = 0;
DataTable clonedt = sourceDt.Copy();
for (int i = 18; i >= 0; i--)
{
clonedt.Columns.Remove(clonedt.Columns[i].ColumnName);
}
clonedt.Columns.Remove("Comments");
List<decimal> valuesList = new List<decimal>();
for (int i = 0; i < clonedt.Columns.Count; i++)
{
if (!string.IsNullOrEmpty(Convert.ToString(clonedt.Rows[rowIndex][i])))
{
decimal val = Convert.ToInt32(clonedt.Rows[rowIndex][i]);
valuesList.Add(val);
}
}
if (valuesList.Count > 0)
{
decimal[] newValues = new decimal[valuesList.Count];
newValues = valuesList.ToArray();
Array.Sort(newValues);
if (newValues.Length % 2 != 0)
{
median = newValues[newValues.Length / 2];
}
else
{
int middle = newValues.Length / 2;
decimal first = newValues[middle];
decimal second = newValues[middle - 1];
median = (first + second) / 2;
}
}
return Convert.ToInt32(median);
}
=============================================== ====
Save File to libarry
=============================
public void UploadResourceExcel(string siteUrl)
{
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls | System.Net.SecurityProtocolType.Tls11 | System.Net.SecurityProtocolType.Tls12;
string realm = TokenHelper.GetRealmFromTargetUrl(new Uri(siteUrl));
string accessToken = TokenHelper.GetAppOnlyAccessToken(TokenHelper.SharePointPrincipal, new Uri(siteUrl).Authority, realm).AccessToken;
using (ClientContext clientContext = TokenHelper.GetClientContextWithAccessToken(siteUrl, accessToken))
{
clientContext.Load(clientContext.Web, web => web.ServerRelativeUrl);
clientContext.ExecuteQuery();
var resourcefolder = CreateFolder(clientContext.Web, "Project Library", "" + ddlGroup.SelectedItem.Text + "/" + txtProjName.Text.Replace(" ", string.Empty) + "" + "/" + Constants.ResourceRoster);
string Url = clientContext.Web.ServerRelativeUrl + "/Project%20Library" + "/" + ddlGroup.SelectedItem.Text + "/" + txtProjName.Text.Replace(" ", string.Empty) + "/" + Constants.ResourceRoster;
string filename = CheckFileExists(clientContext, Url, false);
string fileUrl = String.Format("{0}/{1}", Url, filename);
byte[] filebytes = resourceFileupload.FileBytes;
MemoryStream msFileStream = new MemoryStream(filebytes);
FileCreationInformation newFile = new FileCreationInformation();
newFile.ContentStream = msFileStream;
newFile.Url = fileUrl;
// newFile.Overwrite = true;
List projectList = clientContext.Web.Lists.GetByTitle("Project Library");
try
{
if (!string.IsNullOrEmpty(filename))
{
Microsoft.SharePoint.Client.File newResourceFile = projectList.RootFolder.Files.Add(newFile);
clientContext.ExecuteQuery();
string rosterUrl = clientContext.Url + "/Project%20Library" + "/" + ddlGroup.SelectedItem.Text + "/" + txtProjName.Text.Replace(" ", string.Empty) + "/" + Constants.ResourceRoster + "/" + filename;
fileUrlAnchor.Attributes.Remove("onclick");
fileUrlAnchor.HRef = rosterUrl;
templateFile.HRef = clientContext.Url + "/Templates/Project%20Templates/" + Constants.ResourceRosterTemplate;
ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), "alert1", "alert('Resource Roaster uploaded sucessfully!!')", true);
}
else
{
fileUrlAnchor.Attributes.Add("onclick", "alert('No previous resource roster file available.')");
}
}
catch (Exception ex)
{
lblErrorMsg.Text = "Something went wrong with file upload. Try again after some time.";
throw;
}
}
}
/// <summary>
/// Checking file in resource folder and returing new name with e.g "Project_1.xlsx"
/// </summary>
/// <param name="clientContext"></param>
/// <param name="folderUrl"></param>
/// <param name="isbinding"></param>
/// <returns></returns>
public string CheckFileExists(ClientContext clientContext, string folderUrl, bool isbinding)
{
string filename = string.Empty;
try
{
Folder folder = clientContext.Web.GetFolderByServerRelativeUrl(folderUrl);
clientContext.Load(folder);
clientContext.Load(folder.Files);
clientContext.ExecuteQuery();
List<int> fileIndex = new List<int>();
if (folder.Files.Count == 0)
{
if (isbinding)
return filename;
else
{
filename = Constants.ResourceRosterExcel + 1 + ".xlsx";
}
}
else
{
foreach (Microsoft.SharePoint.Client.File file in folder.Files)
{
if (file.Name.Contains(Constants.ResourceRosterExcel))
{
int findex = Convert.ToInt32(file.Name.Split('_')[1].Split('.')[0]);
fileIndex.Add(findex);
}
}
fileIndex.Sort();
if (isbinding)
filename = Constants.ResourceRosterExcel + fileIndex.Last() + ".xlsx";
else
{
filename = Constants.ResourceRosterExcel + (fileIndex.Last() + 1) + ".xlsx";
}
}
}
catch (Exception ex)
{
}
return filename;
}
No comments:
Post a Comment