ClosedXML Export Import Datagridview

Hello all,
I'm having trouble to find a good way to make Export&Import option to my project for Excel without using the microsoft interop. Because when i use interop is slow and in task manager excel always work after this , and make not good use. And i found out that there were ClosedXML that is way better option.

Here is my code when form is loaded what fill up in datagridview

Code:

---

Using con As New OleDbConnection(ServerStatus)
            Using cmd As New OleDbCommand("SELECT * FROM Connectors order by ID", con)
                cmd.Connection = con
                cmd.CommandType = CommandType.Text
                Using sda As New OleDbDataAdapter(cmd)
                    Using dta As New DataTable()
                        sda.Fill(dta)
                        DataGridView1.DataSource = Nothing
                        'Set AutoGenerateColumns False
                        DataGridView1.AutoGenerateColumns = False
                        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                        'DataDisplay.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
                        'DataDisplay.SelectionMode = DataGridViewSelectionMode.FullRowSelect
                        'DataDisplay.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                        DataGridView1.AllowUserToResizeColumns = False
                        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
                        DataGridView1.AllowUserToResizeRows = False
                        'DataDisplay.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
                        'DataDisplay.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                        DataGridView1.AutoResizeColumns()


                        'Set Columns Count
                        DataGridView1.ColumnCount = 6

                        'Add Columns
                        DataGridView1.Columns(0).Name = "ID"
                        DataGridView1.Columns(0).HeaderText = "ID"
                        DataGridView1.Columns(0).DataPropertyName = "ID"

                        DataGridView1.Columns(1).Name = "cName"
                        DataGridView1.Columns(1).HeaderText = "Name"
                        DataGridView1.Columns(1).DataPropertyName = "cName"

                        DataGridView1.Columns(2).Name = "cYazaki"
                        DataGridView1.Columns(2).HeaderText = "Yazaki"
                        DataGridView1.Columns(2).DataPropertyName = "cYazaki"

                        DataGridView1.Columns(3).Name = "cSupplier"
                        DataGridView1.Columns(3).HeaderText = "Supplier"
                        DataGridView1.Columns(3).DataPropertyName = "cSupplier"

                        DataGridView1.Columns(4).Name = "cStore"
                        DataGridView1.Columns(4).HeaderText = "Store"
                        DataGridView1.Columns(4).DataPropertyName = "cStore"

                        DataGridView1.Columns(5).Name = "cCount"
                        DataGridView1.Columns(5).HeaderText = "Count"
                        DataGridView1.Columns(5).DataPropertyName = "cCount"

                        'Add the Button Column.
                        Dim buttonColumn As DataGridViewButtonColumn = New DataGridViewButtonColumn()
                        buttonColumn.Name = "cDelete"
                        buttonColumn.HeaderText = "Delete"
                        buttonColumn.Text = "Delete"
                        buttonColumn.FlatStyle = FlatStyle.Flat
                        buttonColumn.CellTemplate.Style.BackColor = System.Drawing.Color.White
                        buttonColumn.UseColumnTextForButtonValue = True
                        DataGridView1.Columns.Insert(6, buttonColumn)
                        'End

                        'txtName.Text = dta.Rows(0).Item(1).ToString
                        'txtYazaki.Text = dta.Rows(0).Item(2).ToString
                        'txtSupp.Text = dta.Rows(0).Item(3).ToString
                        'txtStore.Text = dta.Rows(0).Item(4).ToString
                        'txtCount.Text = dta.Rows(0).Item(5).ToString
                        'txtID.Text = dta.Rows(0).Item(0).ToString

                        DataGridView1.DataSource = dta

                    End Using
                End Using
            End Using
        End Using

---

As you see here i have a custom button created inside the datagridview, but i do not want when make Export to be saved this also in the file (without it).
I have been looking for guide on internet but nowhere found anything that may help me, thats why i try to create topic here if someone can assist me to make it.
I hope someone can assist me with creating this.
Thanks

