question

GreatLearning avatar image
0 Votes"
GreatLearning asked GreatLearning answered

Multiplication and Sum in GridView ASP.Net C#

I have a datatable which is bound to GridView datasource as follows.

Overall I want to Multiply 'Quantity' column value with 'Part1 qty' column value until 'column5' cell value is repeating and so on the result of operation should appear underneath the value as highlighted in red for understanding.!

My Gridview Output

130585-gridviewoutput.jpg

I want Following Output

130621-required-output.jpg


What I have done so far is


 protected void GridView1_DataBound(object sender, EventArgs e)
         {
             int gridViewCellCount = GridView1.Rows[0].Cells.Count;
             string[] columnNames = new string[gridViewCellCount];
             for (int k = 0; k < gridViewCellCount; k++)
             {
                 columnNames[k] = ((System.Web.UI.WebControls.DataControlFieldCell)(GridView1.Rows[0].Cells[k])).ContainingField.HeaderText;
             }
    
             for (int i = GridView1.Rows.Count - 1; i > 0; i--)
             {
                 GridViewRow row = GridView1.Rows[i];
                 GridViewRow previousRow = GridView1.Rows[i - 1];
                    
                 var result = Array.FindIndex(columnNames, element => element.EndsWith("QTY"));
                 var Arraymax=columnNames.Max();
                 int maxIndex = columnNames.ToList().IndexOf(Arraymax);
                 decimal MultiplicationResult=0;
                 int counter = 0;
    
                 for (int j = 8; j < row.Cells.Count; j++)
                 {
                     if (row.Cells[j].Text == previousRow.Cells[j].Text)
                     {
                         counter++;
                         if (row.Cells[j].Text != "&nbsp;" && result < maxIndex)
                         {
                             var Quantity = GridView1.Rows[i].Cells[1].Text;
                             var GLQuantity = GridView1.Rows[i].Cells[result].Text;
                             var PreviousQuantity= GridView1.Rows[i-1].Cells[1].Text;
                             var PreviousGLQuantity= GridView1.Rows[i-1].Cells[result].Text;
                             //var Quantity = dt.Rows[i].ItemArray[1];
                             //var GLQuantity = dt.Rows[i].ItemArray[Convert.ToInt64(result)].ToString();
                             var GLQ = GLQuantity.TrimEnd(new Char[] { '0' });
                             var PGLQ = PreviousGLQuantity.TrimEnd(new char[] { '0' });
                             if (GLQ == "")
                             {
                                 GLQ = 0.ToString();
                             }
                             if (PGLQ == "")
                             {
                                 PGLQ = 0.ToString();
                             }
    
                             MultiplicationResult = Convert.ToDecimal(Quantity) * Convert.ToDecimal(GLQ) + Convert.ToDecimal(PreviousQuantity)*Convert.ToDecimal(PGLQ);
    
                             object o = dt.Rows[i].ItemArray[j] + " " + MultiplicationResult.ToString();
                                
                             GridView1.Rows[i].Cells[j].Text = o.ToString();
                             GridView1.Rows[i].Cells[j].Text.Replace("\n", "<br/>");
                             result++;
    
                         }
                         else
                             result++;
    
                         if (previousRow.Cells[j].RowSpan == 0)
                         {
                             if (row.Cells[j].RowSpan == 0)
                             {
                                 previousRow.Cells[j].RowSpan += 2;
                                   
                             }
                             else
                             {
                                 previousRow.Cells[j].RowSpan = row.Cells[j].RowSpan + 1;
    
                             }
                             row.Cells[j].Visible = false;
    
                         }
    
                           
                     }
    
                     else
                         result++;
                 }
             }
               
         }



Thanks In advance..



