Export data to csv file and on every worksheet in vb.net using StreamWriter

Question

I have a list of queries then every queries I want to export to different worksheets. I use StreamWriter for fast transfer to CSV file. (see sample code below:)

Dim _listOfQrys As New List(Of String)

        _listOfQrys.Add("SELECT * FROM Table1")
        _listOfQrys.Add("SELECT * FROM Table2")
        _listOfQrys.Add("SELECT * FROM Table3")


        For Each _query As String In _listOfQrys

            Dim _dataAdap As New SqlDataAdapter(_query, Constr)
            Dim _dataTbl As New DataTable

            _dataTbl.Clear()
            _dataAdap.Fill(_dataTbl)

            If _dataTbl.Rows.Count > 0 Then

                Dim headers = (From header As DataGridViewColumn In _dataTbl.Columns.Cast(Of DataGridViewColumn)() _
                   Select header.HeaderText).ToArray

                Dim rows = From row As DataGridViewRow In _dataTbl.Rows.Cast(Of DataGridViewRow)() _
                           Where Not row.IsNewRow _
                           Select Array.ConvertAll(row.Cells.Cast(Of DataGridViewCell).ToArray, Function(c) If(c.Value IsNot Nothing, c.Value.ToString, ""))

                Using sw As New IO.StreamWriter("sample.csv")
                    sw.WriteLine(String.Join(",", headers))
                    For Each r In rows
                        sw.WriteLine(String.Join(",", r))
                    Next
                End Using

            End If

        Next

My question is how to export those _ListofQrys to different worksheets?

Any alternatives or other solutions is much appreciated. thanks!

I think you want to save in .xls format not in .csv, because in CSV you can't save in sheets.. — CristiC777, Jan 26 '15 at 15:13

Alex Palmer · Answer 1 · 2015-01-26T15:17:19.997

Simple answer: you can't. Even if Excel opens CSV files, they are NOT Excel files, thus you can't put data in worksheets. You should switch your code to make use of Excel Application objects, in this case you have full control over format and data. Something like this:

Dim wb = New ExcelFile()
For Each dataTable As DataTable In dataSet.Tables
    Dim worksheet = wb.Worksheets.Add(dataTable.TableName)
    dataTable.Rows(0)(0) = "Blahblah"
    worksheet.InsertDataTable(dataTable,
        New InsertDataTableOptions("A1") With {.ColumnHeaders = True})
Next
wb.Save("MyExcel.xls")

Hope it helps.

score 0 · Answer 2 · edited May 23 '17 at 11:50

I think I found something like what you want !

  Sub SaveWorksheetsAsCsv()

  Dim WS As Excel.Worksheet
  Dim SaveToDirectory As String

  Dim CurrentWorkbook As String
  Dim CurrentFormat As Long

  CurrentWorkbook = ThisWorkbook.FullName
  CurrentFormat = ThisWorkbook.FileFormat
  ' Store current details for the workbook
  SaveToDirectory = "H:\test\"
  For Each WS In ThisWorkbook.Worksheets
      Sheets(WS.Name).Copy
      ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV
      ActiveWorkbook.Close savechanges:=False
      ThisWorkbook.Activate
  Next

  Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook,    FileFormat:=CurrentFormat
  Application.DisplayAlerts = True
  ' Temporarily turn alerts off to prevent the user being prompted
  '  about overwriting the original file.

  End Sub

source : https://stackoverflow.com/a/10551860/2269262

Export data to csv file and on every worksheet in vb.net using StreamWriter

2 Answers2