First things first, let's fix all those Using blocks. It's pointless creating a connection, command and data adapter separately when you can just create the data adapter and have the rest done for you. As for the DataTable, why would you think that disposing it was a good idea? The vast majority of the rest of that code should be discarded and the equivalent done in the designer.

vb.net Code:

Using adapter As New OleDbdataAdapter("SELECT * FROM Connectors order by ID", ServerStatus)
    Dim table As New DataTable
 
    adapter.Fill(table)
    Datagridview1.DataSource = table
End using

Quote:

---

Originally Posted by jmcilhinney

First things first, let's fix all those Using blocks. It's pointless creating a connection, command and data adapter separately when you can just create the data adapter and have the rest done for you. As for the DataTable, why would you think that disposing it was a good idea? The vast majority of the rest of that code should be discarded and the equivalent done in the designer.

vb.net Code:

Using adapter As New OleDbdataAdapter("SELECT * FROM Connectors order by ID", ServerStatus)
    Dim table As New DataTable
 
    adapter.Fill(table)
    Datagridview1.DataSource = table
End using

---

Thank you for the good information i didn't test it like that now i see that this way is much cleaner as you explaned. Thank you for the Using method.
But how can i make it to be exported and import option to excel. I'm still searching for some information but unlucky.

Also you mean this lines to be maded in design ?

Code:

---

'Set Columns Count
            DataGridView1.ColumnCount = 6

            'Add Columns
            DataGridView1.Columns(0).Name = "ID"
            DataGridView1.Columns(0).HeaderText = "ID"
            DataGridView1.Columns(0).DataPropertyName = "ID"

            DataGridView1.Columns(1).Name = "cName"
            DataGridView1.Columns(1).HeaderText = "Name"
            DataGridView1.Columns(1).DataPropertyName = "cName"

            DataGridView1.Columns(2).Name = "cYazaki"
            DataGridView1.Columns(2).HeaderText = "Yazaki"
            DataGridView1.Columns(2).DataPropertyName = "cYazaki"

            DataGridView1.Columns(3).Name = "cSupplier"
            DataGridView1.Columns(3).HeaderText = "Supplier"
            DataGridView1.Columns(3).DataPropertyName = "cSupplier"

            DataGridView1.Columns(4).Name = "cStore"
            DataGridView1.Columns(4).HeaderText = "Store"
            DataGridView1.Columns(4).DataPropertyName = "cStore"

            DataGridView1.Columns(5).Name = "cCount"
            DataGridView1.Columns(5).HeaderText = "Count"
            DataGridView1.Columns(5).DataPropertyName = "cCount"

            'Add the Button Column.
            Dim buttonColumn As DataGridViewButtonColumn = New DataGridViewButtonColumn()
            buttonColumn.Name = "cDelete"
            buttonColumn.HeaderText = "Delete"
            buttonColumn.Text = "Delete"
            buttonColumn.FlatStyle = FlatStyle.Flat
            buttonColumn.CellTemplate.Style.BackColor = System.Drawing.Color.White
            buttonColumn.UseColumnTextForButtonValue = True
            DataGridView1.Columns.Insert(6, buttonColumn)
            'End

---

But if i make the headers by design mode how can i add custom button for delete then ?
And i do this because my table Connectors has this informations: ID,Name,Yazaki,Supplier,Store,Count,Image . Like this above i choose what to display in the datagridview only.

You can add any columns you want in the designer of whatever type you want in whatever order you want. Either just add the button column and let the grid create the rest when you bind or else create them all in the designer. If you add just the button column then you'll need to change the order at run time. To learn how to do that, follow the CodeBank link in my signature below and check out my thread on adding a combobox column to a DataGridView.

okay i got the idea what you mean will try it like this in different form window.
So now leaved only to create button for export the datagridview information to excel without interop library, how can this work with closedxml ?

Quote:

---

Originally Posted by luckydead

how can this work with closedxml ?

---