dotnet-csharpdotnet-aspnet-webpages
gridviewoutput.jpg (96.4 KiB)
required-output.jpg (144.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanHuang-MSFT avatar image
1 Vote"
LanHuang-MSFT answered LanHuang-MSFT commented

Hi @GreatLearning,
After reading your description, I have a few questions:
1.According to your output in the column5, the value is to plus Quantity* part1 qty group by column5. But however, the value isn't just like 207. How do you calcalate? What's group by of Column1 is 4 and 5?
2. How do you get the column from your gridview output? What's the meaning of each columns represent ?
3. The two tables you provided do not seem to correspond.
Could you post your details to us?

Edit
According to your description,I found some questions of your codes:
1. In your codes,You have row span 8-11 columns. However, In your output you need, you need only 5-7 columns. So, you need for each j from 5.
2. In your codes,your "QTY" column index must more than "Quantity" column. So,you result must more than maxIndex.

I made a modification according to your code, you can refer to it:

Code:

 protected void GridView1_DataBound(object sender, EventArgs e)
         {
             int gridViewCellCount = GridView1.Rows[0].Cells.Count;
             string[] columnNames = new string[gridViewCellCount];
             for (int k = 0; k < gridViewCellCount; k++)
             {
                 columnNames[k] = ((System.Web.UI.WebControls.DataControlFieldCell)(GridView1.Rows[0].Cells[k])).ContainingField.HeaderText;
             }
    
             for (int i = GridView1.Rows.Count - 1; i > 0; i--)
             {
                 GridViewRow row = GridView1.Rows[i];
                 GridViewRow previousRow = GridView1.Rows[i - 1];
    
                 var result = Array.FindIndex(columnNames, element => element.EndsWith("qty"));
                 var Arraymax = columnNames.Max();
                 int maxIndex = columnNames.ToList().IndexOf(Arraymax);
                 decimal MultiplicationResult = 0; 
                 decimal currentCellResult = 0;
                 int counter = 0;
    
                 for (int j = 5; j < 8; j++)
                 {
                     var defaultvalue = row.Cells[j].Text.ToString();
                     var defaultvalueArray = defaultvalue.Split(' ');
                     var originalMultiplicationResult = defaultvalueArray.Count() == 2 ? defaultvalueArray.Last() : "0";
                     var originalCellValue = defaultvalueArray.Count() == 2  ? defaultvalueArray .First(): row.Cells[j].Text.ToString();
                     if (originalCellValue == previousRow.Cells[j].Text)
                     {
                         counter++;
                         if (row.Cells[j].Text != "&nbsp;" && result > maxIndex)
                         {
                             var Quantity = GridView1.Rows[i].Cells[1].Text;
                             var GLQuantity = GridView1.Rows[i].Cells[result].Text;
                             var PreviousQuantity = GridView1.Rows[i - 1].Cells[1].Text;
                             var PreviousGLQuantity = GridView1.Rows[i - 1].Cells[result].Text;
                             var GLQ = GLQuantity.TrimEnd(new Char[] { '0' });
                             var PGLQ = PreviousGLQuantity.TrimEnd(new char[] { '0' });
                             if (GLQ == "")
                             {
                                 GLQ = 0.ToString();
                             }
                             if (PGLQ == "")
                             {
                                 PGLQ = 0.ToString();
                             }
                             currentCellResult = Convert.ToDecimal(Quantity) * Convert.ToDecimal(GLQ);
                             MultiplicationResult = currentCellResult + Convert.ToDecimal(PreviousQuantity) * Convert.ToDecimal(PGLQ);
                             if (row.Cells[j].RowSpan == 0)
                             {
                                 DataTable dt = (DataTable)ViewState["dt"];
                                 object o = dt.Rows[i].ItemArray[j] + " " + MultiplicationResult.ToString();
                                 previousRow.Cells[j].Text = o.ToString();
                             }
                             else
                             {
                                 DataTable dt = (DataTable)ViewState["dt"];
                                 var t = Convert.ToDecimal(originalMultiplicationResult) - Convert.ToDecimal(currentCellResult) + MultiplicationResult;
                                 object o = dt.Rows[i].ItemArray[j] + " " + t.ToString();
                                 previousRow.Cells[j].Text = o.ToString();
                             }
                             result++;
    
                         }
                         else
                             result++;
    
                         if (previousRow.Cells[j].RowSpan == 0)
                         {
                             if (row.Cells[j].RowSpan == 0)
                             {
                                 previousRow.Cells[j].RowSpan +=2;
                             }
                             else
                             {
                                 previousRow.Cells[j].RowSpan = row.Cells[j].RowSpan + 1; 
                             }
                             row.Cells[j].Visible = false;
                         }
                     }
    
                     else
                         result++;
                 }
             }
         }

Result:
131594-text.png
Edit Two
It is recommended that you use Split(Char, StringSplitOptions) to achieve line break.
The modified code is as follows:
replace

 int gridViewCellCount = GridView1.Rows[0].Cells.Count;
 string[] columnNames = new string[gridViewCellCount];

to

 int gridViewCellCount = GridView1.Rows[0].Cells.Count;
 string[] stringSeparators = new string[] { "<br>" };
 string[] columnNames = new string[gridViewCellCount];

replace

 var defaultvalueArray = defaultvalue.Split(' ');

to

 var defaultvalueArray = defaultvalue.Split(stringSeparators, StringSplitOptions.None);

replace

 object o = dt.Rows[i].ItemArray[j] + " " + MultiplicationResult.ToString();
 object o = dt.Rows[i].ItemArray[j] + " " + t.ToString();

to

 object o = dt.Rows[i].ItemArray[j] + stringSeparators[0] + MultiplicationResult.ToString();
 object o = dt.Rows[i].ItemArray[j]  + stringSeparators[0] + t.ToString();

Best regards,
Lan Huang


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.



text.png (16.4 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @LanHuang-MSFT,

Thanks for checking back. please find answers to your questions.

Please find the correct calculation.

1) Column 5 value is coming like since value 325912-403 is common for column1(i.e. unique ID (1,2,3)) we first want to Multiply it by column 'Quantity' into 'part1 qty' and repeat this step remaining two values of 325912-403 as they are adjacent. (We need to group on cell values until they are same )

Our formula becomes (Quantity part1 qty) + (Quantity part1 qty) + (Quantity * part1 qty) i.e.
(1*1) + (23*2) + (22*2) = 113

2) I have a Stored procedure which is responsible for getting the results and has many columns in the Stored Procedure
for demonstrations I pulled few columns.

3) My GridView currently Updated


0 Votes 0 ·

Hi @GreatLearning ,
According to your description,I found some questions of your codes:
1. In your codes,You have row span 8-11 columns. However, In your output you need, you need only 5-7 columns. So, you need for each j from 5.
2. In your codes,your "QTY" column index must more than "Quantity" column. So,you result must more than maxIndex.
So, My idea is :
First you need to set the first row of row span as the Initial value . And then you could add each row Qty* part to the sum. But the sum must show on the first row of the row span.

Best regards,
Lan Huang

0 Votes 0 ·

Sorry but I have not understood your Point

0 Votes 0 ·

Hi @GreatLearning,
I modified the previous reply and gave a detailed code. You can refer to it, which can better help you understand.

0 Votes 0 ·

Thank You @LanHuang-MSFT...... I am able to get the Quantity as expected... Thank you for your valuable efforts.

By any chance can we bring 113 to next Line like below for example.

      **325912-403
           113** 
0 Votes 0 ·

Hi @GreatLearning,
According to your needs, I revised the previous reply.
Best regards,
Lan Huang

1 Vote 1 ·
GreatLearning avatar image
0 Votes"
GreatLearning answered

Is it possible to implement the same functionality using MVC pattern.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.