C#将SQL数据库中数据导入Excel中，并将Excel中反导入SQL数据库中

实际的开发中，我们会经常遇到数据的转化的需要，将Excel中的数据转入到SQL中，或将SQL在数据库表中的数据导入到Excel中。代码如下：

 public partial class Form1 : Form    {        private static string _filePath = string.Empty;        public Form1()        {            InitializeComponent();            BindUser();        }        /// <summary>        /// 绑定数据        /// </summary>        private void BindUser()        {            string sql = "select * from CRMUser";            DataSet ds = DbHelperSQL.Query(sql);            dataGridView1.DataSource = ds.Tables[0];        }        /// <summary>        /// 将Users表中的数据导入Excel中        /// </summary>        private void btnSqlToExcel_Click(object sender, EventArgs e)        {            ExcelFile excelFile = new ExcelFile();            ExcelWorksheet sheet = excelFile.Worksheets.Add("CRMUser");            int columns = dataGridView1.Columns.Count;            int rows = dataGridView1.Rows.Count;            for (int j = 0; j < columns; j++)            {                sheet.Cells[0, j].Value = dataGridView1.Columns[j].HeaderText;            }            for (int i = 1; i < rows; i++)            {                for (int j = 0; j < columns; j++)                {                    sheet.Cells[i, j].Value = dataGridView1[j, i - 1].Value.ToString().Trim();                }            }            excelFile.SaveXls("./CRMUser.xls");            MessageBox.Show("生成成功");        }        /// <summary>        /// 选择要向SQL数据库中导入数据的Excel文件        /// </summary>         private void btnChoose_Click_1(object sender, EventArgs e)        {            using (OpenFileDialog dialog = new OpenFileDialog())            {                dialog.Multiselect = true;                if (dialog.ShowDialog() == DialogResult.OK)                {                    try                    {                        txtPath.Text = dialog.FileName;                    }                    catch { }                }            }        }        /// <summary>        /// 将Excel中的数据导入到SQL数据库中        /// </summary>         private void btnExcelToSql_Click(object sender, EventArgs e)        {            DataSet ds = ImportFromExcel(txtPath.Text.Trim());            DataTable dt = ds.Tables[0];            try            {                string strInsertComm;                for (int i = 0; i < dt.Rows.Count; i++)                {                    strInsertComm = "";                    strInsertComm = "Insert INTO TestExcel(ToolName,[Type],brife,Info,WebSite)";                    strInsertComm += " values(";                    for (int j = 0; j <dt.Columns.Count; j++)                    {                        if (j > 0)                        {                            strInsertComm += ",'" + dt.Rows[i][j].ToString().Trim() + "'";                        }                        else                        {                            strInsertComm += "'" + dt.Rows[i][j].ToString().Trim() + "'";                        }                    }                    strInsertComm += ")";                    //判断当插入的整条数据都不为空时才执行插入操作，避免插入空数据                    if (strInsertComm != "Insert INTO TestExcel(ToolName,[Type],brife,Info,WebSite) values('','','','','')")                    {                        DbHelperSQL.ExecuteSql(strInsertComm);                    }                }                MessageBox.Show("导入成功");            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);            }        }        #region Excel导入SQL数据库        /// <summary>        /// 获取Excel数据表列表        /// </summary>        /// <returns></returns>        public static ArrayList GetExcelTables(string FilePath)        {            //将Excel架构存入数据里            System.Data.DataTable dt = new System.Data.DataTable();            ArrayList TablesList = new ArrayList();            if (File.Exists(FilePath))            {                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." +                     "OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + FilePath))                {                    try                    {                        conn.Open();                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                    }                    catch (Exception exp)                    {                        MessageBox.Show(exp.Message);                    }                    //获取数据表个数                    int tablecount = dt.Rows.Count;                    for (int i = 0; i < tablecount; i = i + 2)                    {                        string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');                        if (TablesList.IndexOf(tablename) < 0)                        {                            TablesList.Add(tablename);                        }                    }                }            }            return TablesList;        }        /// <summary>        /// 导入Excel数据表至DataTable（第一行作为表头）        /// </summary>        /// <returns></returns>        public static System.Data.DataSet FillDataSet(string FilePath)        {            if (!File.Exists(FilePath))            {                throw new Exception("Excel文件不存在！");            }            ArrayList TableList = new ArrayList();            TableList = GetExcelTables(FilePath);            if (TableList.Count <= 0)            {                return null;            }            System.Data.DataTable table;            System.Data.DataSet ds = new DataSet();            OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");            try            {                if (dbcon.State == ConnectionState.Closed)                {                    dbcon.Open();                }                for (int i = 0; i < TableList.Count; i++)                {                    string dtname = TableList[i].ToString();                    try                    {                        OleDbCommand cmd = new OleDbCommand("select * from [" + dtname + "$]", dbcon);                        OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);                        table = new DataTable(dtname);                        adapter.Fill(table);                        ds.Tables.Add(table);                    }                    catch (Exception exp)                    {                        MessageBox.Show(exp.Message);                    }                }            }            finally            {                if (dbcon.State == ConnectionState.Open)                {                    dbcon.Close();                }            }            return ds;        }        /// <summary>        /// Excel导入数据库        /// </summary>        /// <returns></returns>        public static DataSet ImportFromExcel(string FilePath)        {            return FillDataSet(FilePath);        }        #endregion    }