C#

c# excel如何导入到sqlserver数据库


最近在做这个如何把excel导入到数据库中,经过多方查找,终于找到一个适合的,并且经过自己的完善可以正常使用(忘记原作者博客的链接地址了,敬请见谅)

  1. 首先是窗体的创建,文本框显示文件的路径,按钮执行操作,DataGridView显示导入的信息
  2. 代码如下:可根据自己的需求进行修改,我是要导入之后就对我的另一窗体进行刷新,定义了委托,你们可以忽略。

            //定义委托
            public delegate void Refresh();
    
            //定义事件
            public event Refresh myRefresh;
            public ExcelRoprtForm()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                //选中导入的文件
                try
                {
                    //openFileDialog1.Filter = "Excel 文件|*.xls";//指定存放文件格式类型
                    OpenFileDialog fd = new OpenFileDialog();
                    fd.Filter = "Excel文件(*.xls,xlsx)|*.xls;*.xlsx";
                    if (fd.ShowDialog() == DialogResult.OK)
                    {
                        string fileName = fd.FileName.ToString();
                        this.textBox1.Text = fileName;
                    }
    
                }
                catch (Exception ee)
                {
                    MessageBox.Show("打开文件出错!" + ee.Message.ToString());
                }
            }
    
            private DataSet xsldata(string filepath)
            {
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";
    
                //string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filepath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
    
                System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
    
                string strCom = "SELECT * FROM [Sheet1$]";
    
                Conn.Open();
    
                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
    
                DataSet ds = new DataSet();
    
                myCommand.Fill(ds, "[Sheet1$]");
                dataGridView1.DataSource = ds.Tables[0];
                Conn.Close();
                return ds;
            }
            private void button2_Click(object sender, EventArgs e)
            {
                if (textBox1.Text == "")
                {
                    MessageBox.Show("请选择要导入的Excel文档!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                string filepath = textBox1.Text;
                string strcon1 = ConfigurationManager.ConnectionStrings["connString"].ToString();
                SqlConnection conn = new SqlConnection(strcon1);//链接数据库
                conn.Open();
                try
                {
                    DataSet ds = new DataSet();
                    //取得数据集
                    //调用上面的函数
                    ds = xsldata(filepath);
                    int errorcount = 0;//记录错误信息条数
    
                    int insertcount = 0;//记录插入成功条数
    
                    int updatecount = 0;//记录更新信息条数
    
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        string carnumber = ds.Tables[0].Rows[i][0].ToString();
                        int carstatus = Convert.ToInt32(ds.Tables[0].Rows[i][1].ToString());
                        int cartype = Convert.ToInt32(ds.Tables[0].Rows[i][2].ToString());
                        string carbrand = ds.Tables[0].Rows[i][3].ToString();
    
                        if (carnumber != "" && carstatus != 0 && cartype != 0)
                        {
                            SqlCommand selectcmd = new SqlCommand("select count(*) from CarInfo where CarNumber='" + carnumber + "'", conn);
                            
                            int count = Convert.ToInt32(selectcmd.ExecuteScalar());
                            if (count > 0)
                            {
                                updatecount++;
                            }
                            else
                            {
                                SqlCommand insertcmd = new SqlCommand("insert into CarInfo(CarNumber,CarStatusID,CarTypeID,CarBrand) values(" + "'" + carnumber + "'," + carstatus + "," + cartype + ",'" + carbrand + "'" + ")", conn);
    
                                insertcmd.ExecuteNonQuery();
    
                                insertcount++;
    
                            }
                        }
                        else
                        {
                            //MessageBox.Show("电子表格信息有错!");
                            errorcount++;
                        }
                    }
                    myRefresh();
                    MessageBox.Show(insertcount + "条数据导入成功!" + updatecount + "条数据重复!" + errorcount + "条数据部分信息为空没有导入!");
                }
                catch (Exception ex)
                {
    
                    MessageBox.Show(ex.Message);
                }
    
                finally
                {
                    conn.Close();
    
                }
            }
    

      


作者:墨染白青,发布于:2019/03/15
原文:https://www.cnblogs.com/qinmoran123/p/10535027.html