C# winform Excel导入保存数据两种方法

方法一：；微软自带excel 操作类
项目运行：设置 Any CPU
引用：System.Data.OleDb
点击事件导入按钮：

private void buttonX25_Click(object sender, EventArgs e)
   {
       //打开一个文件选择框
       OpenFileDialog ofd = new OpenFileDialog();
       ofd.Title = "Excel文件";
       ofd.FileName = "";
       ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//为了获取特定的系统文件夹，可以使用System.Environment类的静态方法GetFolderPath()。该方法接受一个Environment.SpecialFolder枚举，其中可以定义要返回路径的哪个系统目录
       ofd.Filter = "Excel文件| *.xlsx;*.xls";
       ofd.ValidateNames = true;     //文件有效性验证ValidateNames，验证用户输入是否是一个有效的Windows文件名
       ofd.CheckFileExists = true;  //验证路径有效性
       ofd.CheckPathExists = true; //验证文件有效性


       string strName = string.Empty;
       if (ofd.ShowDialog() == DialogResult.OK)
       {
           strName = ofd.FileName;
       }

       if (strName == "")
       {
           MessageBox.Show("没有选择Excel文件！无法进行数据导入");
           return;
       }
       EXCLChannel hh = new EXCLChannel();
       //调用导入数据方法
       EcxelToDataGridView(strName, hh.dataGridViewX20);


   }

调用方法：绑定DataGridView进行预览

     public void EcxelToDataGridView(string filePath, DataGridViewX dgv)
        {
            string strConn = "";
            if (filePath.EndsWith(".xls"))
            {
                strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data  
                Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", filePath);
            }
            else if (filePath.EndsWith(".xlsx"))
            {
                strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data  
                Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", filePath);
            }
            //根据路径打开一个Excel文件并将数据填充到DataSet中
            //导入时包含Excel中的第一行数据，并且将数字和字符混合的单元格视为文本进行导入
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            System.Data.OleDb.OleDbDataAdapter myCommand = null;
            DataSet dd = null;
            strExcel = "select * from [sheet1$]";
            myCommand = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
            dd = new DataSet();
            myCommand.Fill(dd, "table1");
            //根据DataGridView的列构造一个新的DataTable
            DataTable tb = new DataTable();
            foreach (DataGridViewColumn dgvc in dgv.Columns)
            {
                if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
                {
                    DataColumn dc = new DataColumn();
                    dc.ColumnName = dgvc.DataPropertyName;
                    //dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释，前提是DataGridView必须先绑定一个数据源那怕是空的DataTable
                    tb.Columns.Add(dc);
                }
            }
            //根据Excel的行逐一对上面构造的DataTable的列进行赋值
            foreach (DataRow excelRow in dd.Tables[0].Rows)
            {
                int i = 0;
                DataRow dr = tb.NewRow();
                foreach (DataColumn dc in tb.Columns)
                {
                    dr[dc] = excelRow[i];
                    i++;
                }
                tb.Rows.Add(dr);
            }
            //在DataGridView中显示导入的数据
            dgv.DataSource = tb;

            EXCLChannel fg = new EXCLChannel(tb);
            fg.ShowDialog();
        }
         //另一个窗体  EXCLChannel 实现预览
 public EXCLChannel(DataTable td)
        {
            InitializeComponent();
            this.dataGridViewX20.DataSource = td;          
        }

