我这里有个Excel导入到数据库的实例
不知道能否帮助lz
protected void Button2_Click(object sender, EventArgs e)
{
string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'E:/my.xls';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataTable dt = new DataTable();
myDa.Fill(dt);
if (dt.Rows.Count > 0)
{
try
{
string CnnString = "server=WIN-J8R41VFBJUL\\WORK;database=MyData;uid=sa;pwd=sa";
SqlConnection conn = new SqlConnection(CnnString);
SqlBulkCopy copy = new SqlBulkCopy(CnnString);//与目标服务器连接
copy.DestinationTableName = "excel";//导入到数据库的表名
SqlBulkCopyColumnMapping MapUserID = new SqlBulkCopyColumnMapping();//他们两个的映射
MapUserID.DestinationColumn = "MyId";//数据库字段
MapUserID.SourceColumn = "Id";//excel第一行第一列的字符作为,字段名
copy.ColumnMappings.Add(MapUserID);//添加到cop中去
SqlBulkCopyColumnMapping MapUserName = new SqlBulkCopyColumnMapping();
MapUserName.DestinationColumn = "MyName";
MapUserName.SourceColumn = "Name";
copy.ColumnMappings.Add(MapUserName);
copy.WriteToServer(dt);
Response.Write("导入成功");
}
catch
{
Response.Write("导入失败");
}
}
}
用SQL自带的DTS工具可以直接完成excel的导入工作
期待
期待下面回答