using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace WindowsFormsApplication5
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
DataTable dt = new DataTable();
string connString = "Data Source=.;Initial Catalog=NIU123;Integrated Security=True";
SqlConnection conn;
//选择打开EXCEL表格
private void button1_Click(object sender, EventArgs e)
{
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)//调用这个方法打开选择对话框,并判断是否打开成功。
{
string fileName = fd.FileName;//把选种文件路径赋值给fileName.
bind(fileName);//调用这个方法
}
}
private void bind(string fileName)
{
//连接字符串
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
//SQL语句
string strComm = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter(strComm, strConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds,"SD");
dt = ds.Tables["SD"];
this.dataGridView1.DataSource = dt;
}
catch (Exception err)
{
MessageBox.Show("操作失败!" + err.ToString());
}
}
//写入到数据库
private void button2_Click(object sender, EventArgs e)
{
conn = new SqlConnection(connString);
conn.Open();
if (dataGridView1.Rows.Count > 0)
{
DataRow dr = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
insertToSql(dr);
}
conn.Close();
MessageBox.Show("导入成功!");
}
else
{
MessageBox.Show("没有数据!");
}
}
private void insertToSql(DataRow dr)
{
//excel表中的列名和数据库中的列名一定要对应
string name = dr["UserName"].ToString();
string password = dr["UserPassword"].ToString();
string sql = "insert into Table_20200625 values('" + name + "','" + password + "')";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
}
}
打开EXCEL表格,批量写入到数据库
最新推荐文章于 2024-04-25 22:39:04 发布