Click here to Skip to main content
15,916,042 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I need one empty excel sheet, after clicking button and after entering data in that excel sheet, that can be saved in sql database.Give me some idea.
Thanks,
Sampath bejugama
Posted

1 solution

This is the code for excel sheet uploadt to datbase and updated to datbase...

C#
protected void cmdexcelsheetupload_Click(object sender, EventArgs e)
    {
        {


            {
                string strExcelPath = "";

                AllUtilities.ImageUtilities oIU = new AllUtilities.ImageUtilities();
                try
                {

                    if (txtFile.HasFile)
                        strExcelPath = txtFile.PostedFile.FileName.Trim();
                    string strNewFilename = strExcelPath.ToString();
                    strNewFilename = oIU.ExtractFilename(strNewFilename);
                    string strSaveLocation = Server.MapPath("../Excelsheet/") + strNewFilename;
                    txtFile.SaveAs(strSaveLocation);
                    string path = Server.MapPath("../Excelsheet/3ymmember.xls");
                    // Connection String to Excel Workbook
                    //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\demo.xlsx; Extended Properties=""Excel 12.0; HDR=YES;""";
                    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes"";";
                    // Create Connection to Excel Workbook
                    using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
                    {
                        //OleDbCommand command = new OleDbCommand("Select CAdminLoginId='"+ strCorporateAdmin +"', CUserLoginId, CUserPassword, FirstName, LastName, EmailId, Status FROM [demo$]", connection);
                        OleDbCommand command = new OleDbCommand("Select Name, Address,ContactNo,Email,MemDetail,Reg_No,startDate,endDate FROM [3ymmember$]", connection);
                        connection.Open();

                        if (RadioButtonList1.SelectedIndex == 0)
                        {
                            // Create DbDataReader to Data Worksheet
                            using (DbDataReader dr = command.ExecuteReader())
                            {
                                // SQL Server Connection String
                                string sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                                // Bulk Copy to SQL Server
                                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                                {
                                    bulkCopy.DestinationTableName = "CommiteeMemberFull";
                                    bulkCopy.WriteToServer(dr);
                                    Label1.Text = "Users has been added successfully.";
                                }
                            }


                        }
                        else if (RadioButtonList1.SelectedIndex == 1)
                        {

                            OleDbDataReader odr = command.ExecuteReader();
                            string Name = "";
                            string Address = "";
                            string ContactNo = "";
                            string Email = "";
                            string MemDetail = "";
                            string Reg_No = "";
                            string startDate = "";
                            string endDate = "";
                            while (odr.Read())
                            {
                                Name = valid(odr, 0);//Here we are calling the valid method
                                Address = valid(odr, 1);
                                ContactNo = valid(odr, 2);
                                Email = valid(odr, 3);
                                MemDetail = valid(odr, 4);
                                Reg_No = valid(odr, 5);
                                //Here using this method we are inserting the data into the database
                                insertdataintosql(Name, Address, ContactNo, Email, MemDetail, Reg_No,startDate, endDate);
                            }
                            connection.Close();


                        }

                        else
                        {
                            Label1.Text = "Please Select One Opration!";
                        }
                    }

                }
                catch (Exception ex)
                {
                    Label1.Text = ex.Message;
                    // lblMessage.Text = "There were some problem with update. Please be sure that xls file in proper format and not uploading xls file containing already exiting user name in database.";
                }
                finally
                {
                    Label1.Visible = true;


                }

            }
        }

    }


This is the method.... for insertion...
C#
 public void insertdataintosql(string Name, string Address, string ContactNo, string Email, string MemDetail, string Reg_No, string startDate, string endDate)
    {//inserting data into the Sql Server
        string sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        // SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = "Update CommiteeMemberFull SET Name=@Name, Address=@Address,ContactNo=@ContactNo,Email=@Email,MemDetail=MemDetail ,startDate=startDate,endDate=endDate where Reg_No=@Reg_No";

        cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = Name;
        cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = Address;
        cmd.Parameters.Add("@ContactNo", SqlDbType.NVarChar).Value = ContactNo;
        cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Email;
        cmd.Parameters.Add("@MemDetail", SqlDbType.NVarChar).Value = MemDetail;
        cmd.Parameters.Add("@Reg_No", SqlDbType.NVarChar).Value = Reg_No;
        cmd.Parameters.Add("@startDate", SqlDbType.NVarChar).Value = startDate;
        cmd.Parameters.Add("@endDate", SqlDbType.NVarChar).Value = endDate;

        cmd.CommandType = CommandType.Text;
        connection.Open();
        cmd.Connection = connection;
        cmd.ExecuteNonQuery();
        connection.Close();
        Label1.Text = "Excel Sheet Updated successfuly!";
    }
    protected string valid(OleDbDataReader myreader, int stval)//if any columns are 
    //found null then they are replaced by zero
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }<pre>

<pre lang="cs">private string ReturnExtension(string fileExtension)
  {
      switch (fileExtension)
      {
          case ".htm":
          case ".html":
          case ".log":
              return "text/HTML";
          case ".txt":
              return "text/plain";
          case ".doc":
              return "application/ms-word";
          case ".tiff":
          case ".tif":
              return "image/tiff";
          case ".asf":
              return "video/x-ms-asf";
          case ".avi":
              return "video/avi";
          case ".zip":
              return "application/zip";
          case ".xls":
          case ".csv":
              return "application/vnd.ms-excel";
          case ".gif":
              return "image/gif";
          case ".jpg":
          case "jpeg":
              return "image/jpeg";
          case ".bmp":
              return "image/bmp";
          case ".wav":
              return "audio/wav";
          case ".mp3":
              return "audio/mpeg3";
          case ".mpg":
          case "mpeg":
              return "video/mpeg";
          case ".rtf":
              return "application/rtf";
          case ".asp":
              return "text/asp";
          case ".pdf":
              return "application/pdf";
          case ".fdf":
              return "application/vnd.fdf";
          case ".ppt":
              return "application/mspowerpoint";
          case ".dwg":
              return "image/vnd.dwg";
          case ".msg":
              return "application/msoutlook";
          case ".xml":
          case ".sdxl":
              return "application/xml";
          case ".xdp":
              return "application/vnd.adobe.xdp+xml";
          default:
              return "application/octet-stream";
      }
  }
 
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