Import from Excel or CSV

11 posts, 0 answers
  1. Patrick
    Patrick avatar
    6 posts
    Member since:
    Sep 2007

    Posted 26 Jan 2009 Link to this post

    Hello,

    I am looking for example code that shows how to import, and populate a Grid from Excel data. Is it already somewhere on this forum?

    Thanks,

    Patrick.
  2. Nikolay
    Admin
    Nikolay avatar
    1803 posts

    Posted 30 Jan 2009 Link to this post

    Hi Patrick,

    RadGridView supports only export to Excel functionality, however you cannot import data from Excel into RadGridView. You can find the Export to Excel example in our Quick Start Framework application (a.k.a. Examples), section RadGridView >> Export to Excel. As to the CSV format, you can copy and paste rows in CSV format from one RadGridView to another when both have the same data strcture. An example of the CSV approach you can find in this Knowledge Base article: Copy/Pasting rows in and between RadGridViews (CSV format)

    Sincerely yours,

    Nikolay
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  3. UI for WinForms is Visual Studio 2017 Ready
  4. Bob
    Bob avatar
    9 posts
    Member since:
    Apr 2009

    Posted 06 Jun 2009 Link to this post

    This will let you import a .CSV file...

            Dim fname As String = ""

            If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
                fname = OpenFileDialog1.FileName
            End If

            Dim TextLine As String = ""
            Dim SplitLine() As String

            If System.IO.File.Exists(fname) = True Then
                Dim objReader As New System.IO.StreamReader(fname)

                Do While objReader.Peek() <> -1
                    TextLine = objReader.ReadLine()

                    SplitLine = Split(TextLine, ",")

                    radgridview1.ColumnCount = SplitLine.Count

               'if .RowCount = 0 then we are processing the first row
               'only need this code if you have a header row
                    If radgridview1.RowCount = 0 Then
                        For i = 1 To SplitLine.Count - 1
                            With radgridview1
                                .Columns(i).HeaderText = SplitLine(i).ToString
                                .Columns(i).BestFit()
                            End With
                        Next
                    End If
                    radgridview1.Rows.Add(SplitLine)
                Loop

                'stupid but the only way I could get the datagridview to populate correctly
                'removes the header row
                If radgridview1.RowCount > 1 Then
                    radgridview1.Rows.RemoveAt(0)
                End If

            Else
                RadMessageBox.Show("File Does Not Exist")
            End If

  5. Nikolay
    Admin
    Nikolay avatar
    1803 posts

    Posted 11 Jun 2009 Link to this post

    Hi Bob,

    Thank you for sharing your solution with the community. I am updating your Telerik points for it.

    All the best,
    Nikolay
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  6. Versile
    Versile avatar
    20 posts
    Member since:
    May 2009

    Posted 25 Aug 2009 Link to this post

    Much cleaner method to import Excel data (in my opinion at least) google some of the oledb stuff and you can find examples to import CSV files as well. I've used this method and it's much better than a manual reading of the CSV file as it catches columns that might have "special" characters in them much cleaner.

    1         private void Test(string filename) 
    2         { 
    3             // Connection String to Excel Workbook 
    4             string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + @";Extended Properties=""Excel 8.0;HDR=YES"""
    5  
    6             // Create Connection to Excel Workbook 
    7             System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(excelConnectionString); 
    8             // replace [tbl_30_day$] with the "sheet" name of the data you want from the excel file 
    9             System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("Select * FROM [tbl_30_day$]", connection); 
    10  
    11             // create table to hold excel sheet 
    12             DataTable objtb = new DataTable(); 
    13             try 
    14             { 
    15                 connection.Open(); 
    16  
    17                 System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(command); 
    18  
    19                 da.Fill(objtb); 
    20                 da.Dispose(); 
    21             } 
    22             catch (Exception exd) 
    23             { 
    24                 // variable here to catch exception message / stacktrace 
    25             } 
    26             finally 
    27             { 
    28                 if (connection != null
    29                 { 
    30                     connection.Close(); 
    31                 } 
    32             } 
    33  
    34             travelling_Grid.DataSource = objtb; 
    35         } 



  7. Vassil Petev
    Admin
    Vassil Petev avatar
    1765 posts

    Posted 26 Aug 2009 Link to this post

    Hi Versile,

    Thank you for sharing your solution with the Telerik Community - we have updated your Telerik Points as well.

    Sincerely yours,
    Vassil
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  8. Jeremy
    Jeremy avatar
    1 posts
    Member since:
    Mar 2011

    Posted 31 Jul 2013 Link to this post

    I know this topic is old, but I figured I'd share how I get CSV into a grid. I haven't found a faster cleaner way then this.

    using (CachedCsvReader csv = new CachedCsvReader(new StreamReader(filename), true))
    {
          radGridView1.DataSource = csv;
    }

    This uses the CSV Reader by Sebastien Lorion located at CodeProject.
    http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader
  9. Anton
    Admin
    Anton avatar
    167 posts

    Posted 02 Aug 2013 Link to this post

    Hi Jeremy,

    Thank you for sharing your solution with the community.

    Regards,
    Anton
    Telerik
    TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for WINFORMS.
    Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
    Sign up for Free application insights >>
  10. DrMDodd
    DrMDodd avatar
    5 posts
    Member since:
    May 2007

    Posted 01 May 2014 in reply to Versile Link to this post

    For Excel 2007 (and later) files with the Xlsx file extension (Office Open XML format with macros disabled). The connection string takes the form:
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;<br>Extended Properties="Excel 12.0 Xml;HDR=YES";"HDR=Yes;"

    indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
  11. DrMDodd
    DrMDodd avatar
    5 posts
    Member since:
    May 2007

    Posted 01 May 2014 in reply to DrMDodd Link to this post

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;<br>Extended Properties="Excel 12.0 Xml;HDR=YES";

    Corrected connection string

     

  12. George
    Admin
    George avatar
    500 posts

    Posted 01 May 2014 Link to this post

    Hello Martin,

    Thank you for sharing the solution for the new Excel files with the xlsx extension. I am sure someone will benefit from it.

    Regards,
    George
    Telerik
     
    Check out Telerik Analytics, the service which allows developers to discover app usage patterns, analyze user data, log exceptions, solve problems and profile application performance at run time. Watch the videos and start improving your app based on facts, not hunches.
     
Back to Top
UI for WinForms is Visual Studio 2017 Ready