如何在c# ASP.Net中从Excel上传表数据到SQL Server 2008 ?

protected void btnup_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        if (System.IO.Path.GetExtension(FileUpload1.FileName) == ".xls" || System.IO.Path.GetExtension(FileUpload1.FileName) == ".xlsx")
        {
            FileUpload1.SaveAs(Server.MapPath("~/Excal/sample.xlsx"));
        }
    }
}
protected void Button2_Click(object sender, EventArgs e)
{ 
   string sSourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\sample.xlsx; Extended Properties=""Excel 12.0;HDR=YES;""";
    string sDestConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;

    OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
    using (sSourceConnection)
    {
        string sql = string.Format("Select [ID],[Name],[Designation] FROM [{0}]", "sample$");
        OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
        sSourceConnection.Open();
        using (OleDbDataReader dr = command.ExecuteReader())
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
            {
                bulkCopy.DestinationTableName = "rr";
                //You can mannualy set the column mapping by the following way.
                //bulkCopy.ColumnMappings.Add("MSISDN", "MSISDN");
                bulkCopy.WriteToServer(dr);
            }
        }
    }
    lblmsg.Text = "Record update";
}

1 个解决方案

#1

I have done that before few day. Below code works for me ..

我没几天就做完了。下面的代码对我有效。

   try
        {

           string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                               "Data Source=" + Path of File + ";" +
                              @"Extended Properties=""Excel 12.0 Xml;HDR=Yes""";

            //Create Connection to Excel work book
            OleDbConnection excelConnection =
            new OleDbConnection(excelConnectionString);

            //Create OleDbCommand to fetch data from Excel
            OleDbCommand cmd = new OleDbCommand
            ("Select * from [Sheet1$]",
            excelConnection);

            excelConnection.Open();

            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();

            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            sqlBulk.DestinationTableName = "ExcelTable";  // write your table name

            //sqlBulk.ColumnMappings.Add("ID", "ID");
            //sqlBulk.ColumnMappings.Add("Name", "Name");
            sqlBulk.WriteToServer(dReader);


        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }