//原文出处：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
1 #region DataGridView导出到Excel解决打开时报后缀名不一致的问题
2 /// <summary>
3 /// DataGridView导出至Excel,解决问题:打开Excel文件格式与扩展名指定格式不一致
4 /// </summary>
5 /// <param name="dataGridView">数据源表格</param>
6 /// <param name="isShowExcle">导出时是否显示excel界面</param>
7 /// <returns></returns>
8 public static bool DcExcel(DataGridView dataGridView, bool isShowExcle = true)
9 {
10 int FormatNum;//保存excel文件的格式
11 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
12 string excelVersion = excel.Version;//获取你使用的excel 的版本号
13
14 //声明保存对话框
15 SaveFileDialog saveFileDialog = new SaveFileDialog();
16 //默然文件后缀
17 saveFileDialog.DefaultExt = "xls";
18
19 if (Convert.ToDouble(excelVersion) < 12)//You use Excel 97-2003
20 {
21 FormatNum = -4143;
22 //文件后缀列表
23 saveFileDialog.Filter = "Excel(*.xls)|*.xls";
24 }
25 else//you use excel 2007 or later
26 {
27 FormatNum = 56;
28 //文件后缀列表
29 saveFileDialog.Filter = "Excel(*.xls)|*.xls|Excel(2007-2016)(*.xlsx)|*.xlsx";
30 }
31 Form fr = dataGridView.Parent as Form;
32 if (fr != null)//默认文件名
33 {
34 saveFileDialog.FileName = fr.Text;
35 }
36 //默然路径是系统当前路径
37 saveFileDialog.InitialDirectory = Directory.GetCurrentDirectory();
38 //打开保存对话框
39 if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
40 return false;
41 //返回文件路径
42 string fileName = saveFileDialog.FileName;
43 if (string.IsNullOrEmpty(fileName.Trim()))
44 { return false; }
45 if (dataGridView.Rows.Count == 0)
46 return false;
47 //建立Excel对象
48
49 var objWorkbook = excel.Application.Workbooks.Add(true);
50 excel.Visible = isShowExcle;
51 //生成字段名称
52 for (int i = 0; i < dataGridView.ColumnCount; i++)
53 {
54 excel.Cells[1, i + 1] = dataGridView.Columns[i].HeaderText;
55 excel.Cells[1, i + 1].Font.Bold = true;
56 }
57 //填充数据
58 for (int i = 0; i < dataGridView.RowCount - 1; i++)
59 {
60 for (int j = 0; j < dataGridView.ColumnCount; j++)
61 {
62 if (dataGridView[j, i].ValueType == typeof(string))
63 {
64 excel.Cells[i + 2, j + 1] = "'" + dataGridView[j, i].Value.ToString();
65 }
66 else
67 {
68 excel.Cells[i + 2, j + 1] = dataGridView[j, i].Value.ToString();
69 }
70 }
71 }
72 //Excel.XlFileFormat.xlOpenXMLWorkbook（.xlsx）
73 //Excel.XlFileFormat.xlExcel8（Excel97 - 2003, .xls）
74 //判断excel文件的保存格式是xls还是xlsx
75 var format = fileName.EndsWith(".xls") ? Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8 : Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook;
76 objWorkbook.SaveAs(fileName, format, Missing.Value, Missing.Value, Missing.Value,
77 Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
78 Missing.Value, Missing.Value);
79 return true;
80 }
81 #endregion
