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
    }