将Excel表导入Sql Server
来源:互联网 发布:sql server 截 字符串 编辑:程序博客网 时间:2024/06/05 07:46
通过OleDbDataAdapter将指定路径的.xls文件填充到DataSet中。
using System.Data.OleDb;
using System.Data;
...
...{
protected DataSet ExcelToDS(string FilePath)
...{
DataSet ds=new DataSet();
new OleDbDataAdapter("SELECT * FROM [Sheet1$]", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;").Fill(ds,"Sheet1");
return ds;
}
}
using System.Data;
...
...{
protected DataSet ExcelToDS(string FilePath)
...{
DataSet ds=new DataSet();
new OleDbDataAdapter("SELECT * FROM [Sheet1$]", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;").Fill(ds,"Sheet1");
return ds;
}
}
然后将DataSet按照格式写入数据库中:
using System.Data;
using System.Data.SqlClient;
....
...{
.....
protected void LoadButton_Click(object sender, EventArgs e)
...{
if (FileUpload1.PostedFile.FileName.ToString() != null
&& FileUpload1.PostedFile.FileName.ToString() != ""
&& FileUpload1.PostedFile.FileName.EndsWith(".xls")) //FileUpload1为页面中控件
...{
DataSet ds = null;
try
...{
string path = FileUpload1.FileName;
string strFilePath = MapPath("excel") + "/" + path;
FileUpload1.PostedFile.SaveAs(strFilePath);
ds = ExcelToDS(strFilePath);
for (int i = 0; i < ds.Tables[0].Rows.Count-1; i++)
...{
for (int j = 0; j < 6; j++)
...{
string colvalue = ds.Tables[0].Rows[i].ItemArray[j].ToString();
if (j < 2 && colvalue == "")
...{
Response.Write("<script > window.alert( '数据导入发生错误!') </script > ");
}
}
//得到各列的值
string user_id = ds.Tables[0].Rows[i].ItemArray[0].ToString();
......
string union_name = ds.Tables[0].Rows[i].ItemArray[10].ToString();
SqlConnection MyConnection = new SqlConnection();
MyConnection.ConnectionString = MyConnectionString;//MyConnectionString 为你的数据库连接字符串
SqlCommand MyCommand = new SqlCommand();
MyCommand.CommandText="INSERT INTO [XXX] ( [USER_ID],......,[STATUS] ) VALUES ( @user_id,...,@status)";
MyCommand.Connection = MyConnection;
MyCommand.Parameters.Add(new SqlParameter("@user_id",user_id));
.......
MyCommand.Parameters.Add(new SqlParameter("@status",status));
MyConnection.Open();
MyCommand.ExecuteNonQuery();
MyConnection.Close();
}
}
catch (SqlException exc)
...{
Response.Write("<script >window.alert('导入数据发生错误:"+ exc.Message+"') </script > ");
return;
}
Response.Write("<script>window.alert('已成功导入数据!')</script>");
}
else Response.Write("<script>window.alert('请至少选择一个Excel文件导入!')</script>");
}
.....
}
using System.Data.SqlClient;
....
...{
.....
protected void LoadButton_Click(object sender, EventArgs e)
...{
if (FileUpload1.PostedFile.FileName.ToString() != null
&& FileUpload1.PostedFile.FileName.ToString() != ""
&& FileUpload1.PostedFile.FileName.EndsWith(".xls")) //FileUpload1为页面中控件
...{
DataSet ds = null;
try
...{
string path = FileUpload1.FileName;
string strFilePath = MapPath("excel") + "/" + path;
FileUpload1.PostedFile.SaveAs(strFilePath);
ds = ExcelToDS(strFilePath);
for (int i = 0; i < ds.Tables[0].Rows.Count-1; i++)
...{
for (int j = 0; j < 6; j++)
...{
string colvalue = ds.Tables[0].Rows[i].ItemArray[j].ToString();
if (j < 2 && colvalue == "")
...{
Response.Write("<script > window.alert( '数据导入发生错误!') </script > ");
}
}
//得到各列的值
string user_id = ds.Tables[0].Rows[i].ItemArray[0].ToString();
......
string union_name = ds.Tables[0].Rows[i].ItemArray[10].ToString();
SqlConnection MyConnection = new SqlConnection();
MyConnection.ConnectionString = MyConnectionString;//MyConnectionString 为你的数据库连接字符串
SqlCommand MyCommand = new SqlCommand();
MyCommand.CommandText="INSERT INTO [XXX] ( [USER_ID],......,[STATUS] ) VALUES ( @user_id,...,@status)";
MyCommand.Connection = MyConnection;
MyCommand.Parameters.Add(new SqlParameter("@user_id",user_id));
.......
MyCommand.Parameters.Add(new SqlParameter("@status",status));
MyConnection.Open();
MyCommand.ExecuteNonQuery();
MyConnection.Close();
}
}
catch (SqlException exc)
...{
Response.Write("<script >window.alert('导入数据发生错误:"+ exc.Message+"') </script > ");
return;
}
Response.Write("<script>window.alert('已成功导入数据!')</script>");
}
else Response.Write("<script>window.alert('请至少选择一个Excel文件导入!')</script>");
}
.....
}
- 将Excel表导入Sql Server
- 将Excel导入SQL Server
- 将Excel表导入到SQL Server 2005 的DB
- 如何将 Excel 数据导入 SQL Server
- 如何将 Excel 数据导入 SQL Server
- 将Excel文件数据库导入SQL Server
- 将Excel文件数据库导入SQL Server
- 将Excel文件数据库导入SQL Server
- 将Excel导入到SQL Server中!
- 如何将 Excel 数据导入 SQL Server
- 将excel导入sql server数据库
- 将excel导入数据库 (sql server)
- 将ACCESS.EXCEL导入SQL server (转)
- 通过本地sql server 将excel导入远程sql server
- EXCEL:将SQL SERVER中的数据导入到 EXCEL
- sql server导入excel
- 如何将excel导入到sql server中
- 如何将 Excel 数据导入MS SQL Server 数据库
- SpringSource新应用服务器发布 摒弃Java EE
- DelegatingRequestProcessor
- JasperReport + iReport 实现Java报表套打
- heapsort
- 关于xml的增删改
- 将Excel表导入Sql Server
- C#类型关键字全集说明
- 在vc中的regular dll中使用ado的导入问题
- java clone
- AfxOleInit和OleInitialize的区别
- 解决w3wp.exe内存占用问题(转)
- 关于帧缓冲
- (转)2.3快速设计对话框(Rapid Dialog Design)
- (转) 2.4形状能改变的对话框(Shape-Changing Dialogs)