Excel type calculations in a web form 1

reisende · Mar 23, 2004

Hello everyone.

I was wondering if anybody knew of a way to implement calculations like those used by Excel formulas in a Web based form.

I am trying to do a weekly tracking report where the number of customers for each day is entered in its own cell and then totaled in the last cell in the row.

It would be nice if the number in the total input cell was updated in real time without having to click a calculate button.

I'm pretty sure I'd be able to do it myself if I didn't need it to update in real time. If someone can point me in the right direction it would be greatly appreciated.

BillyRayPreachersSon · Mar 23, 2004

Give this a whirl:

Code:

<html>
<head>
<style type="text/css">
th {
	font-family: verdana, arial, helvetica, sans-serif;
	font-size: 0.7em;
	text-align: left;
}
tfoot td {
	border-top: 2px #000000 solid;
	border-bottom: 1px #000000 solid;
}

input {
	border: 1px #000000 solid;
	margin-right: 2px;
}
</style>
<script type="text/javascript">
<!--
	function findColumnNumber(tr, td)
	{
		for (var loop=0; loop<tr.cells.length; loop++) if (tr.cells[loop] === td) return(loop);
		return(-1);
	}

	function calculateColumn(textObj)
	{
		var cell = textObj.parentNode;
		var row = cell.parentNode;
		var tbody = row.parentNode;
		var tfoot = tbody.parentNode.tFoot;
		var columnNum = findColumnNumber(row, cell);
		var total = 0;
		for (var rowLoop=0; rowLoop<tbody.rows.length; rowLoop++) {
			var tempCellObj = tbody.rows[rowLoop].cells[columnNum];
			var inputObj = tempCellObj.getElementsByTagName('input')[0];
			var numValue = parseFloat(inputObj.value, 10);
			if (!isNaN(numValue)) total +=numValue;
		}
		tfoot.rows[0].cells[columnNum].innerText = total;
	}
//-->
</script>
</head>
<body>
	<table cellpadding="0" cellspacing="5" border="0">
	<thead>
		<tr>
			<th>Column 1</th>
			<th>Column 2</th>
			<th>Column 3</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
		</tr>
		<tr>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
		</tr>
		<tr>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
		</tr>
		<tr>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
			<td><input type="text" size="8" onchange="calculateColumn(this);"></td>
		</tr>
	</tbody>
	<tfoot>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
		</tr>
	</tfoot>
	</table>
</body>
</html>

That's a perfect example of how a well-formed table can greatly ease writing table parsing code, I think.

BTW - I'm not happy with the findColumnNumber() function... Surely there must be a quicker way to find the index of a cell in the cells collection of a row? Anyone? I thought sourceIndex, but don't think it is ;o(

Hope this helps,
Dan

BillyRayPreachersSon · Mar 23, 2004

Sorry - I should have mentioned...

As long as you structure your table much like the one above, with a thead, tbody, and tfoot, then all you need to do is to add the 'onchange="calculateColumn(this);"' to each input cell.

Hope this helps,
Dan

reisende · Mar 24, 2004

Thanks BillyRay, that looks good. I was able to find a nice program online that converted the actual spreadsheet into hmtl and js as well.

I'll give both atry, but it looks like they will both work.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Excel type calculations in a web form 1

reisende

Programmer

BillyRayPreachersSon

Programmer

BillyRayPreachersSon

Programmer

reisende

Programmer

Similar threads

Part and Inventory Search

Sponsor