打印时将行设置为顶部重复-Open XML和Excel

Question

我正在尝试使用Open XML SDK获取xlsx文件的前4行，并在每个打印页面顶部重复显示。 我使用SSRS执行Web服务生成文件流。然后需要修改几个布局和页面设置（边距，页面大小，缩放等），然后将文件交付给用户，因为我的用户对Excel几乎没有了解。 我需要调整的所有其他设置都正常工作，因此我已从此示例中删除了所有该代码。 在尝试使用下面的代码设置重复页面标题时，它会无错执行。 但是，当我尝试执行代码后打开文件时，我收到一个损坏的文件消息，它无法打开。 你有什么想法？这让我发疯了！

using (SpreadsheetDocument xl = SpreadsheetDocument.Open("C:\\" + filename, true))
{
WorkbookPart wbp = xl.WorkbookPart;

//-----------------------------------------
// repeat rows at top when printing
//-----------------------------------------
DefinedNames dn = new DefinedNames();
wbp.Workbook.Append(dn);
wbp.Workbook.Save();

DefinedNames definedNames = wbp.Workbook.Descendants<DefinedNames>().FirstOrDefault();
DefinedName n1 = new DefinedName() { Name = "_xlnm.Print_Titles", LocalSheetId = (UInt32Value)0U };
n1.Text = "alpha_sort_nc!$1:$4";
definedNames.Append(n1);
wbp.Workbook.Save();

xl.Close();
}

- Duncan

Answer 1

更新: @Duncan - 在您的评论后，我试图找出文件损坏的根本原因。结果发现SpreadsheetPinterSettings是文档中的二进制数据。即使在电子表格中添加了已定义名称_xlnm.Print_Titles，在打印/打印预览期间它也不起作用。

看来使用Open XML方法处理电子表格打印机设置并不是很有帮助。

从Open XML SDK Productivity工具反映的代码: 在MS Excel中手动创建打印标题。

// Adds child parts and generates content of the specified part.
public void CreateSpreadsheetPrinterSettingsPart(SpreadsheetPrinterSettingsPart part)
{
    GeneratePartContent(part);

}

// Generates content of part.
private void GeneratePartContent(SpreadsheetPrinterSettingsPart part)
{
    System.IO.Stream data = GetBinaryDataStream(partData);
    part.FeedData(data);
    data.Close();
}

