Karen Wilson
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("Descri ption", 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.SelectedI tem) _
And id.LineItem = rNo _
Select id.Description, id.Qty, id.Unit).ToList
datarow.Item(1) = getInfo.Item(0).Descriptio n
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).ColumnNa me
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.SelectedI tem) _
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).Valu e, "Currency")
Else
'do nothing
End If
Next
Next
Dim Totaldatarow As DataRow = dTable.NewRow
Totaldatarow.Item(1) = "Totals"
dTable.Rows.Add(Totaldatar ow)
'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.T ext)
Dim amount = (From id In d.vw_RFQabstracts _
Where id.PRID = CStr(Me.cbPRList.SelectedI tem) _
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).Valu e, "Currency")
Else
'do nothing
End If
Next
Next
oTable.Rows(1).Shading.Bac kgroundPat ternColor = Word.WdColor.wdColorGray15
oTable.Rows(getLines.Count + 2).Shading.BackgroundPatte rnColor = 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!
Visual Studio Datagrid code:
If grpLines.Count > 0 Then
dTable.Columns.Add("Line Item", GetType(Integer))
dTable.Columns.Add("Descri
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.SelectedI
And id.LineItem = rNo _
Select id.Description, id.Qty, id.Unit).ToList
datarow.Item(1) = getInfo.Item(0).Descriptio
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).ColumnNa
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.SelectedI
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).Valu
Else
'do nothing
End If
Next
Next
Dim Totaldatarow As DataRow = dTable.NewRow
Totaldatarow.Item(1) = "Totals"
dTable.Rows.Add(Totaldatar
'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(
Next
'-------------------------
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(
Dim amount = (From id In d.vw_RFQabstracts _
Where id.PRID = CStr(Me.cbPRList.SelectedI
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).Valu
Else
'do nothing
End If
Next
Next
oTable.Rows(1).Shading.Bac
oTable.Rows(getLines.Count
oTable.Cell(getLines.Count
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!
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!
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
oTable.Cell(2, 5 + i).Range.Text = v