通过剪贴板将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的方法。代码如下：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.saveFileDialog1.Filter = "Excel Workbook|*.xlsx|Excel Macro-Enabled Workbook|*.xlsm|Excel 97-2003 Workbook|*.xls";
            this.saveFileDialog1.FileName = "demo.xlsx";

            LoadData();
        }

        private void LoadData()
        {
            BindingList<Car> cars = new BindingList<Car>();

            cars.Add(new Car("Ford", "Mustang", 1967));
            cars.Add(new Car("Shelby AC", "Cobra", 1965));
            cars.Add(new Car("Chevrolet", "Corvette Sting Ray", 1965));

            this.dataGridView1.DataSource = cars;
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            if (this.saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                filePath = this.saveFileDialog1.FileName;
            }
            else
            {
                return;
            }

            this.dataGridView1.SelectAll();
            Clipboard.SetDataObject(this.dataGridView1.GetClipboardContent());

            Excel.Application objExcel = null;
            Excel.Workbook objWorkbook = null;
            Excel.Worksheet objsheet = null;
            try
            {
                objExcel = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
                objExcel.Visible = false;

                objExcel.get_Range("A1", System.Type.Missing).PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
                objsheet.Name = "Demo";  
                //Set table properties  
                objExcel.Cells.EntireColumn.AutoFit();//auto column width  
                objExcel.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                objExcel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                objExcel.ErrorCheckingOptions.BackgroundChecking = false;  

                //save file
                objWorkbook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            finally
            {
                //Dispose the Excel related objects 
                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;
                GC.Collect(); // force final cleanup.
            }
        }
    }

    public class Car
    {
        private string _make;
        private string _model;
        private int _year;

        public Car(string make, string model, int year)
        {
            _make = make;
            _model = model;
            _year = year;
        }

        public string Make
        {
            get { return _make; }
            set { _make = value; }
        }

        public string Model
        {
            get { return _model; }
            set { _model = value; }
        }

        public int Year
        {
            get { return _year; }
            set { _year = value; }
        }
    }
}

　　导出数据到Excel的操作在事件toolStripButton1_Click中，代码的第49行和50行是将DataGridView当前选中的行复制到系统剪贴板中，62行将剪贴板中的内容粘贴到Excel默认Sheet的A1单元格中。Excel会自动格式化将粘贴的内容，如下图。

　　使用剪贴板导出数据过程比较简单，省去了对Excel对象的遍历和操作，缺点是无法对导出的数据进行格式和样式的设置。如果需要对导出的数据进行样式设置，可以尝试使用OpenXML的方式来修改Excel文件的样式，参考这篇文章http://www.cnblogs.com/jaxu/archive/2012/05/11/2495316.html。修改样式的操作可以在文件流中完成。

代码下载：WindowsFormsApplication1.zip