直接贴代码了,写的比较详细,有问题大家一起研究:
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 Excel导入到Sql
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
string SqlStr = "";
//txtDateBaseName用来自己定义数据库的名称
string DBName = "";
//选择excel文件,将文件路径存放并显示在textbox1中
private void button1_Click(object sender, EventArgs e)
{
txtState.Text = "正在等待操作...";
openFileDialog1.ShowDialog();
this.txtFilePath.Text = openFileDialog1.FileName;
}
//建立数据集
private DataSet ExcelToDataSet(string opnFileName)
{
//如果HDR=YES那么第一行将被作为字段名,为了创建表,这里不将其作为字段名
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + opnFileName + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
OleDbConnection conn = new OleDbConnection(strConn);
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = new DataSet();
strExcel = "select * from [sheet1$]";
try
{
conn.Open();
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, "dtSource");
return ds;
}
catch (Exception ex)
{
MessageBox.Show("导入出错:" + ex, "错误信息");
return ds;
}
finally
{
conn.Close();
conn.Dispose();
}
}
//将excel文件导入到sql中
private void button2_Click(object sender, EventArgs e)
{
if (txtTableName.Text == ""||txtDateBaseName.Text=="")
{
MessageBox.Show("请确保数据库名和表明都写入了信息!!");
return;
}
DataSet ds = new DataSet();
try
{
//从所选择的文件存放到数据集中
ds = ExcelToDataSet(this.txtFilePath.Text.Trim());
string insertsql = "";
//计算出行于列的大小以便读取操作
int column = ds.Tables[0].Columns.Count;
int row = ds.Tables[0].Rows.Count;
//自定义数据库名称
DBName = txtDateBaseName.Text;
//判断这个库是否存在
string ifexit = "SELECT COUNT(*) FROM SYSDATABASES WHERE NAME="+"'"+DBName+"'";
string exitornot=GetValue(ifexit);
//如果数据库存在则删除
if (exitornot == "1")
{
string deleteDB = "DROP DATABASE " + DBName;
if (MessageBox.Show("您输入的库已经存在要删除吗?", "删除库", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
DoSql_CreateDB(deleteDB);
txtState.Text = "删除已存在的数据库:" + DBName;
}
else
return;
}
//生产创建数据库命令
string CreateDB = "CREATE DATABASE "+DBName;
//创建一个数据库
DoSql_CreateDB(CreateDB);
//txtTableName用来自己定义表的名称
string TableName = txtTableName.Text;
//这里定义数据的类型,当然也可以自己定义其他类型
string DateType="varchar(50)";
//创建一个表,开始拼字符串
string CreateTable = "CREATE TABLE" +" " +txtTableName.Text + " (";
string table = "";
//拼接字符串用来生成表
for (int i = 0; i < column; i++)
{
if(i!=column-1)
table += "["+ds.Tables[0].Rows[0][i].ToString().Trim() +"]"+ " " + DateType + ",";
else
table += ds.Tables[0].Rows[0][i].ToString().Trim() + " " + DateType;
}
CreateTable += table + ")";
//执行sql语句,生成一个表
DoSql(CreateTable);
//读取数据集中的值将每一条值插入到数据库中的表里面
for (int i = 1; i < row; i++)
{
string sql = "insert into " + TableName + " values('";
string columnsql = "";
for (int j =0; j < column-1; j++)
{
columnsql += ds.Tables[0].Rows[i][j].ToString().Trim() + "','";
}
columnsql += ds.Tables[0].Rows[i][column - 1].ToString().Trim();
//拼接字符串
sql += columnsql + "')";
//插入到数据库中
DoSql(sql);
//insertsql是把所有的sql语句放在一起,我这里没有这么干,是单条插入
insertsql += sql + ",";
// DoSql(insertsql);
}
if (insertsql != "")
{
txtState.Text = "导入DataSet成功,生产了相应的sql语句!";
}
MessageBox.Show("导入到数据库成功!");
// 在textbox2中显示导入数据库成功的字样
txtState.Text = "导入数据库成功!!";
pictureBox1.Visible = true;
}
catch (Exception ex)
{
txtState.Text = "导入数据出错了!!";
MessageBox.Show("非法操作:"+ex);
}
finally
{
}
}
//显示导入到数据集中的信息
private void button3_Click(object sender, EventArgs e)
{
DataSet ds = ExcelToDataSet(this.txtFilePath.Text.Trim());
this.dataGridView1.DataSource = ds.Tables[0];
}
//封装一个函数用来执行sql语句的函数
private void DoSql(string sql)
{
string CnnString = "Data Source=DGUO3;Initial Catalog=" + DBName + ";Persist Security Info=True;User ID=sa;Password=123456@HP.COM";
SqlConnection conn = new SqlConnection(CnnString);
conn.Open();//打开数据库
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();//
conn.Close();//关闭数据库
}
//专门用来创建数据库
private void DoSql_CreateDB(string sql)
{
string CnnString = "Data Source=DGUO3;Initial Catalog=" + "master" + ";Persist Security Info=True;User ID=sa;Password=123456@HP.COM";
SqlConnection conn = new SqlConnection(CnnString);
conn.Open();//打开数据库
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();//
conn.Close();//关闭数据库
}
//从数据库中取得返回值,以判断库或者表是否存在
public string GetValue(string SqlStr)
{
string Sql = SqlStr;
string CnnString = "Data Source=DGUO3;Initial Catalog=" + "master" + ";Persist Security Info=True;User ID=sa;Password=123456@HP.COM";
SqlConnection conn = new SqlConnection(CnnString);
conn.Open();
SqlCommand cmd = new SqlCommand(Sql, conn);
try
{
if (conn.State == ConnectionState.Open)
{
Sql = Convert.ToString(cmd.ExecuteScalar());
}
}
catch (SqlException e)
{
MessageBox.Show(e.Message);
}
conn.Dispose();
conn.Close();
return Sql;
}
private void pictureBox1_Click(object sender, EventArgs e)
{
pictureBox1.Visible = false;
}
}
}