C# Error: Microsoft Excel cannot access the file '...'. There are several possible reasons

Question

I've developed an ASP.Net MVC application, that is running on a IIS sever. I've wrote a code that reads a CSV and insert the rows of it in a database.

[HttpPost]
    public ActionResult InsertPosition(int id, HttpPostedFileBase position)
    {
        var posicoesExistentes = db.tbPositions.Where(s => s.id_unique == id).AsEnumerable();

        foreach (tbPosition posicao in posicoesExistentes)
        {
            db.tbPositions.Remove(posicao);
        }

        if (!Directory.Exists(Server.MapPath("~/App_Data/")))
        {
            System.IO.Directory.CreateDirectory(Server.MapPath("~/App_Data/"));
        }

        string excelPath = Server.MapPath("~/App_Data/" + position.FileName);
        if (System.IO.File.Exists(excelPath))
        {
            System.IO.File.Delete(excelPath);
        }

        position.SaveAs(excelPath);

        string tempPath = Server.MapPath("~/App_Data/" + "tmp_" + position.FileName);
        System.IO.File.Copy(excelPath, tempPath, true);

        Excel.Application application = new Excel.Application();
        Excel.Workbook workbook = application.Workbooks.Open(tempPath, ReadOnly: true,Editable:false);
        Excel.Worksheet worksheet = workbook.ActiveSheet;
        Excel.Range range = worksheet.UsedRange;
        application.Visible = true;

        for (int row = 1; row < range.Rows.Count - 1; row++)
        {
            tbPosition p = new tbPosition();

            p.position = (((Excel.Range)range.Cells[row, 1]).Text == "") ? null : Convert.ToInt32(((Excel.Range)range.Cells[row, 1]).Text);
            p.left = ((Excel.Range)range.Cells[row, 2]).Text;
            p.right = ((Excel.Range)range.Cells[row, 3]).Text;
            p.paper = ((Excel.Range)range.Cells[row, 4]).Text;
            p.denomination = ((Excel.Range)range.Cells[row, 5]).Text;
            p.material = ((Excel.Range)range.Cells[row, 6]).Text;
            p.norme = ((Excel.Range)range.Cells[row, 7]).Text;
            p.finalized_measures = ((Excel.Range)range.Cells[row, 8]).Text;
            p.observation = ((Excel.Range)range.Cells[row, 9]).Text;
            p.id_unique = id;

            db.tbPositions.Add(p);
            db.SaveChanges();
        }
        workbook.Close(true, Type.Missing, Type.Missing);
        application.Quit();
        System.IO.File.Delete(tempPath);

        return Json("Success", JsonRequestBehavior.AllowGet);
    }

but in return I got the error ' Microsoft Excel cannot access the file '...'. There are several possible reasons' when I try to open the requested excel file.

I've already tried to open the file as readonly, I've already tried to give permissions to the specifieds folders, multiples ways of close the excel file, and create an copy file of the original and read him. But unsuccessful in each one of these solutions. What have I missed here?

Answer 1

Unsupported

The short answer is that trying to programatically manipulate an Excel document using the Automation API is not supported outside of a UI context. You will come across all sorts of frustrations (for example, the API is permitted to show dialogs - how are you going to click on "OK" if it's running on a web-server?).

Microsoft explicitly state this here

Microsoft does not recommend or support server-side Automation of Office.

So what do I use?

I would recommend using the OpenXML SDK - this is free, fully supported and much faster than the Automation API.

Aspose also has a set of products, but they are not free, and I've not used them.

But I HAVE to do it this way

However, if you absolutely have to use the COM API then the following might help you:

HERE BE DRAGONS

The big problem with automation in Excel is that you need to ensure you close every single reference whenever you use them (by calling ReleaseComObject on it).

For example, the following code will cause Excel to stay open:

var range;
range = excelApplication.Range("A1");
range = excelApplication.Range("A2");
System.Runtime.InteropServices.Marshal.ReleaseComObject(range)
range = Nothing

This is because there is still a reference left over from the call to get range "A1".

Therefore, I would recommend writing a wrapper around the Excel class so that any access to, e.g., a range frees any previous ranges accessed before accessing the new range.

For reference, here is the code I used to release COM objects in the class I wrote:

Private Sub ReleaseComObject(ByVal o As Object)
    Try
        If Not IsNothing(o) Then
            While System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0
                'Wait for COM object to be released.'
            End While
        End If
        o = Nothing
    Catch exc As System.Runtime.InteropServices.COMException
        LogError(exc) ' Suppress errors thrown here '
    End Try
End Sub

Answer 2

Try this

  protected void ImportCSV(object sender, EventArgs e)
        {
            importbtn();

        }
        public class Item
        {
            public Item(string line)
            {
                var split = line.Split(',');
                string FIELD1 = split[0];
                string FIELD2 = split[1];
                string FIELD3 = split[2];


                string mainconn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

                using (SqlConnection con = new SqlConnection(mainconn))
                {
                    using (SqlCommand cmd = new SqlCommand("storedProcedureName", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;


                        cmd.Parameters.AddWithValue("@FIELD1", SqlDbType.VarChar).Value = FIELD1;
                        cmd.Parameters.AddWithValue("@FIELD2", SqlDbType.VarChar).Value = FIELD2;
                        cmd.Parameters.AddWithValue("@FIELD3", SqlDbType.VarChar).Value = FIELD3;

                        con.Open();
                        cmd.ExecuteNonQuery();
                    }


                }

            }
        }
        private void importbtn()
        {
            try
            {

                string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
                FileUpload1.SaveAs(csvPath);


                var listOfObjects = File.ReadLines(csvPath).Select(line => new Item(line)).ToList();


                DataTable dt = new DataTable();

                dt.Columns.AddRange(new DataColumn[3] { new DataColumn("FIELD1", typeof(string)),
                new DataColumn("FIELD2", typeof(string)),
                new DataColumn("FIELD3",typeof(string)) });


                string csvData = File.ReadAllText(csvPath);


                foreach (string row in csvData.Split('\n'))
                {

                    if (!string.IsNullOrEmpty(row))
                    {


                        dt.Rows.Add();

                        int i = 0;

                        //Execute a loop over the columns.
                        foreach (string cell in row.Split(','))
                        {


                            dt.Rows[dt.Rows.Count - 1][i] = cell;

                            i++;

                        }

                    }
                }

                GridView1.DataSource = dt;
                GridView1.DataBind();


                Label1.Text = "File Attached Successfully";



            }
            catch (Exception ex)
            {
                Message.Text = "Please Attach any File" /*+ ex.Message*/;

            }
        }