datagridview与Excel的导入导出问题

导入Excel
private void button1_Click(object sender, EventArgs e)
        {
            //this.openFileDialog1.ShowDialog();

            OpenFileDialog openfile = new OpenFileDialog();
            openfile.Filter = "工作薄(*.xls)|*.xls|所有文件(*.*)|*.*";
            textBox1.Text = openfile.FileName;
            if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
                ExcelToDS(openfile.FileName);

        }

public void ExcelToDS(string path)
        {

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            strExcel = "select * from [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            DataTable table1 = new DataTable();

            myCommand.Fill(table1);
            DataColumn result = new DataColumn();
            result.DataType = System.Type.GetType("System.String");
            result.ColumnName = "对比结果";
            table1.Columns.Add(result);
            dataGridView2.DataSource = table1;

        }
**********************************************************************************************************
（二）导出到Excel

private void button2_Click(object sender, EventArgs e)
        {
            if (!ExportDataGridView(dataGridView1, true))
            {
                MessageBox.Show("表格中没有数据，无法导出数据！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        public bool ExportDataGridView(DataGridView dgv, bool isShowExcel)
        {
            if (dgv.Rows.Count == 0)
                return false;
            //建立excel对象
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcel;
            //生成字段名称
            for (int i = 0; i < dgv.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = dgv.Columns.HeaderText;
            }
            //填充数据
            for (int i = 0; i < dgv.RowCount - 1; i++)
            {
                for (int j = 0; j < dgv.ColumnCount; j++)
                {
                    if (dgv[j, i].ValueType == typeof(string))
                    {
                        excel.Cells = "'" + dgv[j, i].Value.ToString();
                    }
                    else
                    {
                        excel.Cells = dgv[j, i].Value.ToString();
                    }
                }
            }
            return true;
        }

更加复杂的：
首先要引入一个.dll文件，此文件从office中获得，如果安装了.net2005,那么请重新再安装office2003，在安装过程中，选择“选择应用程序的高级自定义”，对“编程”项打钩，安装完成后即可获得此dll文件，在com中将此dll文件引入，文件名为“微软exccel 11.0......”
引入此dll后，导入这些包：
using System.Reflection;//用于missing.value
using System.IO;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
先放上最基础的从datagridview导excel的代码
private void button1_Click(object sender, EventArgs e)
        {
            #region 验证可操作性
            //申明保存对话框  
            SaveFileDialog dlg = new SaveFileDialog();
            //默然文件后缀  
            dlg.DefaultExt = "xls ";
            //文件后缀列表  
            dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
            //默然路径是系统当前路径  
            dlg.InitialDirectory = Directory.GetCurrentDirectory();
            //打开保存对话框  
            if (dlg.ShowDialog() == DialogResult.Cancel) return;
            //返回文件路径  
            string fileNameString = dlg.FileName;
            //验证strFileName是否为空或值无效  
            if (fileNameString.Trim() == " ")
            { return; }
            //定义表格内数据的行数和列数  
            int rowscount = dgv.Rows.Count;
            int colscount = dgv.Columns.Count;
            //行数必须大于0  
            if (rowscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            //列数必须大于0  
            if (colscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            //行数不可以大于65536  
            if (rowscount > 65536)
            {
                MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            //列数不可以大于255  
            if (colscount > 255)
            {
                MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            //验证以fileNameString命名的文件是否存在，如果存在删除它  
            FileInfo file = new FileInfo(fileNameString);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
            #endregion
            Microsoft.Office.Interop.Excel.Application objExcel = null;
            Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
            Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
            try
            {
                //申明对象  
                objExcel = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
                //设置EXCEL不可见  
                objExcel.Visible = false;
                //向Excel中写入表格的表头  
                int displayColumnsCount = 1;
                for (int i = 0; i <= dgv.ColumnCount - 1; i++)
                {
                    if (dgv.Columns.Visible == true)
                    {
                        objExcel.Cells[1, displayColumnsCount] = dgv.Columns.HeaderText.Trim();
                        displayColumnsCount++;
                    }
                }
                //向Excel中逐行逐列写入表格中的数据  
                for (int row = 0; row < dgv.RowCount; row++)
                {
                    //tempProgressBar.PerformStep();  
                    displayColumnsCount = 1;
                    for (int col = 0; col < colscount; col++)
                    {
                        if (dgv.Columns[col].Visible == true)//防止dgv中有单元格没填数据而抛错
                        {
                            try
                            {
                                //row+2表示从excel的第二行开始填入datagridview中的数据
                                objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();

                            }
                            catch (Exception)
                            {
                            }
                        }
                        //往后递进一格
                        displayColumnsCount++;
                    }
                }
                //保存文件  
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                //关闭Excel应用  
                if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                if (objExcel != null) objExcel.Quit();
                objsheet = null;
                objWorkbook = null;
                objExcel = null;
            }
            MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

转载于:https://www.cnblogs.com/freedom831215/archive/2009/10/03/1577625.html