c#从Excel中读取数据并且插入到SQLSERVER数据库中
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; using System.Data.SqlClient; namespace BaseInfo { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button7_Click(object sender, EventArgs e) { string path = AppDomain.CurrentDomain.BaseDirectory + "Excel.xls";//要读取的Excel路径 我这里是放在了DEbug中 System.Data.DataTable dt = ReadExcel(path); using (SqlConnection conn = new SqlConnection("数据库连接")) { conn.Open(); SqlTransaction sqlTransaction = conn.BeginTransaction(); // 将事务应用于Command SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = conn; sqlCommand.Transaction = sqlTransaction; try { for (int i = 0; i < dt.Rows.Count - 1; i++) { sqlCommand.CommandText = sql语句;//mssql数据库 sqlCommand.ExecuteNonQuery(); } // 成功提交 sqlTransaction.Commit(); MessageBox.Show("ok"); } catch (Exception ex) { // 出错回滚 sqlTransaction.Rollback(); } } } public static DataTable ReadExcel(string filePath) { try { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; OleDbConnection OleConn = new OleDbConnection(strConn); OleConn.Open(); String sql = "SELECT * FROM 工作簿名称"; OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); DataSet OleDsExcle = new DataSet(); OleDaExcel.Fill(OleDsExcle, "表名"); OleConn.Close(); return OleDsExcle.Tables["表名"]; } catch (Exception err) { MessageBox.Show("数据绑定Excel失败!失败原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return null; } } } }