-
Oct 4th, 2021, 04:10 PM
#1
Thread Starter
Junior Member
Export to Excel From Datagridview
hi, I'm new to Vb.net and I need a little help. I have this Code to Export All Data from Datagridview to Excel and it works well, Problem 1 :
But what I want to do is To export only Specific columns, not all columns from Datagridview (Example I want to Export only Column 3 and 5) Not all Columns.
And Problem 2:
is It is possible to export in an existing template example I have an excel template with Name And ID to export in this existing template not in new.
Thank You.
Code:
' Creating a Excel object.
Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
Dim workbook As Microsoft.Office.Interop.Excel._Workbook = excel.Workbooks.Add(Type.Missing)
Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing
Try
worksheet = workbook.ActiveSheet
'worksheet.Name = "ExportedFromDatGrid" '
worksheet.Name = Me.Text '
Dim cellRowIndex As Integer = 1
Dim cellColumnIndex As Integer = 1
'Loop through each row and read value from each column.
For i As Integer = -1 To dgw.Rows.Count - 1
For j As Integer = 0 To dgw.Columns.Count - 1
' Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
If cellRowIndex = 1 Then
worksheet.Cells(cellRowIndex, cellColumnIndex) = dgw.Columns(j).HeaderText
Else
worksheet.Cells(cellRowIndex, cellColumnIndex) = dgw.Rows(i).Cells(j).Value.ToString()
End If
cellColumnIndex += 1
Next
cellColumnIndex = 1
cellRowIndex += 1
Next
'Getting the location and file name of the excel to save from user.
Dim saveDialog As New SaveFileDialog()
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
saveDialog.FilterIndex = 2
If saveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
workbook.SaveAs(saveDialog.FileName)
MsgBox("Export Successful", vbInformation)
End If
Catch ex As System.Exception
MessageBox.Show(ex.Message) 'e paraqet probleim ne mesazh
Finally
excel.Quit()
workbook = Nothing
excel = Nothing
End Try
-
Oct 5th, 2021, 06:59 AM
#2
Re: Export to Excel From Datagridview
This is simple to export columns in a DataGridView when using the DataSource of the DataGridView with a DataTable using SpreadSheetLight via NuGet.
To get only needed columns, Crate a new DataTable as follows where you replace C1, C2 etc with column names from your DataTable
Code:
Dim view As New DataView(dataTableFromDataGridView)
Dim selected As DataTable = view.ToTable("Selected", False, "C1", "C2")
Simple export code
Code:
Public Shared Sub ExportTable(pFileName As String, pSheetName As String, pDataTable As DataTable, pColumnHeaders As Boolean)
Using doc As New SLDocument()
doc.SelectWorksheet(pSheetName)
doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders)
doc.RenameWorksheet(SLDocument.DefaultFirstSheetName, pSheetName)
doc.SaveAs(pFileName)
End Using
End Sub
Styling can be done via SpreadSheetLight's SLStyle class e.g.
Code:
Using doc As New SLDocument(pFileName)
doc.SelectWorksheet(pSheetName)
doc.ImportDataTable(pStartRow, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders)
Dim style As New SLStyle With {.FormatCode = "MM/dd//yyyy"}
If pDataTable.Columns.Contains("ModifiedDate") Then
doc.SetColumnStyle(pDataTable.Columns("ModifiedDate").Ordinal + 1, style)
End If
doc.Save()
Return True
End Using
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
|