[转]c#excel2007导入DataGridView

(2009-08-07 16:50:41)

private bool ExcelIntoDataGridView(string filepath, DataGridView dgv)
        {
            try
            {
                //根据路径打开一个Excel文件并将数据填充到DataSet中
                string strConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source = ” + filepath + “;Extended Properties =’Excel 8.0;HDR=NO;IMEX=1′”;//导入时包含Excel中的第一行数据，并且将数字和字符混合的单元格视为文本进行导入

                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = “”;
                OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                strExcel = “select  * from   [sheet1$]”;
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                myCommand.Fill(ds, “table1”);

                //根据DataGridView的列构造一个新的DataTable
                DataTable dt = 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
                        dt.Columns.Add(dc);
                    }
                }

                //根据Excel的行逐一对上面构造的DataTable的列进行赋值
                foreach (DataRow excelRow in ds.Tables[0].Rows)
                {
                    int i = 0;
                    DataRow dr = dt.NewRow();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        dr[dc] = excelRow[i];
                        i++;
                    }
                    dt.Rows.Add(dr);
                }
                //在DataGridView中显示导入的数据
                dgv.DataSource = dt;

                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                return false;
            }
        }