Open a excel file but do not Update the cell values.

Posted by: jacky-fan on 8 September 2017, 2:07 pm EST

  • Posted 8 September 2017, 2:07 pm EST

    Hello everyone, 


    How can i Open a excel file but do not Update the cell values?


    Here is a example, i use OpenExcel methode for import a Excel file,


    but inside this Excel file, i got some cells with cross-file reference,


    so these cells displayed a "#REF!" as an error.


    so, how could fpspread disabled this autoupdate function when import my Excel file?


    i have tried  fpSpread1.ActiveSheet.AutoCalculation = false; not working


    thanks advance


     

  • Replied 8 September 2017, 2:07 pm EST

    Hi,


    I tried the above code. I get result as "#N/A" in ExcelBook1 for Cell 0,2.


    Please advise.


     Regards,


    Yamini 


     


     

  • Replied 8 September 2017, 2:07 pm EST

    Hello,


    I tested the issue at my end using 2 excel Workbooks where in workbook2 gets calcualted value from workbook1(i.e. cross-file reference). Then on importing the workbook2 in fpSpread using OpenExcel Method, we have to set the 2nd parameter of the OpenExcel method i.e. ExcelOpenFlags to DoNotRecalculateAfterLoad like:



    fpSpread1.OpenExcel("C:\\Documents and Settings\\VMAdmin\\My Documents\\Book2.xls", FarPoint.Excel.ExcelOpenFlags.DoNotRecalculateAfterLoad);


    Hope this will help.


    Thanks,

  • Replied 8 September 2017, 2:07 pm EST

    Thanks Reeva,


    FarPoint.Excel.ExcelOpenFlags.DoNotRecalculateAfterLoad


    This works perfectly!


     

  • Replied 8 September 2017, 2:07 pm EST

    Hi,


    I have the following requirement:


    " write value to an Excel file (file 1) which is an input to a calculation in another file (file 2) whose result is linked back into the first file (file 1), recalculate both files, and read the result of the calculation back from file 1".


    Basically I want to link 2 workbooks where the 2nd workbook has cells that references cell values from the 1st workbook.


     


    Regards,


    Yamini

  • Replied 8 September 2017, 2:07 pm EST

    Hello,


    You can use the following code snippet for cross reference workbooks. Here the fpSpread1 uses ExcelBook1 and fpspread2 refrences the calculated value from fpSpread1's cells, which is then being saved in ExcelBook2 . We then access the fpSpread2 cell's value in fpspread1 cell's.




                fpSpread1.OpenExcel(@"D:\ExcelBook1.xls");
                fpSpread2.Sheets[0].Cells[1, 0].Value = 14;
                fpSpread2.Sheets[0].Cells[2, 0].Value = 24;


                FarPoint.Win.Spread.Cell acell;
                FarPoint.Win.Spread.Cell mycell;
                FarPoint.Win.Spread.Cell urcell;
                mycell = fpSpread1.Sheets[0].Cells[0, 0];
                urcell = fpSpread1.Sheets[0].Cells[2, 0];
                acell = fpSpread2.Sheets[0].Cells[0, 0];
                acell.Formula = "Product(" + mycell.Value + "," + urcell.Value + ")";
                fpSpread2.SaveExcel(@"D:\ExcelBook2.xls");


                fpSpread2.Sheets[0].Cells[0, 2].Formula = "SUM(A1:A4)";
                fpSpread2.SaveExcel(@"D:\ExcelBook2.xls");


                mycell = fpSpread1.Sheets[0].Cells[0, 2];
                acell = fpSpread2.Sheets[0].Cells[0, 2];


                mycell.Value = acell.Value;
                fpSpread1.SaveExcel(@"D:\ExcelBook1.xls");


    Thanks

  • Replied 8 September 2017, 2:07 pm EST

    Hello Yamini,

    I tried the issue and I am unable to replicate it. However, the code given in the previous post is working fine at my end. Here Cells[0,2] for  ExcelBook1 gets the value from ExcelBook2( i.e. fpSpread2).

    Can you please post a zipped project for the same along with the ExcelBook1 for us to replicate the issue ? Thanks.

     

  • Replied 8 September 2017, 2:07 pm EST

    My code is as follows:


    FarPoint.Win.Spread.FpSpread fpSpread1 = new FarPoint.Win.Spread.FpSpread();


     


    FarPoint.Win.Spread.FpSpread fpSpread2 = new FarPoint.Win.Spread.FpSpread();


    fpSpread1.OpenExcel("C:\\code\\ExcelBook1.xls");
    fpSpread2.OpenExcel("C:\\code\\ExcelBook2.xls");
    fpSpread2.Sheets[0].Cells[1, 0].Value = 14;
    fpSpread2.Sheets[0].Cells[2, 0].Value = 24;


    FarPoint.Win.Spread.Cell acell;
    FarPoint.Win.Spread.Cell mycell;
    FarPoint.Win.Spread.Cell urcell;
    mycell = fpSpread1.Sheets[0].Cells[0, 0];
    urcell = fpSpread1.Sheets[0].Cells[2, 0];
    acell = fpSpread2.Sheets[0].Cells[0, 0];
    acell.Formula = "Product(" + mycell.Value + "," + urcell.Value + ")";
    fpSpread2.SaveExcel("C:\\code\\ExcelBook2.xls");


    fpSpread2.Sheets[0].Cells[0, 2].Formula = "SUM(A1:A4)";
    fpSpread2.SaveExcel("C:\\code\\ExcelBook2.xls");


    mycell = fpSpread1.Sheets[0].Cells[0, 2];
    acell = fpSpread2.Sheets[0].Cells[0, 2];


    mycell.Value = acell.Value;
    fpSpread1.SaveExcel("C:\\code\\ExcelBook1.xls");

  • Replied 8 September 2017, 2:07 pm EST

    Hello Yamini,


    I am able replicate your issue in case when we dont have value in Cells[0, 0] and Cells[2, 0] of fpSpread1 (i.e. ExcelBook1). Can you please check if the cells on which you are applying the formula does not have values?


    For the code snippet to run, the cells being referred in formulas must have values or we should put a check to calculate result only if the cells used in formula have value. 


    Hope this will help. Thanks.

  • Replied 8 September 2017, 2:07 pm EST

    Thanks,it works now.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels