azure information protection changing sheet order for open xml sdk (C#)

Knell, Holger 1 Reputation point
2022-02-28T07:21:55.66+00:00

I am using the Open XML SDK (DocumentFormat.OpenXml) to write *.xlsx files programmatically.
May company has introduced azure information protection a while ago.
As long as the files are not opened by a user and are not classified ( unclassified, public, condifential, restricted) everything works just fine as always.
But after classification the sheetorder has changed and writing to the document does not lead to the desired result any more.

Is the any workaround for this?

Code Example:
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
int sheetNumber = 1;
WorksheetPart worksheetPart = document.WorkbookPart.WorksheetParts.ElementAt(sheetNumber);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
...}

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,408 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Karen Payne MVP 35,201 Reputation points
    2022-03-01T22:50:42.407+00:00

    Consider selecting the Sheet by name rather than index. For example using a free library that does not require Excel to be installed and is easy to read and write cell data.

    https://spreadsheetlight.com/

    https://www.nuget.org/packages/SpreadsheetLight/

    Or

    https://www.nuget.org/packages/SpreadsheetLight.Core/

    /// <summary>
    /// Open file to specific sheet
    /// </summary>
    /// <param name="fileName">Existing Excel file name</param>
    /// <param name="sheetName">Existing Sheet name in file</param>
    public static void Work(string fileName, string sheetName)
    {
        using (var document = new SLDocument(fileName, sheetName))
        {
            // some code do write to the cells of selected sheet
        }
    }
    
    1 person found this answer helpful.