WinForm Excel导入

 

 主要代码如下：

定义一个DataSet   存放Excel读取的数据

 /// <summary>
        /// Excel 表中读取的数据
        /// </summary>
        public DataSet ExcelData
        {
            get;
            set;
        }

获取Excel模板：

  //获取Excel模板
        private void sbtnGet_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            //sfd.Filter = "Excel|*xls";
            sfd.Filter = "(*.xls,*.xlsx)|*.xls;*.xlsx"; 
            sfd.FileName = "学生信息"+DateTime.Now.ToString("yyyy-MM-dd")+".xls";
            if (sfd.ShowDialog(this) == DialogResult.OK) {
                string fileName = Application.StartupPath + "\\config\\学生信息模板.xls";
                if (File.Exists(fileName))
                {
                    File.Copy(fileName, sfd.FileName, true);
                    Function.ShowMsg("下载成功！", CYSoft.Common.CYEnumerate.MsgType.OK);
                }
                else {
                    Function.ShowMsg("未找到学生信息模板文件！", CYSoft.Common.CYEnumerate.MsgType.OK);
                    return;
                }
            }
        }

选择文件：

   //选择文件
        private void btnFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "(*.xls,*.xlsx)|*.xls;*.xlsx";
            if (ofd.ShowDialog(this) == DialogResult.OK) {
                this.btnFile.Text = ofd.FileName;

                ExcelData = null;              

                #region 读取Excel 文件
                //连接字符串   //03版 Microsoft.Jet.OLEDB.4.0
                const string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties='{1};HDR=Yes;IMEX=1'";

                OleDbConnection conn = new OleDbConnection(string.Format(strConn, ofd.FileName, "Excel 8.0"));
                try
                {
                    conn.Open();
                }
                catch
                {
                    conn = new OleDbConnection(string.Format(strConn, ofd.FileName, "Excel 12.0"));
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception ee)
                    {
                        Func.ShowMessage("连接Excel文件错误:" + ee.Message, Func.InfoEnum.HintIE);
                        return;
                    }
                }

                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                DataSet ds = new DataSet();
                foreach (DataRow dr in dt.Rows)
                {
                    string sql = "select * from [" + dr["TABLE_NAME"].ToString() + "]";
                    OleDbDataAdapter aper = new OleDbDataAdapter(sql, conn);                    
                    aper.Fill(ds, dr["TABLE_NAME"].ToString());
                    if (ds.Tables[dr["TABLE_NAME"].ToString()].Columns.Count < 2)
                    {
                        continue;
                    }

                    //dt = myset.Tables[dr["TABLE_NAME"].ToString()];
                    if (ds == null || ds.Tables[0].Rows.Count < 1)
                    {
                        Func.ShowMessage("连接Excel文件错误:", Func.InfoEnum.HintIE);
                        return;
                    }                 
                }               
                conn.Close();
                conn.Dispose();
                #endregion

                ExcelData = ds;
                //校验数据并加载
                InitExcelData2();
                
            }
        }

说明：

数据校验、加载、保存就不写了