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有帮助，创作不易，帮忙三连☆☆☆。