如何在依赖产品名称中显示总金额 [英] how to display the total amount in depend product name

问题描述

嗨...

i在vb.net开发报告系统，我想显示gridview单元格之间的总金额，

i尝试多种方式但结果是空的请帮助我..

我的代码和结果如下..

 私有  Sub  Form1_Load（发件人 As   Object ，e  As  EventArgs）句柄  MyBase  .Load 
 
  Dim  con  As  OleDbConnection = 新 OleDbConnection（  Provider = Microsoft.jet。 oledb.4.0;数据源= D：\Programs \sathish \ novoicedb.mdb）
  Dim  cmd 为 OleDbCommand = 新 OleDbCommand（  SELECT productname，invoiceid， Clientname，invoicedate，totalamount，Amountpaid，balanceamount from invoice，con）
 con.Open（）
  Dim  myDA  As  OleDbDataAdapter = 新 OleDbDataAdapter（cmd）
  Dim  myDataSet  As  DataSet = 新 DataSet（）
 myDA.Fill（myDataSet，< span class =code-string>  invoice）
 DataGridView1.DataSource = myDataSet.Tables（  invoice）。DefaultView 

  Dim  s  As   Dec imal  
s =  0  
 对于 每个行 As  DataGridViewRow  In  DataGridView1.Rows 
 如果 row.Cells（ 4 ）。Value =  Nothing  然后 
 否则 
s = s + 十进制 .Parse（row.Cells（ 4 ）。Value.ToString（））
 结束 如果 
 下一步 
 DataGridView1.Rows（DataGridView1。 Rows.Count  -   1 ）。单元格（ 4 ）。Value = s.ToString（）
 con.Close（）
 con = 没什么 

 Private Sub DataGridView1_CellPainting（sender As Object，e As DataGridViewCellPaintingEventArgs）处理DataGridView1 .CellPainting 
如果e.ColumnIndex = 0并且还e.RowIndex <  >  -1然后
 
使用gridBrush As Brush = New SolidBrush（Me.DataGridView1.GridColor），backColorBrush As Brush = New SolidBrush（e.CellStyle.BackColor）
 
使用gridLinePen作为Pen =新笔（gridBrush）
 
 e.Graphics.FillRectangle（backColorBrush，e.CellBounds）
如果e.RowIndex <  < span class =code-attribute>   DataGridView1.Rows.Count      -      2     AndAlso     DataGridView1.Rows（e.RowIndex     +     1）。细胞（e.ColumnIndex）.Value.ToString（）    <  >  e.Value.ToString（）然后
 e.Graphics.DrawLine（gridLinePen，e.CellBounds.Left，e。 CellBounds.Bottom  -  1，e.CellBounds.Right  -  1，e.CellBounds.Bottom  -  1）
结束如果
 e.Graphics.DrawLine（gridLinePen，e.CellBounds.Right  -  1，e。 CellBounds.Top，e.CellBounds.Right  -  1，e.CellBounds.Bottom）
如果不是e.Value则没有那么
如果e.RowIndex> 0 AndAlso DataGridView1.Rows（e.RowIndex  -  1）.Cells（e.ColumnIndex）.Value.ToString（）= e.Value.ToString（）然后
 Else 
 e.Graphics.DrawString（CType （e.Value，String），e.CellStyle.Font，Brushes.Black，e.CellBounds.X + 2，e.CellBounds.Y + 5，StringFormat.GenericDefault）
 End if 
 End If 
 e.Handled = True 
结束使用
结束使用
结束如果
结束子

，结果是：

 --------------------------------- 
产品名称|总金额
 --------------------------------- 
 S / W | 5000 
 | 2000 
 | 3000 
 WEB HOSTING | 1000 
 | 2000 
 
 13000（这是总金额的总和）
 ---------------------------- ----- 

但我想要：

 --------------------------------- 
 PRODUCT NAME |总金额
 --------------------------------- 
 S / W | 5000 
 | 2000 
 | 3000 
 
 10000（这是S / W AMOUNT的总和）
 
 WEB HOSTING | 1000 
 | 2000 
 
 3000（这是WEB HOSTING AMOUNT的总和）
 --------------------------- ------ 

请发送你的想法...

解决方案

