before page load define the properties of msexcel-
public int StateId;
DataSet ds = new DataSet();
Thread th2;
DataSet myDs = new DataSet();
string fu = null;
int result;
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook oWB;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
then take your first sheet data into dataset -
protected void FillDataSet()
{
try
{
string strFilename = FileUpload.PostedFile.FileName;
strFilename = System.IO.Path.GetFileName(strFilename);
string ext = Path.GetExtension(strFilename);
bool hasHeaders = true;
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (ext.ToLower() == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fu + " ;Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";
Microsoft.Office.Interop.Excel.WorkbookClass workBook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
app.Visible = false;
OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "$]", strConn);
myCmd.Fill(myDs);
string source = @"F:\yourdrive\" + strFilename;
string target = @"F:\yourdrive\temp\" + strFilename;
if (File.Exists(target))
File.Delete(target);
File.Move(source, target);
}
if (ext.ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fu + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=2\"";
Microsoft.Office.Interop.Excel.WorkbookClass workBook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
app.Visible = false;
OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "$]", strConn);
myCmd.Fill(myDs);
string source = @"F:\yourdrive\" + strFilename;
string target = @"F:\yourdrive\temp\" + strFilename;
if (File.Exists(target))
File.Delete(target);
File.Move(source, target);
}
}
catch (Exception ex)
{
Response.Write("Error has been sent to the Technical Team, Please try after some time!");
}
}
after when your data is in dataset ... you can insert this in your database on buton click .