What have you tried? Presumably you have read the documentation and not just assumed that it's too hard. Where exactly are you stuck?

i have installed it via NuGet in project>
But i cannot find any example how to do it with datagridview to export to excel file, searched youtube google so much threads i find alot for asp.net but not vb

the only example i found was this:

Code:

---

'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")
        wb.SaveAs(folderPath & Convert.ToString("DataGridViewExport.xlsx"))
    End Using

---

but got error when click the button on this line:

Code:

---

For Each column As DataGridViewColumn In DataGridView1.Columns
            dt.Columns.Add(column.HeaderText, column.ValueType)
        Next

---

The DataGridView isn't really relevant. You can get data out of a DataGridView in whatever way you want, so you can use whatever starting point you want to use ClosedXML to get data into the Excel file. That said, a DataGridView is tabular and so is an Excel sheet, so it shouldn't be an issue to use a loop or two nested loops to shift data between cells in the grid to cells in the sheet.

i'm sorry i cannot catch up with you, because im beginner can you give details or example how to start or to look like?

I would advice to use the clipboard : copy the datagridview to clipboard and then past it to the excel sheet : https://stackoverflow.com/questions/...e-to-clipboard

i think this will be bad idea if it has more than 1000 rows to copy ?
but still some example with closedxml would be better i had readed that its much faster and better than interop , because there excel is not closed and com object are not released good, also to kill proccess is not good option. that's why i need some help with other method for export without interop

So i found a one way to do it, but i have a problem.
How to make the function exactly to record to excel what shows in datagridview. Because currectly now it displays everything from the db.
i would like to record only this fields that shows in datagridview:

Without the Delete button.
Another problem that i found out with the code:
If i make one time save, then i click again to save i got error on this: It seems that datatable is already used and cannot be second time saved.

Code:

---

Catch ex As Exception
            MessageBox.Show("Couldn't create Excel file.\r Exception: " + ex.Message)

---

you could use a Schema.ini to Export the DGV to a Excelsheet

what do you mean by this ?

Quote:

---

Because currectly now it displays everything from the db.

---

is the data in a Database, and you are loading this to a Datagridview
if so what Database are you using

In the project you have everything that i use for test, can simply download and see it. Also i had edited my previous post with another problem that i found out.

Quote:

---

Originally Posted by luckydead

In the project you have everything that i use for test, can simply download and see it. Also i had edited my previous post with another problem that i found out.

---

sorry but I don't download Files from people I don't know

believe me i woudn't bother doing stupid stuff when i need help, also you can scan it with antivirus to checkyourself i use Nod32 payed so you can be sure. Project is Form1 + library inside + accsess db and code. Open check it and see where exactly fail my code and how to fix it. That's all.

Quote:

---

Originally Posted by luckydead

believe me i woudn't bother doing stupid stuff when i need help, also you can scan it with antivirus to checkyourself i use Nod32 payed so you can be sure. Project is Form1 + library inside + accsess db and code. Open check it and see where exactly fail my code and how to fix it. That's all.

---

well that was helpful Information... I mean that you are using a Access DB
here one way to filter and export directly to a Excel .xlsx

Code:

---

