Search code examples
excelopenxml-sdk

Adding complicated INDEX formula results in Excel file error

I have a problem inserting this formula in Excel spreadsheet using OpenXML SDK.

=INDEX(Codes[[#All];[code1:]];MATCH(MySheet!B1674;Codes[[#All];[code2:]];0))

I've been doing this just by adding text into a cell using method like this:

private Cell ConstructCellFormula(string formula)
    {
        Cell cell = new Cell();
        CellFormula cFormula = new CellFormula();
        cFormula.Text = formula;
        cell.Append(cFormula);
        return cell;
    }

I know that I'm missing something in calling this.

The spreadsheet that I'm working on is a part of many in XLSM file.

Codes

is also there.

What I'm doing is - I remove all existing rows and add new based on data from the database. That's when problems with the file start.

Any help will be appreciated.

Solution

  • Solution was to simply use EN-US syntax with commas instead of semi-colons.

    =INDEX(Codes[[#All],[code1:]],MATCH(MySheet!B1674,Codes[[#All],[code2:]],0))