Export data from datagrid to MS Word using vb.net

[shinichi90]

Hi!!

I was wondering if it's possible for me to export the data from the datagrid and output it into MS Word?

I'm actually storing strings of questions inside the database and I need to find a way to print out all those set of questions into MS Word.

Could someone show me a link or a guide as to how I can do this?

I saw many links that shows how to export the datagrid to Excel but I can't find any that export the data to Word yet.

Thank you.

Regards,
shinichi90

[ForumAccount]

The way I see it, there are two ways to create a table in MS Word:

1. The first requires knowledge of the rtf specification to build a Rich Text Format string that you insert into the Word document. This is the least likely scenario.
2. The second requires Word automation. There are lots of articles on automating MS Word from VB.NET. A place I would start is asking myself some questions:
   
   • How do I use Word in VB.NET?
   • If I record a macro of inserting a table in Word what does it look like?
   • How do I translate that code into .NET code?

[shinichi90]

I managed to write out to ms word using word automation but I don't know how to link the datagrid to it.
How do I do it? Is there a guide or a link?

Thanks.

Regards,
shinichi90

[ForumAccount]

So you have successfully done some simple automation in Word? Your next step should be to record a macro where you insert a table into the document. Then, go look at the code produced by the macro. Mine looked like this (this is point #2 of method #2):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/06/2011 by Parkes Scientific
'
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=2, NumColumns:= _
        5, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    With Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = True
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = True
    End With
End Sub

Your next step is how to perform point #3.

[shinichi90]

I found this code at msdn forum and it already insert a table into ms word.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' Create Word Application
        Dim oWord As Word.Application = CreateObject("Word.Application")
        ' Create new word document
        Dim oDoc As Word.Document = oWord.Documents.Add()
        oWord.Visible = True

        'Insert a 3 x 5 table and fill it with specific data
        Dim r As Integer, c As Integer
        Dim oTable As Word.Table = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, 3, 5)
        oTable.Range.ParagraphFormat.SpaceAfter = 6
        For r = 1 To 3
            For c = 1 To 5
                oTable.Cell(r, c).Range.Text = "Row" & r & "Col" & c
            Next
        Next
        'make the first row bold and italic
        oTable.Rows.Item(1).Range.Font.Bold = True
        oTable.Rows.Item(1).Range.Font.Italic = True
        ' Save this word document
        oDoc.SaveAs("C:\myfile.doc", True)
        oDoc.Close()
        oWord.Application.Quit()

But the problem I'm now facing is I can't get the datagrid to copy over. Is this way possible too?

Thanks
Regards,
shinichi90

[ForumAccount]

But the problem I'm now facing is I can't get the datagrid to copy over. Is this way possible too?

So you have successfully inserted a table into Word? Now you aren't sure of how to write the values of the DataGridView to the table?

First, in your code you create a 3x5 table. So what's the first thing you would have to do with that? Well, you would need to create a table of the dimensions that your DataGridView is. You can find these values via the RowCount and ColumnCount properties.

Second, your code loops through each cell and specifies a value. So what do you have to do with that part? You will need to translate that same logic into accessing the DataGridView's cells. Create a similar loop that uses the bounds of your DataGridView that you previously defined.

Note: Arrays and collections in .NET are 0-based, that means that the first index of the first element will be 0. This means the indexes of the elements of an array with the length of 3 will be 0, 1, and 2. You will need to consider this when you loop through your DataGridView.

Once you've indexed the DataGridView properly to retrieve a DataGridViewCell, you can use the Value property to populate that particular cell inside the Word table.

The code you have already contains the basic structure that you will use. Just start substituting values from your DataGridView to create a table that will fit it's content.

[shinichi90]

I managed to copy it over but I feel that it was rather by luck as I saw a code where it exports data from datagrid to Excel and I just referred to the code.

Dim r As Integer, c As Integer
Dim oTable As Word.Table = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range,DataGridView1.RowCount,DataGridView1.ColumnCo unt)

oTable.Range.ParagraphFormat.SpaceAfter = 6
For r = 0 To DataGridView1.RowCount - 2
For c = 0 To DataGridView1.ColumnCount - 1
oTable.Cell(r + 1, c + 1).Range.Text = DataGridView1(c, r).Value.ToString()

I did not really understand this part of the code:

For r = 0 To DataGridView1.RowCount - 2
            For c = 0 To DataGridView1.ColumnCount - 1
                oTable.Cell(r + 1, c + 1).Range.Text = DataGridView1(c, r).Value.ToString()

Why we need to minus 2 from the row and plus 1 at oTable.Cell?

Although I managed to copy the data over, the column's name was not copied over. Is it possible to copy the column name as well or does it always take the first data in the row as column name?

Thank you.
Regards,
shinichi90

[.paul.]

DataGridView rows have a zero based index + the last row is a new row, so not included.

oTable.Cell indexes appear to start at 1

[.paul.]

have a look at this:

http://www.vbforums.com/showthread.php?t=626953

[ForumAccount]

I managed to copy it over but I feel that it was rather by luck as I saw a code where it exports data from datagrid to Excel and I just referred to the code.

This is okay, just try to understand what the code is doing.

Dim r As Integer, c As Integer
        Dim oTable As Word.Table = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range,DataGridView1.RowCount,DataGridView1.ColumnCount)
 
        oTable.Range.ParagraphFormat.SpaceAfter = 6
        For r = 0 To DataGridView1.RowCount - 2
            For c = 0 To DataGridView1.ColumnCount - 1
                oTable.Cell(r + 1, c + 1).Range.Text = DataGridView1(c, r).Value.ToString()

I did not really understand this part of the code:

