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?
