Creating interactive web applications often requires integrating various technologies. SpreadsheetWeb provides a robust platform that allows developers to build applications using Excel as a backend, combined with custom JavaScript, CSS, and HTML. In this blog post, we'll explore how to create a drawing application using SpreadsheetWeb's script and stylesheet features.
Overview
Our application will enable users to draw on a canvas, snap their drawings to a grid, undo and redo actions, clear the canvas, and save. The application will also track the coordinates of the drawn lines in an Excel table and calculate the area enclosed by the lines.
Prerequisites For Drawing Application
Before starting, ensure you have the following:
- A SpreadsheetWeb account.
- Basic knowledge of JavaScript, CSS, and HTML.
- An Excel file with a table named coordinates for storing line coordinates and a cell named area for calculating the enclosed area.
Step-by-Step Guide
Setting Up the Excel File
Create an Excel file with a table named coordinates with two columns: X and Y. This table will store the coordinates of the points where the user draws lines. Also, add a cell named area where we will calculate the enclosed area using Excel formulas. The cell area can be defined using a formula that computes the area of the polygon formed by the coordinates in the coordinates table.
Adding JavaScript to the Script Feature
Next, we will add the JavaScript code to the Script feature of SpreadsheetWeb. This script will handle the drawing logic, including snapping to a grid, tracking coordinates, and managing undo and redo actions.
$.getScript("https://cdnjs.cloudflare.com/ajax/libs/two.js/0.8.12/two.min.js", function() {
$(document).ready(function() {
// Parameters for the drawing area
var params = {
fullscreen: false,
width: $(".my-drawing").width(),
height: $(".my-drawing").height(),
type: Two.Types.canvas,
autostart: true
};
// Initialize the drawing area
var elem = $(".my-drawing")[0];
var two = new Two(params).appendTo(elem);
var drawing = false;
var lines = [];
var undoStack = [];
var redoStack = [];
var coordinatesStack = [];
var coordinateRow = 0;
var points = [];
var startPoint = null;
var previewLine = null;
// Draw a grid on the canvas
function drawGrid() {
var gridSize = 20;
for (var x = 0; x < params.width; x += gridSize) {
var line = two.makeLine(x, 0, x, params.height);
line.stroke = '#ccc';
line.linewidth = 0.5;
two.add(line);
}
for (var y = 0; y < params.height; y += gridSize) {
var line = two.makeLine(0, y, params.width, y);
line.stroke = '#ccc';
line.linewidth = 0.5;
two.add(line);
}
two.update();
}
// Snap the drawing point to the nearest grid intersection
function snapToGrid(vector) {
var gridSize = 20;
var x = Math.round(vector.x / gridSize) * gridSize;
var y = Math.round(vector.y / gridSize) * gridSize;
return new Two.Vector(x, y);
}
// Get the mouse position relative to the canvas
function getMousePos(event) {
var rect = elem.getBoundingClientRect();
return new Two.Vector(event.clientX - rect.left, event.clientY - rect.top);
}
// Start a drawing line
function startDrawing(event) {
var mouse = getMousePos(event);
var snappedMouse = snapToGrid(mouse);
var point = { x: snappedMouse.x, y: snappedMouse.y };
if (startPoint === null) {
startPoint = point;
points.push(point);
saveCoordinatesToGrid(point, false);
} else {
var line = two.makeLine(points[points.length - 1].x, points[points.length - 1].y, point.x, point.y);
line.stroke = 'blue';
line.linewidth = 2;
line.noFill();
two.add(line);
lines.push(line);
points.push(point);
two.update();
saveCoordinatesToGrid(point, false);
if (point.x === startPoint.x && point.y === startPoint.y) {
completeDrawing();
}
}
}
// Preview the next line segment
function previewDrawing(event) {
if (startPoint === null || points.length === 0) return;
var mouse = getMousePos(event);
var snappedMouse = snapToGrid(mouse);
if (previewLine !== null) {
two.remove(previewLine);
}
previewLine = two.makeLine(points[points.length - 1].x, points[points.length - 1].y, snappedMouse.x, snappedMouse.y);
previewLine.stroke = 'gray';
previewLine.linewidth = 1;
previewLine.dashes = [2, 2];
two.add(previewLine);
two.update();
}
// Complete the drawing when a closed shape is formed
function completeDrawing() {
drawing = false;
points = [];
startPoint = null;
if (previewLine !== null) {
two.remove(previewLine);
previewLine = null;
}
two.update();
}
// Save coordinates to the grid
function saveCoordinatesToGrid(point, isRedo) {
var inputGrid1 = pagosApp.inputGrids.byNameRange("coordinates");
var gridValue = inputGrid1.value();
if (!isRedo) {
coordinateRow = findFirstEmptyRow();
}
gridValue[coordinateRow] = [point.x / 20, point.y / 20];
inputGrid1.value(gridValue);
var coordinates = {
row: coordinateRow,
x: point.x,
y: point.y
};
if (!isRedo) {
coordinateRow++;
}
coordinatesStack.push(coordinates);
}
// Find the first empty row in the grid
function findFirstEmptyRow() {
var inputGrid1 = pagosApp.inputGrids.byNameRange("coordinates");
var gridValue = inputGrid1.value();
for (var i = 1; i < gridValue.length; i++) {
if (!gridValue[i][0]) {
return i;
}
}
return 1;
}
// Clear coordinates from the grid
function clearCoordinatesFromGrid(coordinates) {
var inputGrid1 = pagosApp.inputGrids.byNameRange("coordinates");
var gridValue = inputGrid1.value();
gridValue[coordinates.row] = ["", ""];
inputGrid1.value(gridValue);
}
// Clear all coordinates from the grid
function clearAllCoordinatesFromGrid() {
var inputGrid1 = pagosApp.inputGrids.byNameRange("coordinates");
var gridValue = inputGrid1.value();
for (var i = 1; i < gridValue.length; i++) {
gridValue[i] = ["", ""];
}
inputGrid1.value(gridValue);
}
// Undo the last drawing action
function undo() {
if (lines.length > 0) {
var line = lines.pop();
two.remove(line);
var coordinates = coordinatesStack.pop();
clearCoordinatesFromGrid(coordinates);
points.pop();
undoStack.push({line: line, coordinates: coordinates});
redoStack.push(undoStack.pop());
two.update();
if (points.length === 0) {
startPoint = null;
}
}
}
// Redo the last undone action
function redo() {
if (redoStack.length > 0) {
var redoItem = redoStack.pop();
undoStack.push(redoItem);
two.add(redoItem.line);
lines.push(redoItem.line);
points.push({ x: redoItem.coordinates.x, y: redoItem.coordinates.y });
coordinateRow = findFirstEmptyRow();
saveCoordinatesToGrid({ x: redoItem.coordinates.x , y: redoItem.coordinates.y }, true);
two.update();
}
}
// Clear the drawing
function clearDrawing() {
lines.forEach(function(line) {
two.remove(line);
});
lines = [];
points = [];
startPoint = null;
if (previewLine !== null) {
two.remove(previewLine);
previewLine = null;
}
two.update();
clearAllCoordinatesFromGrid();
undoStack = [];
redoStack = [];
coordinatesStack = [];
coordinateRow = 0;
}
// Save the drawing as a PNG image
function saveDrawing() {
var canvas = two.renderer.domElement;
var dataURL = canvas.toDataURL('image/png');
var a = document.createElement('a');
a.href = dataURL;
a.download = 'drawing.png';
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
}
// Draw existing lines from the coordinates grid
function drawExistingLines() {
var inputGrid1 = pagosApp.inputGrids.byNameRange("coordinates");
var gridValue = inputGrid1.value();
var lastPoint = null;
for (var i = 1; i < gridValue.length; i++) {
var x = gridValue[i][0] * 20;
var y = gridValue[i][1] * 20;
if (x && y) {
if (lastPoint) {
var line = two.makeLine(lastPoint.x, lastPoint.y, x, y);
line.stroke = 'blue';
line.linewidth = 2;
line.noFill();
two.add(line);
lines.push(line);
}
lastPoint = { x: x, y: y };
points.push(lastPoint);
}
}
two.update();
}
elem.addEventListener('mousedown', startDrawing);
elem.addEventListener('mousemove', previewDrawing);
// Event listeners for undo, redo, clear, and save buttons
$('.button1').on('mousedown', function(event) {
event.preventDefault();
event.stopPropagation();
undo();
});
$('.button2').on('mousedown', function(event) {
event.preventDefault();
event.stopPropagation();
redo();
});
$('.button3').on('mousedown', function(event) {
event.preventDefault();
event.stopPropagation();
clearDrawing();
});
$('.button4').on('mousedown', function(event) {
event.preventDefault();
event.stopPropagation();
saveDrawing();
});
drawGrid();
drawExistingLines();
});
});
Adding CSS for Drawing Elements to the Stylesheet Feature
To style our area and buttons, we'll add the following CSS code to the Stylesheet feature of SpreadsheetWeb.
.my-drawing {
height: 750px;
width: 100%;
border: 1px solid #000;
position: relative;
}
.buttons {
position: absolute;
top: 10px;
left: 10px;
z-index: 10;
}
.button1, .button2, .button3, .button4 {
margin-right: 5px;
padding: 5px 10px;
background-color: #f0f0f0;
border: 1px solid #ccc;
cursor: pointer;
display: inline-block;
}
Adding HTML Elements for Drawing to the Content Label
Finally, we need to add the HTML code to the content label in the UI. Switch to code view and paste the following HTML code:
<div class="my-drawing">
<div class="buttons">
<div class="button1">Undo</div>
<div class="button2">Redo</div>
<div class="button3">Clear</div>
<div class="button4">Save</div>
</div>
</div>
Configuring the Interface
- Add a Textbox: Add a textbox to the interface and bind it to the area named range to display the calculated area.
- Add an Input Grid: Add an input grid to the interface and bind it to the coordinates table.
How the Code Works
JavaScript
- Logic: The script initializes a Two.js instance to handle the drawing operations. It sets up event listeners for mouse actions to start drawing, preview lines, and complete drawings.
- Grid and Snapping: The grid is drawn by creating lines at regular intervals. The snapToGrid function ensures that drawn points snap to the nearest grid intersection.
- Coordinate Tracking: The coordinates of each drawn point are saved in the coordinates table in the Excel file. The saveCoordinatesToGrid function handles this, ensuring that coordinates are stored correctly and that undo and redo actions are managed.
- Undo, Redo, and Clear: These functions manage the drawing state, allowing users to undo and redo their actions or clear the drawing entirely.
CSS
- Styling the Drawing Area and Buttons: The CSS code styles the drawing canvas and buttons, positioning the buttons above the canvas for easy access.
HTML
- Setting Up the UI: The HTML code creates the drawing canvas and the control buttons for undo, redo, clear, and save actions.
Conclusion
By following the steps outlined in this guide, you can create a fully functional drawing application using SpreadsheetWeb. This application leverages the power of Excel for data storage and calculation, combined with JavaScript for interactive features and CSS for styling. SpreadsheetWeb's flexibility allows for the integration of these technologies, making it a powerful tool for building custom web applications.
The same application can be adapted to handle various types of calculations by simply updating the formulas in the underlying Excel file. For instance, it can perform engineering calculations based on sections drawn by the user. Additionally, construction estimators can use it to calculate the amount of material needed based on drawings provided by a field agent. This flexibility allows the application to be customized for different industries and purposes, making it a tool for tasks ranging from architectural design to detailed material estimates.
Remember, this application can also be expanded with additional features, such as more advanced drawing tools, different grid sizes, and enhanced styling. The foundation provided here offers a solid starting point for further customization and development.
You can access the sample application here.
