如何把excel文件导入到数据库中

[yaochungeng521]

我现在做一个教师工资管理系统，但是要求以后更新的是用excel导入数据库。这个应该怎么弄，求解。~

[人生难得一只鸡]

http://blog.sina.com.cn/s/blog_66588a630100tj6w.html

[EdsionWang]

mssql里面自带导入导出工具，可以选择数据源，来自excel。要在程序里面做的话，就先把数据读出，再写入sql数据库里面吧

[我爱白富美]

http://topic.csdn.net/u/20120512/22/c199427b-f55f-4a5c-8f8e-4b90e9a15b83.html
这里有刚发的

[yaochungeng521]

楼上说的对啊，是要就是用户不会用数据库，然后直接可以用execl导出数据。

[yaochungeng521]

我现在就是用FileUpload还有Button，然后Button代码

protected void btnUpload_Click(object sender, EventArgs e)
{
string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString.ToString();
OracleConnection con = new OracleConnection(strConnection);
con.Open();
//连接数据库，并打开数据库
if (Convert.ToInt32(lblmes.Text) > 0)

{
foreach (GridViewRow row in this.gridview1.Rows)
{

string zsbh = row.Cells[0].Text.ToString();
string xm = row.Cells[1].Text.ToString();
//DateTime kssj =DateTime.Parse(row.Cells[2].Text);
string sfcx = row.Cells[2].Text.ToString();
//string zslx = row.Cells[4].Text.ToString();
string strSQL = "insert into tb_classic_joke(num,笑话一则,item) values('" + zsbh + "','" + xm + "','" + sfcx + "')";
//数据库中必须存在该表zs
OracleCommand com = new OracleCommand(strSQL, con);
com.ExecuteNonQuery();

this.Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('导入成功！');</script>");
}

}
if (lblmes.Text != "" || Convert.ToInt32(lblmes.Text) != 0)
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('数据不能为空！');</script>");
}
con.Close();

}
}

显示错误 11 找不到类型或命名空间名称“OracleCommand”(是否缺少 using 指令或程序集引用?)

[xk330cool]

这个功能我做过的。用了一个组件。Net.SourceForge.Koogra.dll

//////////////////////////////内存流上传数据///////////////////////////////////
if (FileUpload1.HasFile)
{
#region 验证文件格式
//此处需要判断文件格式
string str = FileUpload1.PostedFile.ContentType;
//Response.Write("文件类型：" + str);
string filename = "";
FileExtension[] fe = { FileExtension.EXCEL };

if (IsAllowedExtension(FileUpload1, fe) && (str == "application/vnd.ms-excel"))
{
string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
errpt.Text = "文件类型为：" + str + "<br>" + "验证通过！";
//filename = "/Images/" + DateTime.Now.ToString("yyyyMMddHHmmss") + fileExt;
//FileUpload1.PostedFile.SaveAs(Server.MapPath(filename));
}
else
{
errpt.Text = "文件类型为：" + str + "<br>" + "验证未通过！";
return;
}
#endregion

#region 数据导入
DataTable dt = null;

try
{
using (System.IO.MemoryStream stream = new System.IO.MemoryStream(FileUpload1.FileBytes))
{
dt = ExcelUtils.TranslateToTable(stream, "sheet1");
errpt.Text ="数据条数为："+ (dt.Rows.Count-1).ToString();

devicesBLL bll = new devicesBLL();
if (dt.Rows.Count!=0)
{
if (bll.ins_Batch(dt))
{
Label1.Text = "批量插入成功！";

}
else
{
Label1.Text = "批量插入失败！请检查原数据格式";
return;
}


}


}


}
catch (Exception ex)
{
errpt.Text = "处理数据文件错误";
errpt.Text = ex.Message;

}
#endregion

}
else
{
errpt.Text = "请选择数据文件";
}
前台的。
/////////////////////////////////////////////////////////////////////////
////////////////////////读取最大值////////////////////////////
bool tag = false;
mydbconn.Open();
mytran = mydbconn.BeginTransaction();