Option Strict On
Imports System.Data.OleDb

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'for .xlsx
        Dim Filename As String = "E:\TestFolder\ExcelTest\abcExcel.xlsx"
        Dim SheetName As String = txtSheetName.Text

        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=E:\NwindTest.mdb")
        Dim sSQL As String = "Select * Into [mySheet]"
        Dim sWhere As String = Nothing

        If txtCountry.Text <> Nothing Then
            'sWhere &= "And (Country Like '" & txtCountry.Text & "%') "
            sWhere &= "And (Country ='" & txtCountry.Text & "') "
        End If

        If txtCompanyName.Text <> Nothing Then
            sWhere &= "And (CompanyName Like '" & txtCompanyName.Text & "%') "
        End If

        If sWhere <> Nothing Then
            sWhere = "Where " & sWhere.Substring(4)
        End If

        sSQL &= "  In '" & Filename & "' 'Excel 12.0 XML;' From Suppliers "
        sSQL &= sWhere & "Order by CompanyName"

        MsgBox(sSQL)
       
        con.Open()
        ExecuteSQL(con, sSql)
        con.Close()
        con = Nothing
    End Sub

   
    Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                  ByVal sSQL As String, _
                                  Optional ByRef ErrMessage As String = Nothing, _
                                  Optional ByVal TransAction As  _
                                  OleDb.OleDbTransaction = Nothing) As Integer
        ErrMessage = Nothing
        Try
            Dim Result As Integer = 0
            Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                Result = Cmd.ExecuteNonQuery
            End Using
            Return Result
        Catch ex As Exception
            ErrMessage = ex.Message
            Return 0
        End Try
    End Function

---

would be better if you check out the code, using openxml method and i have 2 problems only:
1- cant click button second time to save again the datagridview, reason it uses the datatable somehow and not release it
2- cant manage it to make it to record what is showed in datagridview.

Quote:

---

Originally Posted by luckydead

would be better if you check out the code, using openxml method and i have 2 problems only:
1- cant click button second time to save again the datagridview, reason it uses the datatable somehow and not release it
2- cant manage it to make it to record what is showed in datagridview.

---

the way I filter the Data from the Database Table and send it directly to Excel is more efficient to me.

I can filter as many times I want and send the results to a new Sheet in the Excel Workbook, so give
me a good reason why I should change to your method.

i think i found out solution to my 2 problems:
let me share it maybe you can say something is it okay or not in the code as mistake;

1.Button load the db to datagridview

Code:

---

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        DataGridView1.DataSource = GetThatDB()
    End Sub

---

Code:

---

