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

How to create shared formula in Excel file

Thread ID:

Created:

Updated:

Platform:

Replies:

125567 Aug 25,2016 08:37 AM UTC Oct 13,2016 11:25 AM UTC UWP 15
loading
Tags: XlsIO
Sharad Kumar
Asked On August 25, 2016 08:37 AM UTC

Dear Experts,

I am try to create excel formula. I am successful to create Array formula but some other type formula are not created.

I am using following code.

ExcelEngine oengine = new ExcelEngine();
IApplication oApp = oengine.Excel;
oApp.DefaultVersion = ExcelVersion.Excel2007;
IWorkbook oWorkbook  =  oApp.Workbooks.Create(1);
IWorksheets osheets = oWorkbook.Worksheets;
IWorksheet  osheet = osheets .Worksheets[0];

IRange oRange = osheet .Range["A3:A25"];
oRange.Value2 = "=A2+1";
oRange.FormulaR1C1 = "A3:A25";

How create excel formula from A3 to A25 cell range.
Excel file is attach with mail.



Attachment: normaldatacheck_2d8ec5c5.rar

Sridhar Sukumar [Syncfusion]
Replied On August 26, 2016 12:21 PM UTC

Hi Sharad, 
  
Thank you for contacting Syncfusion support. 
  
Improper value is assigned to Range.FormulaR1C1 property in the code snippet you have shared us. To create shared formula in XlsIO kindly use the following code snippet 
  
Code snippet: 
 //Set the shared formula 
IRange range = worksheet.Range["A3:A25"]; 
range.FormulaR1C1 = "R[-1]C + 1"; 
  
We have also created a sample for this which can be downloaded from the following location. 
  
  
Regards, 
Sridhar S 


Sharad Kumar
Replied On August 29, 2016 03:18 AM UTC

Thank you Sridhar.

Sridhar Sukumar [Syncfusion]
Replied On August 29, 2016 09:50 AM UTC

Hi Sharad, 
  
Thank you for updating us. 
  
We are glad to know that your requirement is fulfilled. Please let us know if you need further assistance. 
  
Regards, 
Sridhar S 


Sharad Kumar
Replied On August 29, 2016 11:25 AM UTC

Hi,

My another question is related to conditional formatting.

I am successfully create for some conditional formatting (Iconset, Databar,Color scale ,Formaula etc.) but i am not create top10/below10 and above/below conditional formatting in excel file.

I am using following code.

ExcelEngine oengine = new ExcelEngine();
IApplication oApp = oengine.Excel;
oApp.DefaultVersion = ExcelVersion.Excel2007;
IWorkbook oWorkbook  =  oApp.Workbooks.Create(1);
IWorksheets osheets = oWorkbook.Worksheets;
IWorksheet  osheet = osheets .Worksheets[0];
IRange oRange = osheet.Range[....];
IconditionalFormats fcondition = oRange.ConditionalFormats;

IConditionalFormat fcellcondition = fcondition.AddCondition();
fcellcondition.FormatType = (ExcelCFType)1;


In this code , I am not understand what ExcelCFType format type for top10/below10 and above/below average conditional formatting.

I am send Excel file which contain conditional formatting on cell range "G1:G26", "F1:F26","E1:E26","D1:D26", "C1:C26"  .


Thanks
Sharad kumar


Attachment: conditional_formatting_7205905c.rar

Sridhar Sukumar [Syncfusion]
Replied On August 30, 2016 08:57 AM UTC

Hi Sharad,  
   
Thank you for updating us.  
 
We regret to let you know that currently we do not have support for top10/below10 and above/below averages conditional formatting. However, we have added it to our Feature request list and it will be implemented in any of our upcoming releases.      
  
Please let us know if you need any other assistance on this. 
 
Regards, 
Sridhar S 


Sharad Kumar
Replied On August 30, 2016 10:49 AM UTC

Hi,

Thank you for update me.

My another question is related to conditional formatting.

I am not apply Theme color and TintAndShade color on conditional formatting. but RGB color is apply successfully.

