Click here to Skip to main content
15,908,661 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
my design code like

ASP.NET
<body>
    <form id="form1"  runat="server">
    <div>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="Button1" Text="Upload" OnClick = "Upload" runat="server" />
    </div>
    </form>
</body>


What I have tried:

my code is like

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;
using ACCESS = Microsoft.Office.Interop.Access;


namespace DevRural
{
    public partial class UploadDoc : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void Upload(object sender, EventArgs e)
        {
            //Upload and save the file
            string excelPath = Server.MapPath("~/Excel/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
            FileUpload1.SaveAs(excelPath);

            string conString = string.Empty;
            string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            switch (extension)
            {
                case ".xls": //Excel 97-03
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 or higher
                    conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                    break;

            }
            conString = string.Format(conString, excelPath);
            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {
                excel_con.Open();
                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                DataTable dtExcelData = new DataTable();

                

                //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
                dtExcelData.Columns.AddRange(new DataColumn[24] { new DataColumn("Sno", typeof(int)),
                new DataColumn("BranchCode", typeof(int)),
                new DataColumn("TempRegistrationNo",typeof(int)),
                new DataColumn("RegistrationNo",typeof(string)),
                new DataColumn("EnrollmentNo",typeof(string)),
                new DataColumn("Salutation",typeof(string)),
                new DataColumn("FirstName",typeof(string)),
                new DataColumn("MiddleName",typeof(string)),
                new DataColumn("LastName",typeof(string)),
                new DataColumn("GuardianType",typeof(string)),
                new DataColumn("FatherOrSpouseFirstName",typeof(string)),
                new DataColumn("FatherOrSpouseLastName",typeof(string)),
                new DataColumn("DOB",typeof(string)),
                new DataColumn("Gender",typeof(string)),
                new DataColumn("MaritalStatus",typeof(string)),
                new DataColumn("Category",typeof(string)),
                new DataColumn("Minority",typeof(string)),
                new DataColumn("Religion",typeof(string)),
                new DataColumn("PState",typeof(string)),
                new DataColumn("PDistrict",typeof(string)),
                new DataColumn("PBlockCity",typeof(string)),
                new DataColumn("PPinCode",typeof(int)),
                new DataColumn("PVillageLocation",typeof(string)),
                new DataColumn("PAddress1",typeof(string))          
            
            });

                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                {
                    oda.Fill(dtExcelData);
                }
                excel_con.Close();
                

                //string consString = ConfigurationManager.ConnectionStrings["DDUGKY"].ConnectionString;

                OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\accessdb\rural.mdb");

                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\accessdb\rural.mdb"))
                {
                    //using (SqlBulkCopy sqlBulkCopy = new OleDbDataAdapter(con))  
                 
                    using (OleDbDataAdapter SqlBulkCo =new OleDbDataAdapter() )
                    
                    {
                        FileInfo fi = new FileInfo(FileUpload1.PostedFile.FileName);
                        string ext = fi.Extension;
                        if (ext == ".xls" || ext == ".xlsx")
                        {
                            //Set the database table name
                            //SqlBulkCo.DestinationTableName = "[dbo].[stuReg]";

                            //[OPTIONAL]: Map the Excel columns with that of the database table
                            SqlBulkCo.ColumnMappings.Add("Sno", "Sno");
                            SqlBulkCo.ColumnMappings.Add("BranchCode", "BranchCode");
                            SqlBulkCo.ColumnMappings.Add("TempRegistrationNo", "TempRegistrationNo");

                            SqlBulkCo.ColumnMappings.Add("RegistrationNo", "RegistrationNo");
                            SqlBulkCo.ColumnMappings.Add("EnrollmentNo", "EnrollmentNo");
                            sqlBulkCopy.ColumnMappings.Add("Salutation", "Salutation");

                            sqlBulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                            sqlBulkCopy.ColumnMappings.Add("MiddleName", "MiddleName");
                            sqlBulkCopy.ColumnMappings.Add("LastName", "LastName");

                            sqlBulkCopy.ColumnMappings.Add("GuardianType", "GuardianType");
                            sqlBulkCopy.ColumnMappings.Add("FatherOrSpouseFirstName", "FatherOrSpouseFirstName");
                            sqlBulkCopy.ColumnMappings.Add("FatherOrSpouseLastName", "FatherOrSpouseLastName");


                            sqlBulkCopy.ColumnMappings.Add("DOB", "DOB");
                            sqlBulkCopy.ColumnMappings.Add("Gender", "Gender");
                            sqlBulkCopy.ColumnMappings.Add("MaritalStatus", "MaritalStatus");

                            sqlBulkCopy.ColumnMappings.Add("Category", "Category");
                            sqlBulkCopy.ColumnMappings.Add("Minority", "Minority");
                            sqlBulkCopy.ColumnMappings.Add("Religion", "Religion");

                            sqlBulkCopy.ColumnMappings.Add("PState", "PState");
                            sqlBulkCopy.ColumnMappings.Add("PDistrict", "PDistrict");
                            sqlBulkCopy.ColumnMappings.Add("PBlockCity", "PBlockCity");

                            sqlBulkCopy.ColumnMappings.Add("PPinCode", "PPinCode");
                            sqlBulkCopy.ColumnMappings.Add("PVillageLocation", "PVillageLocation");
                            sqlBulkCopy.ColumnMappings.Add("PAddress1", "PAddress1");

                            con.Open();
                            sqlBulkCopy.WriteToServer(dtExcelData);
                            con.Close();
                            ScriptManager.RegisterStartupScript(Page, GetType(), "script1", "alert('Excel file successfully imported into DB');", true);
                        }
                        else
                        {

                        }

                    }
                }
            }
        }
    }
}
Posted
Updated 18-Apr-16 22:32pm
v2
Comments
Thomas Nielsen - getCore 4-Apr-16 4:16am    
MS Access has a brilliant import guide, so depending on how much you'd actually need to rerun this import a lot of times? ... if not use the import data guide, it rocks at that task.
Possibly you'd have to save as .csv first
Member 11036582 4-Apr-16 4:54am    
dear Nielsen i want client only browse a Excel sheet and save data in my database Table .i have already save in SQL database but dont save in Access Database can u help ?
Thomas Nielsen - getCore 19-Apr-16 4:18am    
Not sure i understand exactly, so they browse for an excel sheet which already exist, download it and make their changes and you then decipher what is new and update the database?
This cannot be done automatically, you'll have to write the logics yourself, which makes sense because you're the defender of your DB integrity
phil.o 4-Apr-16 4:16am    
What is the problem? What is your question?
Member 11036582 4-Apr-16 4:56am    
sir i want upload excel sheet and save excel sheet data in my database i am use Access database. can u help me?

C#
string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel+";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand())
  {
    cmd.Connection = conn;
    cmd.CommandText = "INSERT INTO [MS Access;Database=" + Access + "].[TableName_Person] SELECT * FROM [Sheet1$]";
    conn.Open();
    cmd.ExecuteNonQuery();
  }
}


for further help
C# Corner : Error Display[u7se this link]


try this one i hope it work
 
Share this answer
 
v3
Not very clear of what the user case is, but if you want to allow users to edit an excel sheet online, a fine implementation suggestion is here:
spreadsheet.codeplex.com

Now i'm presuming your challanges is not the simple file upload or download, you didn't at least write that so in case you want or need that i trust you can. Generally not a good idea IMO, but that's not my call.

If your problem is to interact with Excel so that you can update MSACCESS ISAAM DB's i recommend the .net interop assemblies, they're pretty good even if somewhat slow:
https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.aspx

The best and by far fastest option is to use open xml sdk, though some very old versions of excel will not be supported:
Welcome to the Open XML SDK 2.5 for Office[^]
 
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