Import .xls file

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;
            }
           
        }
    }
Ebook Download
View all
Learn
View all