Export Excel file to Sql table

this might help....

C# class library for exporting data to CSV/Excel file[^]

First to make a add reference to excel namespace in your project .
using Excel = Microsoft.Office.Interop.Excel;

This is for Sql Tables to genrate Excel file.
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.SqlClient;
using RKLib.ExportData;
using System.IO;
using System.Data.OleDb;
using System.Data.Common;
using Excel = Microsoft.Office.Interop.Excel;

namespace TextToExcelWindows
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{

SqlConnection cnn;
string connectionString ;
string sql;
int i;
int j;

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

connectionString = "Server=AKS13\\SQL2005;uid=sa;pwd=sql2005;Database=ExcelDB;";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "select ExcelColumns from tbMapping";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataTable dt = new DataTable();
dscmd.Fill(dt);

// Headers.
for (i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}



// Content
for (i = 0; i < dt.Rows.Count; i++)
{
for (j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}

xlWorkBook.SaveAs("C:\\mapping.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

MessageBox.Show("Excel file created , you can find the file C:\\mapping.xls");

}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}


This one for Excel file to Sql table interms of saving data to sql table

string file = "C:\\informations2.xls";

string constring;
constring = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + ";" + "Extended Properties=\"Excel 8.0;IMEX=1\";";
// constring = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + filepath1 + ";" + "Extended Properties='Excel 12.0;IMEX=1\'";
OleDbConnection objConn = new OleDbConnection(constring);
objConn.Open();
DataTable dtExcelSchema;
dtExcelSchema = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + SheetName + "]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataTable dt = new DataTable();
objAdapter1.Fill(dt);
foreach (DataRow dr in dt.Rows)
{

SqlConnection cn = new SqlConnection("Server=AKS13\\SQL2005;uid=sa;pwd=sql2005;Database=ExcelDB");
cn.Open();
string cmdstng = "insert into SqlToExcel(Id,Fname,Lname,Address,City,State,TelephoneNo)values('" + dr.ItemArray[0] + "','" + dr.ItemArray[1] + "','" + dr.ItemArray[2] + "','" + dr.ItemArray[3] + "','" + dr.ItemArray[4] + "','" + dr.ItemArray[5] + "','" + dr.ItemArray[6] + "') ";
SqlCommand cmd = new SqlCommand(cmdstng, cn);
cmd.ExecuteNonQuery();
MessageBox.Show("Record added Successfully..!");
}