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"));
}
