In C#, using Spire.Xls, Group excel sheet rows with collapse/expand option above collapsed rows

Question

I have gone through E-Iceblue tutorials to create excel groups in c# but couldn't find an option to have the collapse/expand option above collapsed rows.

To group rows, I used the below code:

 Worksheet sheet = workbook.Worksheets[0];

        sheet.GroupByRows(2, 9, true);

But the expand/collapse icon is beside the row below the collapsed rows, by default. I need this option on the row above the collapsed rows. I know this can be done manually in excel sheet. In excel 2013, data tab, Outline settings, after unchecking "Summary rows below detail" the collapse/expand icon moves above the details.

My question is, how can we do this in C# code using Spire.Xls?

Answer 1

Edit: CAN be done using Spire.Xls. Found the solution on E-iceblue, Spire.Xls, forum. The following line of code does the job.

sheet.PageSetup.IsSummaryRowBelow = false;

If anyone wants to do this through OpenXML, refer the original answer below.

---

Cannot be done using Spire.Xls (at the time of this answer). Can be done using OpenXML.

Here's the E-iceblue, Spire.Xls, forum link for this topic. As mentioned in the forum, this feature might come in some future release.

For anyone else stumbling upon this topic, this can be done using OpenXML using the code below (Assuming that the rest of the excel sheet code is done using Spire.Xls):

Workbook ReportWorkbook = new Workbook();

//Spire.Xls code here

//Save file
ReportWorkbook.SaveToFile("myExcel.xlsx", ExcelVersion.Version2010);

//Open the excel using OpenXML
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument oXmlSheet =
    DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open("myExcel.xlsx", true))
{
    var oXmlWorkbook = oXmlSheet.WorkbookPart.Workbook;

    //Get the sheet property object for the first sheet
    var sp = oXmlWorkbook.WorkbookPart.WorksheetParts.ToList()[0].Worksheet.SheetProperties;

    //Initialize outline properties
    sp.OutlineProperties = new DocumentFormat.OpenXml.Spreadsheet.OutlineProperties();

    //Update sheet outline properties so that, 
    //the option to expand/collapse row groups is shown beside the row above the grouped rows.
    sp.OutlineProperties.SummaryBelow = false;

    //Save file
    oXmlWorkbook.Save();
}