实现效果：

ExcelToTable.cs

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
namespace PMS
{
    public class ExcelToTable
    {
        public int BulkInsert(DataTable table)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["MySqlConnStr"].ConnectionString;
            if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");
            if (table.Rows.Count == 0) return 0;
            int insertCount = 0;
            string tmpPath = Path.GetTempFileName();
            string csv = DataTableToCsv(table);
            File.WriteAllText(tmpPath, csv);
            MySqlConnection conn = new MySqlConnection(connectionString);
            try
            {
                conn.Open();
                MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                {
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    EscapeCharacter = '"',
                    LineTerminator = "\r\n",
                    FileName = tmpPath,
                    NumberOfLinesToSkip = 0,
                    TableName = table.TableName,
                };
                insertCount = bulk.Load();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            conn.Close();
            File.Delete(tmpPath);
            return insertCount;
        }
        private static string DataTableToCsv(DataTable table)
        {
            DataColumn colum;
            StringBuilder sb = new StringBuilder();
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    colum = table.Columns[i];
                    if (i != 0) sb.Append(",");
                    if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
                    {
                        sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
                    }
                    else sb.Append(row[colum].ToString());
                }
                sb.AppendLine();
            }
            return sb.ToString();
        }
    }
}

WebForm1.aspx.cs

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace PMS
{
    public partial class test : System.Web.UI.Page
    {
        DataTable _dt = null;
        public DataTable ExcelToDS()
        {
            DataTable dt = new DataTable();
            string strConn = "";
            string strFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName);
            string strFileType = Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
            if (strFileType == ".xls" || strFileType == ".xlsx")
            {
                FileUpload1.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));
            }
            string path = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);
            if (strFileType.Trim() == ".xls")
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();
                string strExcel = "select [name],[age] from [sheet1$]"; //查询的excel字段
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                
                dt.TableName = "data"; //表名
                dt.Columns.Add("id"); //id自增列
                dt.Columns.Add("name");
                dt.Columns.Add("age");
                DataColumn dc = new DataColumn("test", typeof(int));
                dc.DefaultValue = 0; //默认值列
                dt.Columns.Add(dc);
                myCommand.Fill(dt);
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            conn.Close();
            return dt;
        }
        private void ExcelToMysql()
        {
            DataTable  dt = new DataTable();
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();
            dt = ExcelToDS();
            
            _dt = dt;
            int a = _dt.Rows.Count;
            int columnNum = 0;
            columnNum = _dt.Columns.Count;
            if (columnNum > 0)
            {
                this.Label1.Text = "总：" + a + "条 " + columnNum + "列";
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();
            watch.Stop();
            TimeSpan ts2 = watch.Elapsed;
            this.Label2.Text = "  用时:" + ts2.TotalMilliseconds + "毫秒";
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            ExcelToMysql();
            ExcelToTable tab = new ExcelToTable();
            int a = 0;
            a = tab.BulkInsert(_dt);
            if (a > 0)
            {
                this.Label2.Text = "本次导入：" + a;
                Response.Write("导入成功！" + "本次导入：" + a);
            }
        }
    }
}

注意：必须创建上传excel的文件夹UploadedExcel，代码后面修改过，效果图懒得重新修改了

参考文档：https://blog.csdn.net/goodchangyong/article/details/79708845

原文链接：https://www.idaobin.com/archives/1297.html

让我恰个饭吧.ヘ(￣ω￣ヘ)

支付宝 ——————- 微信