单击按钮后打开Excel工作表 [英] Open excel sheet after button click

问题描述

我需要一个空的Excel工作表，在单击按钮并在该Excel工作表中输入数据后，可以将其保存在sql数据库中.请给我一些想法.
谢谢，
Sampath bejugama

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

推荐答案

这是将Excel工作表上载到datbase并更新到datbase的代码...

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

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

，连接)； OleDbCommand命令= 新建 OleDbCommand("

", connection); OleDbCommand command = new OleDbCommand("Select Name, Address,ContactNo,Email,MemDetail,Reg_No,startDate,endDate FROM [3ymmember

"，连接)； connection.Open(); 如果(RadioButtonList1.SelectedIndex == 0 ) { // 创建DbDataReader到数据工作表 使用(DbDataReader dr = command.ExecuteReader()) { // SQL Server连接字符串 字符串 sqlConnectionString = ConfigurationManager.ConnectionStrings [" span>].ConnectionString; // 批量复制到SQL Server 使用(SqlBulkCopy bulkCopy = 新 SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName = " ; bulkCopy.WriteToServer(dr); Label1.Text = " ; } } } 其他 如果(RadioButtonList1.SelectedIndex == 1 ) { OleDbDataReader odr = command.ExecuteReader(); 字符串名称= " ; 字符串地址= " ; 字符串 ContactNo = " ; 字符串电子邮件= " ; 字符串 MemDetail = " ; 字符串 Reg_No = " ; 字符串 startDate = " ; 字符串 endDate = " ; while (odr.Read()) { 名称=有效(odr， 0 ); // 此处我们正在调用有效方法 地址=有效(odr， 1 )； ContactNo =有效(odr， 2 )； 电子邮件=有效(odr， 3 )； MemDetail = valid(odr， 4 ); Reg_No =有效(odr， 5 )； // 在此，我们将数据插入数据库中 insertdataintosql(名称，地址，联系号，电子邮件，MemDetail，Reg_No，startDate，endDate); } connection.Close(); } 其他 { Label1.Text = " ; } } } 捕获(例外) { Label1.Text = ex.Message; // lblMessage.Text =更新存在一些问题.请确保xls文件格式正确且未上传xls文件包含数据库中已经存在的用户名.; } 最终 { Label1.Visible = true ; } } } }

", 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...

 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";
      }
  }

这篇关于单击按钮后打开Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！