Vb.net/Excel

[Jacksparrow87]

Hi people,

Just a easy simple question really, I have a datagrid and 2 buttons. What I do is select some search criteria and then click on the search button which displays the data in the datagrid.

What I need help with is the 2nd button which is called 'Save' what I want to do is when this button is clicked, I want to take the information from the datagrid (headers, the data inside etc) and save it in excel. Ideally what I would prefer is a save dialog box to appear and the default location to be coded (C:\Test for the moment) and I can change this later on.

Is this possible? If so could you give me an example of some coding please?

Please let me know. Thanks

 

[Andrzejek]

Add the Reference to Microsoft Excel xx.x object library

    Private Sub Button1_Click(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles Button1.Click
        Dim xl As New Excel.Application
        Dim MyArray As Array

        xl.Workbooks.Add()
        xl.Visible = True

        For i As Integer = 1 To 100
            MyArray = Strings.Split("A" & i & ",B" & i & ",C" & i & ",D" & i, ",")
            xl.Range("A" & i & ":D" & i).Value = MyArray
        Next

        xl = Nothing
    End Sub

This will get you started.


Have fun.

---- Andy

 

[Jacksparrow87]

Thanks for your help, tried pasting your coding in to a button but it highlighted microsoft.excel and states type excel is not defined.

So I added microsoft excel object library 11.0 (there was a microsoft excel object library 5.0) but I thought it would be 11.

However microsoft.excel is still underlined, after this at the top I typed in Import Microsoft but still microsoft.excel is underlined, any help please?

 

[Andrzejek]

I hate when the code I get does not work. :-(

So, I started a new NET Project (in VB.NET 2008), added Microsoft Excel 11.0 Object Library (from COM tab) reference, pasted the code I gave you and - no problem, works like a dream (for me).

And this is the only code in my Form:

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xl As New Excel.Application
        Dim MyArray As Array

        xl.Workbooks.Add()
        xl.Visible = True

        For i As Integer = 1 To 100
            MyArray = Strings.Split("A" & i & ",B" & i & ",C" & i & ",D" & i, ",")
            xl.Range("A" & i & ":D" & i).Value = MyArray
        Next

        xl = Nothing
    End Sub
End Class

Have fun.

---- Andy

 

[Jacksparrow87]

Well I found out some of the problem, it was me using Micrososft.excel 11 when i should have been using 5.0

However another problem has occurred now and that is when I use your coding it highlights Excel.Application part and states 'New cannot be used on an interface' do you understand what this mean? Thanks

 

[Andrzejek]

I use VB.NET 2008, what do you use?
I don't know if the version difference would coused it.....


Have fun.

---- Andy

 

[DaveInIowa]

Reference Microsoft Excel 11.0 and include:

Imports Excel = Microsoft.Office.Interop.Excel

 

[Jacksparrow87]

Thanks Dave, however I've chosen another method:

So far I have the following coding:

     Dim dataSource As DataTable = DirectCast(dglog.DataSource, DataTable)

        Dim currentRow As New System.Text.StringBuilder
        Dim entireFile As New System.Text.StringBuilder

        'Write the column names as headers for the file 
        For Each column As DataColumn In dataSource.Columns
            currentRow.Append(""""c & column.ColumnName & """"c & ",")
        Next

        'Add the current line to the entire file StringBuilder 
        entireFile.Append(currentRow.ToString)

        'Write each data row to the file StringBuilder 
        For rowIndex As Int32 = 0 To dataSource.Rows.Count - 1
            currentRow = New System.Text.StringBuilder
            For Each column As DataColumn In dataSource.Columns
                currentRow.Append(""""c & dataSource.Rows(rowIndex)(column.ColumnName).ToString & """"c & ",")
            Next
            entireFile.Append(currentRow.ToString)
        Next

        'Write the contents of the entireFile StringBuilder to a file. 
        Dim sw As New System.IO.StreamWriter("C:\test\DataGrid.csv")

        sw.Write(entireFile.ToString)
        sw.Close()

but just a few points I was hoping you could help me out on:

1: The csv file gets saved as column1, column2, column3, data1, data2, data3, data1, data2,data3 however I want the data list like:

column1, columns2, column3
data1, data2, data3
data1, data2, data3

2: Is there a chance a save as dialog box appears because the current way the data gets overwritten everytime

and finally:

3: When the data gets saved into the csv file the column headers are saved as the table field names and not as the text on the datagrid for example take the following coding as example(I have each column mapped in my datagrid):

    Dim ts As New DataGridTextBoxColumn 
        ts.MappingName = "TSpent" 
        ts.HeaderText = "Time Spent" 
        ts..Width = 280 
        ts.NullText = "" 
        dglg.GridColumnStyles.Add(ts)

Now if data was to get saved into the csv file, rather than the column heading being Time Spent in the column header it would be TSpent, any ideas on how to get the text in the datagrid header to appear rather than the column field.

Thanks