用C#怎么将Excel文档,导入到Sql Server数据库 。 用C#怎么将Excel文档,导入到Sql Server数据库 。能给具体例子吗?谢谢~~~ 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 protected void btnReadDataTo_Click(object sender, EventArgs e) { if (FuloadExcelFile.FileName == "") return; string fileExt = System.IO.Path.GetExtension(FuloadExcelFile.FileName); if (fileExt != ".xls")//必须是EXCEL文件 return; string filepath = FuloadExcelFile.PostedFile.FileName;//文件路径 DataTable dt = new DataTable(); dt = CallExcel(filepath);//返回EXCEL文件的数据 if(InsertSQLServer(dt,FuloadExcelFile.FileName.Split('.')[0]))//导入数据库 {} } protected DataTable CallExcel(string filepath) { OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0"); con.Open(); string sql = "select * from [Sheet1$]";//选择第一个数据SHEET //OleDbCommand command = new OleDbCommand(sql, con); //OleDbDataReader reader = command.ExecuteReader(); //if (reader.Read()) //{ // reader[0].ToString();//直接读出数据 //} OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con); DataTable dt = new DataTable(); adapter.Fill(dt); //reader.Close(); //command.Dispose(); con.Close(); con.Dispose(); return dt; } protected bool InsertSQLServer(DataTable dt,string dataname) { string strCon = @"Server=BOBER\SQLExpress;Integrated Security=true;";//无数据库名连接 string strTest="testTable"; try { SqlConnection con = new SqlConnection(strCon);//创建数据库 con.Open(); string strSQL = "IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=N'" + dataname + "') CREATE DATABASE [" + dataname + "]"; SqlCommand command = new SqlCommand(strSQL, con); command.ExecuteNonQuery(); //创建数据库表 strSQL = "USE[" + dataname + "] IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[DBO].[" + strTest + "]') AND type in (N'U')) CREATE TABLE " + strTest + " ("; string strColumn=string.Empty; for (int i = 0; i < dt.Columns.Count; i++) { strSQL += "[" + dt.Columns[i].ColumnName + "] [nvarchar](256),"; strColumn+=dt.Columns[i].ColumnName+","; } strSQL += " )"; SqlCommand newcom = new SqlCommand(strSQL, con); newcom.ExecuteNonQuery(); //插入数据 strColumn=strColumn.Substring(0,strColumn.Length-1); for(int i=0;i<dt.Rows.Count;i++) { strSQL = "USE[" + dataname + "] Insert into "+strTest+" (" + strColumn + ") values ("; for (int k = 0; k < dt.Columns.Count; k++) { strSQL += "'"+dt.Rows[i][k].ToString()+"',"; } strSQL = strSQL.Substring(0, strSQL.Length - 1); strSQL += ")"; SqlCommand insertCom = new SqlCommand(strSQL, con); insertCom.ExecuteNonQuery(); } return true; } catch { return false; } }测试可用 导入excel到数据库的方法太多了!通常最简单的做法是首先:读取excel数据到dataset然后:一条条的导入到sqlserver 以前写的,不知道合不合用···http://lymegg1984.blog.163.com/blog/static/393491192008018115915454/ 生成/读取(反向更新数据库) Excel文件(示例代码下载):http://blog.csdn.net/ChengKing/archive/2005/11/29/539514.aspxhttp://blog.csdn.net/ChengKing/category/292667.aspx EXCEL导到数据库,最简单的方法就是不用程序,你可以试试! #region 获取选择的EXCEL文件中的数据 /// <summary> /// 获取选择的EXCEL文件中的数据 /// </summary> /// <param name="filepath">Excel文件的路径</param> /// <returns></returns> private DataTable GetExcelData(string filepath) { //连接字符串 string excelConnectionStr = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source= " + filepath + " ;" + " Extended Properties = Excel 8.0;"; OleDbConnection excelConn = new OleDbConnection(excelConnectionStr); //打开数据源连接 try { if (excelConn.State == ConnectionState.Closed) { excelConn.Open(); } } catch { MessageBox.Show("连接数据源失败,请检查EXCEL文件是否正确!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return null; } //设置查询语句 OleDbDataAdapter selectCmd = new OleDbDataAdapter("select * from [Sheet1$]",excelConn); DataTable dt = new DataTable(); //执行查询Excel表的命令 try { selectCmd.Fill(dt); } catch { MessageBox.Show("该Excel文件的工作表的名字不是[Sheet1$]!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return null; } finally { if (excelConn.State == ConnectionState.Open) { excelConn.Close(); } } //判断DataTable中是否有数据 if (dt.Rows.Count > 0) { return dt; } else { MessageBox.Show("没有读到Excel表中的数据!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return null; } } #endregion 接9楼然后将dt中的数据写入sql server。 ******* 导出到excel EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""' /*********** 导入Excel SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions .Net创建服务程序怎么打开Winform窗体 foreach是什么执行流程呢? 急求C#单例模式例子 C#读写稀疏文件时,FILE_ALLOCATED_RANGE_BUFFER 结构体怎么声明? 如何获取动态添加控件的值 简单问题:c#如何在pictureBox中定义键盘事件? c#windows application界面设计问题,请大侠解答 问一个数据同步的问题? 有關Asp.net RichTextBox控件使用遇到的問題顯亂碼 大家帮忙看看。关于ADO.NET的 各位帮忙看一下 打开上传到Upload文件夹下面的莫个文件
protected void btnReadDataTo_Click(object sender, EventArgs e)
{
if (FuloadExcelFile.FileName == "")
return;
string fileExt = System.IO.Path.GetExtension(FuloadExcelFile.FileName);
if (fileExt != ".xls")//必须是EXCEL文件
return;
string filepath = FuloadExcelFile.PostedFile.FileName;//文件路径
DataTable dt = new DataTable();
dt = CallExcel(filepath);//返回EXCEL文件的数据
if(InsertSQLServer(dt,FuloadExcelFile.FileName.Split('.')[0]))//导入数据库
{}
} protected DataTable CallExcel(string filepath)
{
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0");
con.Open();
string sql = "select * from [Sheet1$]";//选择第一个数据SHEET
//OleDbCommand command = new OleDbCommand(sql, con);
//OleDbDataReader reader = command.ExecuteReader();
//if (reader.Read())
//{
// reader[0].ToString();//直接读出数据
//}
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con);
DataTable dt = new DataTable();
adapter.Fill(dt);
//reader.Close();
//command.Dispose();
con.Close();
con.Dispose();
return dt;
} protected bool InsertSQLServer(DataTable dt,string dataname)
{
string strCon = @"Server=BOBER\SQLExpress;Integrated Security=true;";//无数据库名连接
string strTest="testTable";
try
{
SqlConnection con = new SqlConnection(strCon);//创建数据库
con.Open();
string strSQL = "IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=N'" + dataname + "') CREATE DATABASE [" + dataname + "]";
SqlCommand command = new SqlCommand(strSQL, con);
command.ExecuteNonQuery(); //创建数据库表
strSQL = "USE[" + dataname + "] IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[DBO].[" + strTest + "]') AND type in (N'U')) CREATE TABLE " + strTest + " (";
string strColumn=string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)
{
strSQL += "[" + dt.Columns[i].ColumnName + "] [nvarchar](256),";
strColumn+=dt.Columns[i].ColumnName+",";
}
strSQL += " )";
SqlCommand newcom = new SqlCommand(strSQL, con);
newcom.ExecuteNonQuery();
//插入数据
strColumn=strColumn.Substring(0,strColumn.Length-1);
for(int i=0;i<dt.Rows.Count;i++)
{
strSQL = "USE[" + dataname + "] Insert into "+strTest+" (" + strColumn + ") values (";
for (int k = 0; k < dt.Columns.Count; k++)
{
strSQL += "'"+dt.Rows[i][k].ToString()+"',";
}
strSQL = strSQL.Substring(0, strSQL.Length - 1);
strSQL += ")";
SqlCommand insertCom = new SqlCommand(strSQL, con);
insertCom.ExecuteNonQuery();
}
return true;
}
catch
{
return false;
}
}
测试可用
首先:读取excel数据到dataset
然后:一条条的导入到sqlserver
http://lymegg1984.blog.163.com/blog/static/393491192008018115915454/
http://blog.csdn.net/ChengKing/archive/2005/11/29/539514.aspxhttp://blog.csdn.net/ChengKing/category/292667.aspx
/// <summary>
/// 获取选择的EXCEL文件中的数据
/// </summary>
/// <param name="filepath">Excel文件的路径</param>
/// <returns></returns>
private DataTable GetExcelData(string filepath)
{
//连接字符串
string excelConnectionStr = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source= " + filepath + " ;"
+ " Extended Properties = Excel 8.0;";
OleDbConnection excelConn = new OleDbConnection(excelConnectionStr); //打开数据源连接
try
{
if (excelConn.State == ConnectionState.Closed)
{
excelConn.Open();
}
}
catch
{
MessageBox.Show("连接数据源失败,请检查EXCEL文件是否正确!", "错误",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
} //设置查询语句
OleDbDataAdapter selectCmd = new OleDbDataAdapter("select * from [Sheet1$]",excelConn); DataTable dt = new DataTable(); //执行查询Excel表的命令
try
{
selectCmd.Fill(dt);
}
catch
{
MessageBox.Show("该Excel文件的工作表的名字不是[Sheet1$]!", "错误",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
} //判断DataTable中是否有数据
if (dt.Rows.Count > 0)
{
return dt;
}
else
{
MessageBox.Show("没有读到Excel表中的数据!", "错误",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
#endregion
然后将dt中的数据写入sql server。
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'
/*********** 导入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions