mardi 12 avril 2016

OleDb Read Excel not working after to publish to IIS. I can't catch the cause of the error on ASP.Net MVC 3

I am using Microsoft.ACE.OLEDB.12.0; i tried to read 2007 excel file and it won't work. It only works on excel 2003. Now, I'm having a problem that after i publish to IIS on the same unit that i use for the development now it won't work. I am using ajaxupload.js to get the excel file.

These are my source code:

public void ReadfileBilling()
        {
            HttpPostedFileBase file = Request.Files[0];
            string nm = file.FileName;
            MemoryStream ws = new MemoryStream();
            var imageBytes = ws.ToArray();
            if (file != null && file.ContentLength > 0)
            {


                var uploadedFile = Request.Files[0];
                var fileName = Path.GetFileName(file.FileName);
                var fileSavePath = "";
                //var path = Server.MapPath("../App_Data/");
                var path = Server.MapPath("~/App_Data/");
                fileSavePath = Server.MapPath("~/App_Data/" + fileName);
                uploadedFile.SaveAs(fileSavePath);

                var Indexof = fileName.IndexOf(".");
                var FileExtension = fileName.Substring(Indexof + 1);
                var len = file.ContentLength;
                var type = file.ContentType;

                String File1 = file.FileName;
                FileStream t1 = new System.IO.FileStream(fileSavePath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite);
                StreamReader readFile = new System.IO.StreamReader(t1);

                Importexcel(path, fileName);
            }

        }

public ActionResult Importexcel(string path, string filename)
        {
            string path1 = string.Format("{0}/{1}", path, filename);
            string sqlConnectionString = @"Data Source=GWXPROG\SQL_2k8;Initial Catalog=GeoPIMS;Integrated Security=false; user id=sa; password=P@ssw0rd;";


            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=Excel 12.0;Persist Security Info=False";
            //Create Connection to Excel work book
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
            //Create OleDbCommand to fetch data from Excel
            OleDbCommand cmd = new OleDbCommand("Select [PolicyNo], [BPNO],[PS], [GS], [EC], [CONSOLOAN], [ECARDPLUS],  [SALARY_LOAN],[CASH_ADV],   [EMRGYLN], [EDUC_ASST], [ELA], [SOS], [PLREG], [PLOPT], [REL], [LCH_DCS], [STOCK_PURCHASE], [OPT_LIFE], [CEAP], [EDU_CHILD], [GENESIS], [GENPLUS], [GENFLEXI],[GENSPCL] from [Sheet1$]", excelConnection);

            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();

            SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlConnectionString);
            //Give your Destination table name
            sqlBulk.DestinationTableName = "PAYROLL.GSISBilling";
            sqlBulk.WriteToServer(dReader);
            excelConnection.Close();


            return RedirectToAction("Import");
  }

Aucun commentaire:

Enregistrer un commentaire