Click here to Skip to main content
14,691,731 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have one web application where i m uploading data from excel to upload excel i have used.
I have aspx page like below:
<div class="div">
                                                        <div class="upload_icon">
                                                            <asp:ImageButton runat="server" ID="btnUpload" ImageUrl="images/control_img/down.jpg"
                                                                OnClick="btnUpload_Click" />
                                                        </div>
                                                        <div class="field">
                                                            <div class="browse_field_1">
                                                                <input type="file" id="File1"  runat="server" class="browse_field" />
                                                            </div>
                                                            <%--<div class="file_2">
                            <img src="images/control_img/browse.png" /></div>--%>
                            
                                                        </div>
                                                    </div>
I have made one utility class which upload excel is as follow:
public class Uplaoad
{
    public Uplaoad()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public string UploadFile(HtmlInputFile objHtmlInputFile, string savePath)
    {
        string sFilename = string.Empty;
        HttpPostedFile postedfile = objHtmlInputFile.PostedFile;
        if (postedfile != null)
        {

            int postedfilelen = postedfile.ContentLength;
            string[] arr = new string[2];

            try
            {
                byte[] myData = new byte[postedfilelen + 1];
                postedfile.InputStream.Read(myData, 0, postedfilelen);
                sFilename = System.IO.Path.GetFileName(postedfile.FileName);
                int file_append = 0;
                while ((System.IO.File.Exists(savePath + sFilename)))
                {
                    file_append = file_append + 1;
                    sFilename = System.IO.Path.GetFileNameWithoutExtension(postedfile.FileName) + file_append.ToString() + System.IO.Path.GetExtension(postedfile.FileName);
                }
                System.IO.FileStream newFile = new System.IO.FileStream(savePath + sFilename, System.IO.FileMode.Create);
                newFile.Write(myData, 0, myData.Length);
                newFile.Close();
                arr[0] = "T";
                arr[1] = sFilename;
                return sFilename;
            }
            catch (Exception ex)
            {
                arr[1] = "File upload failed. Please contact system administrator.";
                return sFilename;
            }
        }
        return sFilename;
    }
    public string GetID(string ID)
    {
        string NewIDD = "";
        if (ID.Length == 7)
            NewIDD = ID;
        else if (ID.Length == 6)
            NewIDD = "0" + ID;
        else if (ID.Length == 5)
            NewIDD = "00" + ID;
        else if (ID.Length == 4)
            NewIDD = "000" + ID;
        else if (ID.Length == 3)
            NewIDD = "0000" + ID;
        else if (ID.Length == 2)
            NewIDD = "00000" + ID;
        else if (ID.Length == 1)
            NewIDD = "000000" + ID;
        return NewIDD;
    }
}

now in image button control click event handler i have written class as follow:
protected void btnUpload_Click(object sender, ImageClickEventArgs e)
{
       
        string strconn = null;
        //string LogoURL = "";
        //ExcelProduct
        Uplaoad file_upload = new Uplaoad();
        string savePath = Server.MapPath("Temp/");
        string filepath = file_upload.UploadFile(File1, savePath);
        DataTable dt = new DataTable();
        if (System.IO.Path.GetExtension(File1.PostedFile.FileName).ToLower() == ".xls")
            strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath.ToString() + filepath.ToString() + ";Extended Properties=\"Excel 8.0;IMEX=1\"";
        else
            strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath.ToString() + filepath.ToString() + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
               OleDbConnection olConn = new OleDbConnection(strconn);
        if (olConn.State != ConnectionState.Open)
        {
            olConn.Open();
        }
        
        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", olConn);
       
        da.Fill(dt);
}

when i am uploading excel 2003 its working fine . But it gives error while uploading excel 2007 can any body help me out here
Posted
Updated 8-May-11 21:39pm
v2

1 solution

protected void Button1_Click(object sender, EventArgs e)
    {
        UploadFileAndBind();
    }

    public void UploadFileAndBind()
    {
        //this will check if there is any file in the fileupload control or not.
        if (FileUpload1.HasFile)
        {
            //Fetch the name of the file.
            string strFileName = FileUpload1.PostedFile.FileName;

            //Fetch the Extension of the file.
            string extFile = Path.GetExtension(strFileName ).ToString().ToLower();


            if (extFile == ".xls" || extFile == ".xlsx")
            {
                FileUpload1.SaveAs(Server.MapPath("~/Files/" + strFileName));
                BindGrid (Server.MapPath ("~/Files/" + strFileName),extFile );
            }
            else
            {
                Label1.Text = "File You Are Uploading is not an Excel File.";
                Label1.Visible = true;
            }
        }
    }

    public void BindGrid(string filePath, string filetype)
    {
        //variable and object creation



        string constr = null;
        string qry = null;

        //check if the file is of MS Excel 2003 or Of MS Excel 2007 and set the Connection on that basis.

        //Connection string for the MS Excel 2003.
        if (filetype.Trim() == ".xls")
        {
            constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        }
        //connection string for MS Excel 2007.
        else if (filetype.Trim() == ".xlsx")
        {
            constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        }

        //query to fetch the Excel Sheet.
        qry = "select * from [Sheet1$]";


        OleDbConnection con = new OleDbConnection(constr);
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        OleDbCommand cmd = new OleDbCommand(qry,con );
        OleDbDataAdapter odp = new OleDbDataAdapter(cmd );
        DataSet ds = new DataSet();
        odp.Fill(ds);

        odp.Dispose();
        con.Close();
        con.Dispose();

    }
   
Comments
spydeehunk 9-May-11 2:46am
   
can you check out my code and help me out where i m doing wrong. the code you have provided is not what i want.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900