c#將excel中數據批量導入數據庫對應表

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());
}
}
}
}