Read and Import Excel File into DataSet
In the previous examples we used Microsoft Excel 12.0 Object Library for read or write to and Excel file . In C# without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel file using OLEDB .
Read Excel Sheet Data into DataTable
Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For read the content from an Excel file using |BLBS^ |ADO.NET|BLBE^ | , We can use the SELECT command like in SQL Operations.
sample Select sql
sql = "select * from [Sheet1$]"
Here is the sample Excel file .
Open the connection using OLEDB Provider
(provider=Microsoft.Jet.OLEDB.4.0;Data Source='Your Filename';Extended Properties=Excel 8.0;)Specify which data you want to read
select * from [Sheet1$]
Excel to Dataset
Here is the screen short after reading from Excel file in C# .
using System;
using System.Drawing;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
System.Data.OleDb.OleDbConnection MyConnection ;
System.Data.DataSet DtSet ;
System.Data.OleDb.OleDbDataAdapter MyCommand ;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dataGridView1.DataSource = DtSet.Tables[0];
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show (ex.ToString());
}
}
}
}
Related Topics
- How to create Excel file in C#
- How to open an Excel file in C#
- How to read an Excel file using C#
- How to programmatically Add New Worksheets
- How to delete worksheet from an excel file
- How to format an Excel file using C#
- How to insert a picture in excel from C# App
- How to insert a background picture in excel
- How to create Excel Chart from C#
- How to export excel chart from C#
- How to excel chart in C# picturebox
- C# data validation input box in excel file
- How to insert data to Excel file using OLEDB
- How to update data in Excel file using OLEDB
- How to export databse to excel file
- How to export DataGridView to excel file