Wpf和Winform使用devpress控件库导出Excel并调整报表样式

Wpf和Winform使用devpress控件库导出Excel并调整报表样式

背景

客户需求经常需要出各种报表，部分客户对报表的样式有要求。包括颜色、字体、分页等等。

代码

使用Datagridview导出excel调整样式

DevExpress.XtraGrid.Views.Grid.GridView gdv
#region GridView属性设置
//行号所在列的宽度
gdv.IndicatorWidth = 40;
//顶部面板 可用于分组
gdv.OptionsView.ShowGroupPanel = false;
//显示底部面板 可用于展示统计
gdv.OptionsView.ShowFooter = true;
//奇数行的效果设置是否可用
gdv.OptionsView.EnableAppearanceEvenRow = true;
//失去焦点时 是否保留行选中效果
gdv.OptionsSelection.EnableAppearanceHideSelection = false;
//是否显示焦点单元格样式
gdv.OptionsSelection.EnableAppearanceFocusedCell = false;
//只读
gdv.OptionsBehavior.ReadOnly = true;
//不可编辑 若设置不可编辑 会导致表格中增加的按钮的单击事件不可用
gdv.OptionsBehavior.Editable = false;
//行选中
gdv.FocusRectStyle = DevExpress.XtraGrid.Views.Grid.DrawFocusRectStyle.RowFocus;
//边框
//gdv.BorderStyle = DevExpress.XtraEditors.Controls.BorderStyles.NoBorder;
//关闭列右键菜单
gdv.OptionsMenu.EnableColumnMenu = false;
//列字体对齐方式
gdv.Appearance.HeaderPanel.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
//列字体设置
gdv.Appearance.HeaderPanel.Font = new System.Drawing.Font("微软雅黑", 14F, FontStyle.Bold, GraphicsUnit.Pixel);
//行字体对齐方式
gdv.Appearance.Row.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
//奇数行背景色
gdv.Appearance.EvenRow.BackColor = Color.FromArgb(228, 243, 255);
//焦点行背景色
gdv.Appearance.FocusedRow.BackColor = Color.FromArgb(0, 153, 255);
//焦点行字体颜色
gdv.Appearance.FocusedRow.ForeColor = Color.White;
//FooterPanel字体对齐方式
gdv.Appearance.FooterPanel.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
//行字体
gdv.Appearance.Row.Font = new System.Drawing.Font("微软雅黑", 14F, FontStyle.Regular, GraphicsUnit.Pixel);
//导出相关设置
gdv.AppearancePrint.Row.TextOptions.WordWrap = DevExpress.Utils.WordWrap.Wrap;
gdv.OptionsPrint.AutoWidth = false;
gdv.AppearancePrint.Row.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;

