Open-XML-SDK

Open-XML-SDK copied to clipboard

Describe the bug The SDK cannot find absolute path to local directory in workbook.xml even though it exists in the XML attribute is "x15ac:absPath".

For further context, I refer to question I made on Stackoverflow, where I was recommended to file an issue here: https://stackoverflow.com/questions/73633816/absolute-path-not-detected-in-open-xml-sdk/73641509?noredirect=1#comment130327161_73641509

Screenshots See question on Stackoverflow for screenshots.

To Reproduce

To reproduce use this method on a spreadsheet with absolute path. You may find a data sample with absolute path here: https://github.com/Asbjoedt/CLISC/blob/master/Docs/Mappe1.xlsx

Method that ought to work

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Office2013.ExcelAc;

public bool Check_AbsolutePath(string filepath)
{
    bool absolutepath = false;

    using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, false))
    {
        if (spreadsheet.WorkbookPart.Workbook.AbsolutePath != null)
        {
            Console.WriteLine("--> Absolute path to local directory detected");
            absolutepath = true;
        }
    }
    return absolutepath;
}

Observed behavior No absolute path is found in spreadsheet despite workbook.xml has absolutepath. XML attribute is "x15ac:absPath". I need to remove absolute paths in spreadsheets, which I cannot do if they cannot be found.

Expected behavior Absolute path was found as get and i should be able to remove it by set "".

Desktop (please complete the following information):

• OS: Windows 11
• .NET Target: I dunno, I just use C# in Visual Studio as "console application"
• DocumentFormat.OpenXml Version: 2.18.0

Additional context Thx for your attention, I hope you can fix this.

Hi @Asbjoedt , Thanks for bringing this to our attention. I will look into this and try to determine what the issue is.

Thanks mate.

Is there any update on this issue?

@Asbjoedt , I haven't found why spreadsheet?.WorkbookPart?.Workbook.AbsolutePath is null when the part is in the workbook, I'll keep looking into that, but in the meantime I have a workaround to find the absPath:

var absPath = spreadsheet?.WorkbookPart?.Workbook
                .GetFirstChild<AlternateContent>()
                ?.GetFirstChild<AlternateContentChoice>()
                ?.GetFirstChild<AbsolutePath>();

var url = absPath?.Url;

@mikeebowen Hi Mike. I can confirm the workaround works. I can also set the value to null using the workaround. Looking forward to hearing more about your findings on the "...Workbook.AbsolutePath" bug.

Hi @Asbjoedt, It turns out this is by design. The SpreadsheetDocument.Open method takes an optional third argument openSettings, which is the advanced settings for opening the document. See more at this link.

One of the settings options in the OpenSettings is MarkupCompatibilityProcessSettings, which "Gets or sets the value of the markup compatibility processing mode". The default processing mode is 2007, but the absPath element wasn't introduced until 2013, so you need to tell the SDK which compatibility mode to use like this:

SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(file, false, new OpenSettings() { MarkupCompatibilityProcessSettings = new MarkupCompatibilityProcessSettings(MarkupCompatibilityProcessMode.ProcessAllParts, FileFormatVersions.Office2013) })

So, your code should be something like this:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Office2013.ExcelAc;
using DocumentFormat.OpenXml.Packaging;

public bool Check_AbsolutePath(string filepath)
{
    using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(file, false, new OpenSettings() { 
    MarkupCompatibilityProcessSettings = new 
    MarkupCompatibilityProcessSettings(MarkupCompatibilityProcessMode.ProcessAllParts, FileFormatVersions.Office2013) }))
    {
        if (spreadsheet?.WorkbookPart?.Workbook?.AbsolutePath != null)
        {
            Console.WriteLine("--> Absolute path to local directory detected");
            absolutepath = true;
        }
    }

    return absolutePath;
}

with the above Workbook.AbsolutePath should not be null. I hope that helps!

Hi @mikeebowen Yes, the code you provided works, however it is not user-friendly to implement. Thx for solving this issue.

I'm now trying to remove the AbsolutePath in the workbook. I can't get this work, do you have any tip on how to proceed?

See below for comments on what I have tried.

public void Remove_AbsolutePath(string filepath)
{
    using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, true, new OpenSettings()
    {
        MarkupCompatibilityProcessSettings = new MarkupCompatibilityProcessSettings(MarkupCompatibilityProcessMode.ProcessAllParts, FileFormatVersions.Office2013)
    }))
    {
        Workbook workbook = spreadsheet.WorkbookPart.Workbook;
        AbsolutePath absPath = workbook.AbsolutePath;

        workbook.RemoveChild(absPath); // DOES NOT WORK
        absPath.Remove(); // DOES NOT WORK
        absPath.Url.Value = null; // WORKS BUT KEEPS THE XML IN THE FILE AND ONLY SETS VALUE TO EMPTY
    }
}

Hi @Asbjoedt, try this:

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(file, true, new OpenSettings() { MarkupCompatibilityProcessSettings = new MarkupCompatibilityProcessSettings(MarkupCompatibilityProcessMode.ProcessAllParts, FileFormatVersions.Office2013) }))
{
    spreadsheet?.WorkbookPart?.Workbook?.AbsolutePath?.Remove();

    spreadsheet?.Dispose();
}

@mikeebowen

I tested. It does not work.

It shouldn't be necessary to Dispose() either because the using code should do the same thing.

@Asbjoedt, You need to Dispose(), because otherwise the file does not save, which is likely why it's not working for you. The code block in my comment successfully removes the absPath when I tested it.

Hi Mike. Source: https://stackoverflow.com/questions/212198/what-is-the-c-sharp-using-block-and-why-should-i-use-it

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, true))
{
SOME CODE
}

Is the same as:

SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, true)
SOME CODE
spreadsheet.Dispose();

I have tested using dispose and it does not work for me.

Hi @Asbjoedt, I copied that code from a test application, so I'm sure it works. I understand you don't need to Dispose when you have {} block after the using statement, but that doesn't save the file, the Dispose method does save. If you don't want to use dispose, you can also use Save. This code also works:

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filePath, true, new OpenSettings() { MarkupCompatibilityProcessSettings = new MarkupCompatibilityProcessSettings(MarkupCompatibilityProcessMode.ProcessAllParts, FileFormatVersions.Office2013) }))
{
    spreadsheet?.WorkbookPart?.Workbook?.AbsolutePath?.Remove();

    spreadsheet?.Save();
}

@mikeebowen Hmm, I tested this some more, and it seems you don't have to Save() or Dispose(), when implementing the "using" block in this context. The AbsolutePath is indeed removed, but it is not removed from the spreadsheet before either Save(), Dispose() or the using block ends and then you have to reopen the spreadsheet programmatically anew to continue working with the spreadsheet without any AbsolutePath is registered by Open XML SDK, It's a bit quirky, since usually you can Remove() something from the spreadsheet and it will be immediately registered by Open XML SDK and you can continue to manipulate the same spreadsheet within the same using block.

Thanks for helping clear this one up. Have a continued nice day.