Thanks
sharad kumar



Sharad Kumar
Replied On August 31, 2016 04:38 AM UTC

Hi,

My second Question is related to cell formatting.

I am send Excel file. In this file  E1 number column type is date .
E1 Column data is show correct value when apply Calibri font . but when apply with Arial font , data show garbage value.

ExcelEngine oengine = new ExcelEngine();
IApplication oApp = oengine.Excel;
oApp.DefaultVersion = ExcelVersion.Excel2007;
IWorkbook oWorkbook  =  oApp.Workbooks.Create(1);
IWorksheets osheets = oWorkbook.Worksheets;
IWorksheet  osheet = osheets .Worksheets[0];

IRange oRange = osheet .Range["E1"];
oRange.Value2 = "42017";
oRange.NumberFormat = "d-mmm-yyyy";
oRange.CellStyle.Font.FontName = "Arial";                         // Orignal Data show
oRange.CellStyle.Font.FontName = "Calibri ";                    // Garabge Data Show

Thanks

Attachment: Date_9413696e.rar

Sridhar Sukumar [Syncfusion]
Replied On August 31, 2016 10:15 AM UTC

Hi Sharad,  
 
Thank you updating us. 
 
Query 
Response 
My another question is related to conditional formatting.

I am not apply Theme color and TintAndShade color on conditional formatting. but RGB color is apply successfully.
 
XlsIO doesn't support TintAndShade in conditional formats similar to MS Excel.  
However, we have added the theme color support to our Feature request list and it will be implemented in any of our upcoming releases.      
My second Question is related to cell formatting.

I am send Excel file. In this file  E1 number column type is date .
E1 Column data is show correct value when apply Calibri font . but when apply with Arial font , data show garbage value. 

ExcelEngine oengine = new ExcelEngine();
IApplication oApp = oengine.Excel;
oApp.DefaultVersion = ExcelVersion.Excel2007;
IWorkbook oWorkbook  =  oApp.Workbooks.Create(1);
IWorksheets osheets = oWorkbook.Worksheets;
IWorksheet  osheet = osheets .Worksheets[0];

IRange oRange = osheet .Range["E1"];
oRange.Value2 = "42017";
oRange.NumberFormat = "d-mmm-yyyy";
oRange.CellStyle.Font.FontName = "Arial";                         // Orignal Data show 
oRange.CellStyle.Font.FontName = "Calibri ";                    // Garabge Data Show
 
Whenever the cell value is larger than the cell width, Excel shows the ### in the cell. You need to Autofit the column width to see its actual value. 
 
We request you to Autofit the column width to avoid these kind of issues. Kindly refer the following code snippet to Autofit the UsedRange column width in XlsIO. 
 
Code snippet: 
worksheet.UsedRange.AutofitColumns(); 
 
We have also prepared a sample for this which can be downloaded from the following location. 
 
 
 
 
NOTE: You can post your queries in a separate forum if the issue is not relevant to the forum title. 
 
Regards, 
Sridhar S 
 


Sharad Kumar
Replied On October 7, 2016 08:20 AM UTC

Hi Sridhar,

I am not apply shared formula from  F1 to F26 cell.

Formula vale is ->    =IF(E12<>"","Closed","Open")

Please shared me code snippet which recover to formula.

Excel file is attach which contain shared formula.

Thanks
sharad kumar

 

Attachment: 24ifformulaecheck_3be8e3e7.rar

Sridhar Sukumar [Syncfusion]
Replied On October 10, 2016 10:45 AM UTC

Hi Sharad,   
  
Thank you updating us. 
 
Kindly refer the following code snippet to apply the formula =IF(E12<>"","Closed","Open")” to “F1:F26” range. 
 
Code snippet: 
worksheet.Range["F1:F26"].Formula = "=IF(E12<>\"\",\"Closed\",\"Open\")"; 
 
We have also prepared a sample for this which can be downloaded from the following link 
 
 
Regards, 
Sridhar S. 


Sharad Kumar
Replied On October 10, 2016 11:27 AM UTC

