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.
Printable View
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.
Examples on the forum.
Search -> Export Dataset
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.
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?
Code: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
I'm using the code below but it's not capturing the data in the dataset, instead it just selects the header.
Code: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
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
dc.Value isn't an option. It won't let me use it.
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
That didn't work either, I get this nice error message:
Overload resolution failed because no accessible 'Item' accepts this number of arguments.
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... )
nope, nothing shows up. there is no other parameter. :(
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
I found some other code that shows you how to export to excel:
Code: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()
Thank you (everyone) who tried to help me. :wave: