在 ASP.NET程序实现将excel导入到数据库 EXCEL的格式有固定的三列数据.也有固定的路经,怎么实现,有例子吗 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 //加载Excel private DataSet BindDsFromExcel(string strFileDir, string strDataName) { try { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileDir + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; OleDbConnection OleConn = new OleDbConnection(strConn); OleConn.Open(); String sql = "SELECT * FROM [" + strDataName + "$]"; OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); DataSet OleDsExcle = new DataSet(); OleDaExcel.Fill(OleDsExcle, strDataName); OleConn.Close(); return OleDsExcle; } catch (Exception err) { MessageBox.Show("数据绑定Excel失败!失败原因:" + err.Message, "NaviDMS 2.2", MessageBoxButtons.OK, MessageBoxIcon.Information); return null; } }直接调用就可以。 如果是excel2007,用Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0;HDR=YES";"HDR=Yes;" 表示工作表的第一行是表头,没有数据。 "HDR=No;"与之相反。如果是excel2003,用string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";用oledb连接以后,就可以用select * from [Sheet1$]操作具体的表了,Sheet1$是你excel中的具体的工作薄; 上传excel,然后将excel中指定的工作薄显示到GridView1protected void UploadButton_Click(object sender, EventArgs e) { String savePath = Server.MapPath("../Uploads/Update/"); String fileName = String.Empty; if (FileUpload1.HasFile) { fileName = FileUpload1.FileName; savePath += fileName; FileUpload1.SaveAs(savePath); UploadStatusLabel.Text = "你保存的文件: " + fileName; } OpterData("select * from [Sheet1$]", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties='Excel 12.0;HDR=YES'"); } /// <summary> /// 操作EXCEL 到数据库 /// </summary> private void OpterData(string strSql, string connStr) { OleDbConnection con1 = null; OleDbCommand dbcmd = null; OleDbDataReader dbr = null; try { con1 = new OleDbConnection(connStr); con1.Open(); dbcmd = new OleDbCommand(strSql, con1); dbr = dbcmd.ExecuteReader(CommandBehavior.CloseConnection); //第一行做表头时才可以用字段名取值 if (dbr.Read()) { Response.Write(dbr["测试姓名"]); } this.GridView1.DataSource = dbr; this.GridView1.DataBind(); } catch (Exception Ex) { Response.Write(Ex.Message); } finally { con1.Dispose(); } } private void BTExcel_Click(object sender, System.EventArgs e) { string ExcelPath = this.ExcelPath.PostedFile.FileName.ToString(); string Personnel = this.hilbPersonnel.Text.ToString().Trim(); if (ExcelPath.ToString() == string.Empty) { Response.Write("<script language=javascript>window.alert('对不起,请选择您要导入的 Excel 文件!');window.history.go(-1);</script>"); Response.End(); } try { string ConnString = " Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '"+ ExcelPath.ToString() +"'" + ";Extended Properties = Excel 8.0; "; OleDbConnection Excelconn = new OleDbConnection(ConnString); Excelconn.Open(); OleDbDataAdapter da = new OleDbDataAdapter(" select * from [Sheet1$] ",Excelconn); DataTable dt = new DataTable(); da.Fill(dt); Excelconn.Close(); Conn conn = new Conn(); for (int i = 0;i < dt.Rows.Count;i++) { conn.ExeNonQuery(" Insert Into T_MIBMaterielBadnessHistory(MIB_BadnessID,MIB_MaterielCode,MIB_Type,MIB_BadnessCausation,MIB_DisposalPersonnel,MIB_DisposalMeasure,MIB_DisposalTime) Select isnull(Max(MIB_BadnessID),0) + 1,'"+dt.Rows[i][0].ToString().Trim()+"','"+dt.Rows[i][1].ToString().Trim()+"','"+dt.Rows[i][2].ToString().Trim()+"','"+Personnel.ToString().Trim()+"','"+dt.Rows[i][3].ToString()+"','"+DateTime.Now.ToString()+"' From T_MIBMaterielBadnessHistory "); //// 插入语句 } conn.Close(); Response.Write(string.Format("<script language=javascript>window.alert('恭喜您,数据导入成功!');window.location.href='List.aspx';</script>")); //Response.End(); } catch { Response.Write(string.Format("<script language=javascript>window.alert('对不起,您在导入 Excel 文件中数据时出现了问题:请保证您选取正确的 Excel 文件以及选取的文件处于关闭状态!');window.history.go(-1);</script>")); Response.End(); } }这个是选择 Excel 文件后,导入数据库 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Innovation_Test\\source\\Equipment Suppliers List Short.xls;Extended Properties=Excel 8.0;HDR=Yes;";我的路经这样写可以吗 2楼正解,如果是三列的话,直接把excel数据通过oledb导就可以了,若是多列的话,先把数据存储到dataset里面,再读取到sql server里面 高分求教C#调用excel函数 正则替换 两种方式创建数据库出错,请高手解答 如何在打印textBox.Text的内容时,让字符串中没有“\n”换行符的地方也能根据textBox控件的宽度自动换行? aspnet_wp.exe进程的内存使用的问题。 后台数据存储 哭求助高手-如何将一个多列数据的XML文件用循环的方法用DataGrid显示出来和如何将数据导入SQL数据库中 给分题,大牛们给俺诊断下呗 请问各位一个小问题?为什么不能进行类型转换? 如何在WinForm 中的DataGrid中使得字符串为超链接? 运算符重载时参数为泛型时报错 存储过程不能更新的问题
private DataSet BindDsFromExcel(string strFileDir, string strDataName)
{
try
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileDir + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
String sql = "SELECT * FROM [" + strDataName + "$]"; OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, strDataName);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败!失败原因:" + err.Message, "NaviDMS 2.2",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}
直接调用就可以。
"HDR=Yes;" 表示工作表的第一行是表头,没有数据。 "HDR=No;"与之相反。
如果是excel2003,用string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
用oledb连接以后,就可以用select * from [Sheet1$]操作具体的表了,Sheet1$是你excel中的具体的工作薄;
protected void UploadButton_Click(object sender, EventArgs e)
{
String savePath = Server.MapPath("../Uploads/Update/");
String fileName = String.Empty;
if (FileUpload1.HasFile)
{
fileName = FileUpload1.FileName;
savePath += fileName;
FileUpload1.SaveAs(savePath);
UploadStatusLabel.Text = "你保存的文件: " + fileName;
}
OpterData("select * from [Sheet1$]", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties='Excel 12.0;HDR=YES'");
} /// <summary>
/// 操作EXCEL 到数据库
/// </summary>
private void OpterData(string strSql, string connStr)
{
OleDbConnection con1 = null;
OleDbCommand dbcmd = null;
OleDbDataReader dbr = null;
try
{
con1 = new OleDbConnection(connStr);
con1.Open();
dbcmd = new OleDbCommand(strSql, con1);
dbr = dbcmd.ExecuteReader(CommandBehavior.CloseConnection);
//第一行做表头时才可以用字段名取值
if (dbr.Read())
{
Response.Write(dbr["测试姓名"]);
} this.GridView1.DataSource = dbr;
this.GridView1.DataBind();
}
catch (Exception Ex)
{
Response.Write(Ex.Message);
}
finally
{
con1.Dispose();
}
}
{
string ExcelPath = this.ExcelPath.PostedFile.FileName.ToString();
string Personnel = this.hilbPersonnel.Text.ToString().Trim();
if (ExcelPath.ToString() == string.Empty)
{
Response.Write("<script language=javascript>window.alert('对不起,请选择您要导入的 Excel 文件!');window.history.go(-1);</script>");
Response.End();
} try
{
string ConnString = " Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '"+ ExcelPath.ToString() +"'" + ";Extended Properties = Excel 8.0; ";
OleDbConnection Excelconn = new OleDbConnection(ConnString);
Excelconn.Open(); OleDbDataAdapter da = new OleDbDataAdapter(" select * from [Sheet1$] ",Excelconn);
DataTable dt = new DataTable();
da.Fill(dt);
Excelconn.Close();
Conn conn = new Conn();
for (int i = 0;i < dt.Rows.Count;i++)
{ conn.ExeNonQuery(" Insert Into T_MIBMaterielBadnessHistory(MIB_BadnessID,MIB_MaterielCode,MIB_Type,MIB_BadnessCausation,MIB_DisposalPersonnel,MIB_DisposalMeasure,MIB_DisposalTime) Select isnull(Max(MIB_BadnessID),0) + 1,'"+dt.Rows[i][0].ToString().Trim()+"','"+dt.Rows[i][1].ToString().Trim()+"','"+dt.Rows[i][2].ToString().Trim()+"','"+Personnel.ToString().Trim()+"','"+dt.Rows[i][3].ToString()+"','"+DateTime.Now.ToString()+"' From T_MIBMaterielBadnessHistory "); //// 插入语句
} conn.Close();
Response.Write(string.Format("<script language=javascript>window.alert('恭喜您,数据导入成功!');window.location.href='List.aspx';</script>"));
//Response.End();
}
catch
{
Response.Write(string.Format("<script language=javascript>window.alert('对不起,您在导入 Excel 文件中数据时出现了问题:请保证您选取正确的 Excel 文件以及选取的文件处于关闭状态!');window.history.go(-1);</script>"));
Response.End();
}
}
这个是选择 Excel 文件后,导入数据库
我的路经这样写可以吗