VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > temp > C#教程 >
  • 通过剪贴板将DataGridView中的数据导出到Excel

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

  这里介绍一种直接通过Windows剪贴板将数据从DataGridView导出到Excel的方法。代码如下:

复制代码
  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Windows.Forms;
  9 using Excel = Microsoft.Office.Interop.Excel;
 10 using System.Reflection;
 11 using Microsoft.Office.Interop.Excel;
 12 
 13 namespace WindowsFormsApplication1
 14 {
 15     public partial class Form1 : Form
 16     {
 17         public Form1()
 18         {
 19             InitializeComponent();
 20             this.saveFileDialog1.Filter = "Excel Workbook|*.xlsx|Excel Macro-Enabled Workbook|*.xlsm|Excel 97-2003 Workbook|*.xls";
 21             this.saveFileDialog1.FileName = "demo.xlsx";
 22 
 23             LoadData();
 24         }
 25 
 26         private void LoadData()
 27         {
 28             BindingList<Car> cars = new BindingList<Car>();
 29 
 30             cars.Add(new Car("Ford", "Mustang", 1967));
 31             cars.Add(new Car("Shelby AC", "Cobra", 1965));
 32             cars.Add(new Car("Chevrolet", "Corvette Sting Ray", 1965));
 33 
 34             this.dataGridView1.DataSource = cars;
 35         }
 36 
 37         private void toolStripButton1_Click(object sender, EventArgs e)
 38         {
 39             string filePath = string.Empty;
 40             if (this.saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
 41             {
 42                 filePath = this.saveFileDialog1.FileName;
 43             }
 44             else
 45             {
 46                 return;
 47             }
 48 
 49             this.dataGridView1.SelectAll();
 50             Clipboard.SetDataObject(this.dataGridView1.GetClipboardContent());
 51 
 52             Excel.Application objExcel = null;
 53             Excel.Workbook objWorkbook = null;
 54             Excel.Worksheet objsheet = null;
 55             try
 56             {
 57                 objExcel = new Microsoft.Office.Interop.Excel.Application();
 58                 objWorkbook = objExcel.Workbooks.Add(Missing.Value);
 59                 objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
 60                 objExcel.Visible = false;
 61 
 62                 objExcel.get_Range("A1", System.Type.Missing).PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
 63                 objsheet.Name = "Demo";  
 64                 //Set table properties  
 65                 objExcel.Cells.EntireColumn.AutoFit();//auto column width  
 66                 objExcel.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
 67                 objExcel.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
 68                 objExcel.ErrorCheckingOptions.BackgroundChecking = false;  
 69 
 70                 //save file
 71                 objWorkbook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
 72                         Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
 73                         Missing.Value, Missing.Value);
 74             }
 75             catch (Exception error)
 76             {
 77                 MessageBox.Show(error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
 78                 return;
 79             }
 80             finally
 81             {
 82                 //Dispose the Excel related objects 
 83                 if (objWorkbook != null)
 84                 {
 85                     objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
 86                 }
 87                 if (objExcel.Workbooks != null)
 88                 {
 89                     objExcel.Workbooks.Close();
 90                 }
 91                 if (objExcel != null)
 92                 {
 93                     objExcel.Quit();
 94                 }
 95 
 96                 objsheet = null;
 97                 objWorkbook = null;
 98                 objExcel = null;
 99                 GC.Collect(); // force final cleanup.
100             }
101         }
102     }
103 
104     public class Car
105     {
106         private string _make;
107         private string _model;
108         private int _year;
109 
110         public Car(string make, string model, int year)
111         {
112             _make = make;
113             _model = model;
114             _year = year;
115         }
116 
117         public string Make
118         {
119             get { return _make; }
120             set { _make = value; }
121         }
122 
123         public string Model
124         {
125             get { return _model; }
126             set { _model = value; }
127         }
128 
129         public int Year
130         {
131             get { return _year; }
132             set { _year = value; }
133         }
134     }
135 }
复制代码

  导出数据到Excel的操作在事件toolStripButton1_Click中,代码的第49行和50行是将DataGridView当前选中的行复制到系统剪贴板中,62行将剪贴板中的内容粘贴到Excel默认Sheet的A1单元格中。Excel会自动格式化将粘贴的内容,如下图。

  使用剪贴板导出数据过程比较简单,省去了对Excel对象的遍历和操作,缺点是无法对导出的数据进行格式和样式的设置。如果需要对导出的数据进行样式设置,可以尝试使用OpenXML的方式来修改Excel文件的样式,参考这篇文章http://www.cnblogs.com/jaxu/archive/2012/05/11/2495316.html。修改样式的操作可以在文件流中完成。

代码下载:http://files.cnblogs.com/jaxu/WindowsFormsApplication1.zip

 出处:https://www.cnblogs.com/jaxu/archive/2012/07/24/2605857.html

 


相关教程