OracleCommand cmdReadMaxXH = new OracleCommand("select id from main_devices_sheet2 where id=(select nvl(max(to_number(id)),0) from main_devices_sheet2) for update", mydbconn); //max()在对number类型和为空时存在Bug
cmdReadMaxXH.Transaction = mytran;
try
{
Decimal id = Convert.ToDecimal(cmdReadMaxXH.ExecuteScalar()) + 1;
Decimal num = id;
dt.Columns.Add("id");
//dt.Columns["id"].DataType = System.Type.GetType("System.Decimal");


for (int i = 1; i < dt.Rows.Count; i++) /////////////////???????????????????????????字段
{
dt.Rows[i]["id"] = (num++).ToString();
}


}
catch (Exception ex)
{
errpt = ex.Message + "ins_Batch.SQL_READ_MAX_ID2";
mytran.Rollback();
mydbconn.Close();
tag = false;
return tag;
}



////////////////////////批量导入////////////////////////////
OracleDataAdapter OracleAdapter = new OracleDataAdapter();
string insertSql = "insert into main_devices_sheet2 (id,dev_name,dev_type,dev_category,dev_subcategory,dev_brand,dev_model,dev_receivecom,dev_receivecom2,dev_configuration,dev_use,dev_subuse,dev_receiveinst,dev_position,dev_source,dev_state,dev_onlinetime,dev_useman,dev_maintenstart,dev_maintenend,dev_maintencom,dev_machinenumber,dev_snnumber,dev_maintenman,dev_maintentel,dev_apply,dev_plan,dev_contract,dev_project,dev_originalvalue,dev_fixedassetsnum,dev_assetsvalue,dev_summary) values (:id,:dev_name,:dev_type,:dev_category,:dev_subcategory,:dev_brand,:dev_model,:dev_receivecom,:dev_receivecom2,:dev_configuration,:dev_use,:dev_subuse,:dev_receiveinst,:dev_position,:dev_source,:dev_state,:dev_onlinetime,:dev_useman,:dev_maintenstart,:dev_maintenend,:dev_maintencom,:dev_machinenumber,:dev_snnumber,:dev_maintenman,:dev_maintentel,:dev_apply,:dev_plan,:dev_contract,:dev_project,:dev_originalvalue,:dev_fixedassetsnum,:dev_assetsvalue,:dev_summary)";
OracleAdapter.InsertCommand = new OracleCommand();
OracleAdapter.InsertCommand.CommandType = CommandType.Text;
OracleAdapter.InsertCommand.CommandText = insertSql;
OracleParameter[] insertParms = {
new OracleParameter(":id", OracleType.VarChar,2000, "id"),
new OracleParameter(":dev_name", OracleType.VarChar, 2000, "dev_name"),
new OracleParameter(":dev_type", OracleType.VarChar, 2000, "dev_type"),
new OracleParameter(":dev_category", OracleType.VarChar,2000, "dev_category"),
new OracleParameter(":dev_subcategory", OracleType.VarChar,2000, "dev_subcategory"),
new OracleParameter(":dev_brand", OracleType.VarChar,2000, "dev_brand"),
new OracleParameter(":dev_model", OracleType.VarChar,2000, "dev_model"),
new OracleParameter(":dev_receivecom", OracleType.VarChar,2000, "dev_receivecom"),
new OracleParameter(":dev_receivecom2", OracleType.VarChar,2000, "dev_receivecom2"),
new OracleParameter(":dev_configuration", OracleType.VarChar,2000, "dev_configuration"),
new OracleParameter(":dev_use", OracleType.VarChar,2000, "dev_use"),
new OracleParameter(":dev_subuse", OracleType.VarChar,2000, "dev_subuse"),
new OracleParameter(":dev_receiveinst", OracleType.VarChar,2000, "dev_receiveinst"),
new OracleParameter(":dev_position", OracleType.VarChar,2000, "dev_position"),
new OracleParameter(":dev_source", OracleType.VarChar,2000, "dev_source"),
new OracleParameter(":dev_state", OracleType.VarChar,2000, "dev_state"),
new OracleParameter(":dev_onlinetime", OracleType.VarChar,2000, "dev_onlinetime"),
new OracleParameter(":dev_useman", OracleType.VarChar,2000, "dev_useman"),
new OracleParameter(":dev_maintenstart", OracleType.VarChar,2000, "dev_maintenstart"),
new OracleParameter(":dev_maintenend", OracleType.VarChar,2000, "dev_maintenend"),
new OracleParameter(":dev_maintencom", OracleType.VarChar,2000, "dev_maintencom"),
new OracleParameter(":dev_machinenumber", OracleType.VarChar,2000, "dev_machinenumber"),
new OracleParameter(":dev_snnumber", OracleType.VarChar,2000, "dev_snnumber"),
new OracleParameter(":dev_maintenman", OracleType.VarChar,2000, "dev_maintenman"),
new OracleParameter(":dev_maintentel", OracleType.VarChar,2000, "dev_maintentel"),
new OracleParameter(":dev_apply", OracleType.VarChar,2000, "dev_apply"),
new OracleParameter(":dev_plan", OracleType.VarChar,2000, "dev_plan"),
new OracleParameter(":dev_contract", OracleType.VarChar,2000, "dev_contract"),
new OracleParameter(":dev_project", OracleType.VarChar,2000, "dev_project"),
new OracleParameter(":dev_originalvalue", OracleType.VarChar,2000, "dev_originalvalue"),
new OracleParameter(":dev_fixedassetsnum", OracleType.VarChar,2000, "dev_fixedassetsnum"),
new OracleParameter(":dev_assetsvalue", OracleType.VarChar,2000, "dev_assetsvalue"),
new OracleParameter(":dev_summary", OracleType.VarChar,2000, "dev_summary")
};
OracleAdapter.InsertCommand.Parameters.AddRange(insertParms);
OracleAdapter.InsertCommand.Connection = mydbconn;
OracleAdapter.InsertCommand.Transaction = mytran;

