//原文出处：http://www.yongfa365.com/Item/DataGridViewToExcel.html
  1 #region DataGridView数据显示到Excel
  2 /// <summary> 
  3 /// 打开Excel并将DataGridView控件中数据导出到Excel
  4 /// </summary> 
  5 /// <param name="dgv">DataGridView对象 </param> 
  6 /// <param name="isShowExcle">是否显示Excel界面 </param> 
  7 /// <remarks>
  8 /// add com "Microsoft Excel 11.0 Object Library"
  9 /// using Excel=Microsoft.Office.Interop.Excel;
 10 /// </remarks>
 11 /// <returns> </returns> 
 12 public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
 13 {
 14     if (dgv.Rows.Count == 0)
 15         return false;
 16     //建立Excel对象 
 17     Excel.Application excel = new Excel.Application();
 18     excel.Application.Workbooks.Add(true);
 19     excel.Visible = isShowExcle;
 20     //生成字段名称 
 21     for (int i = 0; i < dgv.ColumnCount; i++)
 22     {
 23         excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
 24     }
 25     //填充数据 
 26     for (int i = 0; i < dgv.RowCount - 1; i++)
 27     {
 28         for (int j = 0; j < dgv.ColumnCount; j++)
 29         {
 30             if (dgv[j, i].ValueType == typeof(string))
 31             {
 32                 excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
 33             }
 34             else
 35             {
 36                 excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
 37             }
 38         }
 39     }
 40     return true;
 41 }
 42 #endregion 
 43 
 44 #region DateGridView导出到csv格式的Excel
 45 /// <summary>
 46 /// 常用方法，列之间加\t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。
 47 /// </summary>
 48 /// <remarks>
 49 /// using System.IO;
 50 /// </remarks>
 51 /// <param name="dgv"></param>
 52 private void DataGridViewToExcel(DataGridView dgv)
 53 {
 54     SaveFileDialog dlg = new SaveFileDialog();
 55     dlg.Filter = "Execl files (*.xls)|*.xls";
 56     dlg.FilterIndex = 0;
 57     dlg.RestoreDirectory = true;
 58     dlg.CreatePrompt = true;
 59     dlg.Title = "保存为Excel文件";
 60 
 61     if (dlg.ShowDialog() == DialogResult.OK)
 62     {
 63         Stream myStream;
 64         myStream = dlg.OpenFile();
 65         StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
 66         string columnTitle = "";
 67         try
 68         {
 69             //写入列标题
 70             for (int i = 0; i < dgv.ColumnCount; i++)
 71             {
 72                 if (i > 0)
 73                 {
 74                     columnTitle += "\t";
 75                 }
 76                 columnTitle += dgv.Columns[i].HeaderText;
 77             }
 78             sw.WriteLine(columnTitle);
 79 
 80             //写入列内容
 81             for (int j = 0; j < dgv.Rows.Count; j++)
 82             {
 83                 string columnValue = "";
 84                 for (int k = 0; k < dgv.Columns.Count; k++)
 85                 {
 86                     if (k > 0)
 87                     {
 88                         columnValue += "\t";
 89                     }
 90                     if (dgv.Rows[j].Cells[k].Value == null)
 91                         columnValue += "";
 92                     else
 93                         columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
 94                 }
 95                 sw.WriteLine(columnValue);
 96             }
 97             sw.Close();
 98             myStream.Close();
 99         }
100         catch (Exception e)
101         {
102             MessageBox.Show(e.ToString());
103         }
104         finally
105         {
106             sw.Close();
107             myStream.Close();
108         }
109     }
110 } 
111 #endregion
112 
113 #region DataGridView导出到Excel，有一定的判断性
114 /// <summary> 
115 ///方法，导出DataGridView中的数据到Excel文件 
116 /// </summary> 
117 /// <remarks>
118 /// add com "Microsoft Excel 11.0 Object Library"
119 /// using Excel=Microsoft.Office.Interop.Excel;
120 /// using System.Reflection;
121 /// </remarks>
122 /// <param name= "dgv"> DataGridView </param> 
123 public static void DataGridViewToExcel(DataGridView dgv)
124 {
125 
126 
127     #region   验证可操作性
128 
129     //申明保存对话框 
130     SaveFileDialog dlg = new SaveFileDialog();
131     //默然文件后缀 
132     dlg.DefaultExt = "xls ";
133     //文件后缀列表 
134     dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
135     //默然路径是系统当前路径 
136     dlg.InitialDirectory = Directory.GetCurrentDirectory();
137     //打开保存对话框 
138     if (dlg.ShowDialog() == DialogResult.Cancel) return;
139     //返回文件路径 
140     string fileNameString = dlg.FileName;
141     //验证strFileName是否为空或值无效 
142     if (fileNameString.Trim() == " ")
143     { return; }
144     //定义表格内数据的行数和列数 
145     int rowscount = dgv.Rows.Count;
146     int colscount = dgv.Columns.Count;
147     //行数必须大于0 
148     if (rowscount <= 0)
149     {
150         MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
151         return;
152     }
153 
154     //列数必须大于0 
155     if (colscount <= 0)
156     {
157         MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
158         return;
159     }
160 
161     //行数不可以大于65536 
162     if (rowscount > 65536)
163     {
164         MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
165         return;
166     }
167 
168     //列数不可以大于255 
169     if (colscount > 255)
170     {
171         MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
172         return;
173     }
174 
175     //验证以fileNameString命名的文件是否存在，如果存在删除它 
176     FileInfo file = new FileInfo(fileNameString);
177     if (file.Exists)
178     {
179         try
180         {
181             file.Delete();
182         }
183         catch (Exception error)
184         {
185             MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
186             return;
187         }
188     }
189     #endregion
190     Excel.Application objExcel = null;
191     Excel.Workbook objWorkbook = null;
192     Excel.Worksheet objsheet = null;
193     try
194     {
195         //申明对象 
196         objExcel = new Microsoft.Office.Interop.Excel.Application();
197         objWorkbook = objExcel.Workbooks.Add(Missing.Value);
198         objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
199         //设置EXCEL不可见 
200         objExcel.Visible = false;
201 
202         //向Excel中写入表格的表头 
203         int displayColumnsCount = 1;
204         for (int i = 0; i <= dgv.ColumnCount - 1; i++)
205         {
206             if (dgv.Columns[i].Visible == true)
207             {
208                 objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
209                 displayColumnsCount++;
210             }
211         }
212         //设置进度条 
213         //tempProgressBar.Refresh(); 
214         //tempProgressBar.Visible   =   true; 
215         //tempProgressBar.Minimum=1; 
216         //tempProgressBar.Maximum=dgv.RowCount; 
217         //tempProgressBar.Step=1; 
218         //向Excel中逐行逐列写入表格中的数据 
219         for (int row = 0; row <= dgv.RowCount - 1; row++)
220         {
221             //tempProgressBar.PerformStep(); 
222 
223             displayColumnsCount = 1;
224             for (int col = 0; col < colscount; col++)
225             {
226                 if (dgv.Columns[col].Visible == true)
227                 {
228                     try
229                     {
230                         objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
231                         displayColumnsCount++;
232                     }
233                     catch (Exception)
234                     {
235 
236                     }
237 
238                 }
239             }
240         }
241         //隐藏进度条 
242         //tempProgressBar.Visible   =   false; 
243         //保存文件 
244         objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
245                 Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
246                 Missing.Value, Missing.Value);
247     }
248     catch (Exception error)
249     {
250         MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
251         return;
252     }
253     finally
254     {
255         //关闭Excel应用 
256         if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
257         if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
258         if (objExcel != null) objExcel.Quit();
259 
260         objsheet = null;
261         objWorkbook = null;
262         objExcel = null;
263     }
264     MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
265 
266 }
267 
268 #endregion