方法二：NPOI导入
引用：NPOI
添加类：ExcelRender 下载
链接：https://pan.baidu.com/s/1objybCCUKWw6ogXxkjThqg
提取码：lw0s

  public void EcxelToDataGridView(string filePath, DataGridViewX dgv)
        {
            FileStream fs = null;
            try
            {

                //String 文件路径，FileMode 文件打开模式，FileAccess 文件访问方式
                 fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                //
                byte[] fileBytes = new byte[fs.Length];
                //从流中读取数据，返回字节数组
                fs.Read(fileBytes, 0, Convert.ToInt32(fs.Length));
                //实例化
                DataTable dtexcel = new DataTable();
                if (filePath.EndsWith(".xls"))
                {
                    dtexcel = ExcelRender.RenderFromExcel(new MemoryStream(fileBytes), 0);
                }
                else
                {
                    dtexcel = ExcelRender.RenderFromExcelxlsx(new MemoryStream(fileBytes), 0);
                }
                if (dtexcel.Rows.Count > 0)
                {
                    EXCLChannel fg = new EXCLChannel(dtexcel);
                    fg.ShowDialog();
                }
                else
                {
                    MessageBox.Show("请检查EXCL表格数据和表头是否正确");
                }
                if (dtexcel.Rows.Count > 0)
                {
                    fileBytes = null;

                }
            }
            catch (Exception)
            {
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }
    //另一个窗体  EXCLChannel
 public EXCLChannel(DataTable td)
        {
            InitializeComponent();
            this.dataGridViewX20.DataSource = td;          
        }

//保存数据库
页面点击保存按钮：

  private void buttonX25_Click(object sender, EventArgs e)
        {
            if (dataGridViewX20.Rows.Count > 0)
            {
              
                int num = 0;
                Dictionary<int, string>[] dis = new Dictionary<int, string>[dataGridViewX20.Rows.Count];
                try
                {
                    foreach (DataGridViewRow DVG in dataGridViewX20.Rows)
                {
                    Dictionary<int, string> di = new Dictionary<int, string>();
                    di.Add(1, DVG.Cells["Fixed_Assets_Code"].Value.ToString());
                    di.Add(2, DVG.Cells["Capital_assets_Code"].Value.ToString());
                    di.Add(3, DVG.Cells["Class_Code"].Value.ToString());
                    di.Add(4, DVG.Cells["Place_Code"].Value.ToString());
                    di.Add(5, DVG.Cells["Department_Code"].Value.ToString());
                    di.Add(6, DVG.Cells["Procurement_Date"].Value.ToString());
                    di.Add(7, DVG.Cells["Manufacture"].Value.ToString());
                    dis[num] = di;
                    num++;
                }
                int iss = ibll.InsertEREXCL(dis);               
                
                    MessageBox.Show("导入成功：" + iss + "条数据!!");
               
                }
                catch (Exception)
                {
                    MessageBox.Show("导入失败，文件内容不符合导入条件");
                }
            }
            else
            {
                MessageBox.Show("没有保存的数据");
            }
        }

BLL代码：

  public int InsertEREXCL5(Dictionary<int, string>[]  bys)
        {
            int numb = 0;
            foreach ( Dictionary<int, string> item in bys)
            {
                //时间转换
                DateTime dt = Convert.ToDateTime("1777-1-1");
                try
                {
                    dt = Convert.ToDateTime(item[6]);
                }
                catch (Exception)
                {
                    dt = Convert.ToDateTime("1777-1-1");

                }
                if (IIn.InsertEREXCL1(item[1],item[2], item[3], item[4], item[5], dt, item[7]) >0)
                {
                    numb++;
                }
            }
            return numb;
        }

//item[1],item[2], item[3], item[4], item[5], dt, item[7] 对应参数
    public  int InsertEREXCL1(string Fixed_Assets_Code, string Capital_assets_Code, string Class_Code, string Place_Code, string Department_Code, DateTime Procurement_Date, string Manufacture)
        {
            lock (obinsertDao)
            {

                SqlParameter[] sqlPams = {
                        new SqlParameter("@type",SqlDbType.NChar),
                        new SqlParameter("@Fixed_Assets_Code",SqlDbType.NChar),
                        ..............省略..........
                         };//声明SQL参数数组并实例化相关参数
                sqlPams[0].Value = "InsertEREXCL";//对参数赋值
                sqlPams[1].Value = Fixed_Assets_Code;//对参数赋值    
            ....................省略.....................
                //myDAL.DAL_SelectDB_Par("存储过程的名字",SQL参数数组);
                return dt;
            }
        }