First need to add the reference “Microsoft.office.Interop.Excel”. You can add it from .Net Components. Add open Dialogbox ,Textbox and two command button on form.
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;
using System.Data.SqlClient;
using System.IO;
namespace ExcelTOSQLDataBase
{
public partial class EXCELTOSQL : Form
{
publicEXCELTOSQL()
{
InitializeComponent();
}
privatevoid button1_Click(objectsender, EventArgs e)
{
if(openFileDialog1.ShowDialog() == DialogResult.OK)
textBox1.Text = openFileDialog1.FileName;
}
privatevoid button2_Click(objectsender, EventArgs e)
{
//Table Name
stringEXCELSQLTable = “Employees”;
stringExcelData = “Select * from [Sheet1$]”;
//SQL Server Connection String
string SQLConnectionString = “Data Source=ComputerName;Initial
Catalog=DataBaseName;Integrated Security=True”;
//Excel File Path
stringExcelFilePath=textBox1.Text;
try
{
string ConnectionString =@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + ExcelFilePath + “;Extended Properties=” + “”Excel 8.0;HDR=YES;””;
OleDbConnection OleDbCnn = new OleDbConnection(ConnectionString);
OleDbCnn.Open();
SqlBulkCopybulkCopy = new SqlBulkCopy(SQLConnectionString);
bulkCopy.DestinationTableName = EXCELSQLTable;
using (OleDbDataAdapterX = new OleDbDataAdapter(ExcelData, OleDbCnn))
{
DataTable SQLT = newDataTable();
X.Fill(SQLT);
bulkCopy.WriteToServer(SQLT);
}
OleDbCnn.Close();
MessageBox.Show(“Data Imported Successfully”);
}
catch (Exception ee)
{
label2.Text = ee.Message;
}
}
}
}
Design and Output
Results:
Error in this programe : external table is not in the expected format
why is that can u solve