VB.NET 2003 Dataset to Excel Export

I have a Dataset and a Dataview, displayed in the following manner (dgData is a Datagrid):

            Dim DataAU As New formChild
            Dim ViewAU As New DataView
            ViewAU.Table = dt
            ViewAU.RowFilter = "Type = 'AU'"
            ViewAU.Sort = "count DESC"
            DataAU.dgData.DataSource = ViewAU
            Dim ts As New DataGridTableStyle
            ts.MappingName = ViewAU.Table.TableName
            DataAU.dgData.TableStyles.Add(ts)
            DataAU.dgData.TableStyles(0).GridColumnStyles(0).Width = 583
            DataAU.dgData.TableStyles(0).GridColumnStyles(1).Width = 35
            DataAU.dgData.TableStyles(0).GridColumnStyles(2).Width = 0
            DataAU.dgData.TableStyles(0).GridColumnStyles(0).HeaderText = "Author"
            DataAU.dgData.TableStyles(0).GridColumnStyles(1).HeaderText = "#"

            Dim DataSO As New formChild
            Dim ViewSO As New DataView
            ViewSO.Table = dt
            ViewSO.RowFilter = "Type = 'SO'"
            ViewSO.Sort = "count DESC"
            DataSO.dgData.DataSource = ViewSO
            Dim ts As New DataGridTableStyle
            ts.MappingName = ViewSO.Table.TableName
            DataSO.dgData.TableStyles.Add(ts)
            DataSO.dgData.TableStyles(0).GridColumnStyles(0).Width = 583
            DataSO.dgData.TableStyles(0).GridColumnStyles(1).Width = 35
            DataSO.dgData.TableStyles(0).GridColumnStyles(2).Width = 0
            DataSO.dgData.TableStyles(0).GridColumnStyles(0).HeaderText = "Parent Publication"
            DataSO.dgData.TableStyles(0).GridColumnStyles(1).HeaderText = "#"

I would like these two datasets to be saved to an Excel document, one in each of the first two worksheets.   A prompt needs to appear to ask a location to save the file.  I'd like to preserve column width formatting, with HeaderText appearing as the worksheet names.  This is a simplified version: there are really 7 different worksheets, but I can modify an example containing just 2.  What code would produce this outcome?
LVL 9
richdiesalProfessorAsked:
Who is Participating?
 
theGhost_k8Connect With a Mentor Database ConsultantCommented:
EE contains may such question :

You can also loop through your dataset and write rows to excel sheets. This is a more flexible approach (requires a bit more code) and allows for custom formatting.

Example code for writing to excel:

You can also import data to excel row by row....

Import Excel reference to your project then...


Dim oXL As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

'create app    
oXL = CreateObject("Excel.Application")

'creat workbook
oBook = oXL.Workbooks.Add()

Set oBook = oXL.ActiveWorkbook

'selecting sheets/renaming sheets
oBook.Worksheets("Sheet1").Select
Set oSheet = oBook.Worksheets("Sheet1")
'rename
oSheet.Name = "MySheet"

'adding more than the defualt 3 sheets
Set oSheet = oBook.Worksheets.Add(, oBook.Worksheets(oBook.Worksheets.Count))
'to name sheet
oSheet.Name = "MyFourthSheet"


'To write to sheet
'row 1 column 1
oSheet.Cells(1,1) = "Data"
'row 1 column 2
oSheet.Cells(1,2) = "Data"

'saving...
Dim fPath = "Yourpath"
Dim fileName = fPath & "\" & myFileName ".xls"
oSheet.SaveAs(fileName)

oXL.Workbooks.Close()
oXL.Quit()
oXL = Nothing
0
 
theGhost_k8Database ConsultantCommented:
the other:
http://www.experts-exchange.com/Programming/Languages/.NET/Q_22109604.html?sfQueryTermInfo=1+dataset+excel+save

here is a procedure you can use. It takes Excel file name and DataTable as arguments and writes the contents of the DataTable to an Excel file.

Public Sub WriteToExcelSpreadsheet(ByVal fileName As String, ByVal dt As System.Data.DataTable)
        Dim iCol, iRow, iColVal As Integer
        Dim missing As Object = System.Reflection.Missing.Value
        Dim bNew As Boolean
        Dim i As Integer
        ' Open the document that was chosen by the dialog
        Dim aBook As Excel.Workbook
        Try
            ''re-initialize excel app
            ExlApp = New Excel.Application

            If ExlApp Is Nothing Then
                ''throw an exception
                Throw (New Exception("Unable to Start Microsoft Excel"))
            Else
                ''supresses overwrite warnings
                ExlApp.DisplayAlerts = False
               
                'aBook = New Excel.Workbook
                ''check if file exists
                If File.Exists(fileName) Then
                    aBook = ExlApp.Workbooks.Open(fileName)
                Else
                    aBook = ExlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
                End If
                With ExlApp
                    .SheetsInNewWorkbook = 1
                    '.Workbooks.Add()
                    .Worksheets(1).Select()
                    'For displaying the column name in the the excel file.
                    For iCol = 0 To dt.Columns.Count - 1
                        ''clear column name before setting a new value
                        .Cells(1, iCol + 1).Value = ""
                        .Cells(1, iCol + 1).Value = dt.Columns(iCol).ColumnName.ToString
                    Next
                    'For displaying the column value row-by-row in the the excel file.
                    For iRow = 0 To dt.Rows.Count - 1
                        For iColVal = 0 To dt.Columns.Count - 1
                            .Cells(iRow + 2, iColVal + 1).Value = Trim(dt.Rows(iRow).ItemArray(iColVal).ToString)
                        Next
                    Next
                    If File.Exists(fileName) Then
                        .ActiveWorkbook().Save() 'fileName)
                    Else
                        .ActiveWorkbook().SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing)
                    End If
                    .ActiveWorkbook.Close()
                End With
                Console.Write("File exported sucessfully")
            End If
        Catch ex As Runtime.InteropServices.COMException
       
            Console.Write("ERROR: " & ex.Message)
        Catch ex As Exception
           
            Console.Write("ERROR: " & ex.Message)
        Finally
            ExlApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp)
            aBook = Nothing
            ExlApp = Nothing

        End Try
    End Sub
0
 
theGhost_k8Database ConsultantCommented:
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
richdiesalProfessorAuthor Commented:
I have tried these.  VB.NET will not recognize the term "Excel."  I have added "Microsoft Excel 11.0 Object Library" as a COM object, but that doesn't seem to have helped.  I get the error "Type Excel has not been defined" when running "Dim oXL As Excel.Application"
0
 
theGhost_k8Database ConsultantCommented:
have u used imports??
0
 
theGhost_k8Database ConsultantCommented:
i mean to say
Imports Excel
0
 
richdiesalProfessorAuthor Commented:
"Namespace or type 'Excel' for the Imports 'Excel' cannot be found." - error on "Imports Excel"

I have a Namespace added called "Excel"
I have a COM object called "Excel"

What's going wrong?
0
 
richdiesalProfessorAuthor Commented:
So, I fixed it.  I removed the Namespace reference and used the line:
Imports Excel = Microsoft.Office.Interop.Excel

...Instead of just "Imports Excel".  That with the accepted solution above was enough to get things working.
0
 
theGhost_k8Database ConsultantCommented:
well yes, its ms.ofc.interop.XL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.