uploading data from excel sheet

Question

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 8-May-11 20:31pm

spydeehunk

Updated 8-May-11 20:39pm

Toniyo Jackson

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mathews Davis · Answer 1 · 2011-05-08T20:36:00

MSIL

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();

    }