This is a migrated thread and some comments may be shown as answers.

Import from Excel or CSV

10 Answers 759 Views
GridView
This is a migrated thread and some comments may be shown as answers.
Patrick
Top achievements
Rank 1
Patrick asked on 26 Jan 2009, 04:37 PM
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.

10 Answers, 1 is accepted

Sort by
0
Nikolay
Telerik team
answered on 30 Jan 2009, 05:50 PM

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.
0
Bob
Top achievements
Rank 1
answered on 06 Jun 2009, 06:48 PM
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

0
Nikolay
Telerik team
answered on 11 Jun 2009, 07:12 AM
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.
0
Versile
Top achievements
Rank 1
answered on 25 Aug 2009, 08:38 PM
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.

        private void Test(string filename) 
        { 
            // Connection String to Excel Workbook 
            string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + @";Extended Properties=""Excel 8.0;HDR=YES"""
 
            // Create Connection to Excel Workbook 
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(excelConnectionString); 
            // replace [tbl_30_day$] with the "sheet" name of the data you want from the excel file 
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("Select * FROM [tbl_30_day$]", connection); 
 
            // create table to hold excel sheet 
            DataTable objtb = new DataTable(); 
            try 
            { 
                connection.Open(); 
 
                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(command); 
 
                da.Fill(objtb); 
                da.Dispose(); 
            } 
            catch (Exception exd) 
            { 
                // variable here to catch exception message / stacktrace 
            } 
            finally 
            { 
                if (connection != null
                { 
                    connection.Close(); 
                } 
            } 
 
            travelling_Grid.DataSource = objtb; 
        } 



0
Vassil Petev
Telerik team
answered on 26 Aug 2009, 12:37 PM
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.
0
Jeremy
Top achievements
Rank 1
answered on 31 Jul 2013, 03:48 PM
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
0
Anton
Telerik team
answered on 02 Aug 2013, 09:26 AM
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 >>
0
DrMDodd
Top achievements
Rank 1
answered on 01 May 2014, 10:16 AM
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.
0
DrMDodd
Top achievements
Rank 1
answered on 01 May 2014, 10:29 AM
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;<br>Extended Properties="Excel 12.0 Xml;HDR=YES";

Corrected connection string

 

0
George
Telerik team
answered on 01 May 2014, 02:24 PM
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.
 
Tags
GridView
Asked by
Patrick
Top achievements
Rank 1
Answers by
Nikolay
Telerik team
Bob
Top achievements
Rank 1
Versile
Top achievements
Rank 1
Vassil Petev
Telerik team
Jeremy
Top achievements
Rank 1
Anton
Telerik team
DrMDodd
Top achievements
Rank 1
George
Telerik team
Share this question
or