Avatar of Webboy2008
Webboy2008
 asked on

asp.net / excel upload

protected void insertdata_Click(object sender, EventArgs e)
    {
        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("example.xls") + "; Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();
            string PartNo = "";
            string Description = "";
            string Mfg = "";
            string PreferredSupplier = "";
            string Category = "";
            string PbQty = "";
            string AlterPartNo01 = "";
            string AlterPartNo02 = "";
            string AlterPartNo03 = "";
            string AlterPartNo04 = "";
            string AlterPartNo05 = "";
            string AlterPartNo06 = "";
            string AlterPartNo07 = "";
            string AlterPartNo08 = "";
            string AlterPartNo09 = "";
            string AlterPartNo10 = "";
            string AlterPartNo11 = "";
            string AlterPartNo12 = "";
            string AlterPartNo13 = "";
            string AlterPartNo14 = "";
            string AlterPartNo15 = "";
            string AlterPartNo16 = "";
            string AlterPartNo17 = "";
            string AlterPartNo18 = "";
            string AlterPartNo19 = "";
            string AlterPartNo20 = "";
            string AlterPartNo21 = "";
            string AlterPartNo22 = "";
            string AlterPartNo23 = "";


            while (odr.Read())
            {
                PartNo = valid(odr, 0);
                Description = valid(odr, 1);
                Mfg = valid(odr, 2);
                PreferredSupplier = valid(odr, 3);
                Category = valid(odr, 4);
                PbQty = valid(odr, 5);
                AlterPartNo01 = valid(odr, 6);
                AlterPartNo02 = valid(odr, 7);
                AlterPartNo03 = valid(odr, 8);
                AlterPartNo04 = valid(odr, 9);
                AlterPartNo05 = valid(odr, 10);
                AlterPartNo06 = valid(odr, 11);
                AlterPartNo07 = valid(odr, 12);
                AlterPartNo08 = valid(odr, 13);
                AlterPartNo09 = valid(odr, 14);
                AlterPartNo10 = valid(odr, 15);
                AlterPartNo11 = valid(odr, 16);
                AlterPartNo12 = valid(odr, 17);
                AlterPartNo13 = valid(odr, 18);
                AlterPartNo14 = valid(odr, 19);
                AlterPartNo15 = valid(odr, 20);
                AlterPartNo16 = valid(odr, 21);
                AlterPartNo17 = valid(odr, 22);
                AlterPartNo18 = valid(odr, 23);
                AlterPartNo19 = valid(odr, 24);
                AlterPartNo20 = valid(odr, 25);
                AlterPartNo21 = valid(odr, 26);
                AlterPartNo22 = valid(odr, 27);
                AlterPartNo23 = valid(odr, 28);
                insertdataintosql(PartNo, Description, Mfg, PreferredSupplier, Category, PbQty, AlterPartNo01, AlterPartNo02, AlterPartNo03, AlterPartNo04, AlterPartNo05, AlterPartNo06, AlterPartNo07, AlterPartNo08, AlterPartNo09, AlterPartNo10, AlterPartNo11, AlterPartNo12, AlterPartNo13, AlterPartNo14, AlterPartNo15, AlterPartNo16, AlterPartNo17, AlterPartNo18, AlterPartNo19, AlterPartNo20, AlterPartNo21, AlterPartNo22, AlterPartNo23);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }

Open in new window

Dear Experts,

I have attached code working fine in asp.net/c#.
However, I have the following line
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("example.xls") + "; Extended Properties=Excel 8.0");
       
Now capture the particular excel file <<example.xls>>
And now I hope to use file upload feature to get a xls file from my users.
Assume they will always put same file name and file type.

Is this possible to convert existing codes to file upload ?
If yes, can you show me how to do that? Or rewrite the codes and show me? Thanks.

For completed working codes, I will give out 500 pts.

Thanks,
ASP.NETC#AJAX

Avatar of undefined
Last Comment
disrupt

8/22/2022 - Mon
disrupt

