Click here to Skip to main content
15,891,851 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
protected void btnImport_Click(object sender, EventArgs e)
        {
            SaveExcel_Database();
            DataTable dt = (DataTable)ViewState["ItemDetl"];

            DataTable dtdistinct = dt.AsEnumerable().GroupBy(x => x["ID"]).Select(x => x.First()).CopyToDataTable();
            dtdistinct.AsEnumerable().ToList().ForEach(r => r.SetField("Name", dt.AsEnumerable().Where(p=>p["ID"].ToString()==r["ID"].ToString()).Count()));
        }

        private void SaveExcel_Database()
        {
            try
            {
                string path = "";
                string path1 = "";
                if (fld.HasFile)
                {
                    string str = "~/Inventory/UploadSheet";
                    string filename = fld.FileName;
                    if (!Directory.Exists(Server.MapPath(str)))
                    {
                        Directory.CreateDirectory(Server.MapPath(str));
                        //Directory.SetAccessControl(
                        path1 = Server.MapPath(str + "/");
                        path = path1 + filename;
                        fld.SaveAs(path1 + filename);
                    }
                    else
                    {
                        path1 = Server.MapPath(str + "/");
                        path = path1 + filename;
                        fld.SaveAs(path1 + filename);
                    }
                    string Extension = Path.GetExtension(fld.PostedFile.FileName);
                    BudgetExcelUpload(Extension, path);
                }
                if ((System.IO.File.Exists(path1)))
                {
                    System.IO.File.Delete(path1);
                }
                if ((System.IO.File.Exists(path)))
                {
                    System.IO.File.Delete(path);
                }
            }
            catch
            {
            }
        }

        private string GetExcelSheetNames(string conStr)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;
            string aa = "";

            try
            {
                objConn = new OleDbConnection(conStr);
                // Open connection with the database.
                objConn.Open();
                // Get the data table containg the schema guid.
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString().Trim();
                    i++;
                }
                // Loop through all of the sheets if you want too…
                for (int j = 0; j < excelSheets.Length; j++)
                {
                    // Query each excel sheet.
                    aa = dt.Rows[0][2].ToString().Trim();
                }
            }
            catch
            {
                
            }
            finally
            {
                // Clean up.
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
            return aa;
        }

        public void BudgetExcelUpload(string Extesion, string path)
        {
            DataTable dtableItem = new DataTable();
            try
            {
                string Extension1 = Extesion;
                string path1 = path;
                string conStr = "";

                switch (Extension1)
                {
                    case ".xls": //Excel 97-03
                        conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                            @"Data Source=" + path1 + ";" +
                            @"Extended Properties=" + Convert.ToChar(34).ToString().Trim() +
                            @"Excel 8.0;HDR=YES" + Convert.ToChar(34).ToString().Trim();
                        break;
                    case ".xlsx": //Excel 07
                        conStr = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                           @"Data Source=" + path1 + ";" +
                           @"Extended Properties=" + Convert.ToChar(34).ToString().Trim() +
                           @"Excel 12.0;HDR=YES" + Convert.ToChar(34).ToString().Trim();
                        break;
                }

                //DataTable dtableSalaryMaster = new DataTable();

                OleDbConnection oconn = new OleDbConnection(conStr);
                string worksheetName = GetExcelSheetNames(conStr);
                OleDbDataAdapter cmd = new OleDbDataAdapter();
                DataSet excelDataSet1 = new DataSet();

                //======================================================
                // FETCHING EMPLOYEE DETAILS FROM EXCEL SHEET
                //======================================================

                cmd = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + worksheetName + "] ", oconn);
                cmd.Fill(dtableItem);

                ViewState["ItemDetl"] = dtableItem;
            }
            catch
            {
            }
        }


What I have tried:

@"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source=" + path1 + ";" +
@"Extended Properties=" + Convert.ToChar(34).ToString().Trim() +
@"Excel 12.0;HDR=YES" + Convert.ToChar(34).ToString().Trim();
Posted
Updated 31-May-16 2:40am
Comments
Richard MacCutchan 31-May-16 3:13am    
Did you actually install the ACE driver?

1 solution

If you are running you code on 64 bit machine, you might face this error as your OLEDB object is 32 bit only, you need to compile your exe with x86 and then it will run as expected.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900