已经把excel文件上传到服务器，怎么将excel中的数据导入到SQL数据库呢？用asp.net和c#
以下代码只能上传excel文件，实现不了将excel中的数据导入到SQL数据库里面，麻烦各位教教我怎么改~~~--------------------编程问答-------------------- for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
public partial class Web_DataImport : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void BtnImport_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(("upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = ExcelSqlConnection(savePath, filename); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
//前面需要在建立一个“upfiles”的文件夹，通过下面的方式获取Excel的值，然后再将这些值用插入到数据库里面
string id = dr[i]["编号"].ToString();
string name = dr[i]["姓名"].ToString();
string sex = dr[i]["性别"].ToString();
string password = dr[i]["密码"].ToString();
string rclass = dr[i]["班级"].ToString();
string profession = dr[i]["专业"].ToString();
string role = dr[i]["身份"].ToString();
//Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
Response.Write("<script>alert('Excle表导入成功!');</script>");
}
}
#region 连接Excel 读取Excel数据 并返回DataSet数据集合
/// <summary>
/// 连接Excel 读取Excel数据 并返回DataSet数据集合
/// </summary>
/// <param name="filepath">Excel服务器路径</param>
/// <param name="tableName">Excel表名称</param>
/// <returns></returns>
public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection ExcelConn = new OleDbConnection(strCon);
try
{
string strCom = string.Format("SELECT * FROM [Sheet1$]");
ExcelConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[" + tableName + "$]");
ExcelConn.Close();
return ds;
}
catch
{
ExcelConn.Close();
return null;
}
}
#endregion
}
{
string intostr = "insert into //你的语句 sqlcon.Open();
sqlcom = new SqlCommand(update_j_sqlstr, sqlcon);
sqlcom.ExecuteNonQuery();//更新
sqlcon.Close();
} --------------------编程问答-------------------- 直接用SQLSERVER自带的导入功能，多好！ --------------------编程问答-------------------- 将EXCEL中的数据导入datatable 然后遍历利用事务一条一条的插入
//执行事务处理--------------------编程问答-------------------- --------------------编程问答-------------------- 还是推荐通过SQL server 导入 很方便
public void DoTran()
{ //建立连接并打开
SqlConnection myConn=GetConn();
myConn.Open();
SqlCommand myComm=new SqlCommand();
//SqlTransaction myTran=new SqlTransaction();
//注意，SqlTransaction类无公开的构造函数
SqlTransaction myTran;
//创建一个事务
myTran=myConn.BeginTransaction();
try
{
//从此开始，基于该连接的数据操作都被认为是事务的一部分
//下面绑定连接和事务对象
myComm.Connection=myConn;
myComm.Transaction=myTran; //定位到pubs数据库
myComm.CommandText="USE pubs";
myComm.ExecuteNonQuery();//操作1
myComm.CommandText=""; //操作2
myComm.ExecuteNonQuery();
//提交事务
myTran.Commit();
}
catch(Exception err)
{
myTran.Roback();
throw new ApplicationException("事务操作出错，系统信息："+err.Message);
}
finally
{
myConn.Close();
}
}
--------------------编程问答-------------------- for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
string intostr = "insert into //你的语句 sqlcon.Open();
sqlcom = new SqlCommand(update_j_sqlstr, sqlcon);
sqlcom.ExecuteNonQuery();//更新
sqlcon.Close();
}
你好(net5354）这段代码放在哪哈 --------------------编程问答-------------------- PasteExcelToExe软件能把EXCEL中数据自动导入EXE文件输入数据的画面中，并能自动提交 --------------------编程问答-------------------- 遍历你导出到DataSet中的数据 ，然后逐条的insert --------------------编程问答-------------------- DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
//前面需要在建立一个“upfiles”的文件夹，通过下面的方式获取Excel的值，然后再将这些值用插入到数据库里面
string id = dr[i]["编号"].ToString();
string name = dr[i]["姓名"].ToString();
string sex = dr[i]["性别"].ToString();
string password = dr[i]["密码"].ToString();
string rclass = dr[i]["班级"].ToString();
string profession = dr[i]["专业"].ToString();
string role = dr[i]["身份"].ToString();
//Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
Response.Write("<script>alert('Excle表导入成功!');</script>");
}
把这段换成6楼的代码 --------------------编程问答--------------------
--------------------编程问答--------------------
bConvert = MathFile(customDs, modelDs);
string dllPath = server.MapPath("/bin/ERJC.DAL.dll");//获取实体类的程序集路径，反射
Assembly assembly = Assembly.LoadFile(dllPath);
string[] classPaths = dllPath.Split('\\');
string classPath = classPaths[classPaths.Length - 1].Replace("dll", "");
string strClassName = string.Empty;
if (bConvert)
{
db.Connection.Open();
System.Data.Common.DbTransaction DBTran = db.Connection.BeginTransaction();
try
{
StringBuilder sb = new StringBuilder();
foreach (System.Data.DataTable dt in customDs.Tables)
{
strClassName = dt.TableName;
object obj = assembly.CreateInstance(classPath + strClassName);
foreach (DataRow dr in dt.Rows)
{
sb.AppendFormat("insert into {0}(", strClassName);
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
if (dr[colIndex] != null)
{
sb.AppendFormat("{0},", dt.Columns[colIndex].ColumnName);
}
}
sb.Remove(sb.Length - 1, 1);
sb.Append(") values (");
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
Type type = obj.GetType();
try
{
if (dr[colIndex] != null)
{
foreach (PropertyInfo p in type.GetProperties())
{
if (p.Name == dt.Columns[colIndex].ColumnName)
{
string dataType = p.PropertyType.FullName;
if (dataType.Contains("Int32"))
{
sb.AppendFormat("{0},", Convert.ToInt32(dr[colIndex]));
}
else if (dataType.Contains("Int64"))
{
sb.AppendFormat("{0},", Convert.ToInt64(dr[colIndex]));
}
else if (dataType.Contains("ToDecimal"))
{
sb.AppendFormat("{0},", Convert.ToDecimal(dr[colIndex]));
}
else if (dataType.Contains("DateTime"))
{
sb.AppendFormat("'{0}',", Convert.ToDateTime(dr[colIndex]));
}
else if (dataType.Contains("String"))
{
sb.AppendFormat("'{0}',", Convert.ToString(dr[colIndex]));
}
}
}
}
}
catch (Exception ex)
{
bConvert = false;
msg = "数据转换出错，请检查数据格式是否正确";
throw ex;
}
finally
{
db.Connection.Close();
}
}
sb.Remove(sb.Length - 1, 1);
sb.Append(")");
}
db.ExecuteStoreCommand(sb.ToString(), null);
}
DBTran.Commit();
db.SaveChanges();
msg = "文件导入成功";
bConvert = true;
}
catch (Exception ex)
{
bConvert = false;
msg = "文件导入失败";
DBTran.Rollback();
throw ex;
}
finally
{
db.Connection.Close();
}
FileUpload1.SaveAs(savePath);我怎么会有错误：E:\NetWork_School\NetWork_UI\ContentManageSystem\User\upfiles\20132427020939导出用户信息.xls”的一部分。 --------------------编程问答-------------------- Refer案例：
http://www.cnblogs.com/insus/archive/2012/06/12/2545801.html --------------------编程问答-------------------- 除 --------------------编程问答-------------------- 除
补充：.NET技术 , ASP.NET
