0

For shits and giggles, I am working to create a spreadsheet application (comparable to Excel, inspired by The SCells Spreadsheet) on Salesforce.com

Each spreadsheet cell will correspond to one SObject record, which will have fields for rows and columns.

As with other spreadsheets, the columns will be identified by a base-26 number, represented by letters of the alphabet (e.g. A is 0, B is 1, AA is 27, etc.)

I've already figured out how to perform the conversion in Apex:

    public class SUTL_BaseAlphabetConverter {
    // Constructor --------------------------------------------------------------------------------------

    private static SUTL_BaseAlphabetConverter INSTANCE;

    private SUTL_BaseAlphabetConverter() {
    }

    public static SUTL_BaseAlphabetConverter getInstance() {
        if (INSTANCE == null) {
            INSTANCE = new SUTL_BaseAlphabetConverter();
        }
        return INSTANCE;
    }

    // Properties --------------------------------------------------------------------------------------

    private static final Integer ALPHABET_BASE_SIZE = 26;
    private static final Map<Integer, String> ALPHABET_BY_DECIMAL_MAP = new Map<Integer, String>();
    private static final Map<String, Integer> DECIMAL_BY_ALPHABET_MAP = new Map<String, Integer>();

    private static final List<String> CHARACTER_RANGE = new List<String>{
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',
        'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
        'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
    };

    private static final Map<String, Integer> POSITION_BY_CHARACTER {
        get {
            if (POSITION_BY_CHARACTER == null) {
                POSITION_BY_CHARACTER = new Map<String, Integer>();
                for (Integer i = 0; i < CHARACTER_RANGE.size(); i++) {
                    POSITION_BY_CHARACTER.put(CHARACTER_RANGE[i], i);
                }
            }
            return POSITION_BY_CHARACTER;
        }
        private set;
    }

    // Public Methods --------------------------------------------------------------------------------------

    public String toAlphabet(Integer value) {
        if (value < 1) {
            throw new SUTL_BaseAlphabetConverterException('No letters for 0 or less');
        }

        if(!ALPHABET_BY_DECIMAL_MAP.containsKey(value)) {
            String alphabet = '';
            DivisionResult divisionResult = new DivisionResult(value, 1);
            while (divisionResult.quotient != 0) {
                Integer decremented = divisionResult.quotient - 1;
                divisionResult = new DivisionResult(decremented, ALPHABET_BASE_SIZE);
                alphabet = CHARACTER_RANGE[divisionResult.remainder] + alphabet;
            }

            this.addToMaps(value, alphabet);
        }
        return ALPHABET_BY_DECIMAL_MAP.get(value);
    }

    public Integer toInteger(String alphabet) {
        String convertableString = alphabet.toUpperCase()
                .replaceAll('[^A-Z]', '');

        if(!DECIMAL_BY_ALPHABET_MAP.containsKey(convertableString)) {
            Integer value = 0;

            Integer orderOfMagnitude = 0;
            for (
                    Integer position = convertableString.length() - 1;
                    position > -1;
                    position--
            ) {
                Integer power = Integer.valueOf(Math.floor(Math.pow(ALPHABET_BASE_SIZE, orderOfMagnitude)));
                String character = convertableString.substring(position, position + 1);
                value += power * (POSITION_BY_CHARACTER.get(character) + 1);
                orderOfMagnitude ++;
            }

            this.addToMaps(value, convertableString);
        }
        return DECIMAL_BY_ALPHABET_MAP.get(convertableString);
    }

    // Helper Methods --------------------------------------------------------------------------------------

    private SUTL_BaseAlphabetConverter addToMaps(Integer value, String alphabet) {
        ALPHABET_BY_DECIMAL_MAP.put(value, alphabet);
        DECIMAL_BY_ALPHABET_MAP.put(alphabet, value);
        return this;
    }

    private class DivisionResult {
        public Integer quotient {get; private set;}
        public Integer remainder {get; private set;}

        public DivisionResult(Integer dividend, Integer devisor) {
            this.quotient = Integer.valueOf(Math.floor(dividend / devisor));
            this.remainder = Integer.valueOf(Math.mod(dividend, devisor));
        }
    }
}

But these are relatively expensive operations and it may sometimes prove useful or desirable to have the converted value readily available on the records.

Would this be possible?

2

You'll have to tweak it a little bit if you want A = 0, AA = 26, but this should do the trick:

IF(CONVERT_TO_BASE26__c >= 26^2, 
    CASE(FLOOR(CONVERT_TO_BASE26__c/(26^2)),
        1, 'A',
        2, 'B',
        3, 'C',
        4, 'D',
        5, 'E',
        6, 'F',
        7, 'G',
        8, 'H',
        9, 'I',
        10, 'J',
        11, 'K',
        12, 'L',
        13, 'M',
        14, 'N',
        15, 'O',
        16, 'P',
        17, 'Q',
        18, 'R',
        19, 'S',
        20, 'T',
        21, 'U',
        22, 'V',
        23, 'W',
        24, 'X',
        25, 'Y',
        26, 'Z',
        ' :: error: ' & TEXT(FLOOR(CONVERT_TO_BASE26__c/26^2))
    ),
    ''
)

&

