DataGridView匯出到Excel的三個方法

原文出處：http://www.yongfa365.com/Item/DataGridViewToExcel.html

#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