Solved

VB.NET 2003 Dataset to Excel Export

Posted on 2007-03-25
9
436 Views
Last Modified: 2007-12-19
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?
0
Comment
Question by:richdiesal
  • 6
  • 3
9 Comments
 
LVL 21

Accepted Solution

by:
theGhost_k8 earned 500 total points
ID: 18792179
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
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 18792183
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
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 18792186
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 9

Author Comment

by:richdiesal
ID: 18792712
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
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 18794016
have u used imports??
0
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 18794017
i mean to say
Imports Excel
0
 
LVL 9

Author Comment

by:richdiesal
ID: 18795880
"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
 
LVL 9

Author Comment

by:richdiesal
ID: 18798145
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
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 18798152
well yes, its ms.ofc.interop.XL
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question