How to Create Excel from DataGridView

[justgreat]

Please can you tell me how do we create an excel file for each datatable of a dataset ?

I tried some codes I found in the net, they don't seem working, so can someone provide me a tutorial or code that shows how to create an excel file from datagrid view ??

[hassa046]

i have an example if you need?

[hassa046]

With this code you have a start

Public Sub ExportToExcel(ByVal vData As Data.DataTable)
        Dim vSheet As Excel.Worksheet = Nothing
        Dim vRange As Excel.Range = Nothing

        Dim intColumnValue As Integer

        Try
            vWorkbooks = vExcel.Workbooks
            vWorkbook = vWorkbooks.Add

            vSheet = CType(vWorkbook.Sheets(1), Worksheet)
            vRange = vSheet.Cells

            'SET THE COLUMN NAMES
            For i As Integer = 0 To (vData.Columns.Count - 1)
                vRange(1, (i + 1)) = vData.Columns(i).ColumnName.ToString
            Next

            'SET THE CELLS VALUES
            For y As Integer = 0 To (vData.Rows.Count - 1)
                For intColumnValue = 0 To (vData.Columns.Count - 1)
                    vRange(y + 2, (intColumnValue + 1)) = vData.Rows(y).ItemArray(intColumnValue).ToString
                Next
            Next

            vSheet.Columns().EntireColumn.AutoFit()
        Catch Err As Exception
            Throw New Exception("I-Synergy.Reporting.Business.MSExcel.ExportToExcel:" & vbCrLf & Err.Message)
        End Try
    End Sub

[justgreat]

Thanks a lot hassa046 for your fast reply, I will try it after lunch break and let you know if it works, thanks a lot

[hassa046]

you're welcome

[justgreat]

Thanks man, with some edits to fit my code, your code work.


I have in my grid, a column with delete button, with your code it's not showing it's value and not making problem, which is great, but I wonder why with the code in the two following links it was not working due to this column, while your code work ?
Any idea ?



Link1


Link2

[hassa046]

Maybe i made smarter code? LOL

The first link uses a combination of early- and late bound variable to excel. Maybe there lies the problem.
Frankly, i don't know.

[justgreat]

Lol, as much as it works, than it's good
By the way, I saw in the net some ways that allow to create an xml and open it by excel, it seems faster than looping through the cells, what do you think about that methods ? do you recommand them ? or it's better to do the way you showed me ?

[hassa046]

The method showed there is another way to generate a xml file with excel formatting. In this case you better can use my method. If you want a real xml what also is importable in excel you should look for another solution to convert a datatable to xml.

[justgreat]

Thanks a lot again.

Do you have any code to import from Excel to datatable or dataset ?

[hassa046]

i convert excel first to csv. that is a lot easier to import. but excel should work also.
I will look into it.

[GaryMazzone]

Whe I do that (move Excel data to database tables) I open the Execl file like a database, select the data into a datareader or DataTable, Open a new connection to the database then use the datareader/Datatable holding the data from Execl and create insert statements to the database table

[justgreat]

GaryMazzone, do you mind to pass me the code please ?

[GaryMazzone]

What don't you know how to do. Both parts are faily stright forward. Attempt it and people will help.

[justgreat]

What don't you know how to do. Both parts are faily stright forward. Attempt it and people will help.

using Excel as a database !

If you don't want to provide your code, there is no problem, don't get mad, i will search in google and find how, but I supposed that it would be faster to ask you cuz you said U already did and i have thought based on your previous post that you are proposing help !

Nevermind


Peace

[GaryMazzone]

Check www.connectionstrings.com for a connection string to treat Excel as a database. Then you use the worksheet name as a table name