Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB.NET 2003 Dataset to Excel Export

Posted on 2007-03-25
9
Medium Priority
?
479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 21

Accepted Solution

by:
K V earned 1500 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:K V
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:K V
ID: 18792186
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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:K V
ID: 18794016
have u used imports??
0
 
LVL 21

Expert Comment

by:K V
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:K V
ID: 18798152
well yes, its ms.ofc.interop.XL
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Suggested Courses

618 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