DataGridView数据导出到Excel

1. /// <summary>   
2. /// 打开Excel并将DataGridView控件中数据导出到Excel   
3. /// </summary>   
4. /// <param name="dgv">DataGridView对象 </param>   
5. /// <param name="isShowExcle">是否显示Excel界面 </param>   
6. /// <remarks>   
7. /// add com "Microsoft Excel 11.0 Object Library"   
8. /// using Excel=Microsoft.Office.Interop.Excel;   
9. /// </remarks>   
10. /// <returns> </returns>   
11. public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)   
12. {   
13.     if (dgv.Rows.Count == 0)   
14.         return false;   
15.     //建立Excel对象   
16.      Excel.Application excel = new Excel.Application();   
17.      excel.Application.Workbooks.Add(true);   
18.      excel.Visible = isShowExcle;   
19.     //生成字段名称   
20.     for (int i = 0; i < dgv.ColumnCount; i++)   
21.      {   
22.          excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;   
23.      }   
24.     //填充数据   
25.     for (int i = 0; i < dgv.RowCount - 1; i++)   
26.      {   
27.         for (int j = 0; j < dgv.ColumnCount; j++)   
28.          {   
29.             if (dgv[j, i].ValueType == typeof(string))   
30.              {   
31.                  excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();   
32.              }   
33.             else  
34.              {   
35.                  excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();   
36.              }   
37.          }   
38.      }
39.    excel.ActiveWorkbook.SaveAs(this.exportSaveFileDialog.FileName, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
    excel.DisplayAlerts = false;
40. excel.Quit();
    return true;   
41. }

 

#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  

 

public static void ExportData(DataGridView srcDgv, string fileName)//导出数据,传入一个datagridview和一个文件路径

        {

            string type = fileName.Substring(fileName.IndexOf(".") + 1);//获得数据类型

            if (type.Equals("xls", StringComparison.CurrentCultureIgnoreCase))//Excel文档

            {

                Excel.Application excel = new Excel.Application();

                try

                {

                    excel.DisplayAlerts = false;

                    excel.Workbooks.Add(true);

                    excel.Visible = false;

 

                    for (int i = 0; i < srcDgv.Columns.Count; i++)//设置标题

                    {

                        excel.Cells[2, i + 1] = srcDgv.Columns[i].HeaderText;

                    }

 

                    for (int i = 0; i < srcDgv.Rows.Count; i++)//填充数据

                    {

                        for (int j = 0; j < srcDgv.Columns.Count; j++)

                        {

                            excel.Cells[i + 3, j + 1] = srcDgv[j, i].Value;

                        }

                    }

 

                    excel.Workbooks[1].SaveCopyAs(fileName);//保存

                }

                finally

                {

                    excel.Quit();

                }

                return;

            }

 

            //保存Word文件

 

            if (type.Equals("doc", StringComparison.CurrentCultureIgnoreCase))

            {

 

                object path = fileName;

 

                Object none = System.Reflection.Missing.Value;

                Word.Application wordApp = new Word.Application();

                Word.Document document = wordApp.Documents.Add(ref none, ref none, ref none, ref none);

                //建立表格

                Word.Table table = document.Tables.Add(document.Paragraphs.Last.Range, srcDgv.Rows.Count + 1, srcDgv.Columns.Count, ref none, ref none);

 

                try

                {

 

                    for (int i = 0; i < srcDgv.Columns.Count; i++)//设置标题

                    {

                        table.Cell(1, i + 1).Range.Text = srcDgv.Columns[i].HeaderText;

                    }

 

                    for (int i = 0; i < srcDgv.Rows.Count; i++)//填充数据

                    {

                        for (int j = 0; j < srcDgv.Columns.Count; j++)

                        {

 

                            table.Cell(i + 2, j + 1).Range.Text = srcDgv[j, i].Value.ToString();

                        }

                    }

 

                    document.SaveAs(ref path, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none);

                    document.Close(ref none, ref none, ref none);

 

                }

                finally

                {

                    wordApp.Quit(ref none, ref none, ref none);

                }

 

            }

 

        }