C#/ASP.NET将Excel数据批量导入到数据库

C#/ASP.NET中将excel的数据导入数据库中的对应表
此代码未经本人测试，仅供参考。

[C#] 纯文本查看 复制代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.IO;
using System.Data.SqlClient;
 
namespace ImportExcelToSqlServer
{
 public partial class Form1 : Form
 {
 public Form1()
 {
 InitializeComponent();
 }
 /// <summary>
 /// tb1:工作簿名称 tb2：需要导入的数据库表名 tb3：数据库连接字符串
/// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 private void btn_Import_Click(object sender, EventArgs e)
 {
 //string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString.ToString();
 string connString = tb3.Text;
 System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
 if (fd.ShowDialog() == DialogResult.OK)
 {
 TransferData(fd.FileName, tb1.Text, connString);
 }
 }
 public void TransferData(string strCon, string sheetName, string connectionString)
 {
 DataSet ds = new DataSet();
 try
 {
 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strCon + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
 //获取sheet页全部数据 
OleDbConnection conn = new OleDbConnection(connStr);
 conn.Open();
 string strExcel = "";
 OleDbDataAdapter myCommand = null;
 strExcel = string.Format("select * from [{0}$]", sheetName);
 myCommand = new OleDbDataAdapter(strExcel, connStr);
 myCommand.Fill(ds, sheetName); 
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
 {
 DataTable dt = ds.Tables[0];
 bcp.BatchSize = 100;//每次传输的行数 
bcp.NotifyAfter = 100;//进度提示的行数 
bcp.DestinationTableName = tb2.Text;//需要导入的数据库表名 

//excel表头与数据库列对应关系
bcp.ColumnMappings.Add("物料编码", "CODE");
 bcp.ColumnMappings.Add("物料名称", "NAME");
 bcp.ColumnMappings.Add("状态", "STATUS");
 bcp.ColumnMappings.Add("物料说明", "MEMO");
 bcp.ColumnMappings.Add("创建人帐号", "CREATOR");
 bcp.ColumnMappings.Add("创建时间", "CREATEDATE");
 bcp.ColumnMappings.Add("计量单位", "Unit");
 bcp.ColumnMappings.Add("材质", "Material");
 bcp.ColumnMappings.Add("库存最大量", "StockUp");
 bcp.ColumnMappings.Add("库存最小量", "StockDown");
 bcp.ColumnMappings.Add("类别编码", "MATERSTRING1");
 bcp.ColumnMappings.Add("规格", "MATERSTRING2");
 bcp.ColumnMappings.Add("型号", "MATERSTRING3");
 bcp.ColumnMappings.Add("助记码", "MATERSTRING4");
 bcp.ColumnMappings.Add("提报单位编码", "MATERSTRING5");
 bcp.ColumnMappings.Add("类别编码", "MATERSTRING6");
 bcp.WriteToServer(dt);
 MessageBox.Show("成功");
 }
 }
catch (Exception ex)
 {
 MessageBox.Show(ex.ToString());
 }
 }
 }
}