external links - OpenXML Add ExternalLink

Question

i have file with two external links and i need add one more. Normally just create what i need in Excel, save file and reopen it with Productivity Tools and find reflected code, but it doesn't work now. When i use reflected code, then Excel cant read file correctly and throw error in part /xl/externalReferences/externalReference3.xml.

      ExternalWorkbookPart part2 = workbookPart.AddNewPart<ExternalWorkbookPart>("rId5");
  ExternalRelationship externalRelationship = part2.AddExternalRelationship("http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath",
     new System.Uri("/AAA.xlsx", System.UriKind.Relative), "rId1");

  ExternalLink externalLink1 = new ExternalLink();

  ExternalBook externalBook1 = new ExternalBook() { Id = "rId1" };
  //externalBook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

  SheetNames sheetNames1 = new SheetNames();
  SheetName sheetName1 = new SheetName() { Val = "Sheet1" };
  sheetNames1.Append(sheetName1);

  SheetDataSet sheetDataSet1 = new SheetDataSet();
  ExternalSheetData externalSheetData1 = new ExternalSheetData() { SheetId = (UInt32Value)0U };

  ExternalRow externalRow1 = new ExternalRow() { RowIndex = (UInt32Value)2U };
  ExternalCell externalCell1 = new ExternalCell() { CellReference = "A2", DataType = CellValues.String };
  Xstring xstring1 = new Xstring();
  xstring1.Text = "link test";

  externalCell1.Append(xstring1);
  externalRow1.Append(externalCell1);
  externalSheetData1.Append(externalRow1);
  sheetDataSet1.Append(externalSheetData1);

  externalBook1.Append(sheetNames1);
  externalBook1.Append(sheetDataSet1);
  externalLink1.Append(externalBook1);

  part2.ExternalLink = externalLink1;

  ExternalReferences externalReferences = workbook.ExternalReferences;
  ExternalReference externalReference = new ExternalReference() { Id = "rId5" };
  externalReferences.Append(externalReference);

I dont know where is error, but probably external link isnt added correctly. When i unzip xlsx file created by code, then he have only 2 ExternalLinks and 1 ExternalReference. But reference file created in Excel have 3 ExternalLinks and 0 ExternalReference.

question from:https://stackoverflow.com/questions/65868275/openxml-add-externallink

Answer 1

Waitting for answers