private void CreateDirectory() { if (fuExcelSheet.HasFile) { try { SPSecurity.RunWithElevatedPrivileges(delegate() { String path = HttpContext.Current.Server.MapPath("/_layouts/Temp/"); if (System.IO.Directory.Exists(path)) { UploadFiles(path); } else { System.IO.Directory.CreateDirectory(path); UploadFiles(path); } }); } catch (Exception ex) { litMessage.Text = "ERROR: " + ex.Message.ToString(); SPErrorLogs.LogExceptionToSPLog(oWebpartName, "CreateDirectory", ex); } } }
protected void rptBindColumns_OnItemDataBound(object sender, RepeaterItemEventArgs e) { try { if (!(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)) { return; } CheckBox chkSelection = (e.Item.FindControl("chkXlNames") as CheckBox); DropDownList drpSPList = (DropDownList)e.Item.FindControl("drpSPlistColumns"); System.Collections.Generic.List <DictionaryEntry> XlColumns = DataAccessProvider.getXlColnames(xlConn, ddlSheetName.SelectedValue, excelPath, excelExten); drpSPList.DataSource = XlColumns; drpSPList.DataTextField = "Value"; drpSPList.DataValueField = "Key"; drpSPList.DataBind(); if (XlColumns.Exists(o => o.Value.ToString().Trim().ToLower().Equals(chkSelection.Text.ToString().Trim().ToLower()))) { drpSPList.SelectedValue = chkSelection.Text; chkSelection.Checked = true; } } catch (Exception ex) { SPErrorLogs.LogExceptionToSPLog(oWebpartName, "rptBindColumns_OnItemDataBound", ex); } }
protected void btnUpload_Click(object sender, EventArgs e) { if (ddlSPList.SelectedValue != "0") { spListGuid = ddlSPList.SelectedValue.Trim().ToString(); SPSecurity.RunWithElevatedPrivileges(delegate() { try { CreateDirectory(); excelPath = HttpContext.Current.Server.MapPath("/_layouts/Temp/" + fuExcelSheet.FileName); if (fuExcelSheet.FileName != string.Empty) { if (excelPath.EndsWith("xls", StringComparison.InvariantCultureIgnoreCase)) { excelExten = "xls"; xlConn = new global::System.Data.OleDb.OleDbConnection(Providers.Utilities.ConnUtilities.CreateXlConnectionString(excelPath, excelExten)); ddlSheetName.DataSource = DataAccessProvider.GetExcelSheetNames(xlConn); ddlSheetName.DataBind(); btnShowData.Visible = true; ddlSheetName.Visible = true; this.Page.ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';", true); } else if (excelPath.EndsWith("xlsx", StringComparison.InvariantCultureIgnoreCase)) { excelExten = "xlsx"; xlConn = new global::System.Data.OleDb.OleDbConnection(Providers.Utilities.ConnUtilities.CreateXlConnectionString(excelPath, excelExten)); ddlSheetName.DataSource = DataAccessProvider.GetExcelSheetNames(xlConn); ddlSheetName.DataBind(); this.Page.ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';", true); litMessage.Text = string.Empty; btnShowData.Visible = true; ddlSheetName.Visible = true; } else { litMessage.Text = "Please Upload .xls or .xlsx file"; } } else { litMessage.Text = "Please provide a valid file path"; } } catch (Exception ex) { litMessage.Text = "Incorrect Format"; rptBindColumns.Visible = false; SPErrorLogs.LogExceptionToSPLog(oWebpartName, "btnUpload_Click", ex); } }); } }
private void insertXlDataToSPList(String XlSPLists) { SPSecurity.RunWithElevatedPrivileges(delegate() { System.Data.DataSet dsXldata = new System.Data.DataSet(); dsXldata = Providers.DataAccess.DataAccessProvider.getXldata(xlConn, ddlSheetName.SelectedValue.ToString(), excelPath, excelExten); String[] XlSpArray = XlSPLists.Split(';'); SPList List = DataAccessProvider.getSPList(spListGuid); int xlrow = 0; bool isRowEmpty; foreach (System.Data.DataRow dr in dsXldata.Tables[0].Rows) { xlrow += 1; isRowEmpty = true; try { SPListItem listitems = List.Items.Add(); foreach (string myString in XlSpArray) { string[] values = myString.Split('-'); if (listitems != null) { spColumnName = values[0]; xlColumnName = values[1]; if (dr[values[1]].ToString().Trim() != String.Empty) { isRowEmpty = false; if (listitems.Fields[values[0]].GetType().Name == "SPFieldDateTime") { DateTime tempDateTime; if (DateTime.TryParse(dr[values[1]].ToString(), out tempDateTime)) { listitems[values[0]] = tempDateTime.ToShortDateString(); } } else { listitems[values[0]] = dr[values[1]]; //values[1] --> Excelcolumn and values[0]---> SPfield name } } } } if (!isRowEmpty) { listitems.Update(); } } catch (Exception ex) { litValidationMessage.Text = " No. of Rows copied to SpList: " + (xlrow - 1) + "<br>" + " Type mismatch between the columns:" + " " + spColumnName + " - " + xlColumnName + " at row " + (xlrow + 1); this.Page.ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';", true); SPErrorLogs.LogExceptionToSPLog(oWebpartName, "insertXlDataToSPList", ex); } } }); }
protected void btnImport_Click(object sender, EventArgs e) { try { SPSecurity.RunWithElevatedPrivileges(delegate() { string message = string.Empty; String XlSPLists = string.Empty; litValidationMessage.Text = string.Empty; litValidationErrorMessage.Text = string.Empty; foreach (RepeaterItem item in this.rptBindColumns.Items) { DropDownList ddlSPlistColumn = item.FindControl("drpSPlistColumns") as DropDownList; CheckBox chkXlColumn = item.FindControl("chkXlNames") as CheckBox; if (chkXlColumn.Checked == true) { if (message.IndexOf(ddlSPlistColumn.SelectedValue) < 0) { message = message + ";" + ddlSPlistColumn.SelectedValue; XlSPLists = XlSPLists + ";" + chkXlColumn.Text + "-" + ddlSPlistColumn.SelectedValue; } else { litValidationMessage.Text = "Repeated spreadsheet columns:" + " " + ddlSPlistColumn.SelectedValue; this.Page.ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';", true); break; } } } if (litValidationMessage.Text == string.Empty && XlSPLists != String.Empty) { insertXlDataToSPList(XlSPLists.Substring(1)); DeleteAllFiles(); lblSuccessMessage.Visible = true; lblSuccessMessage.Text = "Data Imported Successfully"; rptBindColumns.Visible = false; ddlSheetName.Visible = false; btnget.Visible = false; litValidationMessage.Text = string.Empty; litValidationErrorMessage.Text = string.Empty; } else { litValidationMessage.Text = (litValidationMessage.Text != string.Empty) ? litValidationMessage.Text : "Select atleast one column"; this.Page.ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';", true); } }); } catch (Exception ex) { SPErrorLogs.LogExceptionToSPLog(oWebpartName, "btnImport_Click", ex); } }
protected void btnShowData_Click(object sender, EventArgs e) { try { SPSecurity.RunWithElevatedPrivileges(delegate() { xlConn = new global::System.Data.OleDb.OleDbConnection(ConnUtilities.CreateXlConnectionString(excelPath, excelExten)); rptBindColumns.DataSource = DataAccessProvider.getSPListColumns(ddlSPList.SelectedValue.ToString()); rptBindColumns.DataBind(); this.Page.ClientScript.RegisterStartupScript(GetType(), "key", "document.getElementById('light').style.display='block';", true); btnget.Visible = true; rptBindColumns.Visible = true; litValidationMessage.Text = ""; litValidationMessage.Text = ""; }); } catch (Exception ex) { lblSuccessMessage.Text = "Selected worksheet is not correctly formatted. Please try again."; SPErrorLogs.LogExceptionToSPLog(oWebpartName, "btnShowData_Click", ex); } }