Localized Formulas | Document Solutions for Excel, Java Edition

Localized Formulas

In This Topic

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.

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.

Java	Copy Code
//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");

Java

Copy Code

//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:

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.