C#中DataGridView導出Excel的兩種方法
第一種是用數據流導出:
#region SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Execl files (*.xls)|*.xls"; saveFileDialog.FilterIndex = 0; saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = true; saveFileDialog.Title = "Export Excel File"; saveFileDialog.ShowDialog(); if (saveFileDialog.FileName == "") return; Stream myStream; myStream = saveFileDialog.OpenFile(); StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)); string str = ""; try { for (int i = 0; i < dataGridView1.ColumnCount; i++) { if (i > 0) { str += "\t"; } str += dataGridView1.Columns[i].HeaderText; } sw.WriteLine(str); for (int j = 0; j < dataGridView1.Rows.Count; j++) { string tempStr = ""; for (int k = 0; k < dataGridView1.Columns.Count; k++) { if (k > 0) { tempStr += "\t"; } tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString(); } sw.WriteLine(tempStr); } sw.Close(); myStream.Close(); } catch (Exception ex) { //MessageBox.Show(ex.ToString()); } finally { sw.Close(); myStream.Close(); } #endregion
這種方法的優點就是導出比較快,但是excel的表格裡面設置標題,字體樣式等都不能弄,因為你是用數據流直接導出為excel的,除非你能在數據流中設置這些樣式,這個我還沒這本事,哪位大哥會的教一下…嘿嘿
第二種方法是直接寫一個類,這個類直接操作EXCEL的:
using System; using System.Collections.Generic; using System.Windows.Forms; using System.Text; using System.Diagnostics; using System.IO; using Microsoft.Office.Interop.Excel; namespace Excel { public class Export { /// <summary> /// DataGridView導出Excel /// </summary> /// <param name="strCaption">Excel文件中的標題</param> /// <param name="myDGV">DataGridView 控件</param> /// <returns>0:成功;1:DataGridView中無記錄;2:Excel無法啟動;9999:異常錯誤</returns> public int ExportExcel(string strCaption, DataGridView myDGV, SaveFileDialog saveFileDialog) { int result = 9999; //保存 saveFileDialog.Filter = "Execl files (*.xls)|*.xls"; saveFileDialog.FilterIndex = 0; saveFileDialog.RestoreDirectory = true; //saveFileDialog.CreatePrompt = true; saveFileDialog.Title = "Export Excel File"; if ( saveFileDialog.ShowDialog()== DialogResult.OK) { if (saveFileDialog.FileName == "") { MessageBox.Show("請輸入保存文件名!"); saveFileDialog.ShowDialog(); } // 列索引,行索引,總列數,總行數 int ColIndex = 0; int RowIndex = 0; int ColCount = myDGV.ColumnCount; int RowCount = myDGV.RowCount; if (myDGV.RowCount == 0) { result = 1; } // 創建Excel對象 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (xlApp == null) { result = 2; } try { // 創建Excel工作薄 Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1]; // 設置標題 Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]); //標題所占的單元格數與DataGridView中的列數相同 range.MergeCells = true; xlApp.ActiveCell.FormulaR1C1 = strCaption; xlApp.ActiveCell.Font.Size = 20; xlApp.ActiveCell.Font.Bold = true; xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; // 創建緩存數據 object[,] objData = new object[RowCount + 1, ColCount]; //獲取列標題 foreach (DataGridViewColumn col in myDGV.Columns) { objData[RowIndex, ColIndex++] = col.HeaderText; } // 獲取數據 for (RowIndex = 1; RowIndex < RowCount; RowIndex++) { for (ColIndex = 0; ColIndex < ColCount; ColIndex++) { if (myDGV[ColIndex, RowIndex - 1].ValueType == typeof(string) || myDGV[ColIndex, RowIndex - 1].ValueType == typeof(DateTime))//這裡就是驗證DataGridView單元格中的類型,如果是string或是DataTime類型,則在放入緩存時在該內容前加入" "; { objData[RowIndex, ColIndex] = "" + myDGV[ColIndex, RowIndex - 1].Value; } else { objData[RowIndex, ColIndex] = myDGV[ColIndex, RowIndex - 1].Value; } } System.Windows.Forms.Application.DoEvents(); } // 寫入Excel range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount, ColCount]); range.Value2 = objData; xlBook.Saved = true; xlBook.SaveCopyAs(saveFileDialog.FileName); } catch (Exception err) { result = 9999; } finally { xlApp.Quit(); GC.Collect(); //強制回收 } //返回值 result = 0; } return result; } } }
這個優點是能設置樣式什麼的。缺點就是導出速度慢…
以上兩種方法都是參考瞭很多料的..寫在這裡以便於相互學習..
補充一下:用第二種方法導出excel會有格式方面的變化,比如身份證號碼按科學計算法導出瞭,不是按原先的模型
改進方法是在寫入excel時添加一個格式聲明:range.NumberFormatLocal = “@”;
如:// 寫入Excel
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, ColCount]); range.NumberFormatLocal = "@"; range.Value2 = objData;
到此這篇關於C#中DataGridView導出Excel的兩種方法的文章就介紹到這瞭,更多相關C# DataGridView導出Excel內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- 新手小白用C# winform 讀取Excel表的實現
- C#實現學生模塊的增刪改查
- 電腦贈送的office是永久的嗎 電腦永久office低價購買地址推薦
- excel快速提取部分數據怎麼操作 excel查找內容並提取出來的方法
- office辦公軟件有哪些 office辦公軟件匯總