将excel文件上传至sql2005数据库

【实例简介】

用于将.xls格式的excel文件上传至sql2005数据库

【实例截图】

【核心代码】

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Data.OleDb;

namespace ExcelToSQL2005
{
/// <summary>
/// Form1 的摘要说明。
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.OpenFileDialog openFileDialog1;
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button3;
private DataTable dtMessage = null;

/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.Container components = null;

public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();

//
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
dtMessage = new DataTable();
dtMessage.Columns.Add("表名",typeof(string));
dtMessage.Columns.Add("是否创建表",typeof(string));
dtMessage.Columns.Add("是否导入数据",typeof(string));
dtMessage.Columns.Add("错误信息",typeof(string));
}

/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null) 
{
components.Dispose();
}
}
base.Dispose( disposing );
}

#region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
this.textBox1 = new System.Windows.Forms.TextBox();
this.button1 = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.button3 = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
// 
// textBox1
// 
this.textBox1.Location = new System.Drawing.Point(16, 24);
this.textBox1.Name = "textBox1";
this.textBox1.ReadOnly = true;
this.textBox1.Size = new System.Drawing.Size(400, 21);
this.textBox1.TabIndex = 0;
this.textBox1.Text = "";
// 
// button1
// 
this.button1.Location = new System.Drawing.Point(437, 24);
this.button1.Name = "button1";
this.button1.TabIndex = 1;
this.button1.Text = "选择文件";
this.button1.Click = new System.EventHandler(this.button1_Click);
// 
// dataGrid1
// 
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(16, 64);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.ReadOnly = true;
this.dataGrid1.Size = new System.Drawing.Size(496, 352);
this.dataGrid1.TabIndex = 2;
// 
// button3
// 
this.button3.Location = new System.Drawing.Point(16, 432);
this.button3.Name = "button3";
this.button3.Size = new System.Drawing.Size(112, 23);
this.button3.TabIndex = 4;
this.button3.Text = "导入SQL2005";
this.button3.Click = new System.EventHandler(this.button3_Click);
// 
// Form1
// 
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(528, 470);
this.Controls.Add(this.button3);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.button1);
this.Controls.Add(this.textBox1);
this.Name = "Form1";
this.Text = "ExcelToSQL2005";
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);

}
#endregion

/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main() 
{
Application.Run(new Form1());
}

private void button1_Click(object sender, System.EventArgs e)
{
openFileDialog1.Filter = "Excel文件(*.xls)|*.xls";
if(openFileDialog1.ShowDialog() == DialogResult.OK)
{
this.textBox1.Text = openFileDialog1.FileName;
}
}

public DataSet GetDataSetFromExcel(string strFileName)
{
OleDbConnection objConn = null;
DataSet ds = new DataSet();
DataTable schemaTable = new DataTable();
try
{
                string strConn = "Provider=Microsoft.ACE.OleDb.12.0;" "Data Source=" strFileName ";" "Extended Properties='Excel 8.0;IMEX=1;'";
objConn = new OleDbConnection(strConn);
objConn.Open();
schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
                OleDbDataAdapter sqlAda = new OleDbDataAdapter();
                foreach (DataRow dr in schemaTable.Rows)
                {
                    if (dr[2].ToString().Trim().IndexOf("FilterDatabase") == -1)
                    {
                        string strSql = "Select * From [" dr[2].ToString().Trim() "]";
                        OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
                        sqlAda.SelectCommand = objCmd;
                        sqlAda.Fill(ds, dr[2].ToString().Trim());
                        DataRow drMessage = dtMessage.NewRow();
                        drMessage["表名"] = dr[2].ToString().Trim();
                        dtMessage.Rows.Add(drMessage);
                    }
                }
                dtMessage.AcceptChanges();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
objConn.Close();
}

return ds;
}

public bool DataTableToSQL2005(string tableName,DataTable dt)
{
bool b = false;
int flag = 0;
try
{
PDSOFT.Data.DataCommand dc = new PDSOFT.Data.DataCommand();
//创建表
string createTable = "CREATE TABLE [dbo].[" tableName "](";
foreach(DataColumn col in dt.Columns)
{
createTable = "[" col.ColumnName "] [nvarchar](255) NULL,";
}
createTable = createTable.Remove(createTable.Length - 1,1) ")";
dc.ExecSql_Search_DataTable(createTable);
this.SetMessage("是否创建表",tableName,"是","");
flag = 1;
//导入数据
string insertStr = "begin tran t1 ";
string insertData = "";
for(int i = 0; i < dt.Rows.Count; i )
{
insertData = "insert " tableName " values(";
for(int j = 0; j < dt.Columns.Count; j )
{
insertData = "'" dt.Rows[i][j].ToString().Replace("'","") "',";
}
insertData = insertData.Remove(insertData.Length - 1,1);
insertData = ")";
insertStr = insertData " ";
}
insertStr = "commit tran t1";

dc.ExecSql_Search_DataTable(insertStr);
this.SetMessage("是否导入数据",tableName,"是","");
b = true;
}
catch(Exception ex)
{
if(flag == 0)
{
this.SetMessage("是否创建表",tableName,"否",ex.Message);
}
else
{
this.SetMessage("是否导入数据",tableName,"否",ex.Message);
}
b = false;
}
return b;
}

private void SetMessage(string columnName,string tableName,string message,string errorMessage)
{
foreach(DataRow dr in dtMessage.Rows)
{
if(dr["表名"].ToString() == tableName)
{
dr[columnName] = message;
dr["错误信息"] = errorMessage;
dtMessage.AcceptChanges();
break;
}
}
}

private void button3_Click(object sender, System.EventArgs e)
{
if(textBox1.Text.Trim() == "")
{
MessageBox.Show("请选择一个Excel文件");
return;
}
this.Cursor = Cursors.WaitCursor;
            dtMessage.Clear();
            DataSet ds = GetDataSetFromExcel(textBox1.Text.Trim());
            foreach (DataTable dt in ds.Tables)
            {
                DataTableToSQL2005(dt.TableName, dt);
            }
            dataGrid1.DataSource = dtMessage.DefaultView;
            this.Cursor = Cursors.Default;
}
}
}