CODEWITHSUNDEEP
c#.netexcelexcel-formula
Amico
Amico

Reputation: 803

Adding formula to Excel with C# - making the formula shown

I wanted to add formulas to an Excel workSheet.
I managed to do so with the Formula property.

The problem is that when I open the worksheet in Excel, I can see that the formula works - but I can only see the result in the cell. I can't see the formula that was calculated in the Formula Bar at the top of Excel.

Obviously if I enter a formula in Excel itself I can see the result in the cell and the formula in the Formula Bar.

Some of my code: 

for (int i = 0; i < nOfColumns / 3; i++)
{
    Range cells = workSheet.Range[workSheet.Cells[2, i * 3 + 3], workSheet.Cells[lastRowNumber, i * 3 + 3]];
    cells.FormulaR1C1 = "=IF(EXACT(RC[-2],RC[-1]),TRUE,ABS(RC[-2]/RC[-1]-1))";
}

below is a test code. even after I save the workbook - the FormulaHidden is false and I can successfully retrieve the formula insterted. really frustrated

        Microsoft.Office.Interop.Excel.Application excelApp = null;
        Workbooks workBooks = null;
        Workbook workBook = null;
        Worksheet workSheet;

        try
        {

            excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.DisplayAlerts = false;

            workBooks = excelApp.Workbooks;
            workBook = workBooks.Open(filePath, AddToMru: false);
            workSheet = workBook.Worksheets.get_Item(1);

            int nOfColumns = workSheet.UsedRange.Columns.Count;
            int lastRowNumber = workSheet.UsedRange.Rows.Count;

            Range rng = workSheet.Range["C1"];
            rng.Formula = "=SUM(B2:B4)";
            String formula = rng.Formula; //retrieve the formula successfully

            rng.FormulaHidden = false;
            workSheet.Unprotect();

            workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlExclusive);

            formula = rng.Formula;  //retrieve the formula successfully
            bool hidden = rng.FormulaHidden;

        }
        catch (Exception e)
        {
            throw;
        }
        finally
        {
            if (workBook != null)
            {
                workBook.Close();
                workBook = null;
            }
            if (workBooks != null)
            {
                workBooks.Close();
                workBooks = null;
            }
            if (excelApp != null)
            {
                excelApp.Quit();
                excelApp = null;
            }
        }
    }

Anyone know how to make the formula shown, when adding the formulas programatically ?

Upvotes: 11

Views: 76707

Answers (4)

Amico
Amico

Reputation: 803

finally !!! figured it out. this behavior is caused by the SaveAs flags. changed

workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlExclusive);

to

workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlShared);

now the only thing left is to understand what exactly is the different between the two flags. :)

Upvotes: 3

CapelliC
CapelliC

Reputation: 60034

I think localization could be involved in this weird behaviour.

Some time ago, working in Excel, I had the impression that formulas got stored in localized language (I was using italian), then undergo a conversion when compiled. This could make sense, because localized constants are an essential part of the spreadsheet data.

I'm sorry I haven't now Excel available, so I can't be more precise, but I think you could try to localize to english your spreadsheet, or set the formula text in your local language.

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33484

Hiding the formula (by checking Hidden checkbox on Format Cells dialog) & protecting the worksheet (thereafter) will cause the formula to not show in the formula bar.

Example VBA code

Range("C1").FormulaHidden = True    'set this property to false to make formula visible.
Sheet1.Protect

EDIT: In order to see the formula in the formula bar

Range("C1").FormulaHidden = False
Sheet1.Unprotect

Upvotes: 1

BG100
BG100

Reputation: 4531

Go to the Formula tab on the tool bar, and click "Show Formulas".

enter image description here

Upvotes: 0

Related Questions