使用Devpress导出gridview到PDF

        /// <summary>
        /// pdf导出
        /// </summary>
        private void ExportPdf2()
        {
            string startDate = dtpStartDate.Value.ToString("yyyyMMdd");
            string endDate = dtpEndDate.Value.ToString("yyyyMMdd");
            string FileName = "结算单_" + startDate + "_" + endDate;
            IPrintable[] Futuresprintables = { gcFundprofit, gcStockHold, gcFutureHold, gcFutureDone, gcDelivery, gcExercise };
            ExportToPdf2(FileName, true, "结算单", Futuresprintables);
        }

        /// <summary>
        /// 导出为pdf
        /// </summary>
        /// <param name="title"></param>
        /// <param name="isPageForEachLink"></param>
        /// <param name="sheet"></param>
        /// <param name="printables"></param>
        public static void ExportToPdf2(string title, bool isPageForEachLink, string sheet, IPrintable[] printables)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog()
            {
                FileName = title,
                Title = "导出Excel",
                Filter = "Pdf文件(*.pdf)|*.pdf"//"Excel文件(*.xlsx)|*.xlsx|Excel文件(*.xls)|*.xls"
            };
            DialogResult dialogResult = saveFileDialog.ShowDialog();
            if (dialogResult == DialogResult.Cancel)
                return;
            CompositeLink link = new CompositeLink(new PrintingSystem());
            // Create a link that will print a control.
            foreach (var item in printables)
            {
                //DevExpress.XtraPrinting.PrintableComponentLink plink = new PrintableComponentLink() { Component = item };
                DevExpress.XtraPrinting.PrintableComponentLink plink = new PrintableComponentLink()
                {
                    PrintingSystemBase = new PrintingSystemBase(),
                    Component = item,
                    Landscape = true,
                    PaperKind = PaperKind.A4
                };
                plink.PrintingSystemBase.Graph.Font = new Font("SimSun", 6, FontStyle.Bold, GraphicsUnit.Millimeter, 134);
                link.Links.Add(plink);
            }
            PdfExportOptions options = new PdfExportOptions();

            link.ExportToPdf(saveFileDialog.FileName, options);
            if (DevExpress.XtraEditors.XtraMessageBox.Show("保存成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                System.Diagnostics.Process.Start(saveFileDialog.FileName);
        }

使用spire

static void Main(string[] args)
        {
            //加载PDF文档
            Spire.Pdf.PdfDocument sourceDocument = new Spire.Pdf.PdfDocument("d:\\1.pdf");
            //创建新PDF文档
            Spire.Pdf.PdfDocument newDocument = new Spire.Pdf.PdfDocument();
            //设置新文档页边距0
            newDocument.PageSettings.Margins.All = 0;
            //设置文档尺寸和源文件一样
            newDocument.PageSettings.Width = sourceDocument.Pages[0].Size.Width;
            newDocument.PageSettings.Height = sourceDocument.Pages[0].Size.Height;
            //删除第一页，破解水印
            newDocument.Pages.Add();
            newDocument.Pages.RemoveAt(0);
            //页面格式
            Spire.Pdf.Graphics.PdfTextLayout format = new Spire.Pdf.Graphics.PdfTextLayout();
            format.Break = Spire.Pdf.Graphics.PdfLayoutBreakType.FitPage;
            format.Layout = Spire.Pdf.Graphics.PdfLayoutType.Paginate;
            //将源文档每一页绘制到新文档
            foreach (Spire.Pdf.PdfPageBase sourcePage in sourceDocument.Pages)
            {
                //添加新页
                Spire.Pdf.PdfPageBase newPage = newDocument.Pages.Add();
                //创建绘制模板
                var template = sourcePage.CreateTemplate();
                //绘制源内容
                template.Draw(newPage, new PointF(0, 0), format);
                可以自由在新页绘制矩形、文字等信息
                //newPage.Canvas.DrawRectangle(Spire.Pdf.Graphics.PdfBrushes.White, new RectangleF(0, 0, 100, 100));
                //newPage.Canvas.DrawString("文字", new Spire.Pdf.Graphics.PdfFont(Spire.Pdf.Graphics.PdfFontFamily.Courier, 20f), Spire.Pdf.Graphics.PdfBrushes.White, new PointF(0, 0));
            }
 
            newDocument.SaveToFile("d:\\save.pdf");
        }

中文不显示问题 导出字体和dev默认不一致 修改appearanceprint的字体设置

使用devpress导出gridview到excel

        /// <summary>
        /// pdf导出
        /// </summary>
        private void ExportPdf2()
        {
            string startDate = dtpStartDate.Value.ToString("yyyyMMdd");
            string endDate = dtpEndDate.Value.ToString("yyyyMMdd");
            string FileName = "结算单_" + startDate + "_" + endDate;
            IPrintable[] Futuresprintables = { gcFundprofit, gcStockHold, gcFutureHold, gcFutureDone, gcDelivery, gcExercise };
            ExportToPdf2(FileName, true, "结算单", Futuresprintables);
        }

        /// <summary>
        /// 导出为pdf
        /// </summary>
        /// <param name="title"></param>
        /// <param name="isPageForEachLink"></param>
        /// <param name="sheet"></param>
        /// <param name="printables"></param>
        public static void ExportToPdf2(string title, bool isPageForEachLink, string sheet, IPrintable[] printables)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog()
            {
                FileName = title,
                Title = "导出Excel",
                Filter = "Pdf文件(*.pdf)|*.pdf"//"Excel文件(*.xlsx)|*.xlsx|Excel文件(*.xls)|*.xls"
            };
            DialogResult dialogResult = saveFileDialog.ShowDialog();
            if (dialogResult == DialogResult.Cancel)
                return;
            CompositeLink link = new CompositeLink(new PrintingSystem());
            // Create a link that will print a control.
            foreach (var item in printables)
            {
                //DevExpress.XtraPrinting.PrintableComponentLink plink = new PrintableComponentLink() { Component = item };
                DevExpress.XtraPrinting.PrintableComponentLink plink = new PrintableComponentLink()
                {
                    PrintingSystemBase = new PrintingSystemBase(),
                    Component = item,
                    Landscape = true,
                    PaperKind = PaperKind.A4
                };
                plink.PrintingSystemBase.Graph.Font = new Font("SimSun", 6, FontStyle.Bold, GraphicsUnit.Millimeter, 134);
                link.Links.Add(plink);
            }
            PdfExportOptions options = new PdfExportOptions();

            link.ExportToPdf(saveFileDialog.FileName, options);
//调整excel样式
             DevExpress.XtraSpreadsheet.SpreadsheetControl spreadsheetControl = new DevExpress.XtraSpreadsheet.SpreadsheetControl();
                DevExpress.Spreadsheet.IWorkbook workbook = (spreadsheetControl).Document;
                workbook.LoadDocument(FileName);
                foreach (var wksheet in workbook.Worksheets)
                {
                    int splitindex1 = dtFundprofit.Rows.Count + 3;
                    int splitindex2 = splitindex1+ dtStockHold.Rows.Count + 3+1;
                    int splitindex3 = splitindex2+ dtFutureHold.Rows.Count + 3+1;
                    int splitindex4 = splitindex3+ dtFutureDone.Rows.Count + 3+1;
                    int splitindex5 = splitindex4+ dtDelivery.Rows.Count + 3+1;
                    wksheet.Rows.Insert(splitindex1);
                    wksheet.Rows.Insert(splitindex2);
                    wksheet.Rows.Insert(splitindex3);
                    wksheet.Rows.Insert(splitindex4);
                    wksheet.Rows.Insert(splitindex5);
                   //wksheet.Cells.SetInsideBorders(Color.DarkGray, DevExpress.Spreadsheet.BorderLineStyle.None);
                   //wksheet.Cells.SetInsideBorders(Color.White, DevExpress.Spreadsheet.BorderLineStyle.Thin);
                   wksheet.GetUsedRange().Borders.SetAllBorders(Color.White, DevExpress.Spreadsheet.BorderLineStyle.Thin);
                    wksheet.GetUsedRange().Borders.SetOutsideBorders(Color.DarkGray, DevExpress.Spreadsheet.BorderLineStyle.Thick);
                    //new System.Drawing.Font("Microsoft Yahei", 14F, FontStyle.Regular, GraphicsUnit.Pixel);
                    //wksheet.Cells.Borders.SetAllBorders(Color.DarkGray, DevExpress.Spreadsheet.BorderLineStyle.None);
                    //wksheet.GetUsedRange().Borders.SetOutsideBorders(Color.DarkGray, DevExpress.Spreadsheet.BorderLineStyle.Thin);
                }
                workbook.SaveDocument(FileName);
            if (DevExpress.XtraEditors.XtraMessageBox.Show("保存成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                System.Diagnostics.Process.Start(saveFileDialog.FileName);
        }

---

希望对各位coder有帮助，创作不易，帮忙三连☆☆☆。