将DataGridView里面的数据导入到Excel中

拉控键，创建一个简单的实例，如下图所示

（1）将数据库数据导入DataGridView中

代码如下所示

 1         private DataTable _dtInfo = null;
 2         private DataView _dvInfo = null;
 3         //private DataGridView dgvInfo = null;
 4         public Demo()
 5         {
 6             InitializeComponent();
 7         }
 8         private void Form1_Load(object sender, EventArgs e)
 9         {
10             _dtInfo = GetData();
11             _dvInfo = new DataView(_dtInfo, "", "", DataViewRowState.CurrentRows);
12             dataGridView1.DataSource = _dvInfo;
13         }
14         private void button1_Click(object sender, EventArgs e)
15         {
16             InitData();
17         }
18 
19         private void InitData()
20         {
21             try
22             {
23                 if (_dtInfo != null)
24                 {
25                     _dtInfo.Rows.Clear();
26                 }
27                 string con, sql;
28                 con = @"Data Source=。 ;Initial Catalog=FirstDemo;User ID=sa;Pwd=123456";
29                 sql = "select * from Student";
30                 SqlConnection mycon = new SqlConnection(con);
31                 mycon.Open();
32                 SqlDataAdapter myda = new SqlDataAdapter(sql, con);
33                 DataSet myds = new DataSet();
34                 myda.Fill(_dtInfo);
35                 ////myda.Fill(myds, "test1");
36                 _dvInfo = new DataView(_dtInfo, "", "", DataViewRowState.CurrentRows);
37 
38                 dataGridView1.DataSource = _dvInfo;// myds.Tables["test1"];
39                 dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;//选定一行
40                 dataGridView1.ReadOnly = true;//不可编辑
41                 dataGridView1.AllowUserToAddRows = false;//最后一行不显示
42                 dataGridView1.RowHeadersVisible = false;//第一列空白的不显示
43                 mycon.Close();
44             }
45             catch (Exception ex)
46             {
47                 MessageBox.Show("错误信息：" + ex.Message, "出现错误");
48             }
49         }
50 
51        
52         private DataTable GetData()
53         {
54             DataTable dtInfo = new DataTable();
55 
56             return dtInfo;
57         }

将数据库数据显示在DataGridView上

（2）将DataGridView上的数据导出生成为一个Excel文件

代码如下所示：

 1  private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
 2         {
 3 
 4         }
 5         /// <summary>
 6         /// 将数据导入到Excel文件中
 7         /// </summary>
 8         /// <param name="sender"></param>
 9         /// <param name="e"></param>
10         private void btnExport_Click(object sender, EventArgs e)
11         {
12             string fileName = DateTime.Now.ToString("yyyyMMddhhmmss");
13             string saveFileName = "";
14             SaveFileDialog saveDialog = new SaveFileDialog();
15             saveDialog.DefaultExt = "xlsx";
16             saveDialog.Filter = "Excel文件|*.xlsx";
17             saveDialog.FileName = fileName;
18             saveDialog.ShowDialog();
19             saveFileName = saveDialog.FileName;
20             if (saveFileName.IndexOf(":") < 0) return; //被点了取消
21             Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
22             if (xlApp == null)
23             {
24                 MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
25                 return;
26             }
27             Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
28             Microsoft.Office.Interop.Excel.Workbook workbook =
29                         workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
30             Microsoft.Office.Interop.Excel.Worksheet worksheet =
31                         (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
32                                                                                          //写入标题             
33             for (int i = 0; i < dataGridView1.ColumnCount; i++)
34             { worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; }
35             //写入数值
36             for (int r = 0; r < dataGridView1.Rows.Count; r++)
37             {
38                 for (int i = 0; i < dataGridView1.ColumnCount; i++)
39                 {
40                     worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
41                 }
42                 System.Windows.Forms.Application.DoEvents();
43             }
44             worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
45             MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
46             if (saveFileName != "")
47             {
48                 try
49                 {
50                     workbook.Saved = true;
51                     workbook.SaveCopyAs(saveFileName);  //fileSaved = true;                 
52                 }
53                 catch (Exception ex)
54                 {//fileSaved = false;                      
55                     MessageBox.Show("导出文件时出错,文件可能正被打开！
" + ex.Message);
56                 }
57             }
58             xlApp.Quit();
59             GC.Collect();//强行销毁       
60         }

将DataGridView上的数据导入到Excel文件