所有你需要的东西做的是写出正确的查询：

  SELECT  productname，totalamount 
  FROM （
  SELECT  productname，totalamount 
  FROM  invoice 
  UNION   ALL  
  SELECT  productname + '   - 总计：'  AS  productname，SUM（totalamount） AS  totalamount 
  FROM  invoice 
  GROUP   BY  productname + '   - 总计：' 
） AS  T 
  ORDER   BY  productname 

Hi...
i developing report system in vb.net,i want to show the total amount in between gridview cells,
i try to many way but the result is null please help me..
my code and result is below..

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 
 Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Programs\sathish\invoicedb.mdb")
       Dim cmd As OleDbCommand = New OleDbCommand("SELECT productname,invoiceid,Clientname,invoicedate,totalamount,Amountpaid,balanceamount from invoice", con)
       con.Open()
       Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
       Dim myDataSet As DataSet = New DataSet()
       myDA.Fill(myDataSet, "invoice")
       DataGridView1.DataSource = myDataSet.Tables("invoice").DefaultView

Dim s As Decimal
      s = 0
      For Each row As DataGridViewRow In DataGridView1.Rows
          If row.Cells(4).Value = Nothing Then
          Else
             s = s + Decimal.Parse(row.Cells(4).Value.ToString())
          End If
      Next
      DataGridView1.Rows(DataGridView1.Rows.Count - 1).Cells(4).Value = s.ToString()
      con.Close()
      con = Nothing

Private Sub DataGridView1_CellPainting(sender As Object, e As DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
       If e.ColumnIndex = 0 AndAlso e.RowIndex <> -1 Then

           Using gridBrush As Brush = New SolidBrush(Me.DataGridView1.GridColor), backColorBrush As Brush = New SolidBrush(e.CellStyle.BackColor)

               Using gridLinePen As Pen = New Pen(gridBrush)

                   e.Graphics.FillRectangle(backColorBrush, e.CellBounds)
                   If e.RowIndex < DataGridView1.Rows.Count - 2 AndAlso DataGridView1.Rows(e.RowIndex + 1).Cells(e.ColumnIndex).Value.ToString() <> e.Value.ToString() Then
                       e.Graphics.DrawLine(gridLinePen, e.CellBounds.Left, e.CellBounds.Bottom - 1, e.CellBounds.Right - 1, e.CellBounds.Bottom - 1)
                   End If
                   e.Graphics.DrawLine(gridLinePen, e.CellBounds.Right - 1, e.CellBounds.Top, e.CellBounds.Right - 1, e.CellBounds.Bottom)
                   If Not e.Value Is Nothing Then
                       If e.RowIndex > 0 AndAlso DataGridView1.Rows(e.RowIndex - 1).Cells(e.ColumnIndex).Value.ToString() = e.Value.ToString() Then
                       Else
                           e.Graphics.DrawString(CType(e.Value, String), e.CellStyle.Font, Brushes.Black, e.CellBounds.X + 2, e.CellBounds.Y + 5, StringFormat.GenericDefault)
                       End If
                   End If
                   e.Handled = True
               End Using
           End Using
       End If
   End Sub

and the result is:

---------------------------------
PRODUCT NAME  |   TOTAL AMOUNT
---------------------------------
S/W           |    5000
              |    2000
              |    3000
WEB HOSTING   |    1000
              |    2000

                  13000(This is sum of TOTAL AMOUNT)
---------------------------------

But i want:

---------------------------------
PRODUCT NAME  |   TOTAL AMOUNT
---------------------------------
S/W           |    5000
              |    2000
              |    3000
               
                   10000(This is sum of S/W AMOUNT)
  
WEB HOSTING   |    1000
              |    2000

                  3000(This is sum of WEB HOSTING AMOUNT)
---------------------------------

Pls send your ideas...

解决方案

All what you need to do is to write proper query:

SELECT productname, totalamount
FROM(
    SELECT productname, totalamount
    FROM invoice
    UNION ALL
    SELECT productname + ' - Total:' AS productname, SUM(totalamount) AS totalamount
    FROM invoice
    GROUP BY productname + ' - Total:'
) AS T
ORDER BY productname

这篇关于如何在依赖产品名称中显示总金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！