Link to home
Start Free TrialLog in
Avatar of Karen Wilson
Karen WilsonFlag for United States of America

asked on

How do I translate a formula to Word language to build a Table from a Data Grid in VS 2010.

I am exporting information to a table in word and when I come to this one section, I am not successful.  This is the code in VS that I use to create a datagrid.  I can use the VendorID as the data property and show the Vendor Name as the columnheader.  I later use that Vendor ID to grab values and place them in the datagrid.

Visual Studio Datagrid code:
 If grpLines.Count > 0 Then

            dTable.Columns.Add("Line Item", GetType(Integer))
            dTable.Columns.Add("Description", GetType(String))
            dTable.Columns.Add("Qty", GetType(Decimal))
            dTable.Columns.Add("UOM", GetType(String))

            'add the vendors to the table
            For Each v In vendors
                dTable.Columns.Add(v, GetType(String))
            Next

            For Each col In dTable.Columns
                Dim colName As String = col.ToString
                Dim dc As New DataGridViewTextBoxColumn
                dgAbstract.Columns.Add(dc)
                dc.DataPropertyName = col.ToString

                Dim getVame = (From id In d.tblVendors _
                              Where id.VendorID = CStr(colName.ToString) _
                              Or id.TempID = CStr(colName.ToString) _
                              Select id.Name).ToList

                If getVame.Count = 0 Then
                    dc.HeaderText = col.ToString
                Else
                    dc.HeaderText = getVame.Item(0).ToString
                End If

            Next

            For Each rec In grpLines
                Dim rNo = rec
                Dim datarow As DataRow = dTable.NewRow
                datarow.Item(0) = rec.Value

                Dim getInfo = (From id In d.vw_RFQabstracts _
                               Where id.PRID = CStr(Me.cbPRList.SelectedItem) _
                              And id.LineItem = rNo _
                              Select id.Description, id.Qty, id.Unit).ToList

                datarow.Item(1) = getInfo.Item(0).Description
                datarow.Item(2) = getInfo.Item(0).Qty
                datarow.Item(3) = getInfo.Item(0).Unit
                dTable.Rows.Add(datarow)
            Next

            For c As Integer = 1 To dTable.Columns.Count - 1
                Dim col = dTable.Columns(c).ColumnName

                For i As Integer = 0 To dTable.Rows.Count - 1

                    Dim lineItem = dTable.Rows(i).Item(0)

                    Dim amount = (From id In d.vw_RFQabstracts _
                                  Where id.PRID = CStr(Me.cbPRList.SelectedItem) _
                                 And id.LineItem = CInt(lineItem.ToString) _
                                 And id.VendorID = CStr(col) _
                                 Select id.QTotal).ToList

                    If amount.Count >= 1 Then
                        dTable.Rows(i).Item(c) = Format(amount.Item(i).Value, "Currency")
                    Else
                        'do nothing                  
                    End If

                Next
            Next

            Dim Totaldatarow As DataRow = dTable.NewRow
            Totaldatarow.Item(1) = "Totals"
            dTable.Rows.Add(Totaldatarow)

            'Add them up and average it out!

            'set up total
            For c As Integer = 4 To dTable.Columns.Count - 1
                Dim t As Decimal = 0
                For r As Integer = 0 To dTable.Rows.Count - 2
                    Dim v As Decimal
                    v = CDec(dTable.Rows(r).Item(c))
                    t += CDec(v)
                Next
                Dim lastRow As Integer = dTable.Rows.Count - 1
                dTable.Rows(lastRow).Item(c) = Format(CDec(t), "Currency")
            Next

'----------------------------------------my code to Word

 oTable.Cell(1, 1).Range.Text = CStr("Line Item")
                oTable.Cell(1, 2).Range.Text = CStr("Qty")
                oTable.Cell(1, 3).Range.Text = CStr("UOM")
                oTable.Cell(1, 4).Range.Text = CStr("Description")



                For i As Integer = 0 To vendors.Count - 1
                    Dim v As String = vendors.Item(i)
                    Dim vName As String = ""

                    Dim getVame = (From id In d.tblVendors _
                                  Where id.VendorID = CStr(v) _
                                  Or id.TempID = CStr(v) _
                                  Select id.Name).ToList
                    vName = getVame.Item(0)

                    oTable.Cell(1, 5 + i).Range.Text = CStr(vName)
                    oTable.Cell(1, 5 + i).????????? = CStr(v) '<---------- Here is where I want the VendorID saved into a property but I don't know what it's called?  
                Next

-------------This section of course is screwed because I don't have the right value in the column heading/property.
                For c As Integer = 1 To oTable.Columns.Count - 1
                    Dim col = oTable.Cell(c, 1).Range.Text

                    For i As Integer = 0 To oTable.Rows.Count - 1
                        Dim lineItem = CDec(oTable.Rows(i).Cells(0).Range.Text)
                        Dim amount = (From id In d.vw_RFQabstracts _
                                      Where id.PRID = CStr(Me.cbPRList.SelectedItem) _
                                    And id.LineItem = CInt(lineItem.ToString) _
                                     And id.VendorID = CStr(col) _
                                     Select id.QTotal).ToList

                        If amount.Count >= 1 Then
                            'oTable.Rows(i).Cells(c) = Format(amount.Item(i).Value, "Currency")
                        Else
                            'do nothing                  
                        End If
                    Next

                Next

                oTable.Rows(1).Shading.BackgroundPatternColor = Word.WdColor.wdColorGray15
                oTable.Rows(getLines.Count + 2).Shading.BackgroundPatternColor = Word.WdColor.wdColorGray15
                oTable.Cell(getLines.Count + 2, 1).Range.Text = CStr("Totals")

                For Each col In oTable.Columns
                    Dim colName As String = col.ToString

                Next

               For r = 1 To getLines.Count
                    oTable.Cell(r + 1, 1).Range.Text = CDbl(getLines.Item(r - 1).LineItem.ToString)
                    oTable.Cell(r + 1, 2).Range.Text = CStr(getLines.Item(r - 1).Qty.ToString)
                    oTable.Cell(r + 1, 3).Range.Text = CStr(getLines.Item(r - 1).Unit.ToString)
                    oTable.Cell(r + 1, 4).Range.Text = CStr(getLines.Item(r - 1).Description.ToString)

                    'here is where we can put the total

                Next

                oTable.Columns.AutoFit()

            End If

ANY HELP is appreciated!
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Do you want vendorid in same cell or different cell? If in cell 2 then

 oTable.Cell(2, 5 + i).Range.Text = v
Avatar of Karen Wilson

ASKER

In the datagrid, I can have the column text = the vendor name and the vendor ID equal the data property.  I am thinking that in a Word table, I do not have that option.  I was beating the code with a hammer last night and decided I would start with the vendorID, run my calcs and then change it to the vendor name when I was done.  But when I went to grab the vendorID, it now shows it with a bullet at the end of it....  WT heck!  
What I thought was a bullet, appears to be a tab.  At the end of each header text is & vbCr & "".  So I am having to remove it.  My question - is there a way to set a data property on a Word Table column header?  
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I cheated and decided to code a cut and paste of the datatable from VS and insert it into the Word document while I figure this out.  Looks like I require a sledge hammer now!!  I just wanted to make sure I wasn't crazy in thinking that the Word Tables operate the same way as a datagrid or an excel spreadsheet.  Thanks!