-
Sep 20th, 2020, 08:14 AM
#1
Thread Starter
Addicted Member
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
-
Sep 20th, 2020, 09:07 AM
#2
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:
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
-
Sep 20th, 2020, 09:41 AM
#3
Thread Starter
Addicted Member
Re: ClosedXML Export Import Datagridview
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.
-
Sep 20th, 2020, 10:09 AM
#4
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.
-
Sep 20th, 2020, 10:29 AM
#5
Thread Starter
Addicted Member
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 ?
-
Sep 20th, 2020, 10:32 AM
#6
Re: ClosedXML Export Import Datagridview
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?
-
Sep 20th, 2020, 10:42 AM
#7
Thread Starter
Addicted Member
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.
-
Sep 20th, 2020, 11:06 AM
#8
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.
-
Sep 20th, 2020, 11:25 AM
#9
Thread Starter
Addicted Member
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?
-
Sep 20th, 2020, 02:03 PM
#10
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)
-
Sep 20th, 2020, 02:13 PM
#11
Thread Starter
Addicted Member
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
-
Sep 21st, 2020, 10:18 AM
#12
Thread Starter
Addicted Member
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.
-
Sep 21st, 2020, 10:59 AM
#13
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.
-
Sep 21st, 2020, 11:29 AM
#14
Thread Starter
Addicted Member
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.
-
Sep 21st, 2020, 11:58 AM
#15
Re: ClosedXML Export Import Datagridview
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
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.
-
Sep 21st, 2020, 12:13 PM
#16
Thread Starter
Addicted Member
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.
-
Sep 21st, 2020, 12:17 PM
#17
Re: ClosedXML Export Import Datagridview
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
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.
-
Sep 22nd, 2020, 12:26 AM
#18
Thread Starter
Addicted Member
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.
-
Sep 22nd, 2020, 12:59 AM
#19
Re: ClosedXML Export Import Datagridview
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.
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.
-
Sep 22nd, 2020, 01:03 AM
#20
Thread Starter
Addicted Member
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.
-
Sep 22nd, 2020, 01:19 AM
#21
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.
-
Sep 22nd, 2020, 01:22 AM
#22
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|