Using the Clipboard to export data from DataGridView to excel_ practical tips

There are many ways to export data from DataGridView to Excel by using the Office COM component to recycle data in DataGridView into an Excel cell object, and then save the entire Excel workbook. But if the amount of data is too large, such as tens of thousands of rows of data or multiple Excel sheet need to be exported at the same time, the efficiency is lower. You can try to solve the UI deadlock problem by using asynchronous operations or multithreading.

Here is a way to export data from DataGridView to Excel directly through the Windows Clipboard. The code is as follows:

Copy Code code as follows:

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 do, 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;}
}
}
}

Export data to Excel in event Toolstripbutton1_click, line 49th and line 50 of the code are to copy the currently selected row of DataGridView to the system Clipboard. 62 to paste the contents of the Clipboard into the A1 cell of the default sheet in Excel. Excel automatically formats the content that will be pasted, as shown in the following figure.


The process of exporting data using the Clipboard is simpler, eliminating the traversal and manipulation of Excel objects, with the disadvantage that you cannot format and style the exported data. If you need to make style settings for the exported data, you can try to modify the style of the Excel file by using the OpenXML method.