通过剪贴板实现将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; }
}
}
}