Formula Utility Methods

Following example demonstrates some GemBox.Spreadsheet for Java utility methods that can be useful for creating formulas.

Screenshot of Excel cell formula set with GemBox.Spreadsheet for Java
Excel cell formula set with GemBox.Spreadsheet for Java
import com.gembox.spreadsheet.*;

class Program {

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

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

        // Fill first column with values.
        for (int i = 0; i < 10; i++)
            worksheet.getCell(i, 0).setValue(i + 1);

        // Cell B1 has formula '=A1*2', B2 '=A2*2', etc.
        for (int i = 0; i < 10; i++)
            worksheet.getCell(i, 1).setFormula(String.format("=%1$s*2", CellRange.rowColumnToPosition(i, 0)));

        // Cell C1 has formula '=SUM(A1:B1)', C2 '=SUM(A2:B2)', etc.
        for (int i = 0; i < 10; i++)
            worksheet.getCell(i, 2).setFormula(String.format("=SUM(A%1$s:B%1$s)", ExcelRowCollection.rowIndexToName(i)));

        // Cell A12 contains sum of all values from the first row.
        worksheet.getCell("A12").setFormula(String.format("=SUM(A1:%1$s1)", ExcelColumnCollection.columnIndexToName(worksheet.getRow(0).getAllocatedCells().size() - 1)));

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

Check next example or download examples from GitHub.