try
{
string path;
if (!(Path.GetExtension(fuImport.PostedFile.FileName) == ".xls" || Path.GetExtension(fuImport.PostedFile.FileName) == ".xlsx"))
{
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "al", "alert('Please Import a valid Excel file.');", true);
}
else
{
path = Server.MapPath(".");
if (Path.GetExtension(fuImport.PostedFile.FileName) == ".xlsx" || Path.GetExtension(fuImport.PostedFile.FileName) == ".xls")
path = "~\\ImportFile\\Employee\\asc.xlsx";
if (Path.GetExtension(fuImport.PostedFile.FileName) == ".xls")
path = "~\\ImportFile\\Employee\\asc.xls";
fuImport.PostedFile.SaveAs(Server.MapPath(path));
fuImport.Dispose();
//Importing Data from .xls file into FFISM Grid
ImportFFISMData(path);
fuImport.Dispose();
}
}
catch (Exception ex)
{
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "invalid", "alert('Error in importing data :" + ex.Message.ToString() + "');", true);
return;
}
// Import Employee details from .xls file.
private void ImportFFISMData(string path)
{
int dateRowIndex = -1; // Row Index of Date Column
int dateColIndex = -1; // Column Index of Date Column
int nextdateRowIndex = -1; //Column Index of Next Date Column
string excelConnectionString = string.Empty;
if (Path.GetExtension(path) == ".xlsx")
excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(path) + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
else
excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(path) + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
OleDbConnection oleDbConn = new OleDbConnection(excelConnectionString);
OleDbCommand oleDbCmd;
OleDbDataAdapter oleDbAdp;
DataTable oleDt = new DataTable();
DataTable dtTableName = new DataTable();
try
{
oleDbConn.Open();
dtTableName = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string[] tableNames = new string[dtTableName.Rows.Count];
for (int j = 0; j < dtTableName.Rows.Count; j++)
{
tableNames[j] = dtTableName.Rows[j]["TABLE_NAME"].ToString();
}
oleDbCmd = new OleDbCommand("SELECT * FROM [" + tableNames[0] + "]", oleDbConn);
oleDbAdp = new OleDbDataAdapter(oleDbCmd);
oleDbAdp.Fill(oleDt);
oleDbConn.Close();
Session["XLSData"] = oleDt;
}
catch (Exception ex)
{
try
{
string delimeter = "\t";
DataSet ds = new DataSet();
ds = BuildDataSet(Server.MapPath(path).ToString(), "dt", delimeter);
oleDt = ds.Tables[0];
oleDt.Rows.RemoveAt(oleDt.Rows.Count -1);
Session["XLSData"] = oleDt;
oleDbConn.Close();
}
catch
{
oleDbConn.Close();
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "invalid", "alert('Error in importing data :" + ex.Message.ToString() + "');", true);
return;
}
}
}