asked on

exporting data

Hi all,

I have a datagrid that displays some data for me what i need to do now is add a button to export the data to a excel file. I am using vb.net 2005

Thanks for the help

Frank

myadapter = New MySqlDataAdapter
        myData = New DataSet
        Me.DataGridView1.Columns.Clear()
        Searchvalue = ""
        Searchvalue = InputBox("Enter Date", "Search", "", 100, 100)
        Dim conn As New MySqlConnection
        Dim cmd As New MySqlCommand
        conn.ConnectionString = _
                        "server=10.0.0.10;" _
                        & "uid=xxxx;" _
                        & "pwd=xxxx;" _
                        & "database=dnote_db"
        Try
            conn.Open()
            cmd.CommandText = "SELECT `time_entered`,`ord_nr`,`supplier`,`depot`,`product`,`qty`,`user_entered`,`time_printed` FROM data WHERE `depot` LIKE '%wenkem%' AND `time_entered` LIKE '%" & Searchvalue & "%' AND `log` NOT LIKE 'Original Row nr :' OR `depot` LIKE '%wenkem%' AND `user_entered` LIKE '%" & Searchvalue & "%' OR `depot` LIKE '%wenkem%' AND `time_printed` LIKE '%" & Searchvalue & "%' order by `time_entered`"
            cmd.Connection = conn
            myadapter.SelectCommand = cmd
            myadapter.Fill(myData)
            Me.DataGridView1.DataSource = myData.Tables(0)
            Me.DataGridView1.AutoResizeColumns()
 
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

Open in new window

Omego2K

here is an article that provides a way to do it:

http://www.codeproject.com/KB/grid/ExportDatagridviewToExcel.aspx

Excel must be installed on the system using it though.

frankie_jnr

ASKER

if i can output the grid to a csv file it would be fine to

game-master

have a look at this...

http://www.dotnetjohn.com/articles.aspx?articleid=78

http://support.microsoft.com/default.aspx?scid=kb;en-us;306022&Product=vbNET

https://www.experts-exchange.com/questions/21083976/Export-to-Excel.html

i hope this will link will help u...

game-master

game-master

this link can give u lot of idea...

http://support.microsoft.com/kb/306022/

game-master

frankie_jnr

ASKER

hi there i have had a look but am not to sure how i can use it all the examles i cant see them using data from a datagrid and the microsoft site has many defferent ways to do this i am not sure what the best way would be

thanks for the help

frankie_jnr

ASKER

is there not an easyier way to just export to csv? i am in trouble a bit i need to have somthing this afternoon ;) sorry to keep nagging

ASKER CERTIFIED SOLUTION

game-master

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

frankie_jnr

ASKER

I have found a way that exports it and opens excel automatically. My only question is is there a way to auto size the column so that all data is displayed? I am attaching the code

Dim wapp As Microsoft.Office.Interop.Excel.Application
 
        Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet
 
        Dim wbook As Microsoft.Office.Interop.Excel.Workbook
 
        wapp = New Microsoft.Office.Interop.Excel.Application
 
        wapp.Visible = True
 
        wbook = wapp.Workbooks.Add()
 
        wsheet = wbook.ActiveSheet
 
        Dim iX As Integer
 
        Dim iY As Integer
 
        Dim iC As Integer
 
        For iC = 0 To DataGridView1.Columns.Count - 1
 
            wsheet.Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
 
            wsheet.Cells(1, iC + 1).font.bold = True
 
        Next
 
        wsheet.Rows(2).select()
 
        For iX = 0 To DataGridView1.Rows.Count - 2
 
            For iY = 0 To DataGridView1.Columns.Count - 1
 
                wsheet.Cells(iX + 2, iY + 1).value = DataGridView1(iY, iX).Value.ToString
 
            Next
 
        Next
 
        wapp.Visible = True
 
        wapp.UserControl = True

Open in new window

Nasir Razzaq

You can use the Range.Autofit method.
http://msdn.microsoft.com/de-de/library/microsoft.office.interop.excel.range.autofit%28office.11%29.aspx

wsheet.Range("A1", "H10").AutoFit()

frankie_jnr

ASKER

Thank You for the help

Nasir Razzaq

Did my post help at all?

game-master

im glad i could help you...

game-master