Excel cell Formula

GemBox.Spreadsheet can read and write formulas, but the level of support depends on the Excel file format:

  • In XLS, all common and some advanced formulas are supported.
  • In XLSX, all formulas are supported. You only need to avoid array formulas in template files.
  • In ODS, all formulas are supported. However, OpenOffice formulas don't have the same syntax as Excel formulas.

Formulas can't be exported to CSV or HTML file formats.

Also note that formula values are automatically calculated when a file is opened in Excel.

Excel cell formulas set with GemBox.Spreadsheet for Java
Screenshot of Excel cell formulas set with GemBox.Spreadsheet for Java
View on GitHub 
import com.gembox.spreadsheet.*;

class Program {

    public static void main(String[] args) throws java.io.IOException {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");

        ExcelFile workbook = new ExcelFile();
        ExcelWorksheet worksheet = workbook.addWorksheet("Formula");

        int rowIndex = 0;

        worksheet.getColumn(0).setWidth(35 * 256);
        worksheet.getColumn(1).setWidth(15 * 256);
        worksheet.getColumn(2).setWidth(15 * 256);

        worksheet.getCell(rowIndex++, 0).setValue("Examples of typical formulas usage:");

        worksheet.getCell(++rowIndex, 0).setValue("Some data:");
        worksheet.getCell(rowIndex, 1).setValue(3);
        worksheet.getCell(rowIndex, 2).setValue(4.1);
        worksheet.getCell(++rowIndex, 1).setValue(5.2);
        worksheet.getCell(rowIndex, 2).setValue(6);
        worksheet.getCell(++rowIndex, 1).setValue(7);
        worksheet.getCell(rowIndex++, 2).setValue(8.3);

        // Named ranges.
        String namedRange = "Range1";
        worksheet.addNamedRange(namedRange, worksheet.getCells().getSubrange("B3", "C4"));

        // Floats without first digit.
        worksheet.getCell(++rowIndex, 0).setValue("Float number without first digit:");
        worksheet.getCell(rowIndex, 1).setFormula("=.5/23+.1-2");

        // Function using named range.
        worksheet.getCell(++rowIndex, 0).setValue("Named range:");
        worksheet.getCell(rowIndex, 1).setFormula("=SUM(" + namedRange + ")");

        // Function's miss argument.
        worksheet.getCell(++rowIndex, 0).setValue("Function's miss arguments:");
        worksheet.getCell(rowIndex, 1).setFormula("=Count(1,  ,  ,,,2, 23,,,,,, 34,,,54,,,,  ,)");

        // Functions are case-insensitive.
        worksheet.getCell(++rowIndex, 0).setValue("Functions are case-insensitive:");
        worksheet.getCell(rowIndex, 1).setFormula("=cOs( 1 )");

        // Functions.
        worksheet.getCell(++rowIndex, 0).setValue("Supported functions:");

        String nextFunction;
        worksheet.getCell(++rowIndex, 0).setValue("Results");
        worksheet.getCell(rowIndex++, 1).setValue("Formulas");

        nextFunction = "=NOW()+123";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=SECOND(12)/23";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=MINUTE(24)-1343/35";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=(HOUR(56)-23/35)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=WEEKDAY(5)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=YEAR(23)-WEEKDAY(5)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=MONTH(3)-2342/235345";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=((DAY(1)))";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=TIME(1,2,3)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=DATE(1,2,3)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=RAND()";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=TEXT(\"text\", \"$d\")";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=VAR(1,2)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=MOD(1,2)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=NOT(FALSE)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=OR(FALSE)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=AND(TRUE)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=FALSE()";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=TRUE()";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=VALUE(3)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=LEN(\"hello\")";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=MID(\"hello\",1,1)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=ROUND(1,2)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=SIGN(-2)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=INT(3)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=ABS(-3)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=LN(2)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=EXP(4)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=SQRT(2)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=PI()";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=COS(4)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=SIN(3)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=MAX(1,2)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=MIN(1,2)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=AVERAGE(1,2)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=SUM(1,3)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=IF(1,2,3)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=COUNT(1,2,3)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        nextFunction = "=SUBTOTAL(1,B3:C5)";
        worksheet.getCell(rowIndex, 0).setFormula(nextFunction);
        worksheet.getCell(rowIndex++, 1).setValue(nextFunction);

        // Paranthless checks.
        worksheet.getCell(++rowIndex, 0).setValue("Paranthless:");
        worksheet.getCell(rowIndex, 1).setFormula("=((12+2343+34545))");

        // Unary operators.
        worksheet.getCell(++rowIndex, 0).setValue("Unary operators:");
        worksheet.getCell(rowIndex, 1).setFormula("=B5%");
        worksheet.getCell(rowIndex, 2).setFormula("=+++B5");

        // Operand tokens, bool.
        worksheet.getCell(++rowIndex, 0).setValue("Bool values:");
        worksheet.getCell(rowIndex, 1).setFormula("=TRUE");
        worksheet.getCell(rowIndex, 2).setFormula("=FALSE");

        // Operand tokens, int.
        worksheet.getCell(++rowIndex, 0).setValue("Integer values:");
        worksheet.getCell(rowIndex, 1).setFormula("=1");
        worksheet.getCell(rowIndex, 2).setFormula("=20");

        // Operand tokens, num.
        worksheet.getCell(++rowIndex, 0).setValue("Float values:");
        worksheet.getCell(rowIndex, 1).setFormula("=.4");
        worksheet.getCell(rowIndex, 2).setFormula("=2235.5132");

        // Operand tokens, str.
        worksheet.getCell(++rowIndex, 0).setValue("String values:");
        worksheet.getCell(rowIndex, 1).setFormula("=\"hello world!\"");

        // Operand tokens, error.
        worksheet.getCell(++rowIndex, 0).setValue("Error values:");
        worksheet.getCell(rowIndex, 1).setFormula("=#NULL!");
        worksheet.getCell(rowIndex, 2).setFormula("=#DIV/0!");

        // Binary operators.
        worksheet.getCell(++rowIndex, 0).setValue("Binary operators:");
        worksheet.getCell(rowIndex, 1).setFormula("=(1)-(2)+(3/2+34)/2+12232-32-4");

        workbook.save("Formula.%OutputFileType%");
    }
}

See also

Excel header and footer | GemBox.Spreadsheet for Java Example

Excel header and footer | GemBox.Spreadsheet for Java Example

Next steps

GemBox.Spreadsheet for Java is a component that enables you to read, write, edit and convert spreadsheet files from your applications using one simple API.

Download Buy
Published: December 13, 2018 | Modified: December 19, 2022 | Author: Marek Turis