Private Function GetThatDB()
        Dim table As New DataTable
        Using adapter As New OleDbDataAdapter("SELECT ID,cName,cYazaki,cSupplier,cStore,cCount FROM Connectors order by ID", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\MSData2.accdb")
            adapter.Fill(table)
            DataGridView1.DataSource = Nothing
            DataGridView1.AutoGenerateColumns = False
            DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            DataGridView1.AllowUserToResizeColumns = False
            DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
            DataGridView1.AllowUserToResizeRows = False
            DataGridView1.AutoResizeColumns()

            'Set Columns Count
            DataGridView1.ColumnCount = 6

            ''Add Columns
            DataGridView1.Columns(0).Name = "ID"
            DataGridView1.Columns(0).HeaderText = "ID"
            DataGridView1.Columns(0).DataPropertyName = "ID"

            DataGridView1.Columns(1).Name = "cName"
            DataGridView1.Columns(1).HeaderText = "Name"
            DataGridView1.Columns(1).DataPropertyName = "cName"

            DataGridView1.Columns(2).Name = "cYazaki"
            DataGridView1.Columns(2).HeaderText = "Yazaki"
            DataGridView1.Columns(2).DataPropertyName = "cYazaki"

            DataGridView1.Columns(3).Name = "cSupplier"
            DataGridView1.Columns(3).HeaderText = "Supplier"
            DataGridView1.Columns(3).DataPropertyName = "cSupplier"

            DataGridView1.Columns(4).Name = "cStore"
            DataGridView1.Columns(4).HeaderText = "Store"
            DataGridView1.Columns(4).DataPropertyName = "cStore"

            DataGridView1.Columns(5).Name = "cCount"
            DataGridView1.Columns(5).HeaderText = "Count"
            DataGridView1.Columns(5).DataPropertyName = "cCount"

            'Add the Button Column.
            Dim buttonColumn As DataGridViewButtonColumn = New DataGridViewButtonColumn()
            buttonColumn.Name = "cDelete"
            buttonColumn.HeaderText = "Delete"
            buttonColumn.Text = "Delete"
            buttonColumn.FlatStyle = FlatStyle.Flat
            buttonColumn.CellTemplate.Style.BackColor = System.Drawing.Color.White
            buttonColumn.UseColumnTextForButtonValue = True
            DataGridView1.Columns.Insert(6, buttonColumn)
            'End
            DataGridView1.DataSource = table
        End Using
        Return table
    End Function

---

This seems to be okay as result. Working and when i click again save/save/save as many times datatable is not used anymore it is released well.

And button save to excel:

Code:

---

Private Sub btnCreateExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnCreateExcel.Click
        ' Prompt the user to enter a path/filename to save an example Excel file to
        SaveFileDialog1.Title = "Save Export"
        SaveFileDialog1.FileName = "Export.xlsx"
        SaveFileDialog1.Filter = "Excel 2007 Files (*.xlsx)|*.xlsx|Excel 2003 Files (*.xls)|*.xls|All files (*.*)|*.*"
        SaveFileDialog1.FilterIndex = 1
        SaveFileDialog1.RestoreDirectory = True
        SaveFileDialog1.OverwritePrompt = False

        '  If the user hit Cancel, then abort!
        If (SaveFileDialog1.ShowDialog() <> DialogResult.OK) Then
            Return
        End If

        Dim targetFilename As String = SaveFileDialog1.FileName

        '  Step 1: Create a DataSet, and put some sample data in it
        Dim ds As New DataTable
        ds = GetThatDB()
        'Dim ds as new DataSet
        'ds = CreateSampleData

        '  Step 2: Create the Excel file
        Try
            CreateExcelFile.CreateExcelDocument(ds, targetFilename)
        Catch ex As Exception
            MessageBox.Show("Couldn't create Excel file.\r Exception: " + ex.Message)
            Return
        Finally
            MessageBox.Show("Export Successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try

        '  Step 3:  Let's open our new Excel file and shut down this application.
        Dim result As DialogResult = MessageBox.Show("Do you want to open file now?", "Visual WMS", MessageBoxButtons.YesNo)
        If (result = DialogResult.Yes) Then
            Dim p As New Process
            p.StartInfo = New ProcessStartInfo(targetFilename)
            p.Start()
        Else
            Return
        End If


        ' Me.Close()

    End Sub

---

The openxml:
DocumentFormat.OpenXml.dll
The excel Class :

Code:

---

Imports System.Reflection
Imports System.Collections.Generic
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports DocumentFormat.OpenXml

Public Class CreateExcelFile
    Public Shared Function CreateExcelDocument(Of T)(ByVal list As List(Of T), ByVal xlsxFilePath As String) As Boolean
        Dim ds As New DataSet()
        ds.Tables.Add(ListToDataTable(list))

        Return CreateExcelDocument(ds, xlsxFilePath)
    End Function


    ' This function is adapated from: http://www.codeguru.com/forum/showthread.php?t=450171
    ' My thanks to Carl Quirion, for making it "nullable-friendly".
    Public Shared Function ListToDataTable(Of T)(ByVal list As List(Of T)) As DataTable

        Dim dt As New DataTable
        Dim row As DataRow
        For Each info As System.Reflection.PropertyInfo In list.GetType().GetProperties()
            dt.Columns.Add(New DataColumn(info.Name, GetNullableType(info.PropertyType)))
        Next

        For Each tValue As T In list

            row = dt.NewRow()
            For Each info As System.Reflection.PropertyInfo In list.GetType().GetProperties()

                If Not IsNullableType(info.PropertyType) Then
                    row(info.Name) = info.GetValue(tValue, Nothing)
                Else
                    row(info.Name) = info.GetValue(tValue, Nothing)
                End If
            Next
            dt.Rows.Add(row)
        Next
        Return dt
    End Function


    Public Shared Function GetNullableType(ByVal t As Type) As Type

        Dim returnType As Type = t

        If (t.IsGenericType Or t.GetGenericTypeDefinition() Is GetType(Nullable(Of ))) Then
            returnType = Nullable.GetUnderlyingType(t)
        End If

        Return returnType

    End Function


    Public Shared Function IsNullableType(ByVal type As Type) As Boolean

        Return (type Is GetType(String) Or
                type.IsArray Or
                (type.IsGenericType And type.GetGenericTypeDefinition() Is GetType(Nullable(Of ))))
    End Function

    Public Shared Function CreateExcelDocument(ByVal dt As DataTable, ByVal xlsxFilePath As String) As Boolean

        Dim ds As New DataSet
        ds.Tables.Add(dt)

        Return CreateExcelDocument(ds, xlsxFilePath)
    End Function

    Public Shared Function CreateExcelDocument(ByVal ds As DataSet, ByVal excelFilename As String) As Boolean
        Try
            Using document As SpreadsheetDocument = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook)

                Dim workbook As WorkbookPart = document.AddWorkbookPart

                '                document.AddWorkbookPart()
                document.WorkbookPart.Workbook = New DocumentFormat.OpenXml.Spreadsheet.Workbook()

                '  My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
                document.WorkbookPart.Workbook.Append(New BookViews(New WorkbookView()))

                '  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
                Dim workbookStylesPart As WorkbookStylesPart = document.WorkbookPart.AddNewPart(Of WorkbookStylesPart)("rIdStyles")

                Dim stylesheet As New Stylesheet
                workbookStylesPart.Stylesheet = stylesheet
                workbookStylesPart.Stylesheet.Save()

                '                Dim sp As WorkbookStylesPart = document.WorkbookPart.AddNewPart(Of WorkbookStylesPart)()

                CreateParts(ds, document)

            End Using
            Trace.WriteLine("Successfully created: " + excelFilename)
            Return True

        Catch ex As Exception
            Trace.WriteLine("Failed, exception thrown: " + ex.Message)
            Return False
        End Try

    End Function

    Private Shared Sub CreateParts(ByVal ds As DataSet, ByVal spreadsheet As SpreadsheetDocument)

        '  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
        Dim worksheetNumber As UInt64 = 1
        For Each dt As DataTable In ds.Tables
            '  For each worksheet you want to create
            Dim workSheetID As String = "rId" + worksheetNumber.ToString()
            Dim worksheetName As String = dt.TableName

            Dim newWorksheetPart As WorksheetPart = spreadsheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
            newWorksheetPart.Worksheet = New DocumentFormat.OpenXml.Spreadsheet.Worksheet()

            '  If you want to define the Column Widths, you need to do this *before* appending the SheetData
            '  http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/1d93eca8-2949-4d12-8dd9-15cc24128b10/
            '
            '  If you want to calculate the column width, it's not easy.  Have a read of this article:
            '  http://polymathprogrammer.com/2010/01/11/custom-column-widths-in-excel-open-xml/
            '

            Dim columnWidthSize As Int32 = 20    ' Replace the following line with your desired Column Width for column # col
            Dim columns As New Columns

            For colInx As Integer = 0 To dt.Columns.Count
                Dim column As Column = CustomColumnWidth(colInx, columnWidthSize)
                columns.Append(column)
            Next
            newWorksheetPart.Worksheet.Append(columns)

            ' create sheet data
            newWorksheetPart.Worksheet.AppendChild(New DocumentFormat.OpenXml.Spreadsheet.SheetData())

            ' save worksheet
            WriteDataTableToExcelWorksheet(dt, newWorksheetPart)
            newWorksheetPart.Worksheet.Save()

            ' create the worksheet to workbook relation
            If (worksheetNumber = 1) Then
                spreadsheet.WorkbookPart.Workbook.AppendChild(New DocumentFormat.OpenXml.Spreadsheet.Sheets())
            End If

            Dim sheet As DocumentFormat.OpenXml.Spreadsheet.Sheet = New DocumentFormat.OpenXml.Spreadsheet.Sheet
            sheet.Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart)
            sheet.SheetId = worksheetNumber
            sheet.Name = dt.TableName
            '            Sheets.Append(sheet)

            spreadsheet.WorkbookPart.Workbook.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.Sheets).Append(sheet)
            ' AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
        Next
    End Sub

    Private Shared Sub WriteDataTableToExcelWorksheet(ByVal dt As DataTable, ByVal worksheetPart As WorksheetPart)

        Dim worksheet As Worksheet = worksheetPart.Worksheet
        Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()

        Dim cellValue As String = ""

        '  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
        '
        '  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
        '  cells of data, we'll know if to write Text values or Numeric cell values.
        Dim numberOfColumns As Integer = dt.Columns.Count
        Dim IsNumericColumn(numberOfColumns) As Boolean

        Dim excelColumnNames([numberOfColumns]) As String

        For n As Integer = 0 To numberOfColumns
            excelColumnNames(numberOfColumns) = GetExcelColumnName(n)
        Next n

        '
        '  Create the Header row in our Excel Worksheet
        '
        Dim rowIndex As UInt32 = 1

        Dim headerRow As Row = New Row
        headerRow.RowIndex = rowIndex            ' add a row at the top of spreadsheet
        sheetData.Append(headerRow)

        For colInx As Integer = 0 To numberOfColumns - 1
            Dim col As DataColumn = dt.Columns(colInx)
            AppendTextCell(excelColumnNames(colInx) + "1", col.ColumnName, headerRow)
            IsNumericColumn(colInx) = (col.DataType.FullName = "System.Decimal") Or (col.DataType.FullName = "System.Int32")
        Next

        '
        '  Now, step through each row of data in our DataTable...
        '
        Dim cellNumericValue As Double = 0

        For Each dr As DataRow In dt.Rows
            ' ...create a new row, and append a set of this row's data to it.
            rowIndex = rowIndex + 1
            Dim newExcelRow As New Row
            newExcelRow.RowIndex = rowIndex        '  add a row at the top of spreadsheet
            sheetData.Append(newExcelRow)

            For colInx As Integer = 0 To numberOfColumns - 1
                cellValue = dr.ItemArray(colInx).ToString()

                ' Create cell with data
                If (IsNumericColumn(colInx)) Then
                    '  For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
                    '  If this numeric value is NULL, then don't write anything to the Excel file.
                    cellNumericValue = 0
                    If (Double.TryParse(cellValue, cellNumericValue)) Then
                        cellValue = cellNumericValue.ToString()
                        AppendNumericCell(excelColumnNames(colInx) + rowIndex.ToString(), cellValue, newExcelRow)
                    End If
                Else
                    '  For text cells, just write the input data straight out to the Excel file.
                    AppendTextCell(excelColumnNames(colInx) + rowIndex.ToString(), cellValue, newExcelRow)
                End If
            Next

        Next

    End Sub

    Private Shared Function CustomColumnWidth(ByVal columnIndex As Integer, ByVal columnWidth As Double) As Column
        ' This creates a Column variable for a zero-based column-index (eg 0 = Excel Column A), with a particular column width.
        Dim column As New Column
        column.Min = columnIndex + 1
        column.Max = columnIndex + 1
        column.Width = columnWidth
        column.CustomWidth = True
        Return column
    End Function

    Public Shared Sub AppendTextCell(ByVal cellReference As String, ByVal cellStringValue As String, ByVal excelRow As Row)
        '/  Add a new Excel Cell to our Row
        Dim cell As New Cell
        cell.CellReference = cellReference
        cell.DataType = CellValues.String

        Dim cellValue As New CellValue
        cellValue.Text = cellStringValue

        cell.Append(cellValue)

        excelRow.Append(cell)
    End Sub

    Public Shared Sub AppendNumericCell(ByVal cellReference As String, ByVal cellStringValue As String, ByVal excelRow As Row)
        '/  Add a new Excel Cell to our Row
        Dim cell As New Cell
        cell.CellReference = cellReference
        cell.DataType = CellValues.Number

        Dim cellValue As New CellValue
        cellValue.Text = cellStringValue

        cell.Append(cellValue)

        excelRow.Append(cell)
    End Sub

    Public Shared Function GetExcelColumnName(ByVal columnIndex As Integer) As String
        If (columnIndex < 26) Then
            Return Chr(Asc("A") + columnIndex)
        End If

        Dim firstChar As Char,
            secondChar As Char

        firstChar = Chr(Asc("A") + (columnIndex \ 26) - 1)
        secondChar = Chr(Asc("A") + (columnIndex Mod 26))

        Return firstChar + secondChar
    End Function

