通过剪贴板实现将DataGridView中的数据导出到Excel
通过剪贴板实现将DataGridView中的数据导出到Excel
发布时间:2017-01-07 来源:查字典编辑
摘要:将DataGridView中的数据导出到Excel中有许多方法,常见的方法是使用OfficeCOM组件将DataGridView中的数据循环...

将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会自动格式化将粘贴的内容,如下图。

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

使用剪贴板导出数据过程比较简单,省去了对Excel对象的遍历和操作,缺点是无法对导出的数据进行格式和样式的设置。如果需要对导出的数据进行样式设置,可以尝试使用OpenXML的方式来修改Excel文件的样式,

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新asp.net教程学习
热门asp.net教程学习
编程开发子分类