Localized Formulas

GcExcel provides the FormulaLocal and FormulaR1C1Local properties in IRange interface which can be used to retrieve or set localized formulas in the cells of a worksheet. For example, if you set a function in English Excel by using these properties and you open the Excel in Japanese culture, the equivalent localized formula will be used to display the result in Japanese Excel.

Note: These properties support localized formulas only for Japanese and Chinese cultures.

Refer to the following example code which sets the workbook culture as Japanese, sets ASC and JIS functions to FormulaLocal properties and further retrieves the general and localized function names.

 //create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

workbook.Culture = CultureInfo.GetCultureInfo("ja-JP");
var sheet = workbook.ActiveSheet;

sheet.Range["$A$1:$A$3"].Value = new[,]
{
{"Original"},
{"ゴールドシップは1番人気です。"},
{"Halfwidth"}
};

sheet.Range["A5"].Value = "Fullwidth";

var a4 = sheet.Range["A4"];
var a6 = sheet.Range["A6"];

// Equivalent: Formula = "ASC(A2)"
// Because ASC doesn't have localized name in Japanese Excel.
a4.FormulaLocal = "=ASC(A2)";

// Equivalent: Formula = "DBCS(A2)"
// Because JIS is localized name of DBCS in Japanese Excel.
a6.FormulaLocal = "=JIS(A2)";

// Compare different formula properties.
sheet.Range["$B$1:$F$1"].Value = new[,]
{
{nameof(IRange.FormulaLocal), nameof(IRange.Formula),
nameof(IRange.FormulaR1C1Local), nameof(IRange.FormulaR1C1)}
};

sheet.Range["$B$4:$E$4"].Value = new[,]
{
{a4.FormulaLocal, a4.Formula, a4.FormulaR1C1Local, a4.FormulaR1C1}
};

sheet.Range["$B$6:$E$6"].Value = new[,]
{
{a6.FormulaLocal, a6.Formula, a6.FormulaR1C1Local, a6.FormulaR1C1}
};

// Arrange layout
sheet.UsedRange.Columns.AutoFit();
sheet.PageSetup.IsPercentScale = false;
sheet.PageSetup.FitToPagesWide = 1;
sheet.PageSetup.PrintHeadings = true;

//save to a pdf file
workbook.Save("formulalocalandjis.pdf");
}

The below image shows the output of above code:

jis and asc function with localized formula properties

Note: Excel does not support multi-byte string conversions. Hence, the output is saved to a PDF file.