Create an Excel .xls file from a datagridview?

  • Thread starter Thread starter David
  • Start date Start date
D

David

I have a Windows forms application that features a DataGridView. I want to
give the users an option to save that data to an Excel spreadsheet file as a
..xls.

Is there an obvious way to do this that I am missing?

I am currently using the Microsoft.Office.Interop namespace to open Excel
and create the spreadsheet. That works, and it isn't bad, but there are a
couple of downsides. First, you have to launch Excel, which means that Excel
has to be installed on the user's PC, and that might not always be the case.
Second, it was kind of difficult to convince Excel to stop running after I
opened it. (Relasing com objects and such, forcing a garbage collection,
etc.) It didn't seem very "clean".

The data in the datagridview is not data that came from a dataset. I could
force it into a datatable, and then connect to the Excel file as if it were a
database, and probably clear the existing contents and upload new data, but
that also seems to have problems. I don't know if that could work on a file
that didn't already exist.

All I really want is to create a file, without resorting to opening Excel
itself. I want to give this to a user so he can collect data from someplace,
package it as an Excel file, and send it to me. So, a minimum of fuss would
be ideal. I just wondered if there was some "easy" way to do this that I was
overlooking.
 
Try this, simply pass the DataGridView and filename i.e. MyResults.xls to
the sub to create the Excel file in XML format.

Module ExportToExcel_Raw
''' <summary>
''' Creates a simple MS-Excel spreadsheet from data residing in a
DataGridView
''' </summary>
''' <param name="TheGrid">DataGridView currently with data</param>
''' <param name="FileName">Path and filename to write an excel wookbook
too</param>
''' <remarks>
''' REQUIRES CHANGE IN TRY/CATCH FOR USE IN APPLICATIONS AS PER COMMENTS
IN THE CATCH.
''' </remarks>
Public Sub ExportGridToExcel(ByVal TheGrid As DataGridView, ByVal
FileName As String)
If TheGrid.DataSource Is Nothing Then
Throw New Exception("No data to work with")
End If

Try
Dim fs As New IO.StreamWriter(FileName, False)
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook
xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
fs.WriteLine(" <ss:Styles>")
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")
fs.WriteLine(" <ss:Worksheet ss:Name=""Sheet1"">")
fs.WriteLine(" <ss:Table>")
For x As Integer = 0 To TheGrid.Columns.Count - 1
fs.WriteLine(" <ss:Column ss:Width=""{0}""/>",
TheGrid.Columns.Item(x).Width)
Next
fs.WriteLine(" <ss:Row ss:StyleID=""1"">")
For i As Integer = 0 To TheGrid.Columns.Count - 1
fs.WriteLine(" <ss:Cell>")
fs.WriteLine(String.Format(" <ss:Data
ss:Type=""String"">{0}</ss:Data>", TheGrid.Columns.Item(i).HeaderText))
fs.WriteLine(" </ss:Cell>")
Next
fs.WriteLine(" </ss:Row>")
For intRow As Integer = 0 To TheGrid.RowCount - 2
fs.WriteLine(String.Format(" <ss:Row ss:Height
=""{0}"">", TheGrid.Rows(intRow).Height))
For intCol As Integer = 0 To TheGrid.Columns.Count - 1
fs.WriteLine(" <ss:Cell>")
fs.WriteLine(String.Format(" <ss:Data
ss:Type=""String"">{0}</ss:Data>", TheGrid.Item(intCol,
intRow).Value.ToString))
fs.WriteLine(" </ss:Cell>")
Next
fs.WriteLine(" </ss:Row>")
Next
fs.WriteLine(" </ss:Table>")
fs.WriteLine(" </ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()
Catch ex As Exception
' For demoing I am simply telling you we had a problem, in a real
application
' we at least remove the MsgBox and uncomment the next line. What
should also
' be considered is not using --> Throw new exception... because
now the exception
' points to the line with that statement rather then the original
problem.
'
'
MsgBox(ex.Message)
'Throw
End Try

OpenExcelFile(FileName)
Application.DoEvents()
End Sub
''' <summary>
''' Opens a file with the Association under Shell in the system registry
''' </summary>
''' <param name="FileName">Opens file by shell association</param>
''' <remarks></remarks>
Private Sub OpenExcelFile(ByVal FileName As String)
If System.IO.File.Exists(FileName) Then
Process.Start(FileName)
End If
End Sub
End Module
 
Back
Top