DataGridView导出到Excel文件

  针对上一篇复制DataGridView选中单元格复制到Excel中，将DataGridView中数据全部导出到Excel文件可能经要使用的更多。这是在练习一个项目里所写的，完全通过测试。
需要注意的是，在导出到Excel文件，一定要杀Excel的进程。杀进程的语法如下:

 

//杀Excel后台进程
  [DllImport("User32.dll", CharSet = CharSet.Auto)]
  public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int Pid);

   IntPtr intp=new IntPtr(xlApp.Hwnd); //得到excel的句柄，前题是excel对象已经创建

    int id=0;
   GetWindowThreadProcessId(intp, out id);
   System.Diagnostics.Process P = System.Diagnostics.Process.GetProcessById(id);
   P.Kill();

1,下面是主角DataGridView导出到Excel文件，如下:

    /// <summary>
            /// DataGridView中的所有数据导出到Excel
            /// </summary>
            /// <param name="ExportDgv">需要导出的DataGridView</param>
            /// <param name="fullFileName">导出后的文件全路径</param>
            /// <param name="isOpenFile">文件是否打开</param>
            /// <returns></returns>
            public bool OutputFileToExcel(DataGridView ExportDgv, string fullFileName, bool isOpenFile)
            {
                int id=0;
                bool ExportSuccess = false;

                int RowIndex = 0;
                int ColIndex = 0;
                //数据验证
                if (ExportDgv==null)
                {
                    return false;
                }
                if (ExportDgv.ColumnCount==0|| ExportDgv.RowCount==0)
                {
                    return false;
                }
                if (ExportDgv.RowCount > 65536 || ExportDgv.ColumnCount > 256)
                {
                    return false;
                }

               
                //有效的行数、列数
                int rowCount = ExportDgv.Rows.GetRowCount(DataGridViewElementStates.Visible);
                int colCount = ExportDgv.Columns.GetColumnCount(DataGridViewElementStates.Visible);

                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    return false;
                }
                //创建Excel工作薄
                Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
                //创建Excel工作表
                Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];//第1个工作表

                Microsoft.Office.Interop.Excel.Range range;

                IntPtr intp=new IntPtr(xlApp.Hwnd);

                //定义一个缓存，用来存放DataGridView的数据
                object[,] objData = new object[rowCount + 1, colCount];
                

                //1.存放DataGridView的列题行,隐藏列不要
                for (int i = 0; i < ExportDgv.Columns.Count; i++)
                {
                    if (ExportDgv.Columns[i].Visible)
                    {
                        objData[RowIndex, ColIndex++] = ExportDgv.Columns[i].HeaderText;//将列标题放入缓存中
                        
                        //设置成文本型
                        if (ExportDgv.Columns[i].ValueType.ToString()=="System.String")
                        {
                            range = xlSheet.get_Range(xlApp.Cells[1, ColIndex], xlApp.Cells[rowCount + 1, ColIndex]);
                            range.NumberFormat = "@";
                        }
                    }
                }
                //2.存放DataGridView中的数据，
                for (int i = 1; i < rowCount; i++)
                {
                    RowIndex++;
                    ColIndex = 0;
                    for (int j = 0; j < ExportDgv.Columns.Count; j++)
                    {
                        if (ExportDgv.Columns[j].Visible)
                        {
                            objData[RowIndex, ColIndex++] = ExportDgv[j, RowIndex - 1].Value;
                        }
                    }
                    System.Windows.Forms.Application.DoEvents();
                }
              
                //3.写入到Excel
                range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowCount + 1, colCount]);
                range.Value2 = objData;

                //保存
                try
                {
                    //设置文本表格的属性
                    xlApp.Cells.EntireColumn.AutoFit();//自动列宽
                    xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                    xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
                    xlApp.ErrorCheckingOptions.BackgroundChecking = false;

                    //设置列头
                    range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);//第一行数据，列标题
                    range.Font.Bold = true;
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                    xlBook.Save();
                    xlBook.SaveCopyAs(fullFileName);
                    ExportSuccess = true;

                }
                catch
                {
                    ExportSuccess = false;
                }
                finally
                {
                    //释放资源,关闭进程
                    xlApp.Quit();
                    GetWindowThreadProcessId(intp, out id);
                    System.Diagnostics.Process P = System.Diagnostics.Process.GetProcessById(id);
                    P.Kill();
                }
                if (isOpenFile) //打开文件,此处调用了项目中的对文件操作的一个组件                {
                    HR.Class.IO.FileOperat FP = new HR.Class.IO.FileOperat();
                    FP.OpenFile(fullFileName);
                }
                return ExportSuccess;
            }

2,方法的调用

     void 另存为Excel文件_Click(object sender, EventArgs e)
        {
            if (this.RowCount==0||this.ColumnCount==0)
            {
                return;
            }

            SaveFileDialog SFD = new SaveFileDialog();
            SFD.Filter = "Microsoft Excel电子文档(*.xls)|*.xls";
            if (SFD.ShowDialog()==DialogResult.OK)
            {
                this.Cursor = Cursors.WaitCursor;
                HR.Control.DataGridViewOperation.Operate.OutputOperate OO = new HR.Control.DataGridViewOperation.Operate.OutputOperate();
                OO.OutputFileToExcel(this, SFD.FileName, false);
                GC.Collect();
                this.Cursor = Cursors.Default;
            }
    

        }

 void 输出到Excel文件ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            HR.Control.DataGridViewOperation.Operate.OutputOperate OO = new HR.Control.DataGridViewOperation.Operate.OutputOperate();
            OO.OutputFileToExcel(this, System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\temp.xls", true);
        }