CODEWITHSUNDEEP
excelvb.netdatatable
Bart Van Audenhove
Bart Van Audenhove

Reputation: 15

How can I export a DataTable to multiple Excel files?

I have a DataTable in VB.NET with the following data:

DataTable data

Now I want to export the data to Excel using VB.NET but I need an Excel file for each different id in the last column. I tried to do it but I always get 1 Excel file with all the data in it, instead of 8 different files. Can you help?

Upvotes: 0

Views: 555

Answers (3)

G_Hosa_Phat
G_Hosa_Phat

Reputation: 1110

I do something similar in the "daily chores" application I built for myself. I'm writing the DataTable to separate text files, but the basic premise should be the same. Similar to what @NeverHopeless proposed, I've set up a separate method to actually produce the file(s) I need so that I can loop through the DataTable and, for each unique ID, I execute this method. The following is obviously very simplified and would need to be modified to fit your requirements, but the basic elements should all be there:

Dim MyData As DataTable = GetMyData()
Dim tempdossier As String = Now.ToString("yyyyMMddHHmmssffffzz")

For Each Row As DataRow In MyData.Rows
    If Not IsDBNull(Row("dossiersoort")) AndAlso Not String.IsNullOrEmpty(Row("dossiersoort").ToString) Then
        If Row("dossiersoort").ToString <> tempdossier then
            Dim dossierrows() As DataRow

            dossierrows = MyData.Select("dossiersoort = '" & Row("dossiersoort").ToString & "'")
            CreateExcelFile(dossierrows)
        End If

        tempdossier = Row("dossiersoort").ToString
    Else
        tempdossier = Now.ToString("yyyyMMddHHmmssffffzz")
    End If
Next Row

The tempdossier variable is just there to help prevent it from creating duplicate files. I use the "timestamp" value (taken out to the ten-thousandth of a second) as a somewhat reliable way to get a unique value in the case of empty data.

The CreateExcelFile method would basically create an empty Excel file, then loop through the DataRows in your array (which should only be the ones with the same dossiersoort value) to populate the file. From your OP, it sounds like you already know how to actually create/populate the file, so I won't go into that. You can easily loop through the array with a simple For...Next loop using an Integer value (For dossier As Integer = 0 To dossierrows.GetUpperBound(0) or something).

This method could be a Function returning a Boolean value or a File.IO object to ensure that the Excel file was actually created properly, or a simple Sub with the validation happening after it returns execution to the calling method.

Upvotes: 0

NeverHopeless
NeverHopeless

Reputation: 11233

Steps you can follow:

  • Fetch the required data in the DataTable.
  • Find the distinct ids and add them into an array.
  • Iterate through this array.
  • On each iteration, either add a sheet in the Workbook or create a completely new WorkBook at a certain location and use the first sheet. (as per your requirement)
  • Filter the DataTable using the id you find during iteration.
  • Write the filtered data into the sheet.
  • Repeat for all the distinct ids.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460168

You can use EPPlus, it even has a method to export it from a DataTable. ws.Cells["A1"].LoadFromDataTable(dataTable, true);

You get the tables with LINQ:

Dim allTableList As List(Of DataTable) = yourTable.AsEnumerable().
    GroupBy(Function(row) row.Field(Of Int32)("Dossiersort")).
    Select(Function(rowGrp) rowGrp.CopyToDataTable()).
    ToList()

Upvotes: 1

Related Questions