For r = 0 To DataGridView1.RowCount - 2
            For c = 0 To DataGridView1.ColumnCount - 1
                oTable.Cell(r + 1, c + 1).Range.Text = DataGridView1(c, r).Value.ToString()

Why we need to minus 2 from the row...?

The previous coder probably did this because he assumed that the last row was the "Add New Row" row where when you start typing in it, it will let you add rows. Personally I would not do it that way, but to each his own.

and plus 1 at oTable.Cell?

Do you remember when I mentioned that arrays are 0-based in .NET? Well, they aren't in the MS Word world. So that means you will need to +1 to the indexes so that they correspond to the correct indexes in the Word table.

Although I managed to copy the data over, the column's name was not copied over. Is it possible to copy the column name as well or does it always take the first data in the row as column name?

You can absolutely append the column names to the table. You can access the columns of the DataGridView via the Columns property. What would this change then? Well, your row count would have to increase by 1 to account for the header row.

[shinichi90]

Something like this?

DataGridView1.ColumnHeadersVisible = True

Though it doesn't work.....Am i going in the right direction?

Thank you.
Regards,
shinichi90

[ForumAccount]

No, you will have to loop through each column in the DataGridView and place the HeaderText property as the Word's table cell value. Much the same as you are looping through the rows you will have to do to the columns.

[shinichi90]

have a look at this:

http://www.vbforums.com/showthread.php?t=626953

Thank you for the link. It helps me a lot^^.

However, may I know what this does?

Dim oTable As Word.Table = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, items.Count + 1, headers.Count)

        oTable.Range.Paste()

I tried removing that line of code and nothing cames out at the word document. Why do we need to paste it?

Thank you.
Regards,
shinichi90

[.paul.]

the dgv contents is read + formatted into a string + set on the clipboard, then pasted into the table the code creates, which is positioned at the end of the word doc (as it's a new doc, that's at the start).

[shinichi90]

have a look at this:

http://www.vbforums.com/showthread.php?t=626953

Hello again, I've just realised this when I was testing out my application. I input text into a Textbox and it's multi line and I save it into mysql database and then retrive it again through the datagridview and then export it out into ms word.

In the Textbox I input this(2 lines):

Hello
World!!!

and when I access to the database using the datagridview I saw both words but they were now in one line and when I export it into ms word only Hello was there. Moreover, when I add a new data inside without breaking it into 2 lines by pressing ENTER and then export it out to ms word through the data grid, the word World!!! was there but not the data that I have input just now.

I'm don't know why it happened. Could someone help me by explaining why this happens or how this problem came out to be?Was it to do with mysql not accepting multilines from vb.net?

Thank you.
Regards,
shinichi90

[shinichi90]

I managed to get both "hello world!!!" printed out in ms word only if i keep pressing SPACEBAR to go to the next line. Is there a way to overcome this so that I can press ENTER to go to the next line instead of SPACEBAR and both the words "hello world!!!" will still be printed out in ms word?

[shinichi90]

hi again!! bumping the thread up...>< i'm settling for a single line instead of a multiline since it gives more problem.. However, I would like to ask about something else regarding the datagridview. Is it possible to export the datagridview to ms word in paragraph form? My database store questions and I want it to appear as how our normal MCQ(Multiple Choice Questions) test paper appears.

Thanks.
Regards,
shinichi90

[kareninstructor]

I managed to copy it over but I feel that it was rather by luck as I saw a code where it exports data from datagrid to Excel and I just referred to the code.

Dim r As Integer, c As Integer
Dim oTable As Word.Table = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range,DataGridView1.RowCount,DataGridView1.ColumnCo unt)

oTable.Range.ParagraphFormat.SpaceAfter = 6
For r = 0 To DataGridView1.RowCount - 2
For c = 0 To DataGridView1.ColumnCount - 1
oTable.Cell(r + 1, c + 1).Range.Text = DataGridView1(c, r).Value.ToString()

I did not really understand this part of the code:

For r = 0 To DataGridView1.RowCount - 2
            For c = 0 To DataGridView1.ColumnCount - 1
                oTable.Cell(r + 1, c + 1).Range.Text = DataGridView1(c, r).Value.ToString()

Why we need to minus 2 from the row and plus 1 at oTable.Cell?

Although I managed to copy the data over, the column's name was not copied over. Is it possible to copy the column name as well or does it always take the first data in the row as column name?

Thank you.
Regards,
shinichi90

If this is the code you are referering too ignore the -2 and make it -1. The code is from Ken Tucker where his did -1 not -2, the minus two may have come from me for a specific reason and if not directly from me someone who got it from me.

         For intRow As Integer = 0 To TheGrid.RowCount - 2
            fs.WriteLine(String.Format("            <ss:Row ss:Height =""{0}"">", TheGrid.Rows(intRow).Height))
            For intCol As Integer = 0 To TheGrid.Columns.Count - 1
               fs.WriteLine("                <ss:Cell>")
               fs.WriteLine(String.Format("                   <ss:Data ss:Type=""String"">{0}</ss:Data>", TheGrid.Item(intCol, intRow).Value.ToString))
               fs.WriteLine("                </ss:Cell>")
            Next
            fs.WriteLine("            </ss:Row>")
         Next

If you are cycling through the DataGridView rows use the following if you do not want to include the new row row.

For row As Integer = 0 To DataGridView1.Rows.Count - 1
    If DataGridView1.Rows(row).IsNewRow Then
        Console.WriteLine("new row")
    Else
        ' get whatever you want from this row
    End If
Next

If your DataGridView was data bound then you be best to cycle thru the DataTable or DataView rather than the DataGridView.