Results 1 to 22 of 22

Thread: ClosedXML Export Import Datagridview

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,301

    Re: ClosedXML Export Import Datagridview

    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:
    1. Using adapter As New OleDbdataAdapter("SELECT * FROM Connectors order by ID", ServerStatus)
    2.     Dim table As New DataTable
    3.  
    4.     adapter.Fill(table)
    5.     Datagridview1.DataSource = table
    6. End using

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import Datagridview

    Quote Originally Posted by jmcilhinney View Post
    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:
    1. Using adapter As New OleDbdataAdapter("SELECT * FROM Connectors order by ID", ServerStatus)
    2.     Dim table As New DataTable
    3.  
    4.     adapter.Fill(table)
    5.     Datagridview1.DataSource = table
    6. 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.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,301

    Re: ClosedXML Export Import Datagridview

    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.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import 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 ?

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,301

    Re: ClosedXML Export Import Datagridview

    Quote Originally Posted by luckydead View Post
    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?

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import Datagridview

    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
    Last edited by luckydead; Sep 20th, 2020 at 10:48 AM.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,301

    Re: ClosedXML Export Import Datagridview

    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.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import Datagridview

    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?

  10. #10
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: ClosedXML Export Import Datagridview

    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
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import Datagridview

    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

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import Datagridview

    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)
    Last edited by luckydead; Sep 22nd, 2020 at 01:07 PM.

  13. #13
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: ClosedXML Export Import Datagridview

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

    what do you mean by this ?
    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
    Last edited by ChrisE; Sep 21st, 2020 at 11:03 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import Datagridview

    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.

  15. #15
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: ClosedXML Export Import Datagridview

    Quote Originally Posted by luckydead View Post
    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import Datagridview

    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.

  17. #17
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: ClosedXML Export Import Datagridview

    Quote Originally Posted by luckydead View Post
    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import Datagridview

    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.

  19. #19
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: ClosedXML Export Import Datagridview

    Quote Originally Posted by luckydead View Post
    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.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  20. #20

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import Datagridview

    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
    Last edited by luckydead; Sep 22nd, 2020 at 01:08 AM.

  21. #21
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: ClosedXML Export Import Datagridview

    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: ClosedXML Export Import Datagridview

    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.

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