WinForm下DataGridView導出Excel的實現(xiàn)

1.說明:導出的效率說不上很高,但至少是可以接收的.參考網(wǎng)上很多高效導出Excel的方法,實現(xiàn)到時能夠?qū)崿F(xiàn)的,導出速度也很快,不過缺陷在與 不能很好的進行單元格的格式化,比如上圖中的"拼音碼"字段中的值"000000000012120",在導出后就顯示"12120",挺郁悶 的!o(∩_∩)o,廢話不說了,進入正題.......
2.首先添加Excel引用

3.實現(xiàn)代碼
/// <summary>
/// DataGridView導出Excel
/// </summary>
/// <param name="strCaption">Excel文件中的標題</param>
/// <param name="myDGV">DataGridView 控件</param>
/// <returns>0:成功;1ataGridView中無記錄;2:Excel無法啟動;9999:異常錯 誤</returns>
private int ExportExcel(string strCaption, DataGridView myDGV)
{
int result = 9999;
// 列索引，行索引，總列數(shù)，總行數(shù)
int ColIndex = 0;
int RowIndex = 0;
int ColCount = myDGV.ColumnCount;
int RowCount = myDGV.RowCount;

if (myDGV.RowCount == 0)
{
result = 1;
}

// 創(chuàng)建Excel對象
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
result = 2;
}
try
{
// 創(chuàng)建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];
// 設(shè)置標題
Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]); //標題所占的單元格數(shù)與DataGridView中的列數(shù)相同
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;
// 創(chuàng)建緩存數(shù)據(jù)
object[,] objData = new object[RowCount + 1, ColCount];
//獲取列標題
foreach (DataGridViewColumn col in myDGV.Columns)
{
objData[RowIndex, ColIndex++] = col.HeaderText;
}
// 獲取數(shù)據(jù)
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類型,則在放入緩 存時在該內(nèi)容前加入" ";
{
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("C:\\測試" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
//返回值
result = 0;
}
catch (Exception err)
{
result = 9999;
}
finally
{
xlApp.Quit();
GC.Collect(); //強制回收
}
return result;
}
4.調(diào)用方法(上圖中"生成Excel文件"按鈕的on
Click事件)
private void button4_Click(object sender, EventArgs e)
{
int result = this.ExportExcel("測試", this.dataGridView1); //this.dataGridView1ataGridView控件
MessageBox.Show(result.ToString());
}