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();//设置新文档页边距0newDocument.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有帮助，创作不易，帮忙三连☆☆☆。