Localized Formulas

DsExcel provides setFormulaLocal and getFormulaR1C1Local methods in IRange interface which can be used to retrieve or set localized formulas in the cells of a worksheet.

type=note

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

Refer to the following example code which uses ASC and JIS functions in Japanese culture and compares different formula methods.

//create a new workbook
Workbook workbook = new Workbook();
workbook.setCulture(Locale.JAPAN);
IWorksheet sheet = workbook.getActiveSheet();

sheet.getRange("$A$1:$A$3").setValue(new String[][] { { "Original" }, { "･ｴｩ`･?ﾉ･ｷ･ﾃ･ﾗ､ﾏ1ｷｬﾈﾋ壥､ﾇ､ｹ｡｣" }, { "Halfwidth" } });

sheet.getRange("A5").setValue("Fullwidth");

IRange a4 = sheet.getRange("A4");
IRange a6 = sheet.getRange("A6");

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

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

// Compare different formula properties.
sheet.getRange("$B$1:$F$1")
.setValue(new String[][] { { "FormulaLocal", "Formula", "FormulaR1C1Local", "FormulaR1C1" } });

sheet.getRange("$B$4:$E$4").setValue(new Object[][] {
{ a4.getFormulaLocal(), a4.getFormula(), a4.getFormulaR1C1Local(), a4.getFormulaR1C1() } });

sheet.getRange("$B$6:$E$6").setValue(new Object[][] {
{ a6.getFormulaLocal(), a6.getFormula(), a6.getFormulaR1C1Local(), a6.getFormulaR1C1() } });

// Arrange layout
sheet.getUsedRange().getColumns().autoFit();
sheet.getPageSetup().setIsPercentScale(false);
sheet.getPageSetup().setFitToPagesWide(1);
sheet.getPageSetup().setPrintHeadings(true);
    
//save to an pdf file
workbook.save("FormulaLocalAndJis.pdf");

The below image shows the output of above code:

type=note

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