Solved

VB.NET 2003 Dataset to Excel Export

Posted on 2007-03-25
9
411 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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 9

Author Comment

by:richdiesal
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 21

Expert Comment

by:theGhost_k8
Comment Utility
have u used imports??
0
 
LVL 21

Expert Comment

by:theGhost_k8
Comment Utility
i mean to say
Imports Excel
0
 
LVL 9

Author Comment

by:richdiesal
Comment Utility
"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
Comment Utility
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
Comment Utility
well yes, its ms.ofc.interop.XL
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now