I'm trying to add a constraint to one of my columns which would prevent entering values in the column C that are lesser than those in the column B. For example, if the value in B1 is 10, inputting 7 in the column C1 shouldn't be allowed. If the value in B2 is 4, inputting anything less than 4 in the column C1 shouldn't be allowed, etc.
So far I managed to produce a simple file where each cell in the third column is a sum of first and second column's cells - C1=A1+B1, C2=A2+B2... (Notice that column A is used only for the sake of this example). downloadSheet() function is where the the logic is implemented.
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.0/xlsx.full.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.0/FileSaver.min.js"></script>
<button onclick="downloadSheet()">Download excel</button>
<script>
function uigrid_to_sheet(data, columns) {
var o = [],
oo = [],
i = 0,
j = 0;
/* column headers */
for (j = 0; j < columns.length; ++j) oo.push((columns[j]));
o.push(oo);
/* table data */
for (i = 0; i < data.length; ++i) {
oo = [];
for (j = 0; j < data[i].length; ++j) oo.push((data[i][j]));
o.push(oo);
}
/* aoa_to_sheet converts an array of arrays into a worksheet object */
return XLSX.utils.aoa_to_sheet(o);
}
var s2ab = function (s) {
var buf = new ArrayBuffer(s.length); //convert s to arrayBuffer
var view = new Uint8Array(buf); //create uint8array as viewer
for (var i=0; i<s.length; i++) view[i] = s.charCodeAt(i) & 0xFF; //convert to octet
return buf;
}
function downloadSheet() {
var sheetName = 'first_sheet';
var wopts = { bookType: 'xlsx', bookSST: true, type: 'binary' };
var fileName = "the_excel_file.xlsx";
var columns = ['first', 'second', 'result'];
var data = [
[1, 20],
[2, 32],
[3, 18],
[4, 11]
];
var wb = XLSX.utils.book_new();
var ws = uigrid_to_sheet(data, columns);
ws['!ref'] = XLSX.utils.encode_range({
s: { c: 0, r: 0 },
e: { c: 2, r: 0 + data.length }
});
data.forEach((element, i) => {
ws['C'+(i+2)] = { f: 'A'+(i+2) + '+' + 'B'+(i+2) };
});
XLSX.utils.book_append_sheet(wb, ws, sheetName);
var wbout = XLSX.write(wb, wopts);
saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream' }), fileName);
}
</script>
Logically and functionally what I require is different, but I can't seem to figure what formula (or other method) should I use. I tried simply using formula
ws['C1'] = { f: 'C1>=B1' };
but this simpleton attempt unsurprisingly failed with a message that it caused a circular reference.
This requirement was made because our systems allow updating our web shop's prices through an excel file which can be manually edited and sent to server. The problem is that guys in that department can sometimes accidentally omit or add a cypher and then we get ourselves incorrect prices on the web shop. Column B in my example is the purchase price, column C is the Web Shop price, and values in column C should never be allowed to get under that purchase price in column B.
Edited to include entire HTML so someone can just copy paste it into a new HTML file and it should work.