reptile7's JavaScript blog
Monday, March 09, 2020
 
JavaScript Is the New Excel
Blog Entry #407

Instant spreadsheet grid

Before leaving the College Tuition Chooser behind, I'd like to outline an alternative, JavaScript-based approach to the creation of a spreadsheet template; I'll confine the following discussion to the structure and presentation of the template and leave it to you to functionally kit out the structure as you see fit.

In the fourth part of this series we showed that the chooser's cost values can be added and cleared iteratively. More fundamentally, the chooser <input>s themselves can be created and deployed iteratively - why write out all that HTML if we don't have to?

We'll frame our template with a simple spreadsheetDiv div versus a table.

<form id="spreadsheetForm" name="spreadsheetForm">
...pre-spreadsheet controls...
<div id="spreadsheetDiv"></div>
</form>
...post-form JavaScript...

The original chooser table layout comprises 11 rows and 4 columns, and can be expanded or shrunk as desired. It would be better if the user could (within reason) set the number of rows and columns from the get-go:

Number of desired input rows: <input name="inputrows" size="5" /><br />
Number of desired input columns: <input name="inputcols" size="5" /><br />

We'll create the template via a createSpreadsheet( ) function that is called by clicking a button.

<button type="button" onclick="createSpreadsheet( );">Create My Spreadsheet</button>

<script type="text/javascript">
var spreadsheetForm = document.getElementById("spreadsheetForm");
var spreadsheetDiv = document.getElementById("spreadsheetDiv");
function createSpreadsheet( ) { ... }
... </script>

The worksheet cells of a standard spreadsheet are indexed with a top-edge row of A, B, C, ... identifiers and a left-edge column of 1, 2, 3, ... identifiers. Let's set the stage for adding these axes to the template by +1-ing the inputrows and inputcols numbers.

var inputrows = Number(spreadsheetForm.elements["inputrows"].value);
var inputcols = Number(spreadsheetForm.elements["inputcols"].value);
var allrows = inputrows + 1;
var allcols = inputcols + 1;

We're ready to create and deploy the template cells and format them a bit:

spreadsheetDiv.style.whiteSpace = "nowrap";
for (var i = 0; i < allrows * allcols; i++) {
    var cellInput = document.createElement("input");
    cellInput.style.width = "80px";
    cellInput.style.border = "solid 1px black";
    cellInput.style.margin = "0px";
    spreadsheetDiv.appendChild(cellInput);
    if (i % allcols == allcols - 1) spreadsheetDiv.appendChild(document.createElement("br")); }

The template rows are terminated not by the right edge of the viewport but by <br> line breaks placed after the i % allcols == allcols - 1 cells, e.g., if allcols is 10, then <br>s are placed after the i = 9, 19, 29, ... cells.

In the name of completeness: Upon setting the cellInput size to 10, bolding the A-Z headers (vide infra) causes the header cell width to shrink slightly with Firefox although not with other browsers, so I changed the size="10" setting to an approximately equivalent width:80px; setting, which is unaffected by the header bolding.

We can respectively populate the top axis's i = 1-26 fields with A-Z headers via:

var colIDs = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; /* This string can be defined globally, prior to the createSpreadsheet( ) function. */
if (1 <= i && i <= allcols - 1) { /* We'll address the colIDs.length < i situation in the next section. */
    cellInput.value = colIDs.charAt(i - 1); }

Alternatively, the fromCharCode( ) method of the String object can be pressed into service for this operation.

cellInput.value = String.fromCharCode(64 + i);
/* The decimal Unicode/ASCII code positions of the uppercase alphabetic characters run from 65 to 90. */

The headers can be bolded, horizontally centered, and readonly-ed in the normal way:

cellInput.style.fontWeight = "bold";
cellInput.style.textAlign = "center";
cellInput.readOnly = true;

We can respectively populate the left axis's i = allcols, allcols × 2, allcols × 3, ... fields with 1, 2, 3, ... headers via:

if (! (i % allcols)) { cellInput.value = i ? i / allcols : ""; }

The chooser's cost fields were given names matching their coordinates: a1, b1, c1, etc. Let's follow suit:

cellInput.name = (allcols < i) && (i % allcols) ? colIDs.charAt((i - 1) % allcols) + Math.floor(i / allcols) : cellInput.value;

The header fields are named per their values (A, B, C, ... 1, 2, 3, ...) but as a practical matter those fields don't need to be named at all.

Limits

Computer Hope's "How many sheets, rows, and columns can a spreadsheet have?" page reports that the worksheet for recent versions of Excel comprises 1,048,576 rows and 16,384 columns. Do we want to go that big with our template? I don't think so - sounds like a recipe for inducing a hang, if you ask me.

That said, we should be setting the bar higher than a 26-column worksheet.
We can use an

if (colIDs.length < i)
    cellInput.value = colIDs.charAt(Math.floor((i - 1) / colIDs.length - 1)) + colIDs.charAt((i - 1) % colIDs.length);

statement to extend the A-Z headers two-dimensionally à la a standard spreadsheet.

A ... Z AA, AB, ... AZ BA ... BZ ... ZA ... ZZ

This'll give us 702 (26 + 262) columns, which should suffice for most users.

The worksheet for the AppleWorks 5.0 spreadsheet module comprises 500 rows and 40 columns; template creation with these numbers takes about 7 seconds on my computer.

Demo, validation, erasure

I've baked a bit of validation into the demo below.
• Per AppleWorks 5.0, the inputrows number is capped at 500 and the inputcols number is capped at 40 - again, we don't want to cause any unnecessary freezes if we can help it.
• If an inputrows/inputcols value is less than 1 (a blank input is mapped to 0) or is NaN (the input was neither a number nor a blank), then it is set to 1.
• A decimal point-containing inputrows/inputcols number is Math.round( )-ed to the nearest integer.

if (500 < inputrows) { inputrows = 500; spreadsheetForm.inputrows.value = 500; }
if (inputrows < 1 || isNaN(inputrows)) { inputrows = 1; spreadsheetForm.inputrows.value = 1; }
if (/\./.test(inputrows)) { inputrows = Math.round(inputrows); spreadsheetForm.inputrows.value = Math.round(inputrows); } ...

The demo's JavaScript includes a clearAll( ) function that clears the inputrows and inputcols fields and zeroes out the spreadsheet

function clearAll( ) {
    spreadsheetForm.reset( );
    spreadsheetDiv.innerHTML = ""; }
/* In practice, I set the spreadsheetDiv.innerHTML to a set-at-the-outset metatext string - this is a demo, after all... */

and is called by clicking a button.

<button type="button" onclick="clearAll( );">Clear Everything</button>

OK, here we go - check the page source for the full coding.

Number of desired input rows:
Number of desired input columns:


Your spreadsheet template will be generated in this div and below this line of text.
We'll move on to a Super Calculator script in the following entry.
- posted by A. Peak @ 5:38 PM 0 comments

Powered by Blogger

Actually, reptile7's JavaScript blog is powered by Café La Llave. ;-)