aspx:
批量导入: <asp:FileUpload ID="FileUpload1" runat="server" Height="25px" /> <asp:HyperLink ID="HyperLink1" NavigateUrl="~/Question/question_joke.xls" runat="server" Text="下载模板"></asp:HyperLink> <asp:Button runat="server" ID="Button1" OnClick="Button1_Click" Text="查看" Height="25px" Width="54px" /> <asp:Button runat="server" ID="Button2" OnClick="Button2_Click" Text="导入" Height="25px" Width="54px" Enabled="false" /> <asp:GridView ID="gridview1" runat="server" Visible="true" BorderWidth="1px" BorderColor="LightGray" AlternatingRowStyle-BackColor="AliceBlue"> </asp:GridView> <asp:Label ID="lblmes" runat="server" Visible="true" /><%--用于显示数据记录--%>
cs:
/*********************************添加命名空间**************************************/
using System.Data;
using System.Data.OracleClient;
using System.Data.OleDb;
/**********************************************************************************/
public partial class SpreadsheetUpload : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string path = Server.MapPath("");
if (!FileUpload1.HasFile|| FileUpload1.FileName.Substring( FileUpload1.FileName.LastIndexOf('.')+1)!= "xls")
//判断FileUpload是否上传文件，文件名是否是.xls[excel2003]
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(),"","<script>alert('数据为空或导入的不是EXCEL文件.');</script>");
return;
}
else
{
FileUpload1.SaveAs(path + "\\" + FileUpload1.FileName);
//设置上传的文件保存在服务器上的路径[必须存在]
DataSet ds = new DataSet();
string ConnStr = "Provider=Microsoft.Jet.OleDb.4.0;data source=" + path + "\\" + FileUpload1.FileName + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
//连接Excel的字符串
string query = "SELECT * FROM [题库格式$] WHERE ITEM IS NOT NULL"; //Excel中的表名称
OleDbCommand oleCommand = new OleDbCommand(query, new OleDbConnection(ConnStr));
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
oleAdapter.Fill(ds, "table1");
int rowcount = ds.Tables["table1"].Rows.Count; //获取记录数
gridview1.DataSource = ds;
gridview1.DataBind();
lblmes.Text = rowcount.ToString();
}
if (lblmes.Text != "")
{
Button2.Enabled = true;
}
//若不点查看按钮，直接点击导入按钮将出错，因为此时gridview中尚无数据，所以在此做一判断。
}
protected void Button2_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();
}
}
原理：
1.将EXCEL文件通过FileUpdate上传保存到服务器
2.读取该文件到gridview中
3.插入数据库