-
Jan 4th, 2018, 08:47 PM
#1
DataGridView unbound to either Excel or Text
Over the years developers will ask, how do I export a unbound (meaning the DataGridView is populated via the row collection) DataGridView to Excel or comma-delimited file.
The following MSDN code sample I wrote shows how to export a unbound DataGridView to Excel without Excel installed by using a library called SpreadSheetLight or to a comma-delimited file by converting the DataGridView rows and cells to a DataTable then using LINQ to create a comma-delimited string array which is written to a csv file.
The code has been setup so that the excel operations reside in a class project while the methods to interact with the DataGridView are in another class project.
If interested, I did a C# MSDN code sample which does the same as the above with the addition of using Gembox SpreadSheet library.
If you want to explore more about SpreadSheetLight library where all the code samples I've seen are in C#, check out my MSDN code samples in VB.NET.
Ending comment, when dealing with Excel, there are many ways to go e.g. Excel automation which requires Excel to be installed while SpreadSheetLight and Gembox Spreadsheet libraries don't plus both of these libraries have the capabilities to do all common operations while Gembox does exceed SpreadSheetLight yet since SpreadSheetLight is free that is the one to look at first. There also is LINQ solutions such as demonstrated here (by Microsoft's Beth Massi a senior product manager) and several other ones that are fragile.
If and when you decide to work with Excel that is server based then best to stick with SpreadSheetLight and Gembox.
-
Jan 4th, 2018, 09:16 PM
#2
Re: DataGridView unbound to either Excel or Text
Shouldn't this thread be in the CodeBank?
Also, I'm wondering why you need to use a DataTable when creating a CSV instead of going straight from the gird to the CSV. It just seems like an unnecessary step.
-
Jan 5th, 2018, 07:24 AM
#3
Re: DataGridView unbound to either Excel or Text
I moved the post, thought I was in code bank, thanks for the heads up.
You are correct in regards to using a DataTable, in this case I was using a DataTable to reuse the extension method used with the Excel operation to be consistent but sure I could had done straight to csv.
These are in the code sample
Code:
''' <summary>
''' Generates comma delimited rows into a string array.
''' </summary>
''' <param name="sender"></param>
''' <returns></returns>
''' <remarks></remarks>
<Runtime.CompilerServices.Extension()>
Public Function CommaDelimitedRows(ByVal sender As DataGridView) As String()
Return (From row In sender.Rows Where Not DirectCast(row, DataGridViewRow).IsNewRow Let RowItem = String.Join(",", Array.ConvertAll(DirectCast(row, DataGridViewRow).Cells.Cast(Of DataGridViewCell).ToArray, Function(c As DataGridViewCell) If(c.Value Is Nothing, "", c.Value.ToString))) Select RowItem).ToArray
End Function
<Runtime.CompilerServices.Extension()>
Public Sub ExportToCommandDelimitedFile(ByVal pSender As DataGridView, ByVal pFileName As String)
IO.File.WriteAllLines(IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, pFileName), pSender.CommaDelimitedRows())
End Sub
Last edited by kareninstructor; Jan 5th, 2018 at 07:35 AM.
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
|