通过剪贴板将DataGridView中的数据导出到Excel
将DataGridView中的数据导出到Excel中有许多方法，常见的方法是使用Office COM组件将DataGridView中的数据循环复制到Excel Cell对象中，然后再保存整个Excel Workbook。但是如果数据量太大，例如上万行数据或者有多个Excel Sheet需要同时导出，效率会比较低。可以尝试使用异步操作或多线程的方式来解决UI死锁的问题。参考http://www.cnblogs.com/jaxu/archive/2011/08/03/2126497.html
这里介绍一种直接通过Windows剪贴板将数据从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 Car105 {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 Make118 {119 get { return _make; }120 set { _make = value; }121 }122 123 public string Model124 {125 get { return _model; }126 set { _model = value; }127 }128 129 public int Year130 {131 get { return _year; }132 set { _year = value; }133 }134 }135 }导出数据到Excel的操作在事件toolStripButton1_Click中，代码的第49行和50行是将DataGridView当前选中的行复制到系统剪贴板中，62行将剪贴板中的内容粘贴到Excel默认Sheet的A1单元格中。Excel会自动格式化将粘贴的内容，如下图。
使用剪贴板导出数据过程比较简单，省去了对Excel对象的遍历和操作，缺点是无法对导出的数据进行格式和样式的设置。如果需要对导出的数据进行样式设置，可以尝试使用Openhttp://www.cnblogs.com/jaxu/archive/2012/05/11/2495316.html。修改样式的操作可以在文件流中完成。
TAG:
