public DataTable GetExcelData()//ContactInfo,FinanceInfo { NameValueCollection nvc = HttpUtility.ParseQueryString(Request.RequestUri.Query); string FilePath = nvc["FilePath"].ToString(); string SheetName = nvc["SheetName"].ToString(); string AgainstType = nvc["AgainstType"].ToString(); ExcelParser excelObj = new ExcelParser(); DataSet dsXmlFields = excelObj.XmlColumnDefSynonym(AgainstType, ConfigurationManager.ConnectionStrings["ConRenderLogyNew"].ToString(), HttpContext.Current.Server.MapPath("~/CRMFiles/")); if (dsXmlFields.Tables.Count > 1) { DataTable dtExcel = excelObj.ExcelColumnDef(HttpContext.Current.Server.MapPath("~/CRMFiles/") + FilePath, true, SheetName); if (dtExcel.Rows.Count > 0) { var queryRes = (from cs in dsXmlFields.Tables["Name"].AsEnumerable() join cs1 in dsXmlFields.Tables["Synonyms"].AsEnumerable() on cs["Field_Id"].ToString().ToLower().Trim() equals cs1["Field_Id"].ToString().ToLower().Trim() join cs2 in dsXmlFields.Tables["Synonym"].AsEnumerable() on cs1["Synonyms_Id"].ToString().ToLower().Trim() equals cs2["Synonyms_Id"].ToString().ToLower().Trim() join c in dtExcel.AsEnumerable() on cs["Name_Text"].ToString().ToLower().Trim() equals c["COLUMN_NAME"].ToString().ToLower().Trim() orderby Convert.ToInt32(cs["ColumnOrder"].ToString()) select cs["Name_Text"]).Distinct().ToList(); return(excelObj.GetSpecificColumnsValue(HttpContext.Current.Server.MapPath("~/CRMFiles/") + FilePath, true, SheetName, queryRes, "")); } else { return(dtExcel); } } else { return(dsXmlFields.Tables[0]); } }
public ActionResult ImportData() { var r = new List <UploadFilesResult>(); string savedFileName = string.Empty; string fileExtension = string.Empty; foreach (string file in Request.Files) { HttpPostedFileBase hpf = Request.Files[file] as HttpPostedFileBase; if (hpf.ContentLength == 0) { continue; } savedFileName = Path.Combine(Server.MapPath("~/CRMFiles"), Path.GetFileName(DateTime.Now.Millisecond + hpf.FileName)); fileExtension = System.IO.Path.GetExtension(hpf.FileName); hpf.SaveAs(savedFileName); r.Add(new UploadFilesResult() { Name = hpf.FileName, Length = hpf.ContentLength, Type = hpf.ContentType }); } if (r[0].Length > 0) { try { DataContext context = new DataContext(); if (fileExtension == ".xls" || fileExtension == ".xlsx") { ExcelParser excelObj; excelObj = new ExcelParser(); DataTable dt1 = new DataTable("ReturnTable");// = excelObj.ExcelContent(filename, true,""); string SheetName = "Account"; string AgainstType = "contact"; DataSet dsXmlFields = excelObj.XmlColumnDefSynonym(AgainstType, ConfigurationManager.ConnectionStrings["ConRenderLogyNew"].ToString(), Server.MapPath("~/CRMFiles/")); if (dsXmlFields.Tables.Count > 1) { DataTable dtExcel = excelObj.ExcelColumnDef(savedFileName, true, SheetName); if (dtExcel.Rows.Count > 0) { var queryRes = (from cs in dsXmlFields.Tables["Name"].AsEnumerable() join cs1 in dsXmlFields.Tables["Synonyms"].AsEnumerable() on cs["Field_Id"].ToString().ToLower().Trim() equals cs1["Field_Id"].ToString().ToLower().Trim() join cs2 in dsXmlFields.Tables["Synonym"].AsEnumerable() on cs1["Synonyms_Id"].ToString().ToLower().Trim() equals cs2["Synonyms_Id"].ToString().ToLower().Trim() join c in dtExcel.AsEnumerable() on cs["Name_Text"].ToString().ToLower().Trim() equals c["COLUMN_NAME"].ToString().ToLower().Trim() orderby Convert.ToInt32(cs["ColumnOrder"].ToString()) select cs["Name_Text"]).Distinct().ToList(); dt1 = excelObj.GetSpecificColumnsValue(savedFileName, true, SheetName, queryRes, ""); } } using (System.IO.StringWriter writer = new System.IO.StringWriter()) { dt1.WriteXml(writer, XmlWriteMode.WriteSchema, false); StringBuilder getdata = new StringBuilder(); getdata.Append(writer); writer.Close(); writer.Flush(); writer.Close(); var connectionString = ConfigurationManager.ConnectionStrings["ConRenderLogyNew"].ConnectionString; using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand("sp_SaveAccountXML", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@AccountDetailsXML", getdata.ToString()); command.Parameters.AddWithValue("@UserId", Session["UserId"]); connection.Open(); command.ExecuteNonQuery(); connection.Close(); } } } else { if (System.IO.File.Exists(savedFileName)) { System.IO.File.Delete(savedFileName); } ModelState.AddModelError("", "Invalid file"); } } catch (Exception ex) { ModelState.AddModelError("", ex.Message); } finally { if (System.IO.File.Exists(savedFileName)) { System.IO.File.Delete(savedFileName); } } } return(RedirectToAction("Index", "CRM")); }