default.aspx.cs

将excel的数据上传到服务器的数据库

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    public static string pt = "";
    protected void Button1_Click(object sender, EventArgs e)
    {
        sj();
    }

    protected void  sj()
    {
        if (pt != "")
        {
            OleDbConnection myConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pt + ";Extended Properties=\"Excel 12.0;HDR=YES\"");
            // OleDbConnection myConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pt + "';Extended Properties=Excel 8.0;");
            Response.Write(pt);
            myConn.Open();
            OleDbDataAdapter thisAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", myConn);
            OleDbCommandBuilder thisBuilder = new OleDbCommandBuilder(thisAdapter);
            DataSet thisDataSet = new DataSet();
            // thisAdapter.Fill(thisDataSet, "ExcelInfo");
            thisAdapter.Fill(thisDataSet);
            string a = thisDataSet.Tables[0].Rows.Count.ToString();
            string b = " " + thisDataSet.Tables[0].Columns.Count.ToString();
            Response.Write(a + b);
            this.GridView1.DataSource = thisDataSet.Tables[0];
            GridView1.DataBind();
            ArrayList al = new ArrayList();
            for (int i = 0; i < thisDataSet.Tables[0].Rows.Count; i++)
            {
                al.Clear();
                for (int j = 0; j < thisDataSet.Tables[0].Columns.Count; j++)
                {
                    al.Add(thisDataSet.Tables[0].Rows[i][j].ToString());
                }
                Class1.ExecuteNonQuery("Insert into wm(a,b,c) values('" + al[1] + "','" + al[2] + "','" + al[3] + "')");
               
            }
            
            myConn.Close();
            Page.ClientScript.RegisterStartupScript(typeof(Page), "alert", "<script>alert('数据上传成功!');</script>");
            File.Delete(pt);
        }
        else
            Page.ClientScript.RegisterStartupScript(typeof(Page), "alert", "<script>alert('请上传表格数据!');</script>");
    }
    protected void Button2_Click(object sender, EventArgs e)
    {

        if (FileUpload1.HasFile)
        {
            Boolean ok = true;

            if (ok)
            {
                if (FileUpload1.PostedFile.ContentLength < 8200000)
                {
                   // string a = System.DateTime.Today.ToShortDateString();+ a
                    string path = Server.MapPath("files\\" );
                    pt = path + FileUpload1.FileName;
                    if (!Directory.Exists(path))
                    {
                        DirectoryInfo di = Directory.CreateDirectory(path);
                    }
                    String fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                    String[] allowedExtensions = { ".xls",".xlsx" };
                    Boolean kk = false;
                    for (int i = 0; i < allowedExtensions.Length; i++)
                    {
                        if (fileExtension == allowedExtensions[i])
                        {
                            kk = true;
                            break;
                        }

                    }
                        if(kk)
                        {
                            FileUpload1.PostedFile.SaveAs(path + "\\" + FileUpload1.FileName);
                            Page.ClientScript.RegisterStartupScript(typeof(Page), "alert", "<script>alert('添加附加成功！')</script>");
                            sj();

                        }
                        else
                            Page.ClientScript.RegisterStartupScript(typeof(Page), "alert", "<script>alert('上传文件类型不正确，应为office表格格式！')</script>");

                }

                else
                {
                    Page.ClientScript.RegisterStartupScript(typeof(Page), "alert", "<script>alert('文件大于8M，未能上传！')</script>");
                }
            }
        }
       
        else
        {
            Page.ClientScript.RegisterStartupScript(typeof(Page), "alert", "<script>alert('上传空气？呵呵')</script>");
        }
    }
}