Imports System.IO
Imports Microsoft.Office.Interop
Public Class frmMain
Private Sub btnRunSample_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnRunSample.Click
' Use CreateDataset to fire off this function.
' The two arguments are the Path and file name
' for the export and the path and file name for
' the XML file to import.
' CreateDataset("File to export", "File to Import")
CreateDataset("C:\CustomerList.xls", "C:\MyXML.xml")
End Sub
Private Sub CreateDataset( _
ByVal strExportPath As String, _
ByVal strXMLImport As String)
' I am using an XML file for my
' sample but you can use any dataset
' from any number of sources.
Dim dS As New DataSet
Dim fs As FileStream = New FileStream( _
strXMLImport, FileMode.Open)
Try
dS.ReadXml(fs)
dgvSample.DataSource = dS
dgvSample.DataMember = "CustomerList"
Catch ex As Exception
MessageBox.Show(ex.Message & vbNewLine & ex.Source, _
"Error", MessageBoxButtons.OK)
Finally
fs.Close()
End Try
Export2Excel(strExportPath, dS.Tables(0))
End Sub
Private Sub Export2Excel( _
ByVal Filename As String, _
ByVal dt As DataTable)
' I can't take credit for this section of code
' since tt was cut, pasted and modified from some
' code I lifted from http://www.vbforums.com.
' I looked for the original code posted so I could
' credit the author but I couldn't find it anymore.
' This codes works fine for Excel 2003 and can be
' modified slightly to work with XP. Remember to
' add the correct Reference Library to your project.
Dim excel As New Excel.ApplicationClass()
Dim wBook As Excel.Workbook = Nothing
Dim wSheet As Excel.Worksheet = Nothing
Try
wBook = excel.Workbooks.Add()
wSheet = CType(wBook.ActiveSheet(), Excel.Worksheet)
wSheet.Name = "News"
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(1, colIndex) = dc.ColumnName
Next
For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(rowIndex + 1, colIndex) = _
dr(dc.ColumnName)
Next
Next
wSheet.Columns.AutoFit()
wBook.SaveAs(Filename)
Catch ex As Exception
MessageBox.Show(ex.Message & vbNewLine & ex.Source, _
"Error", MessageBoxButtons.OK)
Finally
If Not wBook Is Nothing Then
wBook.Close()
End If
If Not excel Is Nothing Then
excel.Quit()
End If
End Try
End Sub
End Class
*************************************************
*************************************************
*************************************************
Here is what the myXML.xml
<?xml version="1.0" encoding="utf-8"?>
<NewDataSet>
<CustomerList>
<FullName>Tom Servo</FullName>
<AddressLine1>100 Ann St</AddressLine1>
<City>Somewhere</City>
<State>MI</State>
<ZipCode>49094</ZipCode>
</CustomerList>
<CustomerList>
<FullName>Auther Dent</FullName>
<AddressLine1>555 Maple St</AddressLine1>
<City>Nowhere</City>
<State>MI</State>
<ZipCode>49009</ZipCode>
</CustomerList>
<CustomerList>
<FullName>Rand Flagg</FullName>
<AddressLine1>207 Walking Dude</AddressLine1>
<City>Anywhere</City>
<State>MI</State>
<ZipCode>49001</ZipCode>
</CustomerList>
</NewDataSet>