try
{
dt.Rows[0].Delete();


int r = OracleAdapter.Update(dt);
dt.AcceptChanges();

mytran.Commit();
tag = true;
return tag;

}
catch (Exception ex)
{

errpt = ex.Message + "ins_Batch.insertSql";
mytran.Rollback();
mydbconn.Close();
tag = false;
return tag;
}

[yaochungeng521]

楼上的能说下具体应该加一些什么控件吗。谢了

[我爱白富美]

楼主楼阁邮箱吧，我把我做的这部分发给你

[test2050]

除非操作excel用到excel.dll。不然不用其它控件。

[我爱白富美]

算了直接说吧，我忘了放哪了，加一个button或一个工具栏toolStripButton用来获取excel的路径，当然要用到openFileDialog来选择文件，选完这之后加一个button或一个工具栏toolStripButton用来将刚刚选择的excel文件导入

[热爱生活的老张]

string strConn = string.Empty;
                 if (filePath.Substring(filePath.LastIndexOf('.')).Equals(".xls"))
                 {
                     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                 }
                 else
                 {
                     strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                 }
                 OleDbConnection OleConn = new OleDbConnection(strConn);
                 OleConn.Open();
                 string sql = "SELECT * FROM [" + tableName + "]";//[Sheet1$]";//可更改Sheet名称，比如sheet2，等等

                 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                 DataSet ds = new DataSet();
                 OleDaExcel.Fill(ds, tableName);
                 OleConn.Close();
                 return ds;

网上给你找的操作excel的代码，得到数据集你应该知道怎么操作了吧，实在不行写个循环一条一条往数据库写。
这个还真不知道有什么控件可以直接用的，.Net提供的控件是不错，但是不是万能的，很多代码还是要自己写的。要不然估计什么架构师，技术总监都没用了，也没有什么资深工程师，中级工程师了，大家伙一起上阵，狂拖控件一切搞定。

[热爱生活的老张]

[Quote=引用 2 楼 的回复:]

mssql里面自带导入导出工具，可以选择数据源，来自excel。要在程序里面做的话，就先把数据读出，再写入sql数据库里面吧
[/Quote]

这个办法最简单，但是不可能每次都让你直接操作数据库的，何况楼主应该是做一个系统，以后交给别人使用，肯定要用程序导入导出了。