Exemple #1
0
        public ActionResult ImportExcelFile(HttpPostedFileBase file, FormCollection collection)
        {
            CCIRepository repository = CCIRepository.CreateRepository();

            if (Request.Files["FileUpload"].ContentLength > 0)
            {
                string fileExtension = System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);

                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                    string filename     = Path.GetFileName(Request.Files["FileUpload"].FileName);
                    string fileLocation = string.Format("{0}{1}", Server.MapPath("/UploadedFile/"), filename);

                    if (System.IO.File.Exists(fileLocation))
                    {
                        System.IO.File.Delete(fileLocation);
                    }
                    Request.Files["FileUpload"].SaveAs(fileLocation);
                    string excelConnectionString = string.Empty;
                    if (fileExtension == ".xls")
                    {
                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    }
                    else if (fileExtension == ".xlsx")
                    {
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }

                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                    excelConnection.Open();
                    DataTable dt = new DataTable();
                    dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dt == null)
                    {
                        return(null);
                    }
                    String[] excelsheets = new String[dt.Rows.Count];
                    int      t           = 0;
                    foreach (DataRow row in dt.Rows)
                    {
                        excelsheets[t] = row["TABLE_NAME"].ToString();
                        t++;
                    }
                    OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
                    DataSet         ds    = new DataSet();
                    string          query = string.Format("Select * from [{0}]", excelsheets[0]);
                    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                    {
                        dataAdapter.Fill(ds);
                    }
                    AirtelManagementModel _model = new AirtelManagementModel();
                    _model.UploadedDate   = DateTime.Now;
                    _model.DateOfCreation = Convert.ToDateTime(collection["DateOfCreation"].ToString());
                    var temp = repository.InsertImportDate(_model);
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        //AirtelManagementModel _model = new AirtelManagementModel();
                        _model.MobileAcNumber      = Convert.ToInt64(ds.Tables[0].Rows[i]["accountno"]);
                        _model.AirtelNumber        = Convert.ToInt64(ds.Tables[0].Rows[i]["airtelnumber"]);
                        _model.OneTime             = Convert.ToInt32(ds.Tables[0].Rows[i]["Onetimecharges"]);
                        _model.MonthlyCharges      = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["monthlycharges"]);
                        _model.CallCharges         = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["Callcharges"]);
                        _model.ValueAddedServices  = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["Valueaddeservices"]);
                        _model.MobileInternetUsage = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["mobileinternetusage"]);
                        _model.Roaming             = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["roaming"]);
                        _model.Discounts           = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["discounts"]);
                        _model.Taxes        = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["taxes"]);
                        _model.TotalCharges = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["totalcharges"]);
                        _model.WhoUploaded  = System.Web.HttpContext.Current.User.Identity.Name;
                        _model.ImportDateId = temp;
                        //_model.UploadedDate = DateTime.Now;
                        //_model.DateOfCreation = collection["DateOfCreation"].ToString();
                        repository.SaveData(_model);
                        //MemoryStream streampost = new MemoryStream();
                        //WebClient clientpost = new WebClient();
                        //clientpost.Headers["Content-Type"] = "application/json";
                        //DataContractJsonSerializer serializerpost = new DataContractJsonSerializer(typeof(AirtelManagementModel));
                        //serializerpost.WriteObject(streampost, _model);
                        //dynamic serviceUrl = string.Empty;

                        //string url = string.Format("{0},Save Details", serviceUrl);
                        //byte[] datapost = clientpost.UploadData(url, "Post", streampost.ToArray());

                        //streampost = new MemoryStream(datapost);
                        //serializerpost = new DataContractJsonSerializer(typeof(string));
                        //string result = (string)serializerpost.ReadObject(streampost);
                    }
                }
                else
                {
                    ModelState.AddModelError("", "Please Select a excel file");
                    return(null);
                }
            }
            return(RedirectToAction("Success", "Success"));
        }