通过剪贴板将DataGridView中的数据导出到Excel

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Windows.Forms;
  9 using Excel = Microsoft.Office.Interop.Excel;
 10 using System.Reflection;
 11 using Microsoft.Office.Interop.Excel;
 12 
 13 namespace WindowsFormsApplication1
 14 {
 15     public partial class Form1 : Form
 16     {
 17         public Form1()
 18         {
 19             InitializeComponent();
 20             this.saveFileDialog1.Filter = "Excel Workbook|*.xlsx|Excel Macro-Enabled Workbook|*.xlsm|Excel 97-2003 Workbook|*.xls";
 21             this.saveFileDialog1.FileName = "demo.xlsx";
 22 
 23             LoadData();
 24         }
 25 
 26         private void LoadData()
 27         {
 28             BindingList<Car> cars = new BindingList<Car>();
 29 
 30             cars.Add(new Car("Ford", "Mustang", 1967));
 31             cars.Add(new Car("Shelby AC", "Cobra", 1965));
 32             cars.Add(new Car("Chevrolet", "Corvette Sting Ray", 1965));
 33 
 34             this.dataGridView1.DataSource = cars;
 35         }
 36 
 37         private void toolStripButton1_Click(object sender, EventArgs e)
 38         {
 39             string filePath = string.Empty;
 40             if (this.saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
 41             {
 42                 filePath = this.saveFileDialog1.FileName;
 43             }
 44             else
 45             {
 46                 return;
 47             }
 48 
 49             this.dataGridView1.SelectAll();
 50             Clipboard.SetDataObject(this.dataGridView1.GetClipboardContent());
 51 
 52             Excel.Application objExcel = null;
 53             Excel.Workbook objWorkbook = null;
 54             Excel.Worksheet objsheet = null;
 55             try
 56             {
 57                 objExcel = new Microsoft.Office.Interop.Excel.Application();
 58                 objWorkbook = objExcel.Workbooks.Add(Missing.Value);
 59                 objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
 60                 objExcel.Visible = false;
 61 
 62                 objExcel.get_Range("A1", System.Type.Missing).PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
 63                 objsheet.Name = "Demo";  
 64                 //Set table properties  
 65                 objExcel.Cells.EntireColumn.AutoFit();//auto column width  
 66                 objExcel.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
 67                 objExcel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
 68                 objExcel.ErrorCheckingOptions.BackgroundChecking = false;  
 69 
 70                 //save file
 71                 objWorkbook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
 72                         Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
 73                         Missing.Value, Missing.Value);
 74             }
 75             catch (Exception error)
 76             {
 77                 MessageBox.Show(error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
 78                 return;
 79             }
 80             finally
 81             {
 82                 //Dispose the Excel related objects 
 83                 if (objWorkbook != null)
 84                 {
 85                     objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
 86                 }
 87                 if (objExcel.Workbooks != null)
 88                 {
 89                     objExcel.Workbooks.Close();
 90                 }
 91                 if (objExcel != null)
 92                 {
 93                     objExcel.Quit();
 94                 }
 95 
 96                 objsheet = null;
 97                 objWorkbook = null;
 98                 objExcel = null;
 99                 GC.Collect(); // force final cleanup.
100             }
101         }
102     }
103 
104     public class Car
105     {
106         private string _make;
107         private string _model;
108         private int _year;
109 
110         public Car(string make, string model, int year)
111         {
112             _make = make;
113             _model = model;
114             _year = year;
115         }
116 
117         public string Make
118         {
119             get { return _make; }
120             set { _make = value; }
121         }
122 
123         public string Model
124         {
125             get { return _model; }
126             set { _model = value; }
127         }
128 
129         public int Year
130         {
131             get { return _year; }
132             set { _year = value; }
133         }
134     }
135 }