Import dynamically generated Excel to Database using C# in ASP.Net MVC

Last Reply 7 months ago By pandeyism

Posted 7 months ago

Hello,

I have a excel format like this

http://prntscr.com/oyit3f

this is datatable after upload file.

columns with date are dynamically generated using for loop when download template. it may contain dates of monday of 1 year, 2 year...

My table structure is like this 

http://prntscr.com/oyivn4

here i want to store all dates and values against material code.

I am trying to get values from datatable using for (int i = 0; i < dt.Rows.Count; i++) { string materialCodeFromFile = Convert.ToString(dt.Rows[i]["Material Code"]); but it is giving error.

Can someone please suggest how to save details into DB columns from datatable as a rows in sql table.

Thanks in advance for your help.

            string Message = string.Empty;
            try
            {
                #region----Excel Connection and Reading Section-------

                string fileName = Path.GetFileName(selectedfile.FileName);
                string filePath = Path.Combine(Server.MapPath("~/Uploads"), fileName);

                selectedfile.SaveAs(filePath);
                string extension = Path.GetExtension(fileName);
                string excelConnectionString = "";

                switch (extension)
                {
                    case ".xls": //Excel 97-03
                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'";
                        break;
                    case ".xlsx": //Excel 07
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'";
                        break;
                }
                excelConnectionString = String.Format(excelConnectionString, filePath);
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oleDA = new OleDbDataAdapter();
                DataTable dt = new DataTable();
                cmdExcel.Connection = excelConnection;
                excelConnection.Open();
                DataTable dtExcelSchema;
                dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                excelConnection.Close();
                excelConnection.Open();
                cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
                oleDA.SelectCommand = cmdExcel;
                oleDA.Fill(dt);
                excelConnection.Close();

                #endregion

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string materialCodeFromFile = Convert.ToString(dt.Rows[i]["Material Code"]);

                    using (var db = new RVNL2Entities())
                    {
                        int materialId= db.tblMaterialMasters.Where(o => o.MaterialCode == materialCodeFromFile).SingleOrDefault().MaterialDetailsID;

                        if (materialId != 0)
                        {
                            tblPlanedQty objAddQty = new tblPlanedQty();
                            objAddQty.MaterialID = materialId;
                            objAddQty.AddedBy = 1;
                            objAddQty.AddedOn = DateTime.Now;
                            //objAddQty.ImportedDate
                            //objAddQty.Month
                            //objAddQty.Year
                            //objAddQty.PlanedQty
                            //objAddQty.ProjectID

                            db.tblPlanedQties.Add(objAddQty);
                            db.SaveChanges();
                        }
                    }

                    Message = "1";
                }

                #region -----Object Closing and File Deletion Section-----

                dt.Dispose();
                oleDA.Dispose();
                excelConnection.Close();
                excelConnection.Dispose();
                //  Functions.DeleteFileFromServer(filePath);

                #endregion

                return Json(Message, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                Message = "Error: " + ex.Message;
                return Json(Message, JsonRequestBehavior.AllowGet);
            }

 

This question does not have replies that have been liked.