冻结 OpenXml SDK 2.0 for Excel 文档中的窗格

Question

我正在使用 OpenXml 生成 Excel 工作簿，并遵循 http:// /msdn.microsoft.com/en-us/library/cc850837.aspx

如果我可以冻结顶部窗格，那将非常有用，但我找不到方法来做到这一点。我意识到如果我使用 http://linedxml.codeplex.com/ 就可以做到这一点，但现在我想要坚持使用 OpenXml SDK

有什么想法吗？

Answer 1

我试图解决同样的问题，最终打开 Open XML SDK 2.0 Productivity Tool 并使用“比较文件...”功能来比较两个电子表格，一个带有冻结窗格，另一个没有。

当我这样做时，我得到的代码基本上看起来像这样：

WorkbookPart wbp = doc.WorkbookPart;
WorksheetPart wsp = wbp.WorksheetParts.First();

SheetViews sheetviews = wsp.Worksheet.GetFirstChild<SheetViews>();
SheetView sv = sheetviews.GetFirstChild<SheetView>();
Selection selection = sv.GetFirstChild<Selection>();
Pane pane = new Pane(){ VerticalSplit = 1D, TopLeftCell = "A2", ActivePane = PaneValues.BottomLeft, State = PaneStateValues.Frozen };
sv.InsertBefore(pane,selection);
selection.Pane = PaneValues.BottomLeft;

我将其添加到我的程序中，它似乎起到了作用。

Answer 2

您也可以添加选择：

WorkbookPart wbp = doc.WorkbookPart;
WorksheetPart wsp = wbp.WorksheetParts.First(); 

SheetViews sheetViews = wsp.Worksheet.GetFirstChild<SheetViews>();
SheetView sheetView = sheetViews.GetFirstChild<SheetView>();

Selection selection1 = new Selection() { Pane = PaneValues.BottomLeft };

Pane pane1 = new Pane() { VerticalSplit = 1D, TopLeftCell = "A2", ActivePane = PaneValues.BottomLeft, State = PaneStateValues.Frozen };

sheetView.Append(pane1);
sheetView.Append(selection1);

Answer 3

当我使用其他答案中提供的代码时，我不断收到 SheetViews 的空错误。我使用 SDK 快捷会议工具查看带有冻结窗格的 Excel 文档的代码，这帮助我创建了以下代码。我必须创建 SheetViews 和 SheetView 类的新实例并附加它们，而不是使用 GetFirstChild 方法。

这是代码。

WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();

WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();

SheetViews sheetViews = new SheetViews();
SheetView sheetView = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
Pane pane = new Pane() { ActivePane = PaneValues.BottomLeft, State = PaneStateValues.Frozen, TopLeftCell = "A2", VerticalSplit = 1D };
Selection selection = new Selection() { Pane = PaneValues.BottomLeft };
sheetView.Append(pane);
sheetView.Append(selection);
sheetViews.Append(sheetView);
worksheetPart.Worksheet.Append(sheetViews);

需要额外注意的是，在创建 SheetView 时，必须包含 TabSelected 和 WorkbookViewId 值，否则在打开文件时会收到一条错误消息，内容为“我们发现某些内容存在问题......”

此外，对于任何想要的人要冻结第一列而不是第一行，下面是一个示例。

var sheetViews = new SheetViews();
var sheetView = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
var pane = new Pane() { ActivePane = PaneValues.TopRight, HorizontalSplit = 1D, State = PaneStateValues.Frozen, TopLeftCell = "B1" };
var selection = new Selection() { Pane = PaneValues.TopRight };
sheetView.Append(pane);
sheetView.Append(selection);
sheetViews.Append(sheetView);

Answer 4

来自 03/02/2021 的反馈：

您只需在生成类中添加 Excel 文件的内容，如下：

Pane FrozeShutterLine1= new Pane() { VerticalSplit = 1D, TopLeftCell = "A2", ActivePane = PaneValues.BottomLeft, State = PaneStateValues.Frozen };

此行确实允许冻结上面一行的所有百叶窗...

没有固定百叶窗的电子表格的代码：

具有固定百叶窗的电子表格代码：

我分享此反馈是因为在找到如何操作之前我也搜索了一段时间。

Answer 5

MemoryStream documentStream = new ();
    SpreadsheetDocument document = SpreadsheetDocument.Create(documentStream, SpreadsheetDocumentType.Workbook);
    WorkbookPart workbookPart = document.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();

    WorkbookStylesPart stylePart = workbookPart.AddNewPart<WorkbookStylesPart>();
    stylePart.Stylesheet = new ScrapBuyplanStyleSheet().GenerateStyleSheet();
    stylePart.Stylesheet.Save();
    
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet();
    
    // Freeze Panes
    SheetViews sheetViews = new ();
    SheetView sheetView = new () {TabSelected = true, WorkbookViewId = (UInt32Value) 0U};
    Pane pane = new ()
    {
        ActivePane = PaneValues.TopRight,
        State = PaneStateValues.Frozen, 
        TopLeftCell = "B1", 
        //VerticalSplit = 1D,
        HorizontalSplit = 1D
    };
    Selection selection = new () {Pane = PaneValues.TopRight};
    sheetView.Append(pane);
    sheetView.Append(selection);
    sheetViews.Append(sheetView);
    worksheetPart.Worksheet.Append(sheetViews);

创建窗格时出现空指针的解决方案是在添加窗格之前创建工作表

Answer 6

大数据 OpenDocument Writer 示例

经过多次尝试和错误，并使用 这个示例 我发现 SheetViews 位于顶部的工作表内。在我的例子中，为了写入大量数据，我使用流式传输和写入器方法。我把它放在这里是因为这是谷歌为其他人找到我的地方。

