添加标题到DocumentFormat.OpenXml.Spreadsheet

Question

只是想将工作表名称添加到中心页眉。

var headerFooter = worksheetPart1.Worksheet.Elements < HeaderFooter>().FirstOrDefault();
if(headerFooter == null)
headerFooter = new HeaderFooter();
headerFooter.DifferentOddEven = false;
headerFooter.DifferentFirst = false;
var oddHeader = new OddHeader("&C&A");
headerFooter.Append(oddHeader);
worksheetPart1.Worksheet.Append(headerFooter);

我看遍了，尝试了不同的格式，总是得到一个错误打开Excel工作表。

Answer 1

页眉和页脚仅在页面布局视图、打印预览和打印页面中显示。如果你想定义一个头，你需要定义PageMargins,PageSetup,SheetDimension并添加一些命名空间声明。您的WorksheetPart应该生成如下所示:

private static void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
{
Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac xr xr2 xr3" } };
worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
worksheet1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
worksheet1.AddNamespaceDeclaration("xr2", "http://schemas.microsoft.com/office/spreadsheetml/2015/revision2");
worksheet1.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");
worksheet1.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{971DAD3B-06EC-4344-A9C0-EF4D3CB78B7F}"));
SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1" };
SheetViews sheetViews1 = new SheetViews();
SheetView sheetView1 = new SheetView() { TabSelected = true, View = SheetViewValues.PageLayout, ZoomScaleNormal = (UInt32Value)100U, WorkbookViewId = (UInt32Value)0U };
sheetViews1.Append(sheetView1);
SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };
SheetData sheetData1 = new SheetData();
PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
PageSetup pageSetup1 = new PageSetup() { PaperSize = (UInt32Value)9U, Orientation = OrientationValues.Portrait, Id = "rId1" };
HeaderFooter headerFooter1 = new HeaderFooter();
OddHeader oddHeader1 = new OddHeader();
oddHeader1.Text = "&CTest1";
headerFooter1.Append(oddHeader1);
worksheet1.Append(sheetDimension1);
worksheet1.Append(sheetViews1);
worksheet1.Append(sheetFormatProperties1);
worksheet1.Append(sheetData1);
worksheet1.Append(pageMargins1);
worksheet1.Append(pageSetup1);
worksheet1.Append(headerFooter1);
worksheetPart1.Worksheet = worksheet1;
}

，可以通过以下方式触发:

static void Main(string[] args)
{
CreateSpreadsheetWorkbook("Test.xlsx");
}
public static void CreateSpreadsheetWorkbook(string filepath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
GenerateWorksheetPart1Content(worksheetPart);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);

workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}