使用C＃在OPEN XML sdk中添加引用另一个工作簿中的单元格的公式

Amete Blessed 发表于 Dev

Amete有福

我正在使用C＃开发Open XML sdk 2.0。

我有两个工作簿Book1和Book2。假设Book1在其单元格D1中的值为10。然后book2的单元格A1中应该有10个。Book1已经创建，我需要处理book2。

我试图创建一个工作簿Book2，其中单元格A1引用了Book1（Sheet1）中的单元格D1。每当用户更改Book1中的D1时，一旦完成，book2中的值A1也应更改。

到目前为止我有这段代码

Row row2 = new Row()
        {
            RowIndex = 1U,
            Spans = new ListValue<StringValue>()
        };

// string formula= "Sum(B2,B5)"; // this one works 
String formula= "'C:\Workbooks\[Book2.xlsx]Sheet1'!$D$1"; // this does not

Cell cell3 = new Cell() { CellReference = "A1" };
CellFormula formula = new CellFormula(formula);
cell3.Append(formula);

CellValue value = new CellValue();
value.Text = "0";
cell3.Append(value);

row2.Append(cell3);

每当我将行添加到工作簿并保存时，如果我使用以下公式，则说book2损坏

formula= "'C:\Workbooks\[Book2.xlsx]Sheet1'!$D$1"; // this does not

但是，如果我使用一个简单的公式，如下所示。

formula= "Sum(B2,B5)"; // this one works

我不确定如何使用C＃在OPEN XML sdk中添加引用另一个工作簿中的单元格的公式。

骨el

问题是Excel需要一个外部引用来通知它另一个工作表的存在。为此，您需要在上调用AddExternalRelationship方法WorkBookPart：

workbookpart.AddExternalRelationship("http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath", 
        new System.Uri(@"C:\Workbooks\Book2.xlsx", UriKind.Absolute), "rId1");

如果您解压缩引用另一个工作簿的Xlsx文件并导航到该xl文件夹，则将看到一个externalLinks文件夹。这是您需要创建的部分。

这是一个完整的代码示例，该示例将根据我从MSDN文档改编的内容进行工作。

public static void CreateSpreadsheetWorkbook(string filepath)
{
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
        Create(filepath, SpreadsheetDocumentType.Workbook);

    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();

    SheetData sheetData = new SheetData();
    worksheetPart.Worksheet = new Worksheet(sheetData);

    // Add Sheets to the Workbook.
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
        AppendChild<Sheets>(new Sheets());

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet()
    {
        Id = spreadsheetDocument.WorkbookPart.
            GetIdOfPart(worksheetPart),
        SheetId = 1,
        Name = "mySheet"
    };
    sheets.Append(sheet);

    Row row2 = new Row()
        {
            RowIndex = 1U
        };

    //add your formula as per your question
    String formula = @"'C:\Workbooks\[Book2.xlsx]Sheet1'!$D$1";

    Cell cell3 = new Cell() { CellReference = "A1" };
    CellFormula cellFormula = new CellFormula(formula);
    cell3.Append(cellFormula);

    row2.Append(cell3);

    //add a relationship that points to the correct file
    workbookpart.AddExternalRelationship("http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath",
        new System.Uri(@"C:\Workbooks\Book2.xlsx", UriKind.Absolute), "rId1");

    sheetData.Append(row2);

    workbookpart.Workbook.Save();

    // Close the document.
    spreadsheetDocument.Close();
}