冻结第一列示例

            using (var workbook = SpreadsheetDocument.Create(uniqueFilePath, SpreadsheetDocumentType.Workbook))
            {
                List<OpenXmlAttribute> attributeList;
                OpenXmlWriter writer;
                workbook.AddWorkbookPart();
                var workSheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                writer = OpenXmlWriter.Create(workSheetPart);
                /* <Worksheet> */
                writer.WriteStartElement(new Worksheet());

                /*<SheetViews>*/
                writer.WriteStartElement(new SheetViews());

                var tabSelectedAtt    = new OpenXmlAttribute("tabSelected", null, 1.ToString());
                var workBookViewIdAtt = new OpenXmlAttribute("workbookViewId", null, 0.ToString());

                writer.WriteStartElement(new SheetView(), new List <OpenXmlAttribute>()
                {
                    tabSelectedAtt, workBookViewIdAtt
                });

                var xSplitAtt      = new OpenXmlAttribute("xSplit", null, 1.ToString());
                var topLeftCellAtt = new OpenXmlAttribute("topLeftCell", null, "B1");
                var activePane     = new OpenXmlAttribute("activePane", null, "topRight");
                var state          = new OpenXmlAttribute("state", null, "frozen");

                writer.WriteStartElement(new Pane(), new List <OpenXmlAttribute>()
                {
                    xSplitAtt, topLeftCellAtt, activePane, state
                });
                writer.WriteEndElement();
                writer.WriteEndElement();
                writer.WriteEndElement();
                /*</SheetViews>*/


                /* <Columns> */
                writer.WriteStartElement(new Columns());
                int columnsCounter = 1;
                var columnData = reader.GetColumnSchema();
                foreach (var c in columnData) {
                    /* <Column> */
                    attributeList = new List<OpenXmlAttribute>();
                    attributeList.Add(new OpenXmlAttribute("min", null, columnsCounter.ToString()));
                    attributeList.Add(new OpenXmlAttribute("max", null, columnsCounter.ToString()));
                    attributeList.Add(new OpenXmlAttribute("width", null, ((150 /*Width*/ / 6) + 2).ToString())); //attributeList.Add(new OpenXmlAttribute("width", null, ((col.Width / 6) + 2).ToString()));                            
                    writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Column(), attributeList);
                    writer.WriteEndElement();                            
                    columnsCounter++;
                    /* </Column> */
                }
                writer.WriteEndElement();
                /* </Columns> */

                /* <SheetData> */
                writer.WriteStartElement(new SheetData());

                //...

冻结第一行示例

使用 (var workbook = SpreadsheetDocument.Create(uniqueFilePath, SpreadsheetDocumentType.Workbook))
{
列表属性列表；
OpenXmlWriter 编写器；
工作簿.AddWorkbookPart();
var workSheetPart = workbook.WorkbookPart.AddNewPart();
writer = OpenXmlWriter.Create(workSheetPart);
/* */
writer.WriteStartElement(new Worksheet());

/*<SheetViews>*/
writer.WriteStartElement(new SheetViews());

var tabSelectedAtt    = new OpenXmlAttribute("tabSelected", null, 1.ToString());
var workBookViewIdAtt = new OpenXmlAttribute("workbookViewId", null, 0.ToString());

writer.WriteStartElement(new SheetView(), new List <OpenXmlAttribute>()
{
    tabSelectedAtt, workBookViewIdAtt
});

var xSplitAtt      = new OpenXmlAttribute("ySplit", null, 1.ToString());
var topLeftCellAtt = new OpenXmlAttribute("topLeftCell", null, "A2");
var activePane     = new OpenXmlAttribute("activePane", null, "bottomLeft");
var state          = new OpenXmlAttribute("state", null, "frozen");



writer.WriteStartElement(new Pane(), new List <OpenXmlAttribute>()
{
    xSplitAtt, topLeftCellAtt, activePane, state
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndElement();

/*</SheetViews>*/


/* <Columns> */
writer.WriteStartElement(new Columns());
int columnsCounter = 1;
var columnData = reader.GetColumnSchema();
foreach (var c in columnData) {
    /* <Column> */
    attributeList = new List<OpenXmlAttribute>();
    attributeList.Add(new OpenXmlAttribute("min", null, columnsCounter.ToString()));
    attributeList.Add(new OpenXmlAttribute("max", null, columnsCounter.ToString()));
    attributeList.Add(new OpenXmlAttribute("width", null, ((150 /*Width*/ / 6) + 2).ToString())); //attributeList.Add(new OpenXmlAttribute("width", null, ((col.Width / 6) + 2).ToString()));                            
    writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Column(), attributeList);
    writer.WriteEndElement();                            
    columnsCounter++;
    /* </Column> */
}
writer.WriteEndElement();
/* </Columns> */

/* <SheetData> */
writer.WriteStartElement(new SheetData());

Answer 7

我的情况：我需要更多的冻结行。下面我来解释两件事。

A8 是我冻结面板的位置，因此 VerticalSplit = 7 是显示的“上方”行数。如果我放置 VerticalSplit = 4，则显示第 1-4 行，并隐藏第 5-7 行。如果我放置 VerticalSplit > 7 然后抛出错误。

ActiveCell = "A8" 是用户首次打开此生成的文件时将选择的单元格。

关于PaneValues我没有太关注。

var pane = new Pane
{
    VerticalSplit = 7,
    TopLeftCell = "A8",
    ActivePane = PaneValues.BottomLeft,
    State = PaneStateValues.Frozen,
};
sheetView.Append(pane);

var selection = new Selection
{
    Pane = PaneValues.BottomLeft,
    ActiveCell = "A8",
    SequenceOfReferences = new ListValue<StringValue> { InnerText = "A8" }
};
sheetView.Append(selection);