use object for adding formula to Excel c#

Question

I want to add a formula as shown in the third component. When i remove the "=" everything works. But for adding a formula to excel, you need the "="... When I add the "=", the program doesn't work anymore... Then I receive the error: System.Runtime.InteropServices.COMException was unhandled HResult=-2146827284 Message=Exception of HRESULT: 0x800A03EC

Any ideas?

Thank you

        static void Main(string[] args)
    {
        //seed
        var listComponents = new List<Component>
        {
            new Component() {Name = "Vc1", Cell = "A1"},
            new Component() {Name = "Vc2", Cell="B1"},
            new Component() {Name = "Nv", Cell="A2",Calculation = "=((A1+B1)/2)/0,1"}
        };


        //program
        //create excel object, workbook and worksheet
        object misValue = System.Reflection.Missing.Value;
        var excelapp = new Excel.Application();
        Excel.Workbook newWorkbook = excelapp.Workbooks.Add();
        Excel._Worksheet worksheet = (Excel.Worksheet) excelapp.ActiveSheet;

        excelapp.Visible = false;

        //cycle through components
        foreach (var component in listComponents)
        {
            if (component.Calculation != null)
            {
                Excel.Range rng = worksheet.Range[component.Cell];
                rng.Formula = component.Calculation;
                String formula = rng.Formula;
                Console.WriteLine(formula);
            }
        }

        newWorkbook.Close(false, misValue, misValue);
        excelapp.Quit();

        Console.ReadLine();

    }

Are you in a culture that uses commas as the decimal separator? Try the formula with a period instead of comma. If you need a comma you should investigate how to make that culture setting. — Crowcoder, Feb 07 '15 at 14:08
Thank you for your help, @Crowcoder . It was indeed a question of culture setting. In fact, apparantley it doesn't check the culture setting and uses the standard value. ( "." in stead of ","). — Deschuytterp, Feb 07 '15 at 16:06

score 0 · Answer 1 · answered Feb 07 '15 at 14:10

0

I think you need to replace "=((A1+B1)/2)/0,1" with @"=((A1+B1)/2)/0,1"

I have come across such a case and this worked for me.

answered Feb 07 '15 at 14:10

Kavindu Dodanduwa

12,193
3
33
46

score 0 · Accepted Answer · answered Feb 07 '15 at 16:10

0

I needed to change the comma"," in the formula to a point".". Although in my local settings, excel uses the "," apparantley in the object it only uses the standard value ".". Thanks again for your help!

answered Feb 07 '15 at 16:10

Deschuytterp

127
9

use object for adding formula to Excel c#

2 Answers2