Question

ASP.NET（C#），怎么实现将excel数据导入SQL2005数据库中？

请各位大侠帮帮忙：我做的是一个学生信息管理系统，需要将学生信息（学号、姓名、出生年月）批量添加到People数据表（Login、Author、Pass）中，跪求源代码及其提示

Answer 1

简单！库上右键  ==> 任务==>导入数据  出来向导    在数据源里选择EXCEL   下面可以选择EXECEL版本 EXCEL的路径

哦哦哦 是用代码啊 ！   用 NPOI 吧  此组件为数据库表和外部EXCEL的中间物！ 

此例为将EXCEL转为为DataTable 

你可以在写入TABLE的时候把数据写入数据库

Answer 2

有些过一个现成的东西，就是你说的这个需求，全给你贴出来，你自己看吧，哪里有疑问，在问我
public void BindData(DataSet Ds) {
try
{
tempTable = CreatTable(tempTable);
currPage = AspNetPager1.CurrentPageIndex;
this.AspNetPager1.RecordCount = Ds.Tables[0].Rows.Count;
AspNetPager1.CustomInfoHTML = "<span class='font01a'>TOTAL：<b>" + AspNetPager1.RecordCount.ToString() + "</b> RECODES，DESPLAY: ";
AspNetPager1.CustomInfoHTML += "<b>" + ((currPage - 1) * AspNetPager1.PageSize + 1) + "" + "-";
if (AspNetPager1.RecordCount < currPage * AspNetPager1.PageSize)
{
AspNetPager1.CustomInfoHTML += (AspNetPager1.RecordCount).ToString() + "</b></span>";
}
else
{
AspNetPager1.CustomInfoHTML += (currPage * AspNetPager1.PageSize).ToString() + "</b></span>";
}

if (Ds != null && Ds.Tables[0].Rows.Count > 0) {
models.Visible = true;

for (int i = (currPage - 1) * AspNetPager1.PageSize; i < currPage * AspNetPager1.PageSize; i++) {
if (i <= Ds.Tables[0].Rows.Count - 1)
{
DataRow dr = tempTable.NewRow();
dr["BOLNo"] = Ds.Tables[0].Rows[i][0].ToString().ToUpper();
dr["RMANo"] = Ds.Tables[0].Rows[i][1].ToString().ToUpper();
dr["RMAIssualDate"] = Ds.Tables[0].Rows[i][2].ToString().ToUpper();
dr["CustomerName"] = Ds.Tables[0].Rows[i][3].ToString().ToUpper();
dr["Models"] = Ds.Tables[0].Rows[i][5].ToString().ToUpper();
dr["QTYRequest"] = Ds.Tables[0].Rows[i][6].ToString().ToUpper();
tempTable.Rows.Add(dr);
}
}
}
else
{
DataRow dr = tempTable.NewRow();
dr["BOLNo"] = 0;
tempTable.Rows.Add(dr);
}
GridData.DataSource = tempTable;
GridData.DataBind();
}
catch (Exception ex)
{
lbl_Msg.Text = "Please Check Excel Content,Error Message: " + ex.Message;
}
}
/// <summary>
/// 读取Excel表格
/// </summary>
/// <param name="Path"></param>
/// <returns></returns>
public DataSet ReadExcel(string Path, string ExtName)
{
try
{
string strConn = "";
DataSet ds = new DataSet();
if (ExtName.ToUpper() == "XLS")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
}
else if (ExtName.ToUpper() == "XLSX")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
}
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
DataTable schemaTable = OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
string sql = "SELECT * FROM [" + tableName + "]";//可更改Sheet名称，比如sheet2，等等
OleDbDataAdapter myData = new OleDbDataAdapter(sql, OleConn);
myData.Fill(ds);//填充数据
OleConn.Close();
return ds;
}
catch (Exception ex)
{
lbl_Msg.Text = "Excel File Error, Error Information: " + ex.Message;
return null;
}
}
protected void btn_Insert_Click(object sender, EventArgs e)
{
try
{
if (ViewState["Ds"] != null)
{
DataSet Ds = (DataSet)ViewState["Ds"];
if (Ds != null && Ds.Tables[0].Rows.Count > 0)
{
List<T_VenderReturn> listvender = new List<T_VenderReturn>();
List<T_ReturnModel> listmode = new List<T_ReturnModel>();
for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
{
if (Ds.Tables[0].Rows[i][1].ToString() != string.Empty)
{
T_VenderReturn vender = new T_VenderReturn();
vender.F_BOLNo = Ds.Tables[0].Rows[i][0].ToString().ToUpper();
vender.F_RMANo = Ds.Tables[0].Rows[i][1].ToString().ToUpper();
vender.F_RMAIssuedDate = Convert.ToDateTime(FormatDate.Instance.CheckDate(Ds.Tables[0].Rows[i][2].ToString()));
vender.F_CustomerName = Ds.Tables[0].Rows[i][3].ToString().ToUpper();
vender.F_ReturnType = "Company Return";
vender.F_CreatDate =Convert.ToDateTime( DateTime.Now.ToShortDateString());
listvender.Add(vender);
}
}
if (VenderReturnService.Instance.AddVender(listvender))
{
for (int j = 0; j < Ds.Tables[0].Rows.Count; j++)
{
T_ReturnModel mode = new T_ReturnModel();
mode.F_VenderID = VenderReturnService.Instance.GetVenderIDByRMANo(Ds.Tables[0].Rows[j][4].ToString());
mode.F_ModelNo = Ds.Tables[0].Rows[j][5].ToString();
mode.F_QtyRequest = int.Parse(Ds.Tables[0].Rows[j][6].ToString());
mode.F_ReceiveDate = Convert.ToDateTime( DateTime.Now.ToShortDateString());
listmode.Add(mode);
}
VenderReturnService.Instance.AddVenderModel(listmode);
Response.Redirect("Returnlist.aspx");
}

} }
}
catch (Exception ex) {
LogCore.LogControl.WriteError("Add To DataBase:", ex.Message);
lbl_Msg.Text = ex.Message;
}
}

Answer 3

提示一下：1.使用OLEDB方式读取EXCEL文件，并验证文件信息；
2.将1中获得的数据，插入sql数据库中；

Answer 4

我这里有个通用的把excel数据导入数据库的方法不知道合不合适

追问

能发到我的邮箱（tlj40912107@163.com）里面吗？谢谢你！

Answer 5

建议你使用PageOffice，很简单的就能帮你解决这个问题啊，网上搜有很多他的示例代码，你搜搜看，参考一下啊。