代码之家 › 专栏 › 技术社区 › John

使用OpenXML生成电子表格,并尝试在自定义页脚中使用&[Page]of&[Pages]

epplus openxml c#

John · 技术社区 · 7 年前

这似乎是一个简单的字符串转义问题,但我无法找出正确的格式,也无法找到在Excel页脚中使用字段示例的文档。

所涉财产为:

xlSheet.HeaderFooter.OddFooter.LeftAlignedText = "&[Page] of &[Pages]";

以下是为其他上下文生成工作表的较大代码片段:

 using (ExcelPackage pckExport = new ExcelPackage())
            {
                ExcelWorksheet xlSheet = pckExport.Workbook.Worksheets.Add(SystemEnum.GetEnumDescription(typeof(SystemEnum.Reports), SystemEnum.Reports.GroomingCalendarReport.GetHashCode()));
                try
                {
                    int miRow = 1;
                    string pageHeader = "Calendar Report";
                    xlSheet.Cells.Style.Font.Size = 9;
                    xlSheet.Cells.Style.Font.Name = "Times New Roman";
                    DateTime reportDate = Convert.ToDateTime(rptDate);
                    ////set the report header
                    xlSheet.HeaderFooter.OddHeader.CenteredText = "&\"Times New Roman,Bold\"&12" + "\n" + pageHeader + "\n" + " &11for&12 " + reportDate.Date.DayOfWeek.ToString() + " , " + reportDate +
                                                                   "\n" + Location;
                    xlSheet.HeaderFooter.OddFooter.LeftAlignedText = "&[Page] of &[Pages]";
                    xlSheet.HeaderFooter.OddFooter.RightAlignedText = DateTime.Now.ToShortDateString();

生成并打开电子表格并导航到“文件\打印”菜单后,您将看到页脚不太正确:

下面是页脚的抓屏: Page of Pages

如果我转到页面设置并选择自定义页脚,则对话框将显示格式正确的页脚信息,格式为(&第页,共页(&P);[页面]如果我单击OK并让对话框关闭打印预览,则正确显示页脚。

以下是“自定义页脚”对话框的屏幕抓图: Custom Footer Dialog Box

我尝试过使用显式字符串并使用&[第页]但它们都返回预览中的第页]和自定义页脚框中发送的文本。

我已经用尽了在这方面寻求帮助的想法。

有人能帮忙吗?

谢谢

1 回复 | 直到 7 年前

Ernie S 7 年前

如果你看看 ExcelHeaderFooter 你会看到很多非常有用的 const 让你实现你想要的:

var wb = pck.Workbook;
var ws = wb.Worksheets.Add("Sheet1");
ws.Cells[1, 1].Value = "Test";

var footer = $"Page {ExcelHeaderFooter.PageNumber} of {ExcelHeaderFooter.NumberOfPages}";
ws.HeaderFooter.EvenFooter.CenteredText = footer;
ws.HeaderFooter.OddFooter.CenteredText = footer;

#region Static Properties
/// <summary>
/// The code for "current page #"
/// </summary>
public const string PageNumber = @"&P";
/// <summary>
/// The code for "total pages"
/// </summary>
public const string NumberOfPages = @"&N";
/// <summary>
/// The code for "text font color"
/// RGB Color is specified as RRGGBB
/// Theme Color is specified as TTSNN where TT is the theme color Id, S is either "+" or "-" of the tint/shade value, NN is the tint/shade value.
/// </summary>
public const string FontColor = @"&K";
/// <summary>
/// The code for "sheet tab name"
/// </summary>
public const string SheetName = @"&A";
/// <summary>
/// The code for "this workbook's file path"
/// </summary>
public const string FilePath = @"&Z";
/// <summary>
/// The code for "this workbook's file name"
/// </summary>
public const string FileName = @"&F";
/// <summary>
/// The code for "date"
/// </summary>
public const string CurrentDate = @"&D";
/// <summary>
/// The code for "time"
/// </summary>
public const string CurrentTime = @"&T";
/// <summary>
/// The code for "picture as background"
/// </summary>
public const string Image = @"&G";
/// <summary>
/// The code for "outline style"
/// </summary>
public const string OutlineStyle = @"&O";
/// <summary>
/// The code for "shadow style"
/// </summary>
public const string ShadowStyle = @"&H";
#endregion