Click here to Skip to main content
15,884,425 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I am uploading a excel file when the uploaded excel file is not proper format ist giveing error but after that also when i am inserting a proper excel file its given same error also

when uploading 1st time proper excel format working fine then wrong excel format error againg going for proper format also its giving error



C#
try
{
    string fileExtension =
                         System.IO.Path.GetExtension(Request.Files["file"].FileName);

    if (fileExtension == ".xls" || fileExtension == ".xlsx")
    {
        string fileLocation = Server.MapPath("~/ExcelUpload/") + Request.Files["file"].FileName;
        if (System.IO.File.Exists(fileLocation))
        {

            System.IO.File.Delete(fileLocation);
        }
        Request.Files["file"].SaveAs(fileLocation);
        string excelConnectionString = string.Empty;
        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
        fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        //connection String for xls file format.
        if (fileExtension == ".xls")
        {
            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        }
        //connection String for xlsx file format.
        else if (fileExtension == ".xlsx")
        {
            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        }
        //Create Connection to Excel work book and add oledb namespace
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        excelConnection.Open();
        DataTable dt = new DataTable();

        dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (dt == null)
        {
            return null;
        }

        String[] excelSheets = new String[dt.Rows.Count];
        int t = 0;
        //excel data saves in temp file here.
        foreach (DataRow row in dt.Rows)
        {
            excelSheets[t] = row["TABLE_NAME"].ToString();
            t++;
        }
        OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);


        string query = string.Format("Select * from [{0}]", excelSheets[0]);
        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
        {
            dataAdapter.Fill(ds);
        }
    }
    //if (fileExtension.ToString().ToLower().Equals(".xml"))
    //{
    //    string fileLocation = Server.MapPath("~/ExcelUpload/") + Request.Files["FileUpload"].FileName;
    //    if (System.IO.File.Exists(fileLocation))
    //    {
    //        System.IO.File.Delete(fileLocation);
    //    }

    //    Request.Files["FileUpload"].SaveAs(fileLocation);
    //    XmlTextReader xmlreader = new XmlTextReader(fileLocation);

    //    ds.ReadXml(xmlreader);
    //    xmlreader.Close();
    //}
    //  Allquestions alqnn = new Allquestions();



    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
        //string conn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
        SqlConnection con = new SqlConnection(conn);
        string query = "Insert into ALQuestion.QuestionMaster(Question,Option1,Option2,Option3,Option4,Answer,LevelID,SkillID,CreatedOn,Status) Values('" +
        ds.Tables[0].Rows[i][00].ToString() + "','" + ds.Tables[0].Rows[i][01].ToString() +
        "','" + ds.Tables[0].Rows[i][02].ToString() + "','" + ds.Tables[0].Rows[i][03].ToString() + "','" + ds.Tables[0].Rows[i][04].ToString() + "','" + ds.Tables[0].Rows[i][05].ToString() + "','" + LevelId.ToString() + "','" + skillid.ToString() + "','" + now.ToString(("yyyy/MM/dd")) + "','" + "1" + "')";

        con.Open();
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.ExecuteNonQuery();
        con.Close();

    }


    ViewBag.UploadExcelStatus = "Excel Upload Successfully";
    return Json("Excel Upload Successfully");
}
catch
{

    ViewBag.UploadExcelStatus = "Please Upload a Proper Excel File";

    return Json(Please Upload a Proper Excel File");
}


its coming directly to catch
Posted
Comments
[no name] 25-Jan-16 7:21am    
What is the error you are getting? Always try to upload with correct one and also do validation. If excel sheet is not satisfy your validation, then show it is excel file with incorrect data.
Member 11970398 25-Jan-16 7:28am    
suppose i upload timesheet.xls its uploading
then if i go for hello.xls its giving error againg if i am going for timesheet.xls again its giveing error showing file is being in process
koolprasad2003 25-Jan-16 7:54am    
which line give you error ?
Member 11970398 25-Jan-16 8:26am    
ITS COMING TO CATCH DIRECTLY WITH OUT GOING TO TRY BLOCK
koolprasad2003 26-Jan-16 22:56pm    
Try to put some log in your file, or try to write some log file and check which line exactly gives you error

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