无法将excel工作表中的所有数据导入C#中的sql

本文关键字:数据 导入 sql 中的 excel 工作 | 更新日期: 2023-09-27 18:27:44

我有一个excel文件,里面有3个工作表。我想把它导入数据库。我只成功地将一个工作表导入到一个表中。这是我的代码:

string sqlConnectionString = @"Data Source=Magangpti1'SQLEXPRESS;Database=RcColdis;Trusted_Connection=true;Persist Security Info=True";
                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=True";
                OleDbConnection con = null;
                DataTable dt = null;
                con = new OleDbConnection(excelConnectionString);
                con.Open();
                dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                con.Close();

                string sheetname = dt.Rows[0]["Table_Name"].ToString();
                con.Open();
                OleDbCommand cmd = new OleDbCommand("Select * from [" + sheetname + "]", con);
                OleDbDataReader dReader;
                dReader = cmd.ExecuteReader();
                SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlConnectionString);
                sqlBulk.DestinationTableName = "Absolute_IDR";
                sqlBulk.WriteToServer(dReader);
                con.Close();

                string sheetname1 = dt.Rows[1]["Table_Name"].ToString();
                con.Open();
                OleDbCommand cmd1 = new OleDbCommand("Select * from [" + sheetname1 + "] ", con);
                OleDbDataReader dReader1;
                dReader1 = cmd1.ExecuteReader();
                SqlBulkCopy sqlBulk1 = new SqlBulkCopy(sqlConnectionString);
                sqlBulk1.DestinationTableName = "RC_Saham";
                sqlBulk1.WriteToServer(dReader1);
                con.Close();

                string sheetname2 = dt.Rows[2]["Table_Name"].ToString();
                con.Open();
                OleDbCommand cmd2 = new OleDbCommand("Select * from [" + sheetname2 + "]", con);
                OleDbDataReader dReader2;
                dReader2 = cmd2.ExecuteReader();
                SqlBulkCopy sqlBulk2 = new SqlBulkCopy(sqlConnectionString);
                sqlBulk2.DestinationTableName = "RC_Member";
                sqlBulk2.ColumnMappings.Add("Kode AK", "Kode AK");
                sqlBulk2.ColumnMappings.Add("Risk Charge", "Risk Charge"); 
                sqlBulk2.WriteToServer(dReader2);
                con.Close();
               return View("success");

  }
            else
            {
                return View("Index");
            }
        }
    }
}
}

我无法将其余工作表导入数据库。你能帮我吗?

无法将excel工作表中的所有数据导入C#中的sql

是否要将第二张表数据输入dReader,请尝试给出硬编码的表名`

OleDbCommand cmd=new OleDbCommand("从[sheetname1]中选择*",con);`