you want something like add a fileupload control and on click event of a button use the following:
if (FileUpload1.HasFile == true)
{
  string filename = Path.GetFileName(FileUpload1.FileName);
  studentFileUpload.SaveAs(Server.MapPath("~/FileUpload/") + filename);

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/FileUpload/example.xls") + "; Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();
            string PartNo = "";
            string Description = "";
            string Mfg = "";
            string PreferredSupplier = "";
            string Category = "";
            string PbQty = "";
            string AlterPartNo01 = "";
            string AlterPartNo02 = "";
            string AlterPartNo03 = "";
            string AlterPartNo04 = "";
            string AlterPartNo05 = "";
            string AlterPartNo06 = "";
            string AlterPartNo07 = "";
            string AlterPartNo08 = "";
            string AlterPartNo09 = "";
            string AlterPartNo10 = "";
            string AlterPartNo11 = "";
            string AlterPartNo12 = "";
            string AlterPartNo13 = "";
            string AlterPartNo14 = "";
            string AlterPartNo15 = "";
            string AlterPartNo16 = "";
            string AlterPartNo17 = "";
            string AlterPartNo18 = "";
            string AlterPartNo19 = "";
            string AlterPartNo20 = "";
            string AlterPartNo21 = "";
            string AlterPartNo22 = "";
            string AlterPartNo23 = "";


            while (odr.Read())
            {
                PartNo = valid(odr, 0);
                Description = valid(odr, 1);
                Mfg = valid(odr, 2);
                PreferredSupplier = valid(odr, 3);
                Category = valid(odr, 4);
                PbQty = valid(odr, 5);
                AlterPartNo01 = valid(odr, 6);
                AlterPartNo02 = valid(odr, 7);
                AlterPartNo03 = valid(odr, 8);
                AlterPartNo04 = valid(odr, 9);
                AlterPartNo05 = valid(odr, 10);
                AlterPartNo06 = valid(odr, 11);
                AlterPartNo07 = valid(odr, 12);
                AlterPartNo08 = valid(odr, 13);
                AlterPartNo09 = valid(odr, 14);
                AlterPartNo10 = valid(odr, 15);
                AlterPartNo11 = valid(odr, 16);
                AlterPartNo12 = valid(odr, 17);
                AlterPartNo13 = valid(odr, 18);
                AlterPartNo14 = valid(odr, 19);
                AlterPartNo15 = valid(odr, 20);
                AlterPartNo16 = valid(odr, 21);
                AlterPartNo17 = valid(odr, 22);
                AlterPartNo18 = valid(odr, 23);
                AlterPartNo19 = valid(odr, 24);
                AlterPartNo20 = valid(odr, 25);
                AlterPartNo21 = valid(odr, 26);
                AlterPartNo22 = valid(odr, 27);
                AlterPartNo23 = valid(odr, 28);
                insertdataintosql(PartNo, Description, Mfg, PreferredSupplier, Category, PbQty, AlterPartNo01, AlterPartNo02, AlterPartNo03, AlterPartNo04, AlterPartNo05, AlterPartNo06, AlterPartNo07, AlterPartNo08, AlterPartNo09, AlterPartNo10, AlterPartNo11, AlterPartNo12, AlterPartNo13, AlterPartNo14, AlterPartNo15, AlterPartNo16, AlterPartNo17, AlterPartNo18, AlterPartNo19, AlterPartNo20, AlterPartNo21, AlterPartNo22, AlterPartNo23);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }


}

Open in new window

disrupt

and this if u want the filename to be the same as the one they upload:
if (FileUpload1.HasFile == true)
{
  string filename = Path.GetFileName(FileUpload1.FileName);
  studentFileUpload.SaveAs(Server.MapPath("~/FileUpload/") + filename);

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/FileUpload/" + filename) + "; Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();
            string PartNo = "";
            string Description = "";
            string Mfg = "";
            string PreferredSupplier = "";
            string Category = "";
            string PbQty = "";
            string AlterPartNo01 = "";
            string AlterPartNo02 = "";
            string AlterPartNo03 = "";
            string AlterPartNo04 = "";
            string AlterPartNo05 = "";
            string AlterPartNo06 = "";
            string AlterPartNo07 = "";
            string AlterPartNo08 = "";
            string AlterPartNo09 = "";
            string AlterPartNo10 = "";
            string AlterPartNo11 = "";
            string AlterPartNo12 = "";
            string AlterPartNo13 = "";
            string AlterPartNo14 = "";
            string AlterPartNo15 = "";
            string AlterPartNo16 = "";
            string AlterPartNo17 = "";
            string AlterPartNo18 = "";
            string AlterPartNo19 = "";
            string AlterPartNo20 = "";
            string AlterPartNo21 = "";
            string AlterPartNo22 = "";
            string AlterPartNo23 = "";


            while (odr.Read())
            {
                PartNo = valid(odr, 0);
                Description = valid(odr, 1);
                Mfg = valid(odr, 2);
                PreferredSupplier = valid(odr, 3);
                Category = valid(odr, 4);
                PbQty = valid(odr, 5);
                AlterPartNo01 = valid(odr, 6);
                AlterPartNo02 = valid(odr, 7);
                AlterPartNo03 = valid(odr, 8);
                AlterPartNo04 = valid(odr, 9);
                AlterPartNo05 = valid(odr, 10);
                AlterPartNo06 = valid(odr, 11);
                AlterPartNo07 = valid(odr, 12);
                AlterPartNo08 = valid(odr, 13);
                AlterPartNo09 = valid(odr, 14);
                AlterPartNo10 = valid(odr, 15);
                AlterPartNo11 = valid(odr, 16);
                AlterPartNo12 = valid(odr, 17);
                AlterPartNo13 = valid(odr, 18);
                AlterPartNo14 = valid(odr, 19);
                AlterPartNo15 = valid(odr, 20);
                AlterPartNo16 = valid(odr, 21);
                AlterPartNo17 = valid(odr, 22);
                AlterPartNo18 = valid(odr, 23);
                AlterPartNo19 = valid(odr, 24);
                AlterPartNo20 = valid(odr, 25);
                AlterPartNo21 = valid(odr, 26);
                AlterPartNo22 = valid(odr, 27);
                AlterPartNo23 = valid(odr, 28);
                insertdataintosql(PartNo, Description, Mfg, PreferredSupplier, Category, PbQty, AlterPartNo01, AlterPartNo02, AlterPartNo03, AlterPartNo04, AlterPartNo05, AlterPartNo06, AlterPartNo07, AlterPartNo08, AlterPartNo09, AlterPartNo10, AlterPartNo11, AlterPartNo12, AlterPartNo13, AlterPartNo14, AlterPartNo15, AlterPartNo16, AlterPartNo17, AlterPartNo18, AlterPartNo19, AlterPartNo20, AlterPartNo21, AlterPartNo22, AlterPartNo23);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }


}

