Results 1 to 6 of 6

Thread: [RESOLVED] Export to Excel from TabControl Tabs

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Resolved [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

  2. #2

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: Export to Excel from TabControl Tabs

    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

  3. #3
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Export to Excel from TabControl Tabs

    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.

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: Export to Excel from TabControl Tabs

    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?
    Last edited by luckydead; Sep 26th, 2022 at 06:03 AM.

  5. #5
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Export to Excel from TabControl Tabs

    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

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: Export to Excel from TabControl Tabs

    Edit: fixed
    Last edited by luckydead; Sep 27th, 2022 at 04:44 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width