Query tables are not found by Open XML SDK

Viewed 10

I have used Excel to create a data connection to another .xlsx spreadsheet. When I look in the XML of the spreadsheet this data connection was not registered as a relationship in the worksheet/_rels files. This may be an error in Excel or it may not. However, Open XML SDK works in the way, that you can only remove querytables, if they are registered as a relationship to one or more worksheets.

I am using this method to remove any data connections including related query tables.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public void Remove_DataConnections(string filepath)
{
    using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, true))
    {
        // Delete all connections
        ConnectionsPart conn = spreadsheet.WorkbookPart.ConnectionsPart;
        spreadsheet.WorkbookPart.DeletePart(conn);

        // Delete all query tables
        List<WorksheetPart> worksheetparts = spreadsheet.WorkbookPart.WorksheetParts.ToList();
        foreach (WorksheetPart part in worksheetparts)
        {
            List<QueryTablePart> queryTables = part.QueryTableParts.ToList();
            foreach (QueryTablePart qtp in queryTables)
            {
                part.DeletePart(qtp);
            }
        }
    }
}

Is there any other approach than the above to remove query tables in a .xlsx spreadsheet, which have not been registered as a relationship in a worksheetpart? Secondly, is this indeed an error in Excel?

Would my approach be try to fix the missing relationship and then remove the query table as a hack?

You can find a data sample here: https://github.com/Asbjoedt/CLISC/blob/master/Docs/SampleData.zip

Find the file "Another folder/With data connection.xlsx"

0 Answers
Related