怎么將DataGridView中的數(shù)據(jù)導(dǎo)出到Excel文件中

怎么將DataGridView中的數(shù)據(jù)導(dǎo)出到Excel文件中？針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

將DataGridView中的數(shù)據(jù)導(dǎo)出到Excel中有許多方法，常見(jiàn)的方法是使用Office COM組件將DataGridView中的數(shù)據(jù)循環(huán)復(fù)制到Excel Cell對(duì)象中，然后再保存整個(gè)Excel Workbook。但是如果數(shù)據(jù)量太大，例如上萬(wàn)行數(shù)據(jù)或者有多個(gè)Excel Sheet需要同時(shí)導(dǎo)出，效率會(huì)比較低?？梢試L試使用異步操作或多線程的方式來(lái)解決UI死鎖的問(wèn)題。

這里介紹一種直接通過(guò)Windows剪貼板將數(shù)據(jù)從DataGridView導(dǎo)出到Excel的方法。代碼如下：

復(fù)制代碼 代碼如下:

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

導(dǎo)出數(shù)據(jù)到Excel的操作在事件toolStripButton1_Click中，代碼的第49行和50行是將DataGridView當(dāng)前選中的行復(fù)制到系統(tǒng)剪貼板中，62行將剪貼板中的內(nèi)容粘貼到Excel默認(rèn)Sheet的A1單元格中。Excel會(huì)自動(dòng)格式化將粘貼的內(nèi)容，如下圖。


使用剪貼板導(dǎo)出數(shù)據(jù)過(guò)程比較簡(jiǎn)單，省去了對(duì)Excel對(duì)象的遍歷和操作，缺點(diǎn)是無(wú)法對(duì)導(dǎo)出的數(shù)據(jù)進(jìn)行格式和樣式的設(shè)置。如果需要對(duì)導(dǎo)出的數(shù)據(jù)進(jìn)行樣式設(shè)置，可以嘗試使用OpenXML的方式來(lái)修改Excel文件的樣式，

關(guān)于怎么將DataGridView中的數(shù)據(jù)導(dǎo)出到Excel文件中問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。