Results 1 to 3 of 3

Thread: DataGridView unbound to either Excel or Text

  1. #1

    Thread Starter
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    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.

    Name:  1.jpg
Views: 813
Size:  53.4 KB

    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.

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

    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.

  3. #3

    Thread Starter
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    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
  •  



Click Here to Expand Forum to Full Width