none
Excel sheet Pre Populated Formulas values not refreshed RRS feed

  • Question

  • HI

    I have a requiremnet where excel file which has a pre populated formulas, when i populate the dependent column values the formulas caliculations are not updated or refreshed. please help me with the same.

    I started using the method to add the cell uisng InsertBefore or InsertAfter and this helped me to a point, the difficulty here is now i have the formula in Column C2 which is "Sum(A2:B2)" which is prepopulated and i dont have to add anythign runtime to this. But i have Cell D2 to be populated with a value and then i cant useInsertBefore or InsertAfter here as i dont want to add the formula cell "C2" again to the Row. Can you help me here ?

    So my requirement is like this.

    Row 1

    Cell 1 : Captures Values 1 : Filled from code

    Cell 2 : Captures values 2 : Filled from Code

    Cell 3 : Formula calculation : Sum(cell 1:cell2) prepopulated forumla  ( i dont want to add this formula by code)

    Cell 4: Drop Down with list of name defined in Sheet 1 of Taxonomy

    Cell 5 : Formula to Auto Fill the location for he name selected in cell 4.

    =IFNA(LOOKUP(D2,Taxonomy!$A$2:$A$5,Taxonomy!$B$2:$B$5),"")

    Cell 6 : captures the text values : Filled from code 

    Taxonomy Sheet:

    Code below.

    using DocumentFormat.OpenXml; 
    using DocumentFormat.OpenXml.Packaging; 
    using DocumentFormat.OpenXml.Spreadsheet; 
    using Microsoft.IT.Sales.RelationshipManagement.Segmentation.DataContracts; 
    using Newtonsoft.Json.Linq; 
    using System; 
    using System.Collections.Generic; 
    using System.IO; 
    using System.Linq; 
    using System.Threading.Tasks; 
     
    namespace ConsoleApp1 
    { 
        class Program 
        { 
            
            static void Main(string[] args) 
            { 
     
             
               MSDN_PopulateExcel("E:\\Formula.xlsx"); 
             
                Console.WriteLine("Hello World!"); 
            } 
                     
    private static void MSDN_PopulateExcel(string Path)

            {

                WorkbookPart WP;         

                Row R1; 

                Cell C1;

                Cell C2;

     

                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(Path, true))

                {

                    // Get the SharedStringTablePart. If it does not exist, create a new one.

                    WP = spreadSheet.WorkbookPart;

                    var wsp = spreadSheet.WorkbookPart.WorksheetParts.FirstOrDefault(); //.ElementAt(sheetnumber);

                    SheetData sheetData = wsp.Worksheet.Descendants<SheetData>().LastOrDefault();

                   

                    R1 = GetRow(sheetData, 2);

                    //get cell "C2" as referenced cell

                    Cell refCell = R1.GetFirstChild<Cell>();

                    C1 = new Cell();

                    SetCell(C1, "A2", 1);

                    //insert "A2" before "C2"

                    R1.InsertBefore(C1, refCell);

                 

                    C2 = new Cell();

                    SetCell(C2, "B2", 2);

                    //insert "B2" after "A2"

                    R1.InsertAfter(C2, C1);

                    //you could also insert "B2" before "C2"

                    //R1.InsertBefore(C2, refCell);

              

                 

                    spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;

                    spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

                    wsp.Worksheet.Save();

                    // Close the document.

                    spreadSheet.Close();

                }

            }

     private static Row GetRow(SheetData wsData, UInt32 rowIndex)
            {
                var row = wsData.Elements<Row>().
                Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
                if (row == null)
                {
                    row = new Row();
                    row.RowIndex = rowIndex;
                    wsData.Append(row);
                }
                return row;
            }
            private static void SetCell(Cell cell, string Reference, int Value)
            {
                cell.CellReference = Reference;
                cell.DataType = CellValues.Number;
                cell.CellValue = new CellValue();
                cell.CellValue.Text = Value.ToString();
    
            }

       

    Please help me with the code.

    Thanks

    Manoj


    Thanks, Manoj

    Monday, January 15, 2018 8:26 PM

All replies

  • Hello MANOJ MATHE,

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated. 

    By the way, what's value in Cell 6? I did not know which value should be entered in the cell.

    Sorry for any inconvenience and have a nice day! 

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 17, 2018 9:37 AM
  • Hello Manoj Mathe,

    Please try to refer to below code.

     private static void MSDN_PopulateExcel(string Path)
            {
                WorkbookPart WP;          
                Row R1;  
                Cell C1;
                Cell C2;
                Cell C3;
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(Path, true))
                {
                    // Get the SharedStringTablePart. If it does not exist, create a new one.
                    WP = spreadSheet.WorkbookPart;
                    WorksheetPart  wsp = spreadSheet.WorkbookPart.WorksheetParts.ElementAt(1); //.ElementAt(sheetnumber);
                    SheetData sheetData = wsp.Worksheet.Descendants<SheetData>().LastOrDefault();
    
    
                    R1 = GetRow(sheetData, 2);
                    #region Set A2, B2
                    Cell refCell = R1.GetFirstChild<Cell>();
                    C1 = new Cell();
                    SetCell(C1, "A2", 1);
                    R1.InsertBefore(C1, refCell);
                    C2 = new Cell();
                    SetCell(C2, "B2", 2);
                    R1.InsertAfter(C2, C1);
                    #endregion
    
                    #region set data validation
                    Worksheet worksheet1 = wsp.Worksheet;
    
                    WorksheetExtensionList worksheetExtensionList1 = new WorksheetExtensionList();
    
                    WorksheetExtension worksheetExtension1 = new WorksheetExtension() { Uri = "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" };
    
                    X14.DataValidations dataValidations1 = new X14.DataValidations() { Count = (UInt32Value)1U };
    
                    X14.DataValidation dataValidation1 = new X14.DataValidation() { Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true };
    
                    X14.DataValidationForumla1 dataValidationForumla11 = new X14.DataValidationForumla1();
                    Excel.Formula formula1 = new Excel.Formula();
                    formula1.Text = "Taxonomy!$A$2:$A$4";
    
                    dataValidationForumla11.Append(formula1);
                    Excel.ReferenceSequence referenceSequence1 = new Excel.ReferenceSequence();
                    referenceSequence1.Text = "D2";
    
                    dataValidation1.Append(dataValidationForumla11);
                    dataValidation1.Append(referenceSequence1);
    
                    dataValidations1.Append(dataValidation1);
    
                    worksheetExtension1.Append(dataValidations1);
    
                    worksheetExtensionList1.Append(worksheetExtension1);
                    worksheet1.Append(worksheetExtensionList1);
                    #endregion
    
                    #region set E2 formula
                    C3 = new Cell() { CellReference = "E2" };
                    CellFormula cellFormula = new CellFormula("_xlfn.IFNA(LOOKUP(D2,Taxonomy!$A$2:$A$5,Taxonomy!$B$2:$B$5),\"\")");
                    CellValue cellValue = new CellValue();
                    cellValue.Text = "";
    
                    C3.Append(cellFormula);
                    C3.Append(cellValue);
    
                    R1.Append(C3);
    
                    CalculationChainPart calculationChainPart1 = WP.CalculationChainPart;
                    CalculationChain calculationChain1 = calculationChainPart1.CalculationChain;
                    CalculationCell calculationCell1 = new CalculationCell() { CellReference = "E2", SheetId = 1, NewLevel = true };
                    calculationChain1.Append(calculationCell1);
                    #endregion
    
                    wsp.Worksheet.Save();
    
                    spreadSheet.Close();
                }
            }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 18, 2018 10:00 AM