Monday, April 2, 2012

Excel Upload in asp.net

This is in web.config
<connectionStrings>
        <add name="excelo3cnstring" connectionString="Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}';" providerName="OleDb;"/>
        <add name="excelo7cnstring" connectionString="Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}';" providerName="OleDb;"/>
    </connectionStrings>

Microsoft.Jet.OLEDB.4.0 is for MSOffice 2003 and 
Microsoft.ACE.OLEDB.12.0 is for MSOffice 2007

.aspx.cs
protected void btnUpload_Click(object sender, EventArgs e)
        {

            if (FileUpload1.HasFile)
            {

                string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
                string FilePath = Server.MapPath(FolderPath + FileName);
                FileUpload1.SaveAs(FilePath);

                Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);

            }

        }

        private void Import_To_Grid(string FilePath, string Extension, string isHDR)
        {

            string conStr = "";

            switch (Extension)
            {

                case ".xls": //Excel 97-03

                    conStr = ConfigurationManager.ConnectionStrings["excelo3cnstring"]

                             .ConnectionString;

                    break;

                case ".xlsx": //Excel 07

                    conStr = ConfigurationManager.ConnectionStrings["excelo7cnstring"]

                              .ConnectionString;

                    break;

            }

            conStr = String.Format(conStr, FilePath, isHDR);

            OleDbConnection con
Excel= new OleDbConnection(conStr);

            OleDbCommand cmdExcel = new OleDbCommand();

            OleDbDataAdapter odadp = new OleDbDataAdapter();

            DataTable dt = new DataTable();

            cmdExcel.Connection = connExcel;

            //Get the name of First Sheet

           
conExcel.Open();

            DataTable dtExcelSchema;

            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
           
conExcel.Close();

            //Read Data from First Sheet

           
conExcel.Open();
            cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
           
odadp.SelectCommand = cmdExcel;
           
odadp.Fill(dt);
           
conExcel.Close();
            //Bind Data to GridView

            GridView1.Caption = Path.GetFileName(FilePath);
            GridView1.DataSource = dt;     
            GridView1.DataBind();

        }

1 comment:

Comments

Protected by Copyscape Plagiarism Software