Open in new window

Webboy2008

ASKER
can you get me a completed code included the front end like the textbox and browse button?
Thanks
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
disrupt

sure:
FRONT END:

<form id="form1" runat="server">
    <asp:FileUpload id="FileUploadControl" runat="server" />
    <asp:Button runat="server" id="UploadButton" text="Upload" onclick="UploadButton_Click" />
    <br /><br />
    <asp:Label runat="server" id="StatusLabel" text="Upload status: " />
</form>

CODEBEHIND:

protected void UploadButton_Click(object sender, EventArgs e)
{
if (FileUploadControl.HasFile == true)
{
  string filename = Path.GetFileName(FileUploadControl.FileName);
  studentFileUpload.SaveAs(Server.MapPath("~/FileUpload/") + filename);

OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/FileUpload/" + filename) + "; Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();
            string PartNo = "";
            string Description = "";
            string Mfg = "";
            string PreferredSupplier = "";
            string Category = "";
            string PbQty = "";
            string AlterPartNo01 = "";
            string AlterPartNo02 = "";
            string AlterPartNo03 = "";
            string AlterPartNo04 = "";
            string AlterPartNo05 = "";
            string AlterPartNo06 = "";
            string AlterPartNo07 = "";
            string AlterPartNo08 = "";
            string AlterPartNo09 = "";
            string AlterPartNo10 = "";
            string AlterPartNo11 = "";
            string AlterPartNo12 = "";
            string AlterPartNo13 = "";
            string AlterPartNo14 = "";
            string AlterPartNo15 = "";
            string AlterPartNo16 = "";
            string AlterPartNo17 = "";
            string AlterPartNo18 = "";
            string AlterPartNo19 = "";
            string AlterPartNo20 = "";
            string AlterPartNo21 = "";
            string AlterPartNo22 = "";
            string AlterPartNo23 = "";


            while (odr.Read())
            {
                PartNo = valid(odr, 0);
                Description = valid(odr, 1);
                Mfg = valid(odr, 2);
                PreferredSupplier = valid(odr, 3);
                Category = valid(odr, 4);
                PbQty = valid(odr, 5);
                AlterPartNo01 = valid(odr, 6);
                AlterPartNo02 = valid(odr, 7);
                AlterPartNo03 = valid(odr, 8);
                AlterPartNo04 = valid(odr, 9);
                AlterPartNo05 = valid(odr, 10);
                AlterPartNo06 = valid(odr, 11);
                AlterPartNo07 = valid(odr, 12);
                AlterPartNo08 = valid(odr, 13);
                AlterPartNo09 = valid(odr, 14);
                AlterPartNo10 = valid(odr, 15);
                AlterPartNo11 = valid(odr, 16);
                AlterPartNo12 = valid(odr, 17);
                AlterPartNo13 = valid(odr, 18);
                AlterPartNo14 = valid(odr, 19);
                AlterPartNo15 = valid(odr, 20);
                AlterPartNo16 = valid(odr, 21);
                AlterPartNo17 = valid(odr, 22);
                AlterPartNo18 = valid(odr, 23);
                AlterPartNo19 = valid(odr, 24);
                AlterPartNo20 = valid(odr, 25);
                AlterPartNo21 = valid(odr, 26);
                AlterPartNo22 = valid(odr, 27);
                AlterPartNo23 = valid(odr, 28);
                insertdataintosql(PartNo, Description, Mfg, PreferredSupplier, Category, PbQty, AlterPartNo01, AlterPartNo02, AlterPartNo03, AlterPartNo04, AlterPartNo05, AlterPartNo06, AlterPartNo07, AlterPartNo08, AlterPartNo09, AlterPartNo10, AlterPartNo11, AlterPartNo12, AlterPartNo13, AlterPartNo14, AlterPartNo15, AlterPartNo16, AlterPartNo17, AlterPartNo18, AlterPartNo19, AlterPartNo20, AlterPartNo21, AlterPartNo22, AlterPartNo23);
            }
            oconn.Close();
			
			  StatusLabel.Text = "Upload status: File uploaded!";
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
			StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
}
}

Open in new window

Webboy2008

ASKER
appreciate your helps. I will try tonight...
Webboy2008

ASKER
studentFileUpload? What it is ? Your code does not work.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
disrupt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question