[RESOLVED] Export to Excel from TabControl Tabs

How can i export to excel from datagridview from TabControl Tabs and save all tabs names as sheet name and fill the information from all tabs in one excel file?
I prefer ClosedXML library but if you prefer other option i'm open for ideas how can be made.

https://ibb.co/48rNXCJ

So on top i have the tabs names (this will be added as SheetName)
After that i want in each sheet to record the information in two columns
https://ibb.co/jJ7NShs

Code:

---

Protected Sub btExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim wb As XLWorkbook = New XLWorkbook()
    Dim gvExcel As GridView() = New GridView() {GridView1, GridView2, GridView3, GridView4, GridView5, GridView6}
    Dim name As String() = New String() {"Name1", "Name2", "Name3", "Name4", "Name5", "Name6"}

    For i As Integer = 0 To gvExcel.Length - 1

        If gvExcel(i).Visible Then
            gvExcel(i).AllowPaging = False
            gvExcel(i).DataBind()
            Dim dt As DataTable = New DataTable(name(i).ToString())

            For z As Integer = 0 To gvExcel(i).Columns.Count - 1
                dt.Columns.Add(gvExcel(i).Columns(z).HeaderText)
            Next

            For Each row As GridViewRow In gvExcel(i).Rows
                dt.Rows.Add()

                For c As Integer = 0 To row.Cells.Count - 1
                    dt.Rows(dt.Rows.Count - 1)(c) = row.Cells(c).Text
                Next
            Next

            wb.Worksheets.Add(dt)
            gvExcel(i).AllowPaging = True
        End If
    Next

    Response.Clear()
    Response.Buffer = True
    Response.Charset = ""
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    Response.AddHeader("content-disposition", "attachment;filename=Workbook_Name.xlsx")

    Using MyMemoryStream As MemoryStream = New MemoryStream()
        wb.SaveAs(MyMemoryStream)
        MyMemoryStream.WriteTo(Response.OutputStream)
        Response.Flush()
        Response.[End]()
    End Using
End Sub

---

or

Code:

---

Private Sub btnExportExcel_Click(sender As Object, e As EventArgs) Handles btnExportExcel.Click
    'Creating DataTable.
    Dim dt As New DataTable()
 
    'Adding the Columns.
    For Each column As DataGridViewColumn In dataGridView1.Columns
        dt.Columns.Add(column.HeaderText, column.ValueType)
    Next
 
    'Adding the Rows.
    For Each row As DataGridViewRow In dataGridView1.Rows
        dt.Rows.Add()
        For Each cell As DataGridViewCell In row.Cells
            dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString()
        Next
    Next
 
    'Exporting to Excel.
    Dim folderPath As String = "C:\Excel\"
    If Not Directory.Exists(folderPath) Then
        Directory.CreateDirectory(folderPath)
    End If
    Using wb As New XLWorkbook()
        wb.Worksheets.Add(dt, "Customers")
 
        'Set the color of Header Row.
        'A resembles First Column while C resembles Third column.
        wb.Worksheet(1).Cells("A1:C1").Style.Fill.BackgroundColor = XLColor.DarkGreen
        For i As Integer = 1 To dt.Rows.Count
 
            'A resembles First Column while C resembles Third column.
            'Header row is at Position 1 and hence First row starts from Index 2.
            Dim cellRange As String = String.Format("A{0}:C{0}", i + 1)
 
            If i Mod 2 <> 0 Then
                wb.Worksheet(1).Cells(cellRange).Style.Fill.BackgroundColor = XLColor.GreenYellow
            Else
                wb.Worksheet(1).Cells(cellRange).Style.Fill.BackgroundColor = XLColor.Yellow
            End If
        Next
        'Adjust widths of Columns.
        wb.Worksheet(1).Columns().AdjustToContents()
 
        'Save the Excel file.
        wb.SaveAs(folderPath & "DataGridViewExport.xlsx")
    End Using
End Sub

---

in code receive error in header

Code:

---

For Each tp As TabPage In TabControl1.TabPages
            For Each ctl As Control In tp.Controls
                If TypeOf ctl Is DataGridView Then
                    Dim dgv As DataGridView = DirectCast(ctl, DataGridView)
                    'ExportDataGrid(My.Application.Info.DirectoryPath & "\Lines2.txt", dgv)
                    'Creating DataTable.
                    Dim dt As New DataTable()

                    'Adding the Columns.
                    For Each column As DataGridViewColumn In dgv.Columns
                        dt.Columns.Add(column.HeaderText, column.ValueType)
                    Next

                    'Adding the Rows.
                    For Each row As DataGridViewRow In dgv.Rows
                        dt.Rows.Add()
                        For Each cell As DataGridViewCell In row.Cells
                            dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString()
                        Next
                    Next

                    'Exporting to Excel.
                    'Dim folderPath As String = My.Application.Info.DirectoryPath
                    'If Not Directory.Exists(folderPath) Then
                    '    Directory.CreateDirectory(folderPath)
                    'End If
                    Using wb As New XLWorkbook()
                        wb.Worksheets.Add(dt, "Customers")

                        'Set the color of Header Row.
                        'A resembles First Column while C resembles Third column.
                        wb.Worksheet(1).Cells("A1:C1").Style.Fill.BackgroundColor = XLColor.DarkGreen
                        For i As Integer = 1 To dt.Rows.Count

                            'A resembles First Column while C resembles Third column.
                            'Header row is at Position 1 and hence First row starts from Index 2.
                            Dim cellRange As String = String.Format("A{0}:C{0}", i + 1)

                            If i Mod 2 <> 0 Then
                                wb.Worksheet(1).Cells(cellRange).Style.Fill.BackgroundColor = XLColor.GreenYellow
                            Else
                                wb.Worksheet(1).Cells(cellRange).Style.Fill.BackgroundColor = XLColor.Yellow
                            End If
                        Next
                        'Adjust widths of Columns.
                        wb.Worksheet(1).Columns().AdjustToContents()

                        'Save the Excel file.
                        wb.SaveAs(My.Application.Info.DirectoryPath & "\DataGridViewExport.xlsx")
                    End Using
                End If
            Next
        Next

---

I am not sure what you're asking. Perhaps you could explain it another way? However, looking at your code I noticed a few minor issues:

Code:

---

    Dim gvExcel As GridView() = New GridView() {GridView1, GridView2, GridView3, GridView4, GridView5, GridView6}

---

I can't test it atm, but I think this can be shortened to:

Code:

---

    Dim gvExcel As GridView()(GridView1, GridView2, GridView3, GridView4, GridView5, GridView6})

---

Also, you might want to use lists instead of arrays:

Code:

---

Dim name As New List(Of String) ({"Name1", "Name2", "Name3", "Name4", "Name5", "Name6"})

---

Also:
1. Some For loops can be probably be replaced using the ForEach method for arrays and lists.
2. I wouldn't recommend hard coded paths or creating folders in the system's root directory.
3. I am not a huge fan of having massive amounts of code in an event handler. It would be better to use a separate procedure.

i got stuck with this how to fetch the datagridview from each tabpage and export it, cant figure out a way
someone can help in this forum eh?

You’ll have problems accessing controls on inactive tab pages. They need to be selected.
To refer to a TextBox (at index 0) in your SelectedTabPage, cast Controls(0) as a TextBox

Edit: fixed