-
Sep 25th, 2022, 01:33 PM
#1
Thread Starter
Addicted Member
[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
-
Sep 26th, 2022, 02:54 AM
#2
Thread Starter
Addicted Member
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
-
Sep 26th, 2022, 03:06 AM
#3
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.
-
Sep 26th, 2022, 03:34 AM
#4
Thread Starter
Addicted Member
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.
-
Sep 26th, 2022, 08:49 AM
#5
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Sep 27th, 2022, 03:11 AM
#6
Thread Starter
Addicted Member
Re: Export to Excel from TabControl Tabs
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|