The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> HTML and Javascript
Amitesh
post Jun 9 2022, 12:23 AM
Post #1





Group: Members
Posts: 8
Joined: 8-June 22
Member No.: 28,380



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)
Attached File  Tables.html ( 10.95k ) Number of downloads: 585
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Jun 9 2022, 09:01 AM
Post #2


.
********

Group: WDG Moderators
Posts: 9,630
Joined: 10-August 06
Member No.: 7



The radio buttons need to use the same NAME attribute values, otherwise they will not interact with each other.

QUOTE(Amitesh @ Jun 9 2022, 07:23 AM) *

- 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.

QUOTE
- Calculate the sum on the bottom of all the columns

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.

QUOTE
- 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

Afraid I can't help with that.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Amitesh
post Jun 9 2022, 11:02 PM
Post #3





Group: Members
Posts: 8
Joined: 8-June 22
Member No.: 28,380



QUOTE
Christian J: 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.


QUOTE
Christian J: 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.


Would you be able to provide me with the codes.

This post has been edited by Amitesh: Jun 9 2022, 11:04 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Jun 10 2022, 12:35 PM
Post #4


.
********

Group: WDG Moderators
Posts: 9,630
Joined: 10-August 06
Member No.: 7



QUOTE(Amitesh @ Jun 10 2022, 06:02 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.

CODE
<table id="table" border=1>
<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();">

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Amitesh
post Jun 11 2022, 12:59 AM
Post #5





Group: Members
Posts: 8
Joined: 8-June 22
Member No.: 28,380



QUOTE(Christian J @ Jun 10 2022, 12:35 PM) *

QUOTE(Amitesh @ Jun 10 2022, 06:02 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?
QUOTE
<td><input type="text" name="" value="1"></td>

2: Can we use something with which i can set the column width and color instead of Input?
QUOTE
<td>Sum: <input type="text" name="" value="0"></td>
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Jun 11 2022, 03:03 AM
Post #6


.
********

Group: WDG Moderators
Posts: 9,630
Joined: 10-August 06
Member No.: 7



QUOTE(Amitesh @ Jun 11 2022, 07:59 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/We...s_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/We...el/Introduction

QUOTE
Just a few questions
1: Do we have to use name here and in other places?
QUOTE
<td><input type="text" name="" value="1"></td>

Not for this javascript, I just left them there by old habit.


QUOTE
2: Can we use something with which i can set the column width and color instead of Input?
QUOTE
<td>Sum: <input type="text" name="" value="0"></td>

You could use CSS for that.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Amitesh
post Jun 11 2022, 10:38 AM
Post #7





Group: Members
Posts: 8
Joined: 8-June 22
Member No.: 28,380



QUOTE(Christian J @ Jun 11 2022, 03:03 AM) *

QUOTE(Amitesh @ Jun 11 2022, 07:59 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/We...s_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/We...el/Introduction

QUOTE
Just a few questions
1: Do we have to use name here and in other places?
QUOTE
<td><input type="text" name="" value="1"></td>

Not for this javascript, I just left them there by old habit.


QUOTE
2: Can we use something with which i can set the column width and color instead of Input?
QUOTE
<td>Sum: <input type="text" name="" value="0"></td>

You could use CSS for that.



Thank you for all your help.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Jun 12 2022, 04:36 PM
Post #8


.
********

Group: WDG Moderators
Posts: 9,630
Joined: 10-August 06
Member No.: 7



You're welcome!
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Amitesh
post Jun 17 2022, 10:31 AM
Post #9





Group: Members
Posts: 8
Joined: 8-June 22
Member No.: 28,380



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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Amitesh
post Jun 17 2022, 10:38 AM
Post #10





Group: Members
Posts: 8
Joined: 8-June 22
Member No.: 28,380



QUOTE(Christian J @ Jun 10 2022, 12:35 PM) *


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>
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Jun 17 2022, 02:11 PM
Post #11


.
********

Group: WDG Moderators
Posts: 9,630
Joined: 10-August 06
Member No.: 7



QUOTE(Amitesh @ Jun 17 2022, 05:31 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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Jun 17 2022, 02:12 PM
Post #12


.
********

Group: WDG Moderators
Posts: 9,630
Joined: 10-August 06
Member No.: 7



QUOTE(Amitesh @ Jun 17 2022, 05:38 PM) *

Can we use this script to do a multiplication

That's what it appears to do, but I haven't tested it.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Amitesh
post Jun 17 2022, 11:19 PM
Post #13





Group: Members
Posts: 8
Joined: 8-June 22
Member No.: 28,380



QUOTE(Christian J @ Jun 17 2022, 02:11 PM) *

QUOTE(Amitesh @ Jun 17 2022, 05:31 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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Jun 18 2022, 04:15 PM
Post #14


.
********

Group: WDG Moderators
Posts: 9,630
Joined: 10-August 06
Member No.: 7



QUOTE(Amitesh @ Jun 18 2022, 06:19 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?

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Amitesh
post Jun 21 2022, 10:38 AM
Post #15





Group: Members
Posts: 8
Joined: 8-June 22
Member No.: 28,380



QUOTE(Christian J @ Jun 18 2022, 04:15 PM) *

QUOTE(Amitesh @ Jun 18 2022, 06:19 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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
2 User(s) are reading this topic (2 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 28th March 2024 - 06:45 PM