用SqlBulkCopy实现asp.net中的Excel文件上传

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using ExcelDataReader;

public partial class UploadExcel : System.Web.UI.Page
{
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        // 获取上传的Excel文件
        HttpPostedFileBase file = Request.Files["fileUpload"];

        if (file != null && file.ContentLength > 0)
        {
            // 保存Excel文件到服务器的临时位置
            string filePath = Server.MapPath("~/Temp/" + file.FileName);
            file.SaveAs(filePath);

            // 读取Excel文件的数据
            using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
            {
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    // 将Excel数据存储在DataTable中
                    DataTable dataTable = new DataTable();
                    dataTable.Load(reader);

                    // 连接到SQL Server数据库
                    using (SqlConnection connection = new SqlConnection("your_connection_string"))
                    {
                        connection.Open();

                        // 创建SqlBulkCopy对象
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                        {
                            bulkCopy.DestinationTableName = "your_table_name";
                            // 设置列映射关系，如果Excel列名与数据库表列名不一致时需要设置
                            bulkCopy.ColumnMappings.Add("ExcelColumnName", "DatabaseColumnName");

                            // 批量导入数据
                            bulkCopy.WriteToServer(dataTable);
                        }
                    }
                }
            }

            // 删除临时文件
            File.Delete(filePath);

            // 显示上传成功消息
            lblMessage.Text = "Excel文件上传成功！";
        }
        else
        {
            // 显示上传失败消息
            lblMessage.Text = "请选择要上传的Excel文件！";
        }
    }
}