C#winform多个03Excel导入SQL数据库部分代码

注意：事务根本没有用，因为个人是初学阶段，所以只是把Excel中的一条做了一个事务判断。

前台只要一个butten即可。

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.IO;
using System.Web;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

sing System.Text.RegularExpressions;

namespace HandPhone10

{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        DataTable dt = new DataTable();
        //SqlConnection conn;
        //SqlTransaction myTran;
        //string sconn = "Data Source=DDA535F2780A469;Initial Catalog=HandPhone;integrated security=SSPI";
        private void btnBrowse_Click(object sender, EventArgs e)
        {
            string strPath;
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                txtPath.Text=openFileDialog1.FileName;
                strPath=txtPath.Text;
            }
            dataGridView1.DataSource =InputExcel(txtPath.Text);
            //string strFileName=txtPath.Text.Substring( txtPath.Text.IndexOf(".")
            System.Diagnostics.Process.Start(txtPath.Text);
        }

        private void btnSqlImport_Click(object sender, EventArgs e)
        {
            string path = "G:\\Data";
            string[] files = Directory.GetFiles(path);
            DataSet ds = new DataSet();  
            foreach (string file in files)
            {
              
                if (InputExcel(file).Rows.Count > 0)
                {
                    DataRow dr = null;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dr = dt.Rows[i];
                        InsertSql(dr);
                    }
                }
            }
            MessageBox.Show("导入数据库成功！");
            conn.Close();
          }
        public DataTable InputExcel(string path)
        {
            //打开
         
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended properties='Excel 8.0;HDR=Yes;IMEX=1'";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//获取工作表名
                dt.TableName = dt.Rows[0]["TABLE_NAME"].ToString();
                string strTable = "select * from [" + dt.TableName + "]";
                OleDbDataAdapter Da = new OleDbDataAdapter(strTable, strConn);
                DataSet ds = new DataSet();
                Da.Fill(ds, "table1");
                dt = ds.Tables[0];
                //dataGridView1.DataSource = dt;
                return dt;
           
        }
       
        SqlConnection conn;
        SqlTransaction myTran;
        public void InsertSql(DataRow dr)
        {
            try
            {
                string CName = null;//单位名称
                string sCarBrand = null;//车牌号
                string sCarType = null;//车型
                string CAddress = null;//单位地址
                string sCarDate = null;//增车日期
                string sHandPhone = null;//联系电话
                //for (int i = 0; i < dt.Columns.Count; i++)
                //{
                //DataColumn dc = new DataColumn();
                if (dt.Columns.Contains("单位名称"))
                {
                    CName = dr["单位名称"].ToString().Trim();
                    if (CName.IndexOf("'") > -1)
                    {
                        CName = CName.Replace("'", "");
                    }
                    if (CName.Length > 4)
                    {
                        CName = CName.Substring(0, 5);
                    }
                    else if (CName.Length == 0)
                    {
                        CName = null;
                    }
                }
                if (dt.Columns.Contains("车牌号"))
                {
                    sCarBrand = dr["车牌号"].ToString().Trim();
                    if (sCarBrand.IndexOf("'") > -1)
                    {
                        sCarBrand = sCarBrand.Replace("'", "");
                    }
                    if (sCarBrand.Length > 9)
                    {
                        sCarBrand = sCarBrand.Substring(0, 10);
                    }
                    else if (sCarBrand.Length == 0)
                    {
                        sCarBrand = null;
                    }
                }
                if (dt.Columns.Contains("车型"))
                {
                    sCarType = dr["车型"].ToString().Trim();
                    if (sCarType.IndexOf("'") > -1)
                    {
                        sCarType = sCarType.Replace("'", "");
                    }
                    if (sCarType.Length > 9)
                    {
                        sCarType = sCarType.Substring(0, 10);
                    }
                    else if (sCarType.Length == 0)
                    {
                        sCarType = null;
                    }
                }
                if (dt.Columns.Contains("单位地址"))
                {
                    CAddress = dr["单位地址"].ToString().Trim();
                    if (CAddress.IndexOf("'") > -1)
                    {
                        CAddress = CAddress.Replace("'", "");
                    }
                    if (CAddress.Length > 24)
                    {
                        CAddress = CAddress.Substring(0, 25);
                    }
                    else if (CAddress.Length == 0)
                    {
                        CAddress = null;
                    }
                }
                if (dt.Columns.Contains("增车日期"))
                {
                    sCarDate = dr["增车日期"].ToString().Trim();
                    if (sCarDate.IndexOf("'") > -1)
                    {
                        sCarDate = sCarDate.Replace("'", "");
                    }
                    if (sCarDate.Length > 9)
                    {
                        sCarDate = sCarDate.Substring(0, 10);
                    }
                    else if (sCarDate.Length == 0)
                    {
                        sCarDate = null;
                    }
                }
                if (dt.Columns.Contains("联系电话"))
                {
                   sHandPhone = dr["联系电话"].ToString().Trim();
                    Match m = Regex.Match(sHandPhone, @"(1[3|5|8]\d{9})+");
                    if (m.Success)
                    {
                        sHandPhone = m.ToString();
                    }                  
                    else
                    {
                        sHandPhone = null;
                    }

                }

                string sconn = "Data Source=DDA535F2780A469;Initial Catalog=HandPhone;integrated security=SSPI";
                conn = new SqlConnection(sconn);
                conn.Open();
                myTran = conn.BeginTransaction();
                string sql = "insert into CarHandPhoneInfo values('" + CName + "','" + sCarType + "','" + sCarBrand + "','" + sHandPhone + "','" + sCarDate + "','" + CAddress + "')";
                SqlCommand com = new SqlCommand(sql, conn);
                com.Connection = conn;
                com.Transaction = myTran;
                com.ExecuteNonQuery();
                myTran.Commit();
            }
            catch (Exception Ex)
            {
                if (myTran != null)
                    myTran.Rollback();
                //创建并且返回异常的错误信息
                MessageBox.Show(Ex.ToString());
                MessageBox.Show("写入数据库失败");
            }
            finally
            {
                if (conn != null)
                    conn.Close();
                //MessageBox.Show("导入数据库成功！");
                //conn.Close();
            }
        }
    }
}