How to retrieve records in excel in .NET

Question

Hi, How can I retrieve records in Ms Excel in ADO.NET application? Thanks.

Satyapriya Nayak · Answer

Hi Alok, Here in this example we can show the excel records into a datagridview. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace Retrieving_excel_data_to_combobox { public partial class Form1 : Form { public OleDbConnection con; public void pintu(string s) { con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " + "data source='" + s + " '; " + "Extended Properties=Excel 8.0;"); } public OleDbCommand com; public DataSet ds; public OleDbDataAdapter oledbda; public DataTable dt; public string str; public Form1() { InitializeComponent(); } private void btnbrowse_Click(object sender, EventArgs e) { OpenFileDialog openfiledialog1 = new OpenFileDialog(); openfiledialog1.ShowDialog(); openfiledialog1.Filter = "allfiles|*.xls"; TextBox1.Text = openfiledialog1.FileName; } private void btndisplay_Click(object sender, EventArgs e) { pintu(TextBox1.Text); try { con.Open(); str = "select * from [sheet1$]"; com = new OleDbCommand(str, con); ds = new DataSet(); oledbda = new OleDbDataAdapter(com); oledbda.Fill(ds, "[sheet1$]"); con.Close(); DataGridView1.DataSource = ds; DataGridView1.DataMember = "[sheet1$]"; } catch (Exception ex) { MessageBox.Show(ex.Message); } } } } Thanks

Mukesh Kumar · Answer

using System; using System.Data.OleDb; using System.Data; namespace ExcelDataSource { class Program { static void Main(string[] args) { string Filepath = @"..\..\ExcelDb.xlsx"; string conxString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Filepath + ";Extended Properties=Excel 12.0 Xml"; using (OleDbConnection connection = new OleDbConnection(conxString)) { connection.Open(); DataTable ExcelSheets = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); DataSet objDataSet = new DataSet(); for (int i = 0; i { objDataSet = (DataSet)FillDataSet(connection, objDataSet, "[" + ExcelSheets.Rows[i]["TABLE_NAME"].ToString() + "]", ExcelSheets.Rows[i]["TABLE_NAME"].ToString()); } } } private static object FillDataSet(OleDbConnection objConnection, DataSet objDataSet, string strSheetName, string strTableName) { try { string strCommand = "Select * from " + strSheetName; OleDbDataAdapter objExcelData = new OleDbDataAdapter(strCommand, objConnection); objExcelData.Fill(objDataSet, strTableName); return objDataSet; } catch (Exception ex) { Console.WriteLine(ex.Message); return null; } } } }