ClosedXML / ClosedXML/ClosedXML.Report

Parse Error in SetFormulaA1 when using external links.

Open
#400 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
647
Forks
122
PR merge metrics
No merged PRs in 30d

Description

When using SetFormulaA1 to link to an external file, the string cannot contain the name "sheet" or you will get a ParseingException. In the code below, the first use of SetFormulaA1 will work because externalSheetName does not contain the work "sheet" but the second use will fail.

This was built with 0.105

```
// ... (your existing ClosedXML setup)

// Create a new workbook and worksheet
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Sheet1");

// Assume you want to link to cell A1 on "DataSheet" in "ExternalData.xlsx"
string externalWorkbookName = "ExternalData.xlsx";
string externalSheetName = "DataShee_"; //Notice the name is NOT "Sheet"
string externalCellReference = "A1";

// Construct the formula string
string formula = $"='[{externalWorkbookName}]{externalSheetName}'!{externalCellReference}";

// Set the formula in cell A1 of your current worksheet
ws.Cell("A1").SetFormulaA1(formula);

// Recononstruct the formula string and change the name to include the word "Sheet"
externalSheetName = "DataSheet";
formula = $"='[{externalWorkbookName}]{externalSheetName}'!{externalCellReference}";

//This line will not build since formula contains the word "Sheet".
ws.Cell("A1").SetFormulaA1(formula);

// Save the workbook
wb.SaveAs("MyWorkbookWithExternalLink.xlsx");
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.