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
#region DataGridView資料顯示到Excel/// <summary> /// 打開Excel並將DataGridView控制項中資料匯出到Excel/// </summary> /// <param name="dgv">DataGridView物件 </param> /// <param name="isShowExcle">是否顯示Excel介面 </param> /// <remarks>/// add com "Microsoft Excel 11.0 Object Library"/// using Excel=Microsoft.Office.Interop.Excel;/// </remarks>/// <returns> </returns> public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle){ if (dgv.Rows.Count == 0) return false; //建立Excel物件 Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = isShowExcle; //生成欄位名稱 for (int i = 0; i < dgv.ColumnCount; i++) { excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText; } //填充資料 for (int i = 0; i < dgv.RowCount - 1; i++) { for (int j = 0; j < dgv.ColumnCount; j++) { if (dgv[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString(); } } } return true;}#endregion #region DateGridView匯出到csv格式的Excel/// <summary>/// 常用方法，列之間加\t，一行一行輸出，此檔其實是csv檔，不過預設可以當成Excel打開。/// </summary>/// <remarks>/// using System.IO;/// </remarks>/// <param name="dgv"></param>private void DataGridViewToExcel(DataGridView dgv){ SaveFileDialog dlg = new SaveFileDialog(); dlg.Filter = "Execl files (*.xls)|*.xls"; dlg.FilterIndex = 0; dlg.RestoreDirectory = true; dlg.CreatePrompt = true; dlg.Title = "保存為Excel檔"; if (dlg.ShowDialog() == DialogResult.OK) { Stream myStream; myStream = dlg.OpenFile(); StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)); string columnTitle = ""; try { //寫入列標題 for (int i = 0; i < dgv.ColumnCount; i++) { if (i > 0) { columnTitle += "\t"; } columnTitle += dgv.Columns[i].HeaderText; } sw.WriteLine(columnTitle); //寫入列內容 for (int j = 0; j < dgv.Rows.Count; j++) { string columnValue = ""; for (int k = 0; k < dgv.Columns.Count; k++) { if (k > 0) { columnValue += "\t"; } if (dgv.Rows[j].Cells[k].Value == null) columnValue += ""; else columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim(); } sw.WriteLine(columnValue); } sw.Close(); myStream.Close(); } catch (Exception e) { MessageBox.Show(e.ToString()); } finally { sw.Close(); myStream.Close(); } }} #endregion#region DataGridView匯出到Excel，有一定的判斷性/// <summary> ///方法，匯出DataGridView中的資料到Excel檔 /// </summary> /// <remarks>/// add com "Microsoft Excel 11.0 Object Library"/// using Excel=Microsoft.Office.Interop.Excel;/// using System.Reflection;/// </remarks>/// <param name= "dgv"> DataGridView </param> public static void DataGridViewToExcel(DataGridView dgv){ #region 驗證可操作性 //申明保存對話方塊 SaveFileDialog dlg = new SaveFileDialog(); //默然檔尾碼 dlg.DefaultExt = "xls "; //檔尾碼清單 dlg.Filter = "EXCEL檔(*.XLS)|*.xls "; //默然路徑是系統當前路徑 dlg.InitialDirectory = Directory.GetCurrentDirectory(); //打開保存對話方塊 if (dlg.ShowDialog() == DialogResult.Cancel) return; //返回檔路徑 string fileNameString = dlg.FileName; //驗證strFileName是否為空或值無效 if (fileNameString.Trim() == " ") { return; } //定義表格內資料的行數和列數 int rowscount = dgv.Rows.Count; int colscount = dgv.Columns.Count; //行數必須大於0 if (rowscount <= 0) { MessageBox.Show("沒有資料可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //列數必須大於0 if (colscount <= 0) { MessageBox.Show("沒有資料可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //行數不可以大於65536 if (rowscount > 65536) { MessageBox.Show("資料記錄數太多(最多不能超過65536條)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //列數不可以大於255 if (colscount > 255) { MessageBox.Show("資料記錄行數太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //驗證以fileNameString命名的檔是否存在，如果存在刪除它 FileInfo file = new FileInfo(fileNameString); if (file.Exists) { try { file.Delete(); } catch (Exception error) { MessageBox.Show(error.Message, "刪除失敗 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } } #endregion 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; //設置EXCEL不可見 objExcel.Visible = false; //向Excel中寫入表格的表頭 int displayColumnsCount = 1; for (int i = 0; i <= dgv.ColumnCount - 1; i++) { if (dgv.Columns[i].Visible == true) { objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim(); displayColumnsCount++; } } //設置進度條 //tempProgressBar.Refresh(); //tempProgressBar.Visible = true; //tempProgressBar.Minimum=1; //tempProgressBar.Maximum=dgv.RowCount; //tempProgressBar.Step=1; //向Excel中逐行逐列寫入表格中的資料 for (int row = 0; row <= dgv.RowCount - 1; row++) { //tempProgressBar.PerformStep(); displayColumnsCount = 1; for (int col = 0; col < colscount; col++) { if (dgv.Columns[col].Visible == true) { try { objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim(); displayColumnsCount++; } catch (Exception) { } } } }
//隱藏進度條 //tempProgressBar.Visible = false; //保存檔 objWorkbook.SaveAs(fileNameString, 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, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } finally { //關閉Excel應用 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; } MessageBox.Show(fileNameString + "\n\n匯出完畢! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);}#endregion
全站熱搜
留言列表
