Import from Excel or CSV

[Patrick]

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.

[Nikolay]

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.

[Bob]

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

[Nikolay]

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.

[Versile]

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;
	}

[Vassil Petev]

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.

[Jeremy]

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

[Anton]

Hi Jeremy,

Thank you for sharing your solution with the community.

Regards,
Anton
Telerik

[DrMDodd]

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.

[DrMDodd]

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

Corrected connection string

 

[George]

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