IF(MOD(CONVERT_TO_BASE26__c, 26^2) >= 26, 
    CASE(FLOOR(MOD(CONVERT_TO_BASE26__c, 26^2)/26),
        1, 'A',
        2, 'B',
        3, 'C',
        4, 'D',
        5, 'E',
        6, 'F',
        7, 'G',
        8, 'H',
        9, 'I',
        10, 'J',
        11, 'K',
        12, 'L',
        13, 'M',
        14, 'N',
        15, 'O',
        16, 'P',
        17, 'Q',
        18, 'R',
        19, 'S',
        20, 'T',
        21, 'U',
        22, 'V',
        23, 'W',
        24, 'X',
        25, 'Y',
        26, 'Z',
        ' :: error: ' & TEXT(FLOOR(MOD(CONVERT_TO_BASE26__c, 26^2)/26))
    ),
    ''
)

&

IF(MOD(MOD(CONVERT_TO_BASE26__c, 26^2), 26) >= 1, 
    CASE(MOD(MOD(CONVERT_TO_BASE26__c, 26^2), 26),
        1, 'A',
        2, 'B',
        3, 'C',
        4, 'D',
        5, 'E',
        6, 'F',
        7, 'G',
        8, 'H',
        9, 'I',
        10, 'J',
        11, 'K',
        12, 'L',
        13, 'M',
        14, 'N',
        15, 'O',
        16, 'P',
        17, 'Q',
        18, 'R',
        19, 'S',
        20, 'T',
        21, 'U',
        22, 'V',
        23, 'W',
        24, 'X',
        25, 'Y',
        26, 'Z',
        ' :: error: ' & TEXT(MOD(MOD(CONVERT_TO_BASE26__c, 26^2), 26))
    ),
    ''
)

And now back to base10:

IF(LEN(CONVERT_TO_BASE10__c) > 3, 
    'Value must be 3 characters or fewer',

    IF(LEN(CONVERT_TO_BASE10__c) > 2),
        CASE(LEFT(CONVERT_TO_BASE10__c, 1),
            'A', 1*(26^2),
            'B', 2*(26^2),
            'C', 3*(26^2),
            'D', 4*(26^2),
            'E', 5*(26^2),
            'F', 6*(26^2),
            'G', 7*(26^2),
            'H', 8*(26^2),
            'I', 9*(26^2),
            'J', 10*(26^2),
            'K', 11*(26^2),
            'L', 12*(26^2),
            'M', 13*(26^2),
            'N', 14*(26^2),
            'O', 15*(26^2),
            'P', 16*(26^2),
            'Q', 17*(26^2),
            'R', 18*(26^2),
            'S', 19*(26^2),
            'T', 20*(26^2),
            'U', 21*(26^2),
            'V', 22*(26^2),
            'W', 23*(26^2),
            'X', 24*(26^2),
            'Y', 25*(26^2),
            'Z', 26*(26^2),
            0
        ),
    0)

    +

    IF(LEN(CONVERT_TO_BASE10__c) > 1),
        CASE(MID(CONVERT_TO_BASE10__c, 2, 1),
            'A', 1*26,
            'B', 2*26,
            'C', 3*26,
            'D', 4*26,
            'E', 5*26,
            'F', 6*26,
            'G', 7*26,
            'H', 8*26,
            'I', 9*26,
            'J', 10*26,
            'K', 11*26,
            'L', 12*26,
            'M', 13*26,
            'N', 14*26,
            'O', 15*26,
            'P', 16*26,
            'Q', 17*26,
            'R', 18*26,
            'S', 19*26,
            'T', 20*26,
            'U', 21*26,
            'V', 22*26,
            'W', 23*26,
            'X', 24*26,
            'Y', 25*26,
            'Z', 26*26,
            0
        ),
    0)

    + 

    IF(LEN(CONVERT_TO_BASE10__c) > 0),
        CASE(RIGHT(CONVERT_TO_BASE10__c, 1),
            'A', 1,
            'B', 2,
            'C', 3,
            'D', 4,
            'E', 5,
            'F', 6,
            'G', 7,
            'H', 8,
            'I', 9,
            'J', 10,
            'K', 11,
            'L', 12,
            'M', 13,
            'N', 14,
            'O', 15,
            'P', 16,
            'Q', 17,
            'R', 18,
            'S', 19,
            'T', 20,
            'U', 21,
            'V', 22,
            'W', 23,
            'X', 24,
            'Y', 25,
            'Z', 26,
            0
        ),
    0)
)

The only problem with the Base64 -> Base10 formula is that it's over the 5k character limit for formulas. There are a couple ways you can handle this:

  • You could chop off the first inner conditional, which would limit it to supporting A -- ZZ instead of A -- ZZZ. That would still give you support for just over 700 columns. Since sObjects (currently) have a limit of 800 fields, that might not do.

OR

  • You could split the 3 sections out into (2 or) 3 custom fields, and use a separate formula field to sum the others.

Let me know how it goes. This was kind'a fun! The perfect excuse to procrastinate :)

| improve this answer | |
  • Cheers for response! My pet is on back burner now, but will let you know when I have some time. :-) – Brian Kessler Nov 2 '18 at 23:20
  • 1
    I checked it in my dev org tonight. Looks like it's all working as it should. :) – Shane Steinfeld Nov 3 '18 at 3:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.