51代码网ORACLEMYSQLSQL SERVER其它数据库java/jspasp/asp.netC/C++/VC++APP应用其它语言服务器应用
您现在的位置: 51代码网 >> asp.net >> 文章正文

C#导入excel2007

更新时间:2012-1-7:  来源:51代码网

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

            string strName = this.txtPutInFile.Text;
            if (strName == "")
            {
                MessageBox.Show("没有选择excel文件!!无法导入--!!");
                return;
            }
            PreExitExcel();
            EcxelToDataGridView(strName, this.dgvEmail);
            //MessageBox.Show("导入成功!!!");
        }
        /// <summary>
        /// excel数据导入方法
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="dgv"></param>
        public void EcxelToDataGridView(string filePath, DataGridView dgv)
        {
            //根据路径打开一个excel文件并将数据填充到dataset中
            string strConn = @"Provider = Microsoft.Ace.OLEDB.12.0; Data Source = " + filePath + "; Extended Properties = 'Excel 12.0;HDR = NO; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            strExcel = "select * from [sheet1$]";
            DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string tableName = schemaTable.Rows[0][2].ToString().Trim();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "table1");
            //根据DataGridView的列构造一个DataTable
            DataTable td = ds.Tables[0];
            try
            {
                //MessageBox.Show(td.ToString());
                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;
                        //MessageBox.Show(dc.ToString());
                        td.Columns.Add(dc);
                    }
                }

                //根据excel的行逐一对上面的构造的datatable的列进行赋值

                for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
                {
                    //int j = 0;
                    DataRow dr = td.NewRow();
                    DataRow excelRow = ds.Tables[0].Rows[i];
                    foreach (DataColumn dc in td.Columns)
                    {
                        dr[dc] = excelRow[i];
                        //MessageBox.Show(dr[dc].ToString());
                        i++;
                    }
                    td.Rows.Add(dr);
                }
                //在datagridview中显示导入的数据
                dgv.DataSource = td;
            }
            catch (Exception)
            {
                MessageBox.Show("该表已存在你即将导入的excel文件...,请点击清空按钮重新导入...");
                return;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 预关闭未退出的Excel进程方法
        /// </summary>
        public void PreExitExcel()
        {
            System.Diagnostics.Process[] allProcess = System.Diagnostics.Process.GetProcesses();
            foreach (System.Diagnostics.Process thisprocess in allProcess)
            {
                string processName = thisprocess.ProcessName;
                if (processName.ToLower() == "excel")
                {
                    try
                    {
                        thisprocess.Kill();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                        return;
                    }
                }
            }
        }
        #endregion

  • 上一篇文章:
  • 下一篇文章: 没有了
  • 赞助商链接
    推荐文章
  • 此栏目下没有推荐文章
  • {
    设为首页 | 加入收藏 | 友情链接 | 网站地图 | 联系站长 |