从Excel中读取数据插入到数据库中
<div id="cnblogs_post_body">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;
}
}
}
}
页:
[1]