Building a spreadsheet in Script# using HTML5
Building large scale applications in Javascript like Microsoft Excel can get tedious when it involves complex logic and data types. Using Script# to write such applications allows us to leverage the productivity of C#, Visual Studio IDE and related tools like ReSharper. In this tutorial, we will create a basic spreadsheet application in the Script# HTML5 Application project.
-
The source code for this tutorial is available in the scriptsharp-spreadsheet repository. You are encouraged to extend the spreadsheet and share your updates back into the repo.
Create a new Script# HTML5 Application project called Spreadsheet following the steps 1-4 from the Hello World tutorial.
Add a new stylesheet to the project and call it spreadsheet.css. We will add styling rules for our project in this file as needed.
Once you create the file, go to solution explorer and select spreadsheet.css. Then go the the properties window (F4) and set the option for "Copy to Output Directory" to "Copy Always" (just like we did for spreadsheet.html). This will ensure that the updated file is copied to the bin\debug folder every time we build the project.
Now, add a reference to spreadsheet.css in the html's head section:
<link rel="stylesheet" type="text/css" href="spreadsheet.css"/>
Next, create a div element in the body which will be used as the container for the spreadsheet:
<div id="spreadsheet">
</div>
We're done with modifying the html file for the rest of the tutorial. All our logic to generate and control the spreadsheet will be written in Script#.
Add a private field to reference the table that will represent the spreadsheet:
private readonly TableElement _table; // Table element that will contain the spreadsheet
Create a public constructor and initializes the table in it:
/// <summary>
/// Spreadsheet Constructor
/// </summary>
public Sheet()
{
// Create a Table Element in memory
_table = (TableElement)Document.CreateElement("table");
}
Next, let's add a method to render the table in the HTML:
/// <summary>
/// This function renders the spreadsheet in an HTML element
/// </summary>
/// <param name="divName">Name of a container div to create the spreadsheet in</param>
public void Render(string divName)
{
// Get a reference to the Div Element and add the table as its child element
Document.GetElementById<DivElement>(divName).AppendChild(_table);
}
using Spreadsheet;
In the static constructor of Page.cs, create an instance of the Sheet class and assign the table that it generates as a child of the spreadsheet div element that we created in the HTML file.
static Page()
{
// Create a new instance of the spreadsheet
Sheet sheet = new Sheet();
// Render the spreadsheet in the "spreadsheet" div element of the html file
sheet.Render("spreadsheet");
}
FYI, here's what that code is translated into in Javascript:
(function() {
var sheet = new Sheet();
sheet.render('spreadsheet');
})();
Notice that comments are not part of the output and this is the unminified version of the output file. As we proceed with the tutorial, feel free to view the generated javascript code in spreadsheet.js. It's a nice way to brush up on Javascript if you are a bit rusty.
Now we're done modifying Page.cs for the rest of this tutorial. The remaining logic will be placed in sheet.cs and the related style rules will be placed in spreadsheet.css.
Next, go to the bin\debug folder and run the application by double clicking spreadsheet.html. Ensure that there are no errors in the the console debugger and if everything looks good let's proceed with the fun stuff!!!
Add a method called RenderColumnHeaders(). In there we will create a row for column headers and add cells for each column with titles that are alphabets from A-Z. The first column will be blank as it will be the column that also holds the row headers.
// Render Column Headers
private void RenderColumnHeaders()
{
// Create a row for the column headers
TableRowElement header = _table.InsertRow();
header.ID = "headerRow";
// Create a cell for the first column where we will also add row headers
TableCellElement blankCell = header.InsertCell(0);
blankCell.ID = "blankCell";
// Create 26 Column Headers and name them with the letters of the English Alphabets
// Iterating through the ASCII indexes for A-Z
for (int i = 65; i < 91; i++)
{
// Create a cell element in the header row starting at index 1
TableCellElement cell = header.InsertCell(i - 64);
// Set the cell id to the letter corresponding to the ASCII index
cell.ID = string.FromCharCode(i);
// Set the value of the cell to the letter corresponding to the ASCII index
cell.TextContent = string.FromCharCode(i);
}
}
Call the above method in the Sheet() constructor:
public Sheet()
{
// Create a Table Element in memory
_table = (TableElement)Document.CreateElement("table");
RenderColumnHeaders();
}
Build the project and load the spreadsheet.html file. You should see something like this::
This doesn't look like much, but we have created the columns for our spreadsheet . If you look in the debugger elements, you will see something like this:
Let's create a function called called RenderRows(int rowCount) that will generate rows including row headers, cells per column and a text input element per cell :
/// <summary>
/// Create rows in the spreadsheet
/// </summary>
/// <param name="rowCount">Number of rows to create</param>
private void RenderRows(int rowCount)
{
// We're iterating from row index 1 because we want the title of the rows
// to be equal to the index. In addition, row 0 is the column header row
for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
{
// Create a new row in the table
TableRowElement row = _table.InsertRow(rowIndex);
// Create a row header cell and set its id and value to the row index
TableCellElement cell = row.InsertCell(0);
cell.ID = rowIndex.ToString();
cell.TextContent = rowIndex.ToString();
// Create cells for each column in the spreadsheet from A to Z
for (int cellIndex = 65; cellIndex < 91; cellIndex++)
{
// Insert cells at the corresponding column index (starting from column 1)
cell = row.InsertCell(cellIndex - 64);
// Create a text input element inside the cell
InputElement input = (InputElement)Document.CreateElement("input");
input.Type = "text";
// Set the ID of the element to the Column Letter and Row Number like A1, B1, etc.
input.ID = string.FromCharCode(cellIndex) + rowIndex;
// Add the input element as a child of the cell
cell.AppendChild(input);
}
}
}
Call the above method in the Sheet() constructor:
public Sheet()
{
// Create a Table Element in memory
_table = (TableElement)Document.CreateElement("table");
RenderColumnHeaders();
RenderRows(20);
}
After you build and reload the page in the browser, you will see something like this:
This is now starting to look like a spreadsheet, but let's make it a little prettier before we proceed any further.
In this section we will add several CSS elements to spreadsheet.css to make our output look more like a spreadsheet than a table.
Let's constrain the spreadsheet div to the width of the page with horizontal and vertical scrollbars as needed and create a border around it:
#spreadsheet {
overflow-y: auto;
overflow-x: auto;
border: 1px solid darkgray;
width: 100%;
}
Collapse the border between table elements into a single border:
table {
border-collapse: collapse;
}
Compress the border between columns by removing all the padding and margin between columns and change the border style:
td {
border: 1px solid lightgray;
padding: 0;
margin: 0;
}
Add styling to the row and column headers:
tr:first-child td, td:first-child {
background-color: whitesmoke;
text-align: center;
border-color: darkgray;
padding: 1px 3px;
}
Add hover effects to the row and column:
tr:first-child td:hover, td:first-child:hover {
background-color: lightgray;
color: darkgreen;
border-bottom: 3px solid darkgreen;
cursor: pointer;
}
Add text input styling along with focus and hover effects:
input {
text-align: right;
border: none;
height: 22px;
width: 80px;
font-size: 14px;
}
input:hover {
border: solid;
border-width: 2px;
border-color: darkgreen;
cursor: default;
}
input:focus {
text-align: left;
border: solid;
border-color: darkgreen;
border-width: 2px;
outline: none;
}
Finally, add a class that we will use later in Script# to dynamically attach to a selected cell's row and column headers:
.selected {
background-color: lightgray !important;
color: darkgreen;
border-bottom: 3px solid darkgreen !important;
}
Now, we've polished our table to look more like a spreadsheet. Build and reload the page to see something like this:
Let's add a method called ProcessCell(Element element) which will analyze the input text and store it as a formula as well as display the result.
/// <summary>
/// Process the text entered in a text input element.
/// Extract a formula and store it as part of the element.
/// Process the formula and display the result in the text element.
/// </summary>
/// <param name="input">Input element of a cell to process</param>
private void ProcessCell(InputElement input)
{
// Ensure that there's a value in the input element that is a formula involving another cell
if (input.Value.Length > 4 && input.Value.StartsWith("="))
{
// Set the input value as a data-formula attribute on the text input element
input.SetAttribute("data-formula", input.Value);
// For this tutorial, we will split the formula on the "+" operation only
string[] items = input.Value.Substring(1).Split("+");
Number result = 0;
// Traverse through each item in the equation
foreach (string item in items)
{
// If the item is not a number, it is assumed to be a formula
if (Number.IsNaN((Number)(object)item))
{
// Get a reference to the cell, parse its value and then add it to our result
result += Number.Parse(((InputElement)Document.GetElementById(item)).Value);
}
else
{
result += Number.Parse(item);
}
}
// Replace the input's formula with the result. We've stored a reference to the formula as part of its data-formula attribute
input.Value = result.ToString();
}
}
Let's create a method called AttachEvents(InputElement input) and call it from the RenderRows(int rowCount) method after we create the TextInput Element and attach it to the cell.
Add the following code in RenderRows(int rowCount) after the cell.AppendChild(input) call:
// Create and attach spreadsheet events to this input element
AttachEvents(input);
In the AttachEvents(InputElement input) method, let's create a blur and focus event:
// OnBlur, call ProcessCell with the element that lost focus
input.AddEventListener("blur", delegate(ElementEvent @event)
{
ProcessCell((InputElement)@event.SrcElement);
}, false);
// OnFocus, get the data-formula attribute value for the element
// and display it so the user can modify it if needed
input.AddEventListener("focus", delegate(ElementEvent @event)
{
object formula = @event.SrcElement.GetAttribute("data-formula");
if (formula != null && formula.ToString().Length > 1)
{
input.Value = formula.ToString();
}
}, false);
Now, we have a functional spreadsheet that allows us to enter formulas like "=D9+E11+8" including referencing other cells.
In this section, we will add a few more events to make our spreadsheet a bit more user friendly. By now you should be familiar with how all this has come together so we'll zip through this section.
Let's add events to highlight the row and column for the selected cells by dynamically attaching a class called "selected" that we created earlier at the bottom of spreadsheet.css. Before we can do that, we need to create a couple of helper methods which will allow us to get a reference to the row and column of an element:
Get reference to column header:
private TableCellElement GetColumnHeader(Element element)
{
return Document.GetElementById<TableCellElement>(element.ID.Substring(0, 1));
}
Get reference to row header:
private TableRowElement GetRowHeader(Element element)
{
return Document.GetElementById<TableRowElement>(element.ID.Substring(1));
}
Modify the blur event to include:
// Dynamically remove class="selected" on the row and column of the element that lost focus
GetRowHeader(@event.SrcElement).ClassList.Remove("selected");
GetColumnHeader(@event.SrcElement).ClassList.Remove("selected");
Modify the focus event to include:
// Dynamically add class="selected" on the row and column of the element that received focus
GetRowHeader(@event.SrcElement).ClassList.Add("selected");
GetColumnHeader(@event.SrcElement).ClassList.Add("selected");
Next, **add keyboard events that allow us to press escape to cancel an edit, enter to process a formula or arrow keys to navigate through the spreadsheet. Before creating those events, we'll also add a helper method that will allow jumping to a neighboring cell by specifying the distance:
/// <summary>
/// This method allows us to specify a cell and set focus to one of its neighboring cells
/// </summary>
/// <param name="id">Text Input Element id for the source cell</param>
/// <param name="horizontal">Direction to move horizontally. Negative value moves left.</param>
/// <param name="vertical">Direction to move vertically. Negative value moves up.</param>
private void SetFocusFromCellTo(string id, int horizontal, int vertical)
{
int row = int.Parse(id.Substring(1));
string column = id.Substring(0, 1);
InputElement element = Document.GetElementById<InputElement>(string.Format("{0}{1}", string.FromCharCode(column.CharCodeAt(0) + horizontal), row + vertical));
if (element != null)
{
element.Focus();
}
}
Finally, in AttachEvents(InputElement element), add the following events:
Event that processes a cell on pressing enter and moves to the bottom cell:
input.AddEventListener("keypress", delegate(ElementEvent @event)
{
if (@event.KeyCode == 13) // Enter Key
{
input.Blur();
SetFocusFromCellTo(@event.SrcElement.ID, 0, 1);
@event.PreventDefault();
}
}, false);
Event that processes arrow keys to navigate through the spreadsheet as well as the escape key to cancel an edit that's in progress:
input.AddEventListener("keydown", delegate(ElementEvent @event)
{
if (@event.KeyCode == 27) //Escape
{
input.Value = "";
input.Blur();
}
else if (@event.KeyCode == 38) // up arrow
{
SetFocusFromCellTo(@event.SrcElement.ID, 0, -1);
@event.PreventDefault();
}
else if (@event.KeyCode == 40) // down arrow
{
SetFocusFromCellTo(@event.SrcElement.ID, 0, 1);
@event.PreventDefault();
}
else if (@event.KeyCode == 37) // left arrow
{
SetFocusFromCellTo(@event.SrcElement.ID, -1, 0);
@event.PreventDefault();
}
else if (@event.KeyCode == 39) // right arrow
{
SetFocusFromCellTo(@event.SrcElement.ID, 1, 0);
@event.PreventDefault();
}
}, true);
You have now learned to build an HTML5 Application in Script#. Please don't hesitate to ask questions on StackOverflow or post to the Script# issue tracker with suggestions for improving the tutorial or new tutorials.
As mentioned earlier, the source code for this tutorial is available in the scriptsharp-spreadsheet repository. You are encouraged to extend the spreadsheet and share your updates back into the repo.