DevExpress控件导出Excel

实现思路是把传入的DataSet 遍历成多个DataTable ，创建多个GridControl 控件存放每个DataTable ，再把每个GridControl 放入PrintableComponentLink 里，最后放入我们的link 里，用link.ExportToXls()方法导出excel表格。

  private void ExportToExcel(DataSet dataSet)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog()
            {
                Title = "导出Excel",
                Filter = "Excel文件(*.xlsx)|*.xlsx|Excel文件(*.xls)|*.xls"
            };

            DialogResult dialogResult = saveFileDialog.ShowDialog();
            if (dialogResult != DialogResult.OK)
                return;

            string FileName = saveFileDialog.FileName;
            
            string extension = Path.GetExtension(FileName);//获取拓展名
            
            DevExpress.XtraPrinting.PrintingSystem ps = new DevExpress.XtraPrinting.PrintingSystem();
           
            DevExpress.XtraPrintingLinks.CompositeLink link = new DevExpress.XtraPrintingLinks.CompositeLink(ps);
            int i = 0;           
            foreach (DataTable dt in dataSet.Tables)
            {
                GridControl gridControl = new GridControl() { DataSource = dt };
                this.panel1.Controls.Add(gridControl);//需要把控件放到窗体上，不然没有效果。
                GridView gridView = new GridView();
                gridControl.MainView = gridView;
                gridView.OptionsPrint.AutoWidth = false;   
            	gridControl.Visible = false;
            	
                PrintableComponentLink plink = new PrintableComponentLink { Component = gridControl };               
                link.Links.Add(plink);
                
            }
          
            link.CreatePageForEachLink();//一个table是一个sheet
             if (extension.ToUpper() == ".XLSX")
            {
                XlsxExportOptions op = new XlsxExportOptions();
                op.SheetName = "2020-".ToString();
                op.ExportMode = XlsxExportMode.SingleFilePageByPage;
                link.ExportToXlsx(FileName, op);
            }
            else if (extension.ToUpper() == ".XLS")
            {
                XlsExportOptions op = new XlsExportOptions();
                op.SheetName = "2020-".ToString();
                op.ExportMode = XlsExportMode.SingleFilePageByPage;
                link.ExportToXls(FileName, op);
            }
        }

参考代码：

  		/// <summary>
        /// DevExpress控件通用导出Excel,支持多个控件同时导出在同一个Sheet表或者分不同工作薄
        /// eg:ExportToXlsx("test",true,"控件",gridControl1,gridControl2);
        /// 将gridControl1和gridControl2的数据一同导出到同一个文件不同的工作薄
        /// eg:ExportToXlsx("test",false,"",gridControl1,gridControl2);
        /// 将gridControl1和gridControl2的数据一同导出到同一个文件同一个的工作薄
        /// <param name="title">文件名</param>
        /// <param name="isPageForEachLink">多个打印控件是否分多个工作薄显示</param>
        /// <param name="sheetName">工作薄名称</param>
        /// <param name="printables">控件集 eg:GridControl,PivotGridControl,TreeList,ChartControl...</param>
        public static void ExportToExcel(string title, bool isPageForEachLink, string sheetName, params DevExpress.XtraPrinting.IPrintable[] printables)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog()
            {
                FileName = title,
                Title = "导出Excel",
                Filter = "Excel文件(*.xlsx)|*.xlsx|Excel文件(*.xls)|*.xls"
            };
            DialogResult dialogResult = saveFileDialog.ShowDialog();
            if (dialogResult != DialogResult.OK)
                return;
            string FileName = saveFileDialog.FileName;
            DevExpress.XtraPrintingLinks.CompositeLink link = new DevExpress.XtraPrintingLinks.CompositeLink(new DevExpress.XtraPrinting.PrintingSystem());
            foreach (var item in printables)
            {
                var plink = new DevExpress.XtraPrinting.PrintableComponentLink() { Component = item };
                link.Links.Add(plink);
            }
            if (isPageForEachLink)//15.1 的Xls不支持这个功能，15.2未知
                link.CreatePageForEachLink();
            if (string.IsNullOrEmpty(sheetName)) sheetName = "Sheet";//默认工作薄名称
            try
            {
                int count = 1;
                //在重复名称后加（序号）
                while (System.IO.File.Exists(FileName))
                {
                    if (FileName.Contains(")."))
                    {
                        int start = FileName.LastIndexOf("(");
                        int end = FileName.LastIndexOf(").") - FileName.LastIndexOf("(") + 2;
                        FileName = FileName.Replace(FileName.Substring(start, end), string.Format("({0}).", count));
                    }
                    else
                    {
                        FileName = FileName.Replace(".", string.Format("({0}).", count));
                    }
                    count++;
                }
                if (FileName.LastIndexOf(".xlsx") >= FileName.Length - 5)
                {
                    DevExpress.XtraPrinting.XlsxExportOptions options = new DevExpress.XtraPrinting.XlsxExportOptions() { SheetName = sheetName };
                    if (isPageForEachLink)
                        options.ExportMode = DevExpress.XtraPrinting.XlsxExportMode.SingleFilePageByPage;
                    link.ExportToXlsx(FileName, options);
                }
                else
                {
                    DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions() { SheetName = sheetName };
                    if (isPageForEachLink) //15.Xls没有这个属性，15.2未知
                        options.ExportMode = DevExpress.XtraPrinting.XlsExportMode.SingleFilePageByPage;
                    link.ExportToXls(FileName, options);
                }
                if (DevExpress.XtraEditors.XtraMessageBox.Show("保存成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                    System.Diagnostics.Process.Start(FileName);//打开指定路径下的文件
            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message);
            }
        }