?
Solved

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

Posted on 2011-10-30
5
Medium Priority
?
280 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:Karen Wilson
  • 3
  • 2
5 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37054732
Do you want vendorid in same cell or different cell? If in cell 2 then

 oTable.Cell(2, 5 + i).Range.Text = v
0
 

Author Comment

by:Karen Wilson
ID: 37055876
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!  
0
 

Author Comment

by:Karen Wilson
ID: 37056213
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?  
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 37057591
I dont think there is. Why dont you do all the calculations in the datatable which has both vendorname and vendorid columns?
0
 

Author Closing Comment

by:Karen Wilson
ID: 37061794
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!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question