Click here to Skip to main content
14,634,521 members
Rate this:
Please Sign up or sign in to vote.
I have about 20 files , all with 20 or more sheets. I need to import specific data from all sheets in tables (as the same columns as the table). My code only import one sheet. How to import multiple sheets? and How Import specific cells from Excel?.I have read many questions but none do what I need and the most are old

Database SQL SERver

I hope ur help me. Im newbie.

What I have tried:

<pre>public ActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public ActionResult Index(HttpPostedFileBase postedFile)
    {
        string filePath = string.Empty;
        if (postedFile != null)
        {
            string path = Server.MapPath("~/Uploads/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            filePath = path + Path.GetFileName(postedFile.FileName);
            string extension = Path.GetExtension(postedFile.FileName);
            postedFile.SaveAs(filePath);

            string conString = string.Empty;
            switch (extension)
            {
                case ".xls": //Excel 97-03.
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 and above.
                    conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                    break;
            }

            DataTable dt = new DataTable();
            conString = string.Format(conString, filePath);

            using (OleDbConnection connExcel = new OleDbConnection(conString))
            {
                using (OleDbCommand cmdExcel = new OleDbCommand())
                {
                    using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                    {
                        cmdExcel.Connection = connExcel;

                        //Get the name of First Sheet.
                        connExcel.Open();
                        DataTable dtExcelSchema;
                        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                        connExcel.Close();

                        //Read Data from First Sheet.
                        connExcel.Open();
                        cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                        odaExcel.SelectCommand = cmdExcel;
                        odaExcel.Fill(dt);
                        connExcel.Close();
                    }
                }
            }

            conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name.
                    sqlBulkCopy.DestinationTableName = "dbo.Table_1";

                    //[OPTIONAL]: Map the Excel columns with that of the database table
                    sqlBulkCopy.ColumnMappings.Add("Rut", "Rut");
                    sqlBulkCopy.ColumnMappings.Add("Nombres", "Nombres");
                    sqlBulkCopy.ColumnMappings.Add("Malla", "Malla");

                    con.Open();
                    sqlBulkCopy.WriteToServer(dt);
                    con.Close();
                }
            }
        }

        return View();
    }
}
Posted
Updated 25-Sep-18 20:18pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

All you need to do is to loop through the collection of sheets.

DataRow[] sheetsToImport = dtExcelSchema.Rows;
foreach(DataRow r in sheetsToImport)
{
    string sheetName = r["TABLE_NAME"].ToString();
    cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
    //import instructions here!
}
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100