使用FILEUPLOAD控件将EXCEL文导入并保存至数据库

Finder_Way

于 2011-09-17 14:51:15 发布

阅读量2.3k

点赞数

分类专栏： ASP.NET C# 文章标签： excel button hyperlink string dataset server

C# 同时被 2 个专栏收录

212 篇文章

订阅专栏

ASP.NET

115 篇文章

订阅专栏

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.插入数据库