How to create shared formula in Excel file

[Sharad Kumar]

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]

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. 

  

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

  

Regards, 

Sridhar S 

[Sharad Kumar]

Thank you Sridhar.

[Sridhar Sukumar]

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]

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]

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]

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]

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]

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.    Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/NumberFormat1275646791.zip

 

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]

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]

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 

 

Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/CreateFormula-466411547.zip 

 

Regards, 

Sridhar S. 

[Sharad Kumar]

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]

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]

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]

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]

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.