Export DataGridView to Excel

Hello all,

I'd like to export a DataGridView to Excel. I am using .NET 2.0 and VB.NET.

But I don't know if the user has Excel on their machines, so can't use COM
Excel object.

Any ideas please? How is an Excel file put together?

Many thanks,

Mike

re: Export DataGridView to Excel

Mile,

I was faced with the same problem. Because users had different versions of
Excel installed, I could not find one solution.

Finally I created an html table from the datagridview bindingsource. If you
save this as *.xls, it seems to open in all versions of Excel.

You can dress up the html with a header and description, but the basis is
the table.

See if that works for you.

Rick

re: Export DataGridView to Excel

No problem, I've written some code to do this using CSV...

Public Sub WriteCSVFile(ByVal dgv As DataGridView)

Dim OutputStr As String = ""

Dim nCol As DataGridViewColumn
' Write the column headers
For Each nCol In dgv.Columns
' First row, write column names
If nCol.Visible = True Then
OutputStr = OutputStr & "," & nCol.HeaderText
End If
Next
' Trim off the first comma
If OutputStr.StartsWith(",") Then OutputStr = OutputStr.Substring(1,
OutputStr.Length - 1)

Dim nRow As DataGridViewRow
Dim nCell As DataGridViewCell
Dim OutputLine As String = ""
For Each nRow In dgv.Rows
For Each nCell In nRow.Cells
If nCell.Visible = True Then
OutputLine = OutputLine & "," &
nCell.FormattedValue.ToString.Replace(",", "")
End If
Next
' Trim off the first comma
If OutputLine.StartsWith(",") Then OutputLine =
OutputLine.Substring(1, OutputLine.Length - 1)
' New line? Add it
If OutputLine <"" Then OutputStr = OutputStr & vbCrLf &
OutputLine
Next
MsgBox(OutputStr)
End Sub

re: Export DataGridView to Excel

"Rick" <Rick@LakeValleySeed.comwrote in message
news:OCyD5k5hHHA.3960@TK2MSFTNGP02.phx.gbl...Thanks Rick,

That's a great idea. Just a normal HTML table saved as ".xls"

I've made a CSV solution for now which works well but has of course no
formatting.

*fap*

re: Export DataGridView to Excel

Mike Wilson schreef:I did it using CarlosAg Excel writer (it generates excel xml)...

http://www.timvw.be/datagridview-to-excel/

(It tries to mimic the CellStyle and format as much as possible.. Be
aware that excel has a limitted color palette, eg: Color.LightGreen as
BackColor in a Cell would be greyish in the sheet)

--
Tim Van Wassenhove <url:http://www.timvw.be/>

re: Export DataGridView to Excel

"Tim Van Wassenhove" <timvw@newsgroup.nospamwrote in message
news:ujlTmDEiHHA.1708@TK2MSFTNGP03.phx.gbl...Hi Tim,

Useful, thanks. I'll have a read and pass the info on.

Mike