EXCEL表里的数据导入SQL中

最新推荐文章于 2024-09-27 16:36:00 发布

baomihua21

最新推荐文章于 2024-09-27 16:36:00 发布

阅读量692

点赞数

文章标签： C# Excel

本文链接：https://blog.csdn.net/baomihua21/article/details/8797206

版权

本文介绍了一种将Excel数据批量导入SQL数据库的方法。通过C#应用程序实现，利用OleDb连接Excel文件并读取数据到DataSet中，再逐条插入到指定的数据库表。此过程涉及文件路径的选择、数据读取及SQL语句的执行。

摘要由CSDN通过智能技术生成


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

namespace SqlExcel
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }
        SqlHelper sqlhelper = new SqlHelper();

        /// <summary>
        /// 选择excle路径
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog dialog = new OpenFileDialog())
            {
                dialog.Multiselect = true;
                if (dialog.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        textBox1.Text = dialog.FileName;
                    }
                    catch { }
                }
            }

        }
        /// <summary>
        /// 先把excle读到Dataset里
        /// </summary>
        /// <param name="filenameurl">文件路径名</param>
        /// <returns></returns>
        public DataSet ExecleDs(string filenameurl)
        {

            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]",conn);
            OleDbDataAdapter odda = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            odda.Fill(ds);
            return ds;
        }

        /// <summary>
        /// 把Dataset里的数据一行一行的插入到数据表里
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            int ii = 0;
            DataSet ds = ExecleDs(textBox1.Text);
            System.Data.DataTable dt = ds.Tables[0];

            try
            {
                string strInsertComm;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    strInsertComm = "";
                    strInsertComm = "Insert INTO T_Student(F_Num,F_Name,F_Sex,F_Dept)";
                    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 += ")";
                    ii = sqlhelper.getcomnum(strInsertComm);//执行SQL语句
                }
                if (ii > 0)
                {
                    MessageBox.Show("导入成功！");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }