We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on : Nov 16th 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

In Write excel- How to preserve formula in the newly created sheet

Thread ID:

Created:

Updated:

Platform:

Replies:

129360 Mar 14,2017 01:59 PM UTC Mar 16,2017 01:07 PM UTC Xamarin.Forms 3
loading
Tags: XlsIO
Ramesh GR
Asked On March 14, 2017 01:59 PM UTC

Hi,

I am facing the following issue.

I open existing excel with the help of Xamarin stream IO. Actually I want to update values in a cell which will change the other values in the another worksheet(in the same workbook) based on the formula in the original worksheet.

I am able to modify the value in the newly written sheet but the values in other worksheet (in the same workbook) are not updated as per the formula.

Example- Workbook1--Sheet1- A3=2.   --->  Workbook1--Sheet2- A3= 4 [ something like Formula  A3[sheet1]*2]

I am able to create new workbook2, update the value of A3 in sheet1 to '4'' but the formula is not applied in the sheet2 and retains the old value only. 

Please help me resolve this.

Please find my code chunk below. Thanks in advance.

void OnButtonClicked(object sender, EventArgs args)
        {

            Debug.WriteLine(NumberEntry.Text);
            //Create an instance of ExcelEngine.
            using (ExcelEngine excelEngine = new ExcelEngine())
            {

                //ExcelEngine excelEngine = new ExcelEngine();
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2010;

                string resourcePath = "SyncfusionExcelDemo.Employee.xlsx";
                //"App" is the class of Portable project.
                Assembly assembly = typeof(App).GetTypeInfo().Assembly;
                Stream fileStream = assembly.GetManifestResourceStream(resourcePath);

                //MemoryStream stream2 = new MemoryStream();
                //stream2 = (MemoryStream)fileStream;

                //Opens the workbook 
                IWorkbook workbook = application.Workbooks.Open(fileStream);

                IWorksheet worksheet = workbook.Worksheets[0];
                //update the cell with new value
                worksheet["A3"].Text = "5";


                //workbook.Worksheets[0]["A3"].Text = "4";
                workbook.Worksheets[0].EnableSheetCalculations();
                workbook.Worksheets[1].EnableSheetCalculations();

                ICalculationOptions cal = workbook.CalculationOptions;
                cal.RecalcOnSave = true;

                workbook.CalculationOptions.IsIterationEnabled = true;

                MemoryStream stream = new MemoryStream();
                workbook.SaveAs(stream);

                

                workbook.Close();
                excelEngine.Dispose();

                //Save the stream into xlsx file
                Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("sample.xlsx", "application/msexcel", stream);
                
            }
        }
  

Sridhar Sukumar [Syncfusion]
Replied On March 15, 2017 10:37 AM UTC

Hi Ramesh,  
  
Thank you contacting Syncfusion support.  
  
We are able to reproduce the issue while opening the Excel documents in phone. XlsIO does not serialize the formula value for formula ranges when creating a formula in XlsIO. Instead of that, XlsIO set the CaluculateOnOpen property as true for formula ranges. So, while opening the output file in Excel, it automatically calculates the formula value if the CalculateOnOpen property is set as true. But, viewers other than Microsoft Excel does not calculate the formula value.   
  
XlsIO serialize the formula value only when the calculated value is get from the formula ranges. So, if you want calculated value, we request you to enable the CalcEngine for the worksheet and get the calculated value for the formula ranges. Kindly refer the following code snippet to achieve this.  
  
Code snippet:  
workbook.Worksheets[1].EnableSheetCalculations();  
string value = workbook.Worksheets[1].Range["A3"].CalculatedValue;  
  
Please let us know if you have any concerns.  
  
Regards,  
Sridhar S. 


Ramesh GR
Replied On March 15, 2017 02:12 PM UTC

Tanks for your response!!

Is Xamarin XlsIO support macro formulas in the imported Excel?


I have requirement like.. Excel file with extension .xlsm (macro formula supported excel file). Am imported local excel and edited some cell values and try to generate new excel with local excel sheet, but its hanged and not generated new excel.




Sridhar Sukumar [Syncfusion]
Replied On March 16, 2017 01:07 PM UTC

Hi Ramesh,   
   
Thank you for updating us. 

 

We are unable to reproduce the issue from our side. We have shared a simple sample for your reference which can be downloaded from following location. 

 

Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/MacroFile1289124615.zip 

 

Kindly modify the sample to reproduce the issue and share us the modified sample along with the issue reproducing input files which will be helpful for us to give you a prompt solution at the earliest.  
 
Regards, 
Sridhar S. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;