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

饮食贵在节,读书贵在精,锻炼贵在恒,节饮食养胃,多读书养胆,喜运动延生!人生需要有目标,有追求,然有所以。
将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; }
}
}
}

导出数据到Excel的操作在事件toolStripButton1_Click中,代码的第49行和50行是将DataGridView当前选中的行复制到系统剪贴板中,62行将剪贴板中的内容粘贴到Excel默认Sheet的A1单元格中。Excel会自动格式化将粘贴的内容,如下图。
使用剪贴板导出数据过程比较简单,省去了对Excel对象的遍历和操作,缺点是无法对导出的数据进行格式和样式的设置。如果需要对导出的数据进行样式设置,可以尝试使用OpenXML的方式来修改Excel文件的样式,

以上就是通过剪贴板实现将DataGridView中的数据导出到Excel。珍惜自己的过去,满意自己的现在,乐观自己的未来。更多关于通过剪贴板实现将DataGridView中的数据导出到Excel请关注haodaima.com其它相关文章!

您可能有感兴趣的文章
ASP.NET中Response.BufferOutput属性的使用技巧

ASP.NET轻量级MVC框架Nancy的基本用法

使用grpcui测试ASP.NET core的gRPC服务

ASP.NET Core中的对象池介绍

.NET集成ORM框架HiSql