关于datagirdview导入和导出excel

本程序是c#编写的winform下实现

先说一下导出excel

第一步先引用excel com组件

然后可以写代码如下

private void insertDataToExcel()
        {
            Microsoft.Office.Interop.Excel.ApplicationClass myExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            myExcel.Visible = true;
            if (myExcel == null)
            {
                MessageBox.Show("EXCEL无法启动！","信息提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                return;
            }
            int rowcount = 0;
            int columcount = 0;
            try
            {
                myExcel.Application.Workbooks.Add(true);
                rowcount = this.dataGridView1.RowCount;
                columcount = this.dataGridView1.ColumnCount;
                for (int m = 0; m < columcount; m++)
                {
                    myExcel.Cells[1, m + 1] = this.dataGridView1.Columns[m].HeaderText;
                }
                for (int i = 0; i < rowcount; i++)
                {
                    for (int j = 0; j < columcount; j++)
                    {
                        myExcel.Cells[i+2,j+1]=this.dataGridView1[j,i].Value.ToString();
                    }
                }
            }
            catch(Exception ee)
            {
                MessageBox.Show(ee.Message);
            }
        }

下面是excel中的数据表导入到datagridview中的代码

//通过打开找到要导入的excel数据表

private void excelToDatagridview_Click(object sender, EventArgs e)
        {
            OpenFileDialog OpenFileDialog1 = new OpenFileDialog();
            DataTable mdt = new DataTable();
            if (OpenFileDialog1.ShowDialog() == DialogResult.OK)
            {
                mdt = ExcelToDatagridview(OpenFileDialog1.FileName.ToString(),"Sheet1"); ;
                this.dataGridView2.DataSource = mdt;
            }

}

private DataTable ExcelToDatagridview(string strExcelFileName,string strSheetName)
        {
            //源的定义
            //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0';";
            //Sql语句
            //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
            string strExcel = "select * from  [sheet1$]";
            //定义存放的数据表
            DataSet ds = new DataSet();
            //连接数据源
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            //适配到数据源
            OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
            adapter.Fill(ds, strSheetName);
            conn.Close();
            return ds.Tables[strSheetName];
        }