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

2020-01-17 23:28:10

字体：大中小

来源：转载

供稿：网友

将DataGridView中的数据导出到Excel中有许多方法，常见的方法是使用Office COM组件将DataGridView中的数据循环复制到Excel Cell对象中，然后再保存整个Excel Workbook。但是如果数据量太大，例如上万行数据或者有多个Excel Sheet需要同时导出，效率会比较低。可以尝试使用异步操作或多线程的方式来解决UI死锁的问题。

这里介绍一种直接通过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; } 
} 
} 
} 