Exemplo n.º 1
0
    protected void btnimport_Click1(object sender, ImageClickEventArgs e)
    {
        try
        {
            //string FilePath = "D:\\upload";
            //bool blSucces = false;
            string filename = string.Empty;
            //string[] allowdFile = { ".pdf" };
            ////Here we are allowing only pdf file so verifying selected file pdf or not
            //string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
            //bool isValidFile = allowdFile.Contains(FileExt);
            //if (!isValidFile)
            //{
            //    lblMsg.ForeColor = System.Drawing.Color.Red;
            //    lblMsg.Text = "Please upload only exl ";
            //}
            if (fileuploademp.HasFile)
            {
                //string[] allowdFile = { ".xlsx" };
                //Get file name of selected file
                filename = System.IO.Path.GetFileName(fileuploademp.FileName);
                fileuploademp.PostedFile.SaveAs(Server.MapPath("~/Uploads/" + filename));


                ArrayList alist        = new ArrayList();
                string    connString   = "";
                string    strFileType  = Path.GetExtension(fileuploademp.FileName).ToLower();
                string    fileBasePath = Server.MapPath("~/Uploads/");
                string    fileName     = Path.GetFileName(this.fileuploademp.FileName);
                string    fullFilePath = fileBasePath + fileName;

                //Connection String to Excel Workbook
                if (strFileType.Trim() == ".xls")
                {
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath +
                                 ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
                }
                else if (strFileType.Trim() == ".xlsx")
                {
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath +
                                 ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
                }
                if (fileuploademp.HasFile)
                {
                    //string query = "select student,rollno,course from [sheet1$]";
                    string query = "select student,rollno,course from [sheet1$]";
                    using (OleDbConnection conn = new OleDbConnection(connString))
                    {
                        if (conn.State == ConnectionState.Closed)
                        {
                            conn.Open();
                        }
                        OleDbCommand     cmd = new OleDbCommand(query, conn);
                        OleDbDataAdapter da  = new OleDbDataAdapter(cmd);
                        DataSet          ds  = new DataSet();
                        da.Fill(ds);
                        FillInDB(ds);
                        Session["griddata"]       = ds.Tables[0];
                        GridViewImport.DataSource = Session["griddata"];
                        GridViewImport.DataBind();



                        //OleDbCommand command = new OleDbCommand(query, conn);



                        //// Create DbDataReader to Data Worksheet
                        //using (DbDataReader dr = command.ExecuteReader())
                        //{

                        //    // SQL Server Connection String
                        //    Connect con = new Connect();
                        //    string sqlConnectionString = WebConfigurationManager.ConnectionStrings["con"].ToString();

                        //    // Bulk Copy to SQL Server
                        //    using (System.Data.SqlClient.SqlBulkCopy bulkCopy =
                        //    new System.Data.SqlClient.SqlBulkCopy(sqlConnectionString))
                        //    {
                        //        bulkCopy.DestinationTableName = "tdatamigrationtable";
                        //        bulkCopy.WriteToServer(dr);
                        //        lblmsg.Text = "The data has been exported succefuly from Excel to SQL";
                        //    }
                        //}
                    }
                }



// Create DbDataReader to Data Worksheet



                Employee obj_emp = new Employee();



                // obj_emp.importdatafromexcel("~/Uploads/" + filename);
            }
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.Message.ToString();
        }
    }
Exemplo n.º 2
0
    protected void btnimport_Click1(object sender, ImageClickEventArgs e)
    {
        try
        {
            //string FilePath = "D:\\upload";
            //bool blSucces = false;
            string filename = string.Empty;
            //string[] allowdFile = { ".pdf" };
            ////Here we are allowing only pdf file so verifying selected file pdf or not
            //string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
            //bool isValidFile = allowdFile.Contains(FileExt);
            //if (!isValidFile)
            //{
            //    lblMsg.ForeColor = System.Drawing.Color.Red;
            //    lblMsg.Text = "Please upload only exl ";
            //}
            if (fileuploademp.HasFile)
            {
                //string[] allowdFile = { ".xlsx" };
                //Get file name of selected file
                filename = System.IO.Path.GetFileName(fileuploademp.FileName);
                fileuploademp.PostedFile.SaveAs(Server.MapPath("~/Uploads/" + filename));


                ArrayList alist        = new ArrayList();
                string    connString   = "";
                string    strFileType  = Path.GetExtension(fileuploademp.FileName).ToLower();
                string    fileBasePath = Server.MapPath("~/Uploads/");
                string    fileName     = Path.GetFileName(this.fileuploademp.FileName);
                string    fullFilePath = fileBasePath + fileName;

                //Connection String to Excel Workbook
                if (strFileType.Trim() == ".xls")
                {
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath +
                                 ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
                }
                else if (strFileType.Trim() == ".xlsx")
                {
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath +
                                 ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
                }
                if (fileuploademp.HasFile)
                {
                    //string query = "select student,rollno,course from [sheet1$]";
                    string query = "select EmployeeID,Name,Competency,Location,Vertical,CustomerName,ProjectName,DeliveryManager,AccountCategory from [sheet1$]";
                    using (OleDbConnection conn = new OleDbConnection(connString))
                    {
                        if (conn.State == ConnectionState.Closed)
                        {
                            conn.Open();
                        }
                        OleDbCommand     cmd = new OleDbCommand(query, conn);
                        OleDbDataAdapter da  = new OleDbDataAdapter(cmd);
                        DataSet          ds  = new DataSet();
                        da.Fill(ds);
                        FillInDB(ds);
                        Validate_UploadedData();
                        Session["griddata"]       = ds.Tables[0];
                        GridViewImport.DataSource = Session["griddata"];
                        GridViewImport.DataBind();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.Message.ToString();
        }
    }