#region Binary Data
private string partData = "MQAyADcALgAwAC4AMAAuADEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEEAAbcAHweXf+AAwEAAH/qCm8IZAABAA8A/P8CAAEA/P8DAAEATABlAHQAdABlAHIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQAAAAAAAAABAAAAAgAAAAABAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFBSSVbiMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAYAAAAAAAQJxAnECcAABAnAAAAAAAAAABYAywGAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAgAAAAAAAABQGBAAXEsDAGhDBAAAAAAAAAAAAAEAAQAAAAAAAAAAAAAAAAAAAAAA8q/OjRwAAAAAAAAAAAABAAAAAAD/AP8AAAABAAAAAAACAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAAAAAAAAAAAAAAAAABYAwAAU01USgAAAAAQAEgDSABQACAAQwBvAGwAbwByACAATABhAHMAZQByAEoAZQB0ACAAMgA4ADAAMAAgAFMAZQByAGkAZQBzACAAUABTAAAASFBPcmllbnRSb3RhdGUxODAARmFsc2UAUG9zdFNjcmlwdEN1c3RvbVBhZ2VTaXplAEZhbHNlAER1cGxleABOb25lAElucHV0U2xvdABBdXRvAEhQRHVwbGljYXRlSm9iTmFtZU92ZXJyaWRlAFNXRlcAUGFnZVNpemUATEVUVEVSAFBhZ2VSZWdpb24AAExlYWRpbmdFZGdlAABNZWRpYVR5cGUAQXV0bwBDb2xsYXRlAFRydWUAT3V0cHV0QmluAEF1dG8AU3RhcGxlTG9jYXRpb24ATm9uZQBIUENvdmVycwBPdGhlcl9QYWdlcwBUZXh0QXNCbGFjawBGYWxzZQBKQ0xSZXNvbHV0aW9uADYwMGRwaQBIUENvbnN1bWVyQ3VzdG9tUGFwZXIAVHJ1ZQBQcmludFF1YWxpdHlHcm91cABQUUdyb3VwXzEASlJIREluc3RhbGxlZABKUkhET2ZmAEhQRG9jUHJvcFJlc291cmNlRGF0YQBIUENhYkZpbGVOYW1lAEpSQ29uc3RyYWludHMASlJDSERGdWxsAEhQQ29sb3JNb2RlAENPTE9SX01PREUASFBQSkxFbmNvZGluZwBVVEY4AEhQSm9iQWNjb3VudGluZwBIUEpPQkFDQ1RfSk9CQUNOVABIUEJvcm5PbkRhdGUASFBCT0QASFBYTUxGaWxlVXNlZABocG1jcGRwcy54bWwAUFNBbGlnbm1lbnRGaWxlAEhQWkxTTEhOAEhQU21hcnRIdWIASW5ldF9TSURfMjYzX0JJRF81MTRfSElEXzI2NQBIUENvbG9yQXNHcmF5AEZhbHNlAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAUBgAAElVUEgQABEAAAAAAAAAAAAAAAAAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQABAGQAAQABAAMAAgAAAAEAAAACAAAATABlAHQAdABlAHIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAH/qCm8IAAD///////////////8BAAAABgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAAAAAAAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAgAFsAbgBvAG4AZQBdAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACAAWwBuAG8AbgBlAF0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA0AAAAUAAAAAAAAAABAAAAAAAAAMDAwAAAAAAAwMDAAAAAAAAAAAAAAAAAAAAAAAAAfwAAAQAAAGQAAAAAAAAAAAAAAAAAAAAAAIA/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABKAEUARQBWAEEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAAAAAAAAAAAAAABAAAADwAAAAABAAAAAAAADwAAAAABAAAAAAAA/////wAAAAAAAAAADwAAAAABAAAPAAAAAAEAAAAAAAAAAAAAAAAAAAAAAAA0CAAANAgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAGQAAAABAAAATQBpAGMAcgBvAHMAbwBmAHQAIABFAHgAYwBlAGwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIAAAD/////RVhDRUwuRVhFAAAAAAAAAAEAAAABAAAAAQAAAAEAAAABAAAAAQAAAAEAAAABAAAAAQAAAAEAAAABAAAAAQAAAAEAAAABAAAAAQAAAAEAAAABAAAAAQAAAAEAAAABAAAAAQAAAAEAAAABAAAAAQAAAAEAAAAPAAAAAAEAAA8AAAAAAQAADwAAAAABAAAPAAAAAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACAAIAAAAAAQwA6AFwAUAByAG8AZwByAGEAbQAgAEYAaQBsAGUAcwBcAE0AaQBjAHIAbwBzAG8AZgB0ACAATwBmAGYAaQBjAGUAXABPAGYAZgBpAGMAZQAxADQAXABFAFgAQwBFAEwALgBFAFgARQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=";

private System.IO.Stream GetBinaryDataStream(string base64String)
{
    return new System.IO.MemoryStream(System.Convert.FromBase64String(base64String));
}

#endregion

另一种方法 [如果您有兴趣的话 :)]：由于二进制数据部分，使用Interop的代码片段非常完美地解决了问题！

Excel.Application xlApp = new Excel.Application();
Excel._Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:/Users/jeeva/sample-excel-files/SampleExcel.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];

xlWorksheet.PageSetup.PrintTitleRows = "$1:$1";

xlWorkbook.Save();
xlWorkbook.Close();

---

之前的内容：

这三行都是不必要的。

---

DefinedNames dn = new DefinedNames();
wbp.Workbook.Append(dn);
wbp.Workbook.Save();

要设置打印标题，DefinedName文本应该按照以下格式："工作表名称!行范围或列范围"。

