0

**I have Excel Sheet Name xyz.xls I am able to validate that FileUploaded can only be of .xls extension. Now,I want to check that ColumnsName of Xls Should be must match to Database Columns...OtherWise Display error.. Excel Sheet columns must be.. [st_id],[st_rollno],[st_name],[branch_name],[sem_no],[batch_year] **

 protected void ShowGridView()
    {

        if ((txtFilePath.HasFile))
        {

            OleDbConnection conn = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            string query = null;
            string connString = "";
            string strFileName = txtFilePath.FileName;
            //string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
            string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower();

            //Check file type
            if (strFileType == ".xls" || strFileType == ".xlsx")
            {
                //print progressbar
            txtFilePath.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));



            }
            else
            {
                lblMessage.Text = "Only Excel files(.xls or .xlsx) allowed";
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Visible = true;
                return;
            }

            string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);

            //Connection String to Excel Workbook
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }


            query = "SELECT [st_id],[st_rollno],[st_name],[branch_name],[sem_no],[batch_year] FROM [student$]";
            //query = "SELECT [Country],[Capital] FROM [Sheet1$] WHERE [Currency]='Rupee'"
            //query = "SELECT [Country],[Capital] FROM [Sheet1$]"

            //Create the connection object
            conn = new OleDbConnection(connString);
            //Open connection
            if (conn.State == ConnectionState.Closed) conn.Open();
            //Create the command object
            cmd = new OleDbCommand(query, conn);
            da = new OleDbDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds);

            grvExcelData.DataSource = ds.Tables[0];
            grvExcelData.DataBind();

            lblMessage.Text = "Data retrieved successfully! Total Recodes:" + ds.Tables[0].Rows.Count;
            lblMessage.ForeColor = System.Drawing.Color.Green;
            lblMessage.Visible = true;

            da.Dispose();
            conn.Close();
            conn.Dispose();
        }
        else
        {
            lblMessage.Text = "Please select an excel file first";
            lblMessage.ForeColor = System.Drawing.Color.Red;
            lblMessage.Visible = true;
        }

    }
user2053138
  • 141
  • 2
  • 6
  • 16
  • What do you want us to help with? Is there an error, is the behavior not expected, do you want a code review, what? – Jon Adams Feb 27 '13 at 16:58
  • I think you should do some research before asking such a broad question here is a helpful link: http://stackoverflow.com/questions/7662882/get-column-name-from-excel-worksheet. – TheKingDave Feb 27 '13 at 17:02
  • Jon Adams : I want a code/logic, How to check Excel Sheet's Columns must be of same name as table of database ,before insertion in the databse...... – user2053138 Feb 27 '13 at 17:02

0 Answers0