Thanks for replay.

Repair file is attach which have shared formula.
My code snippet is ->
IRange oRange = osheet .Range["F2:F26"];
oRange.Formula = "=IF(E12<>\"\",\"Closed\",\"Open\")";

My repaired file is not match to the original file  (cell F3,F11,F18,F25 have different value ).

and how to differentiate shared formula in different cases (sometimes i am use FormulaR1C1 function and some time I am use Formula function).

Thanks
sharad kumar

Sharad Kumar
Replied On October 10, 2016 11:28 AM UTC

Thanks for replay.

Repair file is attach which have shared formula.
My code snippet is ->
IRange oRange = osheet .Range["F2:F26"];
oRange.Formula = "=IF(E12<>\"\",\"Closed\",\"Open\")";

My repaired file is not match to the original file  (cell F3,F11,F18,F25 have different value ).

and how to differentiate shared formula in different cases (sometimes i am use FormulaR1C1 function and some time I am use Formula function).

Thanks
sharad kumar

Attachment: Repaired_24ifformulaecheck_9b2f463.rar

Abirami Varadharajan [Syncfusion]
Replied On October 11, 2016 01:25 PM UTC

Hi Sharad, 

Please find the details for you queries below. 

Query 
Details 
My repaired file is not match to the original file  (cell F3,F11,F18,F25 have different value ). 
As same formula =IF(E12<>"","Closed","Open") is set to range from F2:F26, Closed is assigned to all the range as E12 is not empty. 

So, We suggest you to set shared formula for range F2:F26 to get the results as expected. Kindly refer below code snippet to achieve this. 

Code Example: 

            worksheet.EnableSheetCalculations(); 
            IRange formulaRange = worksheet.Range["F2:F26"]; 
            worksheet.Range["F2:F26"].FormulaR1C1 = "=IF(RC[-1]<>\"\",\"Closed\",\"Open\")"; 
            string dummy = null; 
            foreach (IRange range in formulaRange) 
            { 
                if (range.HasFormula) 
                    dummy = range.CalculatedValue; 
            }  
            worksheet.DisableSheetCalculations(); 
 
 how to differentiate shared formula in different cases (sometimes i am use FormulaR1C1 function and some time I am use Formula function). 
FormulaR1C1 property sets the incremental formula or shared formula. Formula property sets same formula value for all the range. 

Please let us know if you have any concerns. 

Regards, 
Abirami. 


Sharad Kumar
Replied On October 12, 2016 04:16 AM UTC

Thanks for replay.

Your script is run successfully .
In your script, formula value is changed manually .

I repair multiple excel file at one time which have multiple shared formula.
In case how can identify my formula have a cell number and where cell number is replace with RC[-1] in my formula value.



Thanks
sharad

Abirami Varadharajan [Syncfusion]
Replied On October 13, 2016 11:25 AM UTC

Hi Sharad, 
 
Thank you for updating us. 
 
We cannot automate the code to decide where to Formula and where to use FormulaR1C1. It should be decided during development by the developer based on the requirement. However, there is an another option where XlsIO supports to set incremental formula for a range internally. 
Code Example: 
            application.EnableIncrementalFormula = true; 
            worksheet.Range["F2:F26"].Formula = "=IF(E2<>\"\",\"Closed\",\"Open\")"; 
            application.EnableIncrementalFormula = false; 
 
The above code will set the formulas as below: 
 
Cell F3 = "=IF(E3<>\"\",\"Closed\",\"Open\")" 
Cell F4 = "=IF(E2<>\"\",\"Closed\",\"Open\")" 
 
NOTE: This approach need to be carried out if all the cell references of a formula need to be incremented. 
 
For example, If you need to set formula "=IF(E2<>B2,\"Closed\",\"Open\")" for range F2 to F26, the formula gets updated as "=IF(E5<>B5,\"Closed\",\"Open\")" for all the cell F5. 
 
Please let us know if you have any concerns. 
 
Regards, 
Abirami. 


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

;