End Class

---

I think you are making this far to complcated

create a new Project and try this....
it will save whatever is in the DGV to a CSV File that you can open with Excel,
or execute the next Part also and it will create a Excel xlsx Workbook from that csv

add a Button and a DGV to a Form
the sample Data is 30000 rows with 6 columns

Code:

---

Option Strict On

Public Class Form1


    Private mRandomClass As New Random()
    Private dtb As New System.Data.DataTable

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        'write sample Data to csv
        Dim headers = (From header As DataGridViewColumn In _
                  DataGridView1.Columns.Cast(Of DataGridViewColumn)() _
                    Select header.HeaderText).ToArray

        Dim rows = From row As DataGridViewRow In _
                  DataGridView1.Rows.Cast(Of DataGridViewRow)() _
                  Where Not row.IsNewRow _
                  Select Array.ConvertAll(row.Cells.Cast(Of DataGridViewCell).ToArray, _
                            Function(c) If(c.Value IsNot Nothing, c.Value.ToString, ""))

        Using sw As New IO.StreamWriter("E:\TestFolder\ExcelTest\XcsvTest.csv")
            sw.WriteLine(String.Join(";", headers))
            For Each r In rows
                sw.WriteLine(String.Join(";", r))
            Next
        End Using
        'now write csv to new Excelworkbook
        Dim Con As String
        Con = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=E:\TestFolder\ExcelTest\myExcel.xlsx;Extended Properties=""Excel 12.0 Xml"""

        Dim ExcelCon As New System.Data.OleDb.OleDbConnection(Con)
        ExcelCon.Open()
        Dim SQLString As String = "SELECT * INTO [mySheetName] FROM [Text;DATABASE=E:\TestFolder\ExcelTest].[XcsvTest.csv]"
        Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLString, ExcelCon)
        ExcelCommand.ExecuteNonQuery()
        ExcelCon.Close()
    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'Load your Table with 300000 rows and 6 columns ...
        'with some sample Data
        dtb.Columns.Add("Column1", GetType(Integer))
        dtb.Columns.Add("Column2", GetType(Double))
        dtb.Columns.Add("Column3", GetType(Date))
        dtb.Columns.Add("Column4", GetType(Decimal))
        dtb.Columns.Add("Column5", GetType(Integer))
        dtb.Columns.Add("Column6", GetType(Integer))

        For i As Integer = 0 To 30000
            Dim Zahl As Double = mRandomClass.Next(100, 10000000)
            Dim Datum As Date = Date.Now.AddSeconds(Convert.ToDouble(mRandomClass.Next(1, 1000000)))
            Dim Geld As Decimal = mRandomClass.Next(1, 100000000)
            dtb.LoadDataRow(New Object() {1 + i, _
                                                  Zahl.ToString("#,##0.00"), _
                                                  Datum.ToString("dd.MM.yyyy HH:mm:ss"), _
                                                  Geld, _
                                                  mRandomClass.Next, _
                                                  mRandomClass.Next}, _
                                              True)
        Next
        DataGridView1.DataSource = dtb
    End Sub
End Class

---

thanks for the other option, but mine doesnt seems to complicated and it is generated very fast no memory over or cpu when export.
But would appriciate if you can check my code and tell me if i make mistakes somewhere or how must be better would appriciate it.