例如：行范围：Worksheet1!$1:$1 和 列范围：Worksheet1!$A:$A。

using (SpreadsheetDocument xl = SpreadsheetDocument.Open(@"C:/Users/jeeva/sample-excel-files/SampleExcel.xlsx", true))
{             
    WorkbookPart wbp = xl.WorkbookPart;

    // Input for this program
    string name = "_xlnm.Print_Titles";
    string comment = "this is Print Titles for Sheet1";
    string text = "Sheet1!$1:$1";

    if (wbp.Workbook.DefinedNames == null) 
    {
        wbp.Workbook.DefinedNames = new DefinedNames();
    }                

    bool bFound = false;
    foreach (DefinedName d in wbp.Workbook.DefinedNames)
    {
        // if found overwrite existing defined name
        if (d.Name.Value.Equals(name, StringComparison.InvariantCultureIgnoreCase)) 
        {
            bFound = true;
            d.Text = text;
            d.Comment = comment;
            break;
        }
    }

    if (!bFound) // if not found, add one
    {
        wbp.Workbook.DefinedNames.Append(getDefinedName(name, text, comment));
    }

    wbp.Workbook.Save();
    xl.Close();
}

private static DefinedName getDefinedName(string Name, string Text, string Comment)
{
    DefinedName dn = new DefinedName();
    dn.Text = Text;
    dn.Name = Name;
    dn.Comment = Comment;
    dn.LocalSheetId = (UInt32Value)1U;
    dn.Xlm = true; // since its a special schema name
}

- jeevatkm

---

感谢您的回复，jeevatkm。我尝试了您的建议，但问题是如果我删除不必要的三行，则definedNames对象为空，因此当我尝试将printTitles对象附加到definedNames集合时，程序会崩溃。然而，我的方法也不起作用，因为当我尝试打开电子表格时，它会损坏。还有其他想法吗？ - Duncan

@Duncan - 我更新了帖子以进行空值检查，并需要澄清一下，您是否已根据您的Excel文件更新了行范围“alpha_sort_nc！$A$1：$F$1”的值，然后是否出现文件损坏？请告诉我。 - jeevatkm

是的，我尝试按照您建议的更改行范围。问题归结为这样。如果文件已经有一个定义的名称集合并且您尝试编辑它，则一切都正常工作。但是，当您尝试添加集合并将重复的行定义名称附加到其中时，Excel会出现“Excel发现无法读取的字符”消息。 - Duncan

@Duncan - 我已经对文件损坏问题进行了分析，并在帖子中更新了分析结果、替代方案和我尝试过的代码片段。希望这能有所帮助！ - jeevatkm

非常感谢jeevatkm。我会在几个小时后尝试这个，当我有时间的时候。我很感激你的时间和努力。 - Duncan

Answer 2

以下代码块解决了我的问题：

string fileName = AppDomain.CurrentDomain.BaseDirectory + "/Content/Master-License-Export.xlsx";
                    var spreadsheetDocument = SpreadsheetDocument.Open(fileName, true);           
                    WorkbookPart wbp = spreadsheetDocument.WorkbookPart;

                    UInt32 localSheetId = 0;    //LocalSheetIds are 0-indexed.

                    var definedName = new DefinedName
                    {
                        Name = "_xlnm.Print_Titles",
                        LocalSheetId = localSheetId,
                        Text = String.Format("\'{0}\'!${1}:${2}", "Master-License-Export", 1, 4)
                    };

                    if (wbp.Workbook.DefinedNames == null)
                    {
                        var definedNamesCol = new DefinedNames();
                        wbp.Workbook.Append(definedNamesCol);
                    }

                    wbp.Workbook.DefinedNames.Append(definedName);

                    wbp.Workbook.Save();
                    spreadsheetDocument.Close(); 

- Manveer Singh

Answer 3

这可以通过ClosedXML实现。

Worksheet.PageSetup.SetRowsToRepeatAtTop(1, 5);

- Justin Obney