Documents for Excel, Java Edition Documentation
Features / Formulas / Localized Formulas
In This Topic
    Localized Formulas
    In This Topic

    GcExcel 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");

     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.