Printable Version of Topic
Click here to view this topic in its original format
HTMLHelp Forums _ Client-side Scripting _ HTML and Javascript
Posted by: Amitesh Jun 9 2022, 12:23 AM
I am working on a project to convert an excel file into a webpage. I have been able to design the page but now need to do some calculations (preferably automatic)
I have attached an html file with some codding i have done. I need the following calculations
- Calculate the sum of rows in the 2nd last column
- Calculate the sum on the bottom of all the columns
- In the last column i need a total from the sum multiplied by certain numbers as per mentioned in the tables below using high and low complexity
High Complexity:
Channel Existing New PLACEHOLDER ECCO Shell Expire Links Metadata PLACEHOLDER Images Modified More Than 6 Months Word Count More Than 2,000
Articles 180 240 15 10 2 10 0 30 60 60
Component Metadata 0 0 0 0 0 2 0 0 0 0
CAP/CAT/LE 75 75 15 10 2 10 0 30 60 60
DIR Component 45 45 15 2 2 2 0 30 45 45
Promotions 60 60 15 10 2 10 0 30 60 60
Alert/News 60 60 15 10 2 10 0 30 60 60
Update/News 60 60 15 10 2 10 0 30 60 60
ECCO Shell 15 15 15 10 2 10 0 30 15 15
Category Landing Page 180 240 15 10 2 10 0 30 60 60
Rewrite 240 240 15 10 2 10 0 30 60 60
Low Complexity:
Channel Existing New ECCO Shell Expire Links Metadata PLACEHOLDER Images Modified More Than 6 Months Word Count More Than 2,000
Articles 180 240 15 10 10 10 0 30 60 60
Component 45 45 15 10 10 2 0 30 60 60
CAP/CAT/LE 75 75 15 10 10 10 0 30 60 60
DIR Component 45 45 15 10 10 2 0 30 60 60
Promotions 60 60 15 10 10 10 0 30 60 60
Alert/News 60 60 15 10 10 10 0 30 60 60
Update/News 60 60 15 10 10 10 0 30 60 60
ECCO Shell 15 15 15 10 10 10 0 30 60 60
Category Landing Page 180 240 15 10 10 10 0 30 60 60
Rewrite 240 240 15 10 10 10 0 30 60 60
Attached File(s)
Tables.html ( 10.95k )
Number of downloads: 587
Posted by: Christian J Jun 9 2022, 09:01 AM
The radio buttons need to use the same NAME attribute values, otherwise they will not interact with each other.
- Calculate the sum of rows in the 2nd last column
You could let the javascript loop through each table row; then let a second, nested loop sum each INPUT value in the outer loop's current row.
The sum of that column's INPUT values? Then first count the number of columns, and loop through; then let a second, nested loop go through the table rows and sum the current column's INPUT value.
Afraid I can't help with that.
Posted by: Amitesh Jun 9 2022, 11:02 PM
Would you be able to provide me with the codes.
Posted by: Christian J Jun 10 2022, 12:35 PM
Would you be able to provide me with the codes.
I can't write the whole thing for you, but here's a simplified example of how to sum together row- and column values. Working with nested loops may feel confusing if you're not used to it, but it often helps to add alertboxes in the script to check what's going on in each part.
Also keep in mind you're only allowed to use each unique ID value once per page, that's why I'm using DOM methods below tp find the proper elements.
<tr>
<th>Request Type</th>
<th>Existing</th>
<th>New</th>
<th>Total</th>
<th>Forecast in Minutes</th>
</tr>
<tr>
<th>Articles</th>
<td><input type="text" name="" value="1"></td>
<td><input type="text" name="" value="2"></td>
<td>Sum: <input type="text" name="" value="0"></td>
<td>x</td>
</tr>
<tr>
<th>Component Metadata</th>
<td><input type="text" name="" value="3"></td>
<td><input type="text" name="" value="4"></td>
<td>Sum: <input type="text" name="" value="0"></td>
<td>x</td>
</tr>
<tr>
<th>Task total:</th>
<td>Sum: <input type="text" name="" value="0"></td>
<td>Sum: <input type="text" name="" value="0"></td>
<td>Sum: <input type="text" name="" value="0"></td>
<td>x</td>
</tr>
</table>
<script type="text/javascript">
function Calculate()
{
var table = document.getElementById('table');
/* -------- sum each row ----------- */
// "i=1", since the first row is skipped.
// "length-1", since the last row is skipped.
for(var i=1; i<table.rows.length-1; i++)
{
var row_sum=0;
// "j=1", since the first column is skipped.
// "length-2", since the last two columns are skipped.
for(var j=1; j<table.rows[i].cells.length-2; j++)
{
// "input[0]" is the first (and only) INPUT element in each cell.
// "*1" converts the type of the INPUT value from string to number.
row_sum+=table.rows[i].cells[j].getElementsByTagName('input')[0].value*1;
}
// Display result in the second-last cell in each column
table.rows[i].lastElementChild.previousElementSibling.getElementsByTagName('input')[0].value=row_sum;
}
/*--------- sum each column -----------------*/
// Number of columns in the first row.
var columns=table.rows[0].cells.length;
// "x=1", since the first column is skipped.
// "-1", since last column is skipped.
for(var x=1; x<columns-1; x++)
{
var col_sum=0;
// "y=1", since the first row is skipped.
// "length-1" since the last row is skipped.
for(var y=1; y<table.rows.length-1; y++)
{
// "input[0]" is the first (and only) INPUT element in each cell.
// "*1" converts the type of the INPUT value from string to number.
col_sum+=table.rows[y].cells[x].getElementsByTagName('input')[0].value*1;
}
// Display result in the bottom row of each column
table.rows[table.rows.length-1].cells[x].getElementsByTagName('input')[0].value=col_sum;
}
}
</script>
<input type="button" name="" value="Calculate" onclick="Calculate();">
Posted by: Amitesh Jun 11 2022, 12:59 AM
Would you be able to provide me with the codes.
I can't write the whole thing for you, but here's a simplified example of how to sum together row- and column values. Working with nested loops may feel confusing if you're not used to it, but it often helps to add alertboxes in the script to check what's going on in each part.
Also keep in mind you're only allowed to use each unique ID value once per page, that's why I'm using DOM methods below tp find the proper elements.
Thank you for the suggested codes. It works great.
I do not have much knowledge of javascript looping.
Just a few questions
1: Do we have to use name here and in other places?
2: Can we use something with which i can set the column width and color instead of Input?
Posted by: Christian J Jun 11 2022, 03:03 AM
I do not have much knowledge of javascript looping.
This seems very thorough (lots of things I don't know are mentioned): https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Loops_and_iteration
Here are a couple of pages about the DOM tree, that could also be useful:
https://www.w3.org/wiki/Traversing_the_DOM
https://developer.mozilla.org/en-US/docs/Web/API/Document_Object_Model/Introduction
1: Do we have to use name here and in other places?
Not for this javascript, I just left them there by old habit.
You could use CSS for that.
Posted by: Amitesh Jun 11 2022, 10:38 AM
I do not have much knowledge of javascript looping.
This seems very thorough (lots of things I don't know are mentioned): https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Loops_and_iteration
Here are a couple of pages about the DOM tree, that could also be useful:
https://www.w3.org/wiki/Traversing_the_DOM
https://developer.mozilla.org/en-US/docs/Web/API/Document_Object_Model/Introduction
1: Do we have to use name here and in other places?
Not for this javascript, I just left them there by old habit.
You could use CSS for that.
Thank you for all your help.
Posted by: Christian J Jun 12 2022, 04:36 PM
You're welcome!
Posted by: Amitesh Jun 17 2022, 10:31 AM
Is it possible to convert these tables into hidden tables and then use them for multiplication?
High Complexity:
Channel Existing New PLACEHOLDER ECCO Shell Expire Links Metadata PLACEHOLDER Images Modified More Than 6 Months Word Count More Than 2,000
Articles 180 240 15 10 2 10 0 30 60 60
Component Metadata 0 0 0 0 0 2 0 0 0 0
CAP/CAT/LE 75 75 15 10 2 10 0 30 60 60
DIR Component 45 45 15 2 2 2 0 30 45 45
Promotions 60 60 15 10 2 10 0 30 60 60
Alert/News 60 60 15 10 2 10 0 30 60 60
Update/News 60 60 15 10 2 10 0 30 60 60
ECCO Shell 15 15 15 10 2 10 0 30 15 15
Category Landing Page 180 240 15 10 2 10 0 30 60 60
Rewrite 240 240 15 10 2 10 0 30 60 60
Low Complexity:
Channel Existing New ECCO Shell Expire Links Metadata PLACEHOLDER Images Modified More Than 6 Months Word Count More Than 2,000
Articles 180 240 15 10 10 10 0 30 60 60
Component 45 45 15 10 10 2 0 30 60 60
CAP/CAT/LE 75 75 15 10 10 10 0 30 60 60
DIR Component 45 45 15 10 10 2 0 30 60 60
Promotions 60 60 15 10 10 10 0 30 60 60
Alert/News 60 60 15 10 10 10 0 30 60 60
Update/News 60 60 15 10 10 10 0 30 60 60
ECCO Shell 15 15 15 10 10 10 0 30 60 60
Category Landing Page 180 240 15 10 10 10 0 30 60 60
Rewrite 240 240 15 10 10 10 0 30 60 60
Posted by: Amitesh Jun 17 2022, 10:38 AM
I can't write the whole thing for you, but here's a simplified example of how to sum together row- and column values. Working with nested loops may feel confusing if you're not used to it, but it often helps to add alertboxes in the script to check what's going on in each part.
Also keep in mind you're only allowed to use each unique ID value once per page, that's why I'm using DOM methods below tp find the proper elements.
Can we use this script to do a multiplication
<script type="text/javascript">
function getValues() {
var rows = document.querySelectorAll("tr.package-row");
rows.forEach(function (currentRow) {
var numberUsed = Number(currentRow.querySelector('#numberUsed').value);
var price = Number(currentRow.querySelector('#price').value);
var inPackage = Number(currentRow.querySelector('#inPackage').value);
var revenue = 0;
document.querySelectorAll('numberUsed');
if (numberUsed == "") {
if (isNaN(inPackage) || isNaN(price)) {
return;
}
revenue = price * inPackage;
}
else {
if (isNaN(numberUsed) || isNaN(price)) {
return;
}
revenue = price * numberUsed;
}
var value = revenue * 5;
currentRow.querySelector("#revenue").innerHTML = revenue;
currentRow.querySelector("#value").innerHTML = value;
});
}
</script>
Posted by: Christian J Jun 17 2022, 02:11 PM
Is it possible to convert these tables into hidden tables and then use them for multiplication?
Maybe you could hide them with CSS, but that sounds like an unnecessary complication. Where does the table cell data come from in the first place --from Excel files? In that case maybe you could convert those Excel files into some more practical format for multiplication, such as javascript variables, or an array.
Posted by: Christian J Jun 17 2022, 02:12 PM
Can we use this script to do a multiplication
That's what it appears to do, but I haven't tested it.
Posted by: Amitesh Jun 17 2022, 11:19 PM
Is it possible to convert these tables into hidden tables and then use them for multiplication?
Maybe you could hide them with CSS, but that sounds like an unnecessary complication. Where does the table cell data come from in the first place --from Excel files? In that case maybe you could convert those Excel files into some more practical format for multiplication, such as javascript variables, or an array.
Yes these are from excel. I am trying to convert an excel file into html. Therefore all this work.
I am fine by converting these tables into javascript variables.
If i can use these variables for multiplication with the total amount of the rows my work will be complete.
Posted by: Christian J Jun 18 2022, 04:15 PM
Yes these are from excel. I am trying to convert an excel file into html. Therefore all this work.
I don't understand what functionality you want the HTML page to have. For example, should the user be able to change text field values from the web browser, and the HTML will recalculate the output? In that case you would need a visible table with INPUT fields. Or is it something else you have in mind?
Posted by: Amitesh Jun 21 2022, 10:38 AM
Yes these are from excel. I am trying to convert an excel file into html. Therefore all this work.
I don't understand what functionality you want the HTML page to have. For example, should the user be able to change text field values from the web browser, and the HTML will recalculate the output? In that case you would need a visible table with INPUT fields. Or is it something else you have in mind?
Hi Christian,
So what i need is as follows:
1) Sum all the rows. (done as per your javascript codes)
2) Sum all the columns. (done as per your javascript codes)
3) As per the column selected multiply the sum with a set number as per the table and display in the last column.
Eg: If you have added a number in Row Articles and column New as 2 and the complexity is Low then the last column should calculate the total as 2 X 240 = 480
Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)