[RESOLVED] [2005] Exporting

[onlyGirl]

Does anybody know how to export a dataset to either a text of csv file? The file will need to be created when exporting, it will not be there already.

[stimbo]

Examples on the forum.

Search -> Export Dataset

[Shaggy Hiker]

Would XML be acceptable? Oh well, it hardly matters, since I am on a computer where I couldn't give you an example anyways, but dataset to XML is very very easy....I just can't remember it off the top of my head.

[onlyGirl]

Ok, well I found this code on the forum but my question now is, I already have the datatable that the data is populated to. The code on the sample creates a datatable. Do I ommit this or not?

4.	Public Sub Export(ByVal Filename As String, ByVal dt As DataTable)
5.	        Dim excel As New Excel.ApplicationClass()
6.	        Dim wBook As Excel.Workbook = Nothing
7.	        Dim wSheet As Excel.Worksheet = Nothing
8.	 
9.	        Try
10.	 
11.	            wBook = excel.Workbooks.Add()
12.	            wSheet = wBook.ActiveSheet()
13.	            wSheet.Name = "History Card Report"
14.	 
15.	            Dim dc As System.Data.DataColumn
16.	            Dim dr As System.Data.DataRow
17.	            Dim colIndex As Integer = 0
18.	            Dim rowIndex As Integer = 0
19.	 
20.	            For Each dc In dt.Columns
21.	                colIndex = colIndex + 1
22.	                excel.Cells(1, colIndex) = dc.ColumnName
23.	            Next
24.	 
25.	            For Each dr In dt.Rows
26.	                rowIndex = rowIndex + 2
27.	                colIndex = 0
28.	                For Each dc In dt.Columns
29.	                    colIndex = colIndex + 1
30.	                    excel.Cells(rowIndex + 1, colIndex) = (dc.ColumnName)
31.	                Next
32.	            Next
33.	 
34.	            wSheet.Columns.AutoFit()
35.	 
36.	            wBook.SaveAs(Filename)
37.	           
38.	        Catch ex As Exception
39.	 
40.	            ShowErrorForm(ex)
41.	 
42.	        Finally
43.	 
44.	            If Not wBook Is Nothing Then
45.	                wBook.Close()
46.	            End If
47.	 
48.	            If Not excel Is Nothing Then
49.	                excel.Quit()
50.	            End If
51.	 
52.	        End Try
53.	 
54.	    End Sub

[onlyGirl]

I'm using the code below but it's not capturing the data in the dataset, instead it just selects the header.

Public Sub Export(ByVal Filename As String)
        Dim excel As New Microsoft.Office.Interop.Excel.Application
        Dim wbook As Microsoft.Office.Interop.Excel.Workbook
        Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim dc As DataColumn
        Dim dr As DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0
        Try
            If numForReference = 1 Then
                wbook = excel.Workbooks.Add()
                wsheet = wbook.ActiveSheet()

                For Each dc In Me.dsSelect.Tables(0).Columns
                    colIndex = colIndex + 1
                    excel.Cells(1, colIndex) = dc.ColumnName
                Next

                For Each dr In Me.dsSelect.Tables(0).Rows
                    rowIndex = rowIndex + 2
                    colIndex = 0
                    For Each dc In Me.dsSelect.Tables(0).Columns
                        colIndex = colIndex + 1
                        excel.Cells(rowIndex + 1, colIndex) = (dc.ColumnName)
                    Next
                Next
                wsheet.Columns.AutoFit()
                wbook.SaveAs()

            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ReservoirGrail", MessageBoxButtons.OK, MessageBoxIcon.Error)

        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

[techgnome]

That's because you're assigning the cell the ColumnHeader rather than the data that is in the column.

excel.Cells(rowIndex + 1, colIndex) = (dc.ColumnName)
s/b
dc.Value

-tg

[onlyGirl]

dc.Value isn't an option. It won't let me use it.

[techgnome]

I'm asleep at the keyboard here.... you should be getting them from the dataRow... dr.Item(dc.ColumnName) (I'm working off of memory here, but I think that should work.)

-tg

[onlyGirl]

That didn't work either, I get this nice error message:

Overload resolution failed because no accessible 'Item' accepts this number of arguments.

[stimbo]

What happens if you put a , after dc.ColumnName? Look at the intelli-sense and see if it tells you another parameter(s) that needs to be specified.

(Could just be that the code is wrong of course... )

[onlyGirl]

nope, nothing shows up. there is no other parameter.

[techgnome]

OK.... 1) Get over the dataColumn.... that's never going to give you the value.

2) In the DATAROW variable, the IS a property there.... use the intellisense... it's going to be (or should be) fairly obvious what you want.

NEver mind.... see the screen shots attached... they show the ITEM property of the data row and how to use it.

-tg

[onlyGirl]

I found some other code that shows you how to export to excel:

Dim colIndex, rowIndex As Integer
        Dim excel As New Excel.Application

        excel.Visible = False
        wsheet = excel.Workbooks.Add.Worksheets.Add
        Try
            With wsheet
                Dim col, dc As DataColumn
                Dim i, r, c As Integer

                testImport.Show()
                testImport.Label1.Text = "Please wait, reading and saving data."
                Application.DoEvents()

                testImport.ProgressBar1.Value = 15
                Application.DoEvents()
                For Each dc In Me.dsSelect.Tables(0).Columns
                    colIndex = colIndex + 1
                    excel.Cells(1, colIndex) = (dc.ColumnName)
                Next


                For i = 0 To Me.dsSelect.Tables.Count - 1
                    colIndex = 1
                    Dim rowOn As Integer = 1

                    testImport.ProgressBar1.Value = 30
                    Application.DoEvents()

                    For c = 0 To Me.dsSelect.Tables.Count - 1
                        excel.Cells(rowOn, colIndex).value = Me.dsSelect.Tables(i).Columns(c).ColumnName
                        colIndex = colIndex + 1
                    Next
                    colIndex = 1
                    rowOn = 2

                    For r = 0 To Me.dsSelect.Tables(i).Rows.Count - 1

                        testImport.ProgressBar1.Value = 45
                        Application.DoEvents()

                        For c = 0 To Me.dsSelect.Tables(i).Columns.Count - 1
                            excel.Cells(rowOn, colIndex).value = Me.dsSelect.Tables(i).Rows(r).Item(c)

                            colIndex = colIndex + 1

                            testImport.ProgressBar1.Value = 60
                            Application.DoEvents()

                        Next
                        rowOn = rowOn + 1
                        colIndex = 1
                    Next
                Next
                wsheet.Columns.AutoFit()

            End With
            'testImport.ProgressBar1.Value = 100
            'Application.DoEvents()
            'testImport.Close()
            SaveFileDialog1.ShowDialog()

[onlyGirl]

Thank you (everyone) who tried to help me.