在C#中如何实现用代码将excel导入到sql数据库中

Question

如题所述

Answer 1

1.打开sqlserver 选择数据库 点击右健[所有任务]-[导入数据]
2.按照向导 选择数据源 [micrsoft excel 97-2000] 选择要导入的文件
3.接下来的向导选择要导入到的数据库，该导入支持新建表，和表数据追加都可以，按照向导步骤操作就可以啦！

Answer 2

/// <summary>
/// 从Excel读取数据
/// </summary>
/// <param name="filePath">路径</param>
/// <returns>DataSet</returns>
public DataSet ImportFromExcel(string filePath)
{
DataSet ds = new DataSet();
string connString = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
DataTable table = OleDbHelper.GetExcelTables(connString);
if(table == null || table.Rows.Count <= 0)
{
return null;
}

foreach(DataRow dr in table.Rows)
{
string cmdText = "select * from [" + dr["TABLE_NAME"].ToString() + "]";
DataTable dt = OleDbHelper.FillDataTable(connString, cmdText);
dt.TableName = dr["TABLE_NAME"].ToString();
ds.Tables.Add(dt);
}

return ds;
}

接下来只要把DataSet写入数据库本回答被提问者采纳

Answer 3

下面是一个我以前写的程序代码，自己看看吧。有什么不懂得就说。
using System.Data.SqlClient;
using System.Data.Common;//use for converting excel file to SQL table by OleDb
using System.Data.OleDb;

private void btnConvert_Click(object sender, EventArgs e)
{
//convert Excel to SQL using OLEDB
string path = txtExcel.Text;

string execelConnectionStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=book_edited2.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

using (OleDbConnection conn = new OleDbConnection(execelConnectionStr))
{
OleDbCommand cmd = new OleDbCommand("select Typlabel, Product,ProdIndex FROM [Sheet1$]", conn);
conn.Open();

using (DbDataReader dr = cmd.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=yy;Initial Catalog=wincorhighpot ;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ModelTable";
bulkCopy.WriteToServer(dr);
}
}
}
MessageBox.Show("Update ModelTable Completed.");
}

Answer 4

转换吧。