Export Unbound DataGrid To CSV File\Excel with WINDOWS FORM

Hot Topic

multithreads... no!
Is there BeginUpdate() functionality for the DataGridView?
Binding Source always suggests dataset changes
multitasking
Focus() method
Questions, questions, questions.
How to make a property not show up in propertyGrid
Databinding radiobuttons
System.ArgumentException while deploying
How to create a control LIKE the property window on VS.net

Windows Forms

what is rating?
Might be a stupid question but...
shared SQLconnection accross all forms.
multiple forms
Scrolling textbox
Sample Code: TreeGridView - collapsing/expanding DataGridView
Determining max size of Image in PictureBox (.NET 2.0)
Really easy question
event on scaning barcode
FileSystemWatcher

Export Unbound DataGrid To CSV File\Excel with WINDOWS FORM

I've spent hours hunting around the internet to find out how to do this and i can't seem to do it. I've found endless posts on ASP.NET but im using WinForms/VB2005

I would prefer not to have to manuall configure each export process at the time as the DataGridView can be one of at least 4 different structures depending on the options the user selected to fill it.

There must be a simple way of taking all records and layout and playing it in excel.

There is also no dataset for this GridView..

Can anyone help me please Im tearing my hair out and its probably really easy!

Cheers

Answer this question

Export Unbound DataGrid To CSV File\Excel with WINDOWS FORM

  • itfareed

    McWhirter wrote:

    For anyone else trying to work out how to do this, here is one way.

    This will work if the dgv is bound or unbound. Doesn't make any difference

    You need to add the reference for the Microsoft Excel 11 (or 5) object library

    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 = False

    /* In csharp this metod need a object*/

    /*workbooks.add(object template)*/

    wbook = wapp.Workbooks.Add()

    wsheet = wbook.ActiveSheet

    Try

    Dim iX As Integer

    Dim iY As Integer

    Dim iC As Integer

    Dim CC As Integer = FrmMain.DataGridView1.Columns.Count

    For iC = 0 To FrmMain.DataGridView1.Columns.Count - 1

    wsheet.Cells(1, iC + 1).Value = FrmMain.DataGridView1.Columns(iC).HeaderText

    wsheet.Cells(1, iC + 1).font.bold = True

    wsheet.Cells(1, iC + 1).horizontalalignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter

    wsheet.Rows(1).autofit()

    Next

    For iX = 0 To FrmMain.DataGridView1.Rows.Count - 1

    For iY = 0 To FrmMain.DataGridView1.Columns.Count - 1

    wsheet.Cells(iX + 2, iY + 1).value = FrmMain.DataGridView1(iY, iX).Value.ToString

    wsheet.Cells(iX + 2, iY + 1).horizontalalignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft

    Next

    Next

    wapp.Visible = True

    Catch ex As Exception

    MsgBox(ex.Message)

    End Try

    wapp.UserControl = True


  • nkat2112

    Dear McWhirter,

    I finished to "Export Unbound DataGrid To CSV File\Excel with WINDOWS FORM".

    But, I want to Export to Excel file with data format "Text"

    Can you help me

    Thanks !



  • rich-75

    Hi McWhirter,

    I have some questions about the sample code you posted. I am programming in C# and the code works fine except that the font and horozontalalignment properties are not available at the individual cell level.

    wsheet.Cells(1, iC + 1).Font.Bold = true; (NOT AVAILABLE - Says 'object' does not contain a definition for 'Font' )

    wsheet.Cells.Font.Bold = true; (AVAILABLE - Whole worksheet font is set to bold)

    Is this code working for you

    Do you have any insight of the problem I am facing

    Also I need to do cell coloring, any ideas

    Any help is greatly appreciated.

    Thank You in advance.

    Sohaib.

  • Ahmad Pirani

    For anyone else trying to work out how to do this, here is one way.

    This will work if the dgv is bound or unbound. Doesn't make any difference

    You need  to add the reference for the Microsoft Excel 11  (or 5) object library

     

    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 = False

    wbook = wapp.Workbooks.Add()

    wsheet = wbook.ActiveSheet

    Try

    Dim iX As Integer

    Dim iY As Integer

    Dim iC As Integer

    Dim CC As Integer = FrmMain.DataGridView1.Columns.Count

    For iC = 0 To FrmMain.DataGridView1.Columns.Count - 1

    wsheet.Cells(1, iC + 1).Value = FrmMain.DataGridView1.Columns(iC).HeaderText

    wsheet.Cells(1, iC + 1).font.bold = True

    wsheet.Cells(1, iC + 1).horizontalalignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter

    wsheet.Rows(1).autofit()

    Next

    For iX = 0 To FrmMain.DataGridView1.Rows.Count - 1

    For iY = 0 To FrmMain.DataGridView1.Columns.Count - 1

    wsheet.Cells(iX + 2, iY + 1).value = FrmMain.DataGridView1(iY, iX).Value.ToString

    wsheet.Cells(iX + 2, iY + 1).horizontalalignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft

    Next

    Next

    wapp.Visible = True

    Catch ex As Exception

    MsgBox(ex.Message)

    End Try

    wapp.UserControl = True


  • Niall Baird

    I'll have to apologise and say that i have no idea :(

    I didn't get into the cell colouring and formatting as it wasn't required in the end. I am working on some other things at the moment that will probably need it. If you find out how please post it and i will do the same :)

    Cheers

  • sandrop

    anyone
  • Export Unbound DataGrid To CSV File\Excel with WINDOWS FORM
Answer this question