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

Last Reply 22 days ago By pandeyism

Posted 22 days 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);
            }

 

You are viewing reply posted by: pandeyism 22 days ago.
Posted 22 days ago

Hi userNK,

Refer below code.

Code

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("f1", typeof(string)),
                    new DataColumn("f2", typeof(string)), 
                    new DataColumn("f3", typeof(string)),
                    new DataColumn("01/07/2019", typeof(string)), 
                    new DataColumn("01/14/2019", typeof(string)),
                    new DataColumn("01/21/2018", typeof(string)),
                    new DataColumn("02/28/2019", typeof(string)),
                    new DataColumn("02/04/2019", typeof(string)),
                    new DataColumn("02/11/2019", typeof(string)),
                    new DataColumn("02/18/2019", typeof(string)),
                    new DataColumn("02/25/2019", typeof(string)),
                    new DataColumn("03/04/2019", typeof(string)) });

dt.Rows.Add("Material Code", "MaterialName", "Planned Quantity", "", "", "", "", "", "", "", "", "");
dt.Rows.Add("OHM010", "25 KV Double", "10000", "", "200", "", "400", "500", "250", "", "", "");
dt.Rows.Add("OHM011", "25KV Double", "20000", "100", "200", "", "", "350", "", "", "50", "");
// Get Above Data from excel.

DataTable dtnew = new DataTable();
dtnew.Columns.Add("MaterialID");
dtnew.Columns.Add("PlanedQty");
dtnew.Columns.Add("ImportedDate");
dtnew.Columns.Add("Month");
dtnew.Columns.Add("YearAddedOn");
for (int i = 1; i < dt.Rows.Count; i++)
{
    for (int j = 3; j < dt.Columns.Count; j++)
    {
        dtnew.Rows.Add(dt.Rows[i]["f1"].ToString(), dt.Rows[i][j].ToString(), dt.Columns[j].ColumnName, Convert.ToDateTime(dt.Columns[j].ColumnName).Month, DateTime.Today.Year + "  " + DateTime.Today.ToString("MM/dd/yyyy"));
    }
}

for (int i = 0; i < dtnew.Rows.Count; i++)
{
    var db = new RVNL2Entities();
    int materialId = db.tblMaterialMasters.Where(o => o.MaterialCode == dtnew.Rows[i]["MaterialID"].ToString()).SingleOrDefault().MaterialDetailsID;
    if (materialId != 0)
    {
        tblPlanedQty objAddQty = new tblPlanedQty();
        objAddQty.MaterialID = materialId;
        objAddQty.AddedBy = 1;
        objAddQty.AddedOn = DateTime.Now;
        objAddQty.ImportedDate = dtnew.Rows[i]["ImportedDate"].ToString();
        objAddQty.Month = dtnew.Rows[i]["Month"].ToString();
        objAddQty.Year = dtnew.Rows[i]["YearAddedOn"].ToString();
        objAddQty.PlanedQty = dtnew.Rows[i]["PlanedQty"].ToString();
        objAddQty.ProjectID = 4;

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