解析Winform中导入导出Excel实现代码

#region 导出EXCEL
        private void DataGridViewToExcel(DataTable dt, string filename)
        {
            try
            {
                SaveFileDialog dlg = new SaveFileDialog();
                dlg.Filter = "Execl files (*.xls)|*.xls";
                dlg.FilterIndex = 0;
                dlg.RestoreDirectory = true;
                dlg.Title = "保存为Excel文件";
                dlg.FileName = filename;
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    Stream myStream;
                    myStream = dlg.OpenFile();
                    StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                    string colHeaders = "", ls_item = "";
                    //写入列标题   
                    colHeaders += "部门" + "t";
                    colHeaders += "登录账号" + "t";
                    colHeaders += "姓名" + "t";
                    colHeaders += "密码" + "t";
                    colHeaders += "电话" + "t";
                    colHeaders += "手机" + "t";
                    colHeaders += "Email" + "t";
                    colHeaders += "生日" + "t";
                    colHeaders += "过期时间";
                    sw.WriteLine(colHeaders);
                    DataRow[] myRow = dt.Select();
                    //写入列内容   
                    foreach (DataRow row in myRow)
                    {
                        ls_item += row["dept_name"].ToString() + "t";
                        ls_item += row["user_loginName"].ToString() + "t";
                        ls_item += row["user_realName"].ToString() + "t";
                        ls_item += "" + "t";
                        ls_item += row["user_telephone"].ToString() + "t";
                        ls_item += row["user_mobile"].ToString() + "t";
                        ls_item += row["user_email"].ToString() + "t";
                        ls_item += row["user_birthday"].ToString() + "t";
                        ls_item += row["user_expiredTime"].ToString();
                        sw.WriteLine(ls_item);
                        ls_item = "";
                    }
                    sw.Close();
                    myStream.Close();
                    MessageBox.Show("导出[" + filename + "]成功", "提示");

                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
        #endregion

#region Excel导入

private void tsinuser_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "表格文件 (*.xls)|*.xls";
            openFileDialog.RestoreDirectory = true;
            openFileDialog.FilterIndex = 1;
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                InExcelData(openFileDialog.FileName);
            }
        }
        private bool InExcelData(string filePath)
        {
            try
            {
                HG_dms_folderEntity folderEnt = new HG_dms_folderEntity(); string folderid;
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";

                OleDbConnection con = new OleDbConnection(strConn);
                con.Open();
                string[] names = GetExcelSheetNames(con);//GetExcelSheetNames(filePath);
                if (names != null)
                {
                    if (names.Length > 0)
                    {
                        foreach (string name in names)
                        {
                            OleDbCommand cmd = con.CreateCommand();
                            cmd.CommandText = string.Format(" select * from [{0}]", name);//[sheetName$]要如此格式
                            OleDbDataReader odr = cmd.ExecuteReader();

                            while (odr.Read())
                            {
                                if (odr[0].ToString() != "")
                                {
                                    if (odr[0].ToString() == "部门")//过滤列头 按你的实际Excel文件
                                        continue;
                                    if (userLogic.CheckUserLogin(odr[1].ToString()) == 0)
                                    {
                                        ……………………写入数据库

                                    }
                                }
                            }
                            odr.Close();
                        }
                    }
                }
                con.Close();
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        /// <summary>
        /// 查询表名
        /// </summary>
        /// <param name="con"></param>
        /// <returns></returns>
        public static string[] GetExcelSheetNames(OleDbConnection con)
        {
            try
            {
                DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { null, null, null, "Table" });//检索Excel的架构信息
                var sheet = new string[dt.Rows.Count];
                for (int i = 0, j = dt.Rows.Count; i < j; i++)
                {
                    sheet[i] = dt.Rows[i]["TABLE_NAME"].ToString();
                }
                return sheet;
            }
            catch
            {
                return null;
            }
        }

#endregion