DevExpress GridControl GridView 导出到 Excel 类

说明：

1>GridView 导出到 Excel (如果分页，只导出当前页数据)

2>GridView 导出到 Excel

3>方法2可以参考DataTable 导出到 Excel

自定义类如下：

 #region GridView 导出到 Excel   Method First
        /// <summary>
        ///  GridView 导出到 Excel   (如果分页，只导出当前页数据)
        /// </summary>
        /// <param name="gv">GridView</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="isOpen">导出完成后是否打开Excel   如果不打开会自动杀死当前Excel进程</param>
        /// <returns>错误信息，如果为空，说明用户取消导入</returns>
        public static string GvToExcel(DevExpress.XtraGrid.Views.Grid.GridView gv, string sheetName, bool isOpen)
        {
            //返回值
            string str = "";
            string fileName = "";
            //保存对话框，是否保存
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "(*.xls,*.xlsx)|*.xls;*.xlsx";
            if (sfd.ShowDialog() != DialogResult.OK)
            {
                str = "";
                return str;
            }
            fileName = sfd.FileName;

            if (gv == null)
            {
                str = "GridView 不能为空！";
                return str;
            }
            if (gv.RowCount < 1)
            {
                str = "没有记录可以导出";
                return str;
            }

            //获取列名、列标题  放到List集合中
            System.Collections.ArrayList listCaption = new System.Collections.ArrayList();
            System.Collections.ArrayList listFieldName = new System.Collections.ArrayList();
            for (int i = 0; i < gv.Columns.Count; i++)
            {
                if (gv.Columns[i].Visible)
                {
                    listCaption.Add(gv.Columns[i].Caption);
                    listFieldName.Add(gv.Columns[i].FieldName);
                }
            }

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Worksheet wSheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
            Microsoft.Office.Interop.Excel.Range range;
            if (sheetName != null && sheetName.Length > 0)
            {
                wSheet.Name = sheetName;
            }
            else
            {
                wSheet.Name = gv.Name;
            }

            try
            {
                //写标题
                for (int j = 0; j < listCaption.Count; j++)
                {
                    excel.Cells[1, j + 1] = listCaption[j];
                    range = (Microsoft.Office.Interop.Excel.Range)wSheet.Cells[1, j + 1];
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                }
                //写内容
                for (int m = 0; m < gv.RowCount; m++)
                {
                    for (int n = 0; n < listCaption.Count; n++)
                    {
                        excel.Cells[m + 2, n + 1] = gv.GetRowCellValue(m, listFieldName[n].ToString()) is DBNull ? "" : gv.GetRowCellValue(m, listFieldName[n].ToString()).ToString();
                    }
                }

                wSheet.SaveAs(fileName,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                str = "导出成功！共导出 " + gv.RowCount + " 条数据";
                return str;
            }
            catch
            {
                str = "导出异常，请重试";
                return str;
            }
            finally
            {
                if (isOpen)
                {
                    //前台显示给用户
                    excel.Visible = true;
                    wSheet = null;
                    excel = null;
                }
                else
                {
                    excel.Visible = false;
                    excel.Quit();
                    // excel = null;
                    //杀死Excel 进程
                    KillProcess(excel);
                }
                GC.Collect();
            }
        }
        #endregion

        #region GridView 导出到 Excel   Method Second
        /// <summary>
        /// GridView 导出到 Excel 
        /// </summary>
        /// <param name="dt">GridControl.DataSource ==dt</param>
        /// <param name="gv">GridView 名称</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="isOpen">导出完成后是否打开Excel   如果不打开会自动杀死当前Excel进程</param>
        /// <returns>错误信息，如果为空，说明用户取消导入</returns>
        public static string GvToExcel(DataTable dt, DevExpress.XtraGrid.Views.Grid.GridView gv, string sheetName, bool isOpen) {
            //返回值
            string str = "";           
            
            //执行方法,如果datatable为空，导出当前页数据，否则，导出所有页数据
            if(dt==null){
                   str=GvToExcel(gv,sheetName,isOpen);
            }else{
                    if (gv == null)
                    {
                        str = "GridView 不能为空！";
                        return str;
                    }
                    if (gv.RowCount < 1)
                    {
                        str = "没有记录可以导出";
                        return str;
                    }

                    //获取列名、列标题  放到List集合中
                    System.Collections.ArrayList listCaption = new System.Collections.ArrayList();
                    System.Collections.ArrayList listFieldName = new System.Collections.ArrayList();
                    for (int i = 0; i < gv.Columns.Count; i++)
                    {
                        if (gv.Columns[i].Visible)
                        {
                            listCaption.Add(gv.Columns[i].Caption);
                            listFieldName.Add(gv.Columns[i].FieldName);
                        }
                    }
                    //将ArrayList 转换成 一维数组
                    string[] captions = (string[])listCaption.ToArray(typeof(string));
                    string[] fieldName =(string[]) listFieldName.ToArray(typeof(string));
                    //推荐使用List<>  因为ArrayList转换时会装、拆箱，影响效率
                    //List<string> list = new List<string>();
                    //list.Add("aa");
                    //string[] aaa =list.ToArray();

                    str=DtToExcel(dt,captions,fieldName,sheetName,isOpen);
            }           
           return str;
        }
        #endregion

调用（举例）：

 string str =Functionjsj.GvToExcel(gridView1, "学生信息", true);
            if (str.Length > 0) {
                MessageBox.Show(str, System.Windows.Forms.Application.ProductName);
                return;
            }

//说明  导出的表为 dt表中gridview1显示的列的集合

 string str = Functionjsj.GvToExcel(dt,gridView1, "学生信息", true);
            if (str.Length > 0)
            {
                MessageBox.Show(str, System.Windows.Forms.Application.ProductName);
                return;
            }

 

版权声明：本文为